diff options
Diffstat (limited to 'mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient.jvm/GHTDB.ORACLE.sql')
| -rwxr-xr-x | mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient.jvm/GHTDB.ORACLE.sql | 979 |
1 files changed, 0 insertions, 979 deletions
diff --git a/mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient.jvm/GHTDB.ORACLE.sql b/mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient.jvm/GHTDB.ORACLE.sql deleted file mode 100755 index a5feb72a00b..00000000000 --- a/mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient.jvm/GHTDB.ORACLE.sql +++ /dev/null @@ -1,979 +0,0 @@ -DROP INDEX GHTDB.categoryname; -DROP INDEX GHTDB.city; -DROP INDEX GHTDB.companyname_1; -DROP INDEX GHTDB.postalcode_2; -DROP INDEX GHTDB.region; -DROP INDEX GHTDB.lastname; -DROP INDEX GHTDB.postalcode_1; -DROP INDEX GHTDB.categoryid; -DROP INDEX GHTDB.productname; -DROP INDEX GHTDB.suppliersproducts; -DROP INDEX GHTDB.companyname; -DROP INDEX GHTDB.postalcode; - -DROP TABLE GHTDB.TYPES_SIMPLE; -DROP TABLE GHTDB.TYPES_EXTENDED; -DROP TABLE GHTDB.TYPES_SPECIFIC; -DROP TABLE GHTDB.categories; -DROP TABLE GHTDB.customercustomerdemo; -DROP TABLE GHTDB.customerdemographics; -DROP TABLE GHTDB.customers; -DROP TABLE GHTDB.employees; -DROP TABLE GHTDB.employeeterritories; -DROP TABLE GHTDB.gh_emptytable; -DROP TABLE GHTDB."Order Details"; -DROP TABLE GHTDB.orders; -DROP TABLE GHTDB.products; -DROP TABLE GHTDB.region; -DROP TABLE GHTDB.Results; -DROP TABLE GHTDB.shippers; -DROP TABLE GHTDB.shoppingcart; -DROP TABLE GHTDB.suppliers; -DROP TABLE GHTDB.territories; - -DROP SEQUENCE GHTDB.s_463_1_reviews; -DROP SEQUENCE GHTDB.s_464_1_shoppingcart; -DROP SEQUENCE GHTDB.s_59_1_products; -DROP SEQUENCE GHTDB.s_60_1_orders; -DROP SEQUENCE GHTDB.s_62_1_suppliers; -DROP SEQUENCE GHTDB.s_63_1_shippers; -DROP SEQUENCE GHTDB.s_65_1_employees; -DROP SEQUENCE GHTDB.s_68_1_categories; - -DROP USER GHTDB CASCADE; - -CREATE USER "GHTDB" - IDENTIFIED BY "GHTDB" DEFAULT TABLESPACE "USERS" - TEMPORARY TABLESPACE "TEMP" ; - -GRANT CONNECT, RESOURCE, CREATE TABLE TO "GHTDB"; - -CREATE OR REPLACE PACKAGE "GHTDB"."GHTPKG" AS - TYPE RCT1 IS REF CURSOR; - IDENTITY INTEGER; - - procedure ghsp_pkgAmbig(res out rct1); - procedure ghsp_inPkg(CustomerIdPrm IN CHAR, result OUT RCT1); -END; -/ - -CREATE TABLE GHTDB.TYPES_SIMPLE ( - ID CHAR(10) NULL, - T_NUMBER NUMBER(10) NULL, - T_LONG LONG NULL, - T_FLOAT FLOAT(10) NULL, - T_VARCHAR VARCHAR2(10) NULL, - T_NVARCHAR NVARCHAR2(10) NULL, - T_CHAR CHAR(10) NULL, - T_NCHAR NCHAR(10) NULL -) - TABLESPACE USERS -/ - - - -CREATE TABLE GHTDB.TYPES_EXTENDED ( - ID CHAR(10) NULL, - T_RAW RAW(10) NULL, - T_LONGRAW LONG RAW NULL, - T_DATE DATE NULL, - T_BLOB BLOB NULL, - T_CLOB CLOB NULL, - T_NCLOB NCLOB NULL -) - TABLESPACE USERS -/ - -CREATE TABLE GHTDB.TYPES_SPECIFIC ( - ID CHAR(10) NULL, - T_ROWID ROWID NULL, - T_UROWID UROWID NULL, - T_BFILE BFILE NULL, - T_XMLTYPE SYS.XMLTYPE NULL -) - TABLESPACE USERS -/ - -CREATE TABLE GHTDB.categories ( - categoryid NUMBER(10,0) NOT NULL, - categoryname VARCHAR2(30) NOT NULL, - description CLOB NULL -) - TABLESPACE USERS -/ - - -CREATE INDEX GHTDB.categoryname - ON GHTDB.categories ( - categoryname - ) - TABLESPACE USERS -/ - -CREATE TABLE GHTDB.customercustomerdemo ( - customerid CHAR(10) NOT NULL, - customertypeid CHAR(20) NOT NULL -) - TABLESPACE USERS -/ - - -ALTER TABLE GHTDB.customercustomerdemo - ADD CONSTRAINT pk_customercustomerdemo UNIQUE ( - customerid, - customertypeid - ) -/ - -CREATE TABLE GHTDB.customerdemographics ( - customertypeid CHAR(20) NOT NULL, - customerdesc CLOB NULL -) - TABLESPACE USERS -/ - - -ALTER TABLE GHTDB.customerdemographics - ADD CONSTRAINT pk_customerdemographics UNIQUE ( - customertypeid - ) -/ - -CREATE TABLE GHTDB.customers ( - customerid CHAR(10) NOT NULL, - companyname VARCHAR2(80) NOT NULL, - contactname VARCHAR2(60) NULL, - contacttitle VARCHAR2(60) NULL, - address VARCHAR2(120) NULL, - city VARCHAR2(30) NULL, - region VARCHAR2(30) NULL, - postalcode VARCHAR2(20) NULL, - country VARCHAR2(30) NULL, - phone VARCHAR2(48) NULL, - fax VARCHAR2(48) NULL -) - TABLESPACE USERS -/ - - -CREATE INDEX GHTDB.city - ON GHTDB.customers ( - city - ) -/ - -CREATE INDEX GHTDB.companyname_1 - ON GHTDB.customers ( - companyname - ) -/ - -CREATE INDEX GHTDB.postalcode_2 - ON GHTDB.customers ( - postalcode - ) -/ - -CREATE INDEX GHTDB.region - ON GHTDB.customers ( - region - ) -/ - -ALTER TABLE GHTDB.customers - ADD CONSTRAINT uk_customers_customerid UNIQUE ( - customerid - ) -/ - -CREATE TABLE GHTDB.employees ( - employeeid NUMBER(10,0) NOT NULL, - lastname VARCHAR2(40) NOT NULL, - firstname VARCHAR2(20) NOT NULL, - title VARCHAR2(60) NULL, - titleofcourtesy VARCHAR2(50) NULL, - birthdate DATE NULL, - hiredate DATE NULL, - address VARCHAR2(120) NULL, - city VARCHAR2(30) NULL, - region VARCHAR2(30) NULL, - postalcode VARCHAR2(20) NULL, - country VARCHAR2(30) NULL, - homephone VARCHAR2(48) NULL, - extension VARCHAR2(8) NULL, - notes CLOB NULL, - reportsto NUMBER(10,0) NULL, - photopath VARCHAR2(510) NULL, - mycolumn NUMBER(10,0) NULL -) - TABLESPACE USERS -/ - - -CREATE INDEX GHTDB.lastname - ON GHTDB.employees ( - lastname - ) - TABLESPACE USERS -/ - -CREATE INDEX GHTDB.postalcode_1 - ON GHTDB.employees ( - postalcode - ) - TABLESPACE USERS -/ - -ALTER TABLE GHTDB.employees - ADD CONSTRAINT pk_employees UNIQUE ( - employeeid - ) -/ - -CREATE TABLE GHTDB.employeeterritories ( - employeeid NUMBER(10,0) NOT NULL, - territoryid VARCHAR2(40) NOT NULL -) - TABLESPACE USERS -/ - - - -CREATE TABLE GHTDB.gh_emptytable ( - col1 INTEGER NULL, - col2 VARCHAR2(50) NULL -) - TABLESPACE USERS -/ - - -CREATE TABLE GHTDB."Order Details" ( - orderid NUMBER(10,0) NOT NULL, - productid NUMBER(10,0) NOT NULL, - unitprice NUMBER(19,4) NOT NULL, - quantity NUMBER(5,0) NOT NULL, - discount FLOAT(126) NOT NULL -) - TABLESPACE USERS -/ - - -CREATE TABLE GHTDB.orders ( - orderid NUMBER(10,0) NOT NULL, - customerid NCHAR(5) NULL, - employeeid NUMBER(10,0) NULL, - orderdate DATE NULL, - requireddate DATE NULL, - shippeddate DATE NULL, - shipvia NUMBER(10,0) NULL, - freight NUMBER(19,4) NULL, - shipname NVARCHAR2(40) NULL, - shipaddress NVARCHAR2(60) NULL, - shipcity NVARCHAR2(15) NULL, - shipregion NVARCHAR2(15) NULL, - shippostalcode NVARCHAR2(10) NULL, - shipcountry NVARCHAR2(15) NULL -) - TABLESPACE USERS -/ - -ALTER TABLE GHTDB.orders - ADD CONSTRAINT orders_uk11075049410018 UNIQUE ( - orderid - ) -/ - -ALTER TABLE GHTDB.orders - ADD CONSTRAINT orders_fk21075049699981 FOREIGN KEY ( - orderid - ) REFERENCES GHTDB.orders ( - orderid - ) -/ - -CREATE TABLE GHTDB.products ( - productid NUMBER(10,0) NOT NULL, - productname VARCHAR2(80) NOT NULL, - supplierid NUMBER(10,0) NULL, - categoryid NUMBER(10,0) NULL, - quantityperunit VARCHAR2(40) NULL, - unitprice NUMBER(19,4) DEFAULT (0) NULL, - unitsinstock NUMBER(5,0) DEFAULT (0) NULL, - unitsonorder NUMBER(5,0) DEFAULT (0) NULL, - reorderlevel NUMBER(5,0) DEFAULT (0) NULL, - discontinued NUMBER(1,0) DEFAULT (0) NOT NULL -) - TABLESPACE USERS -/ - - -ALTER TABLE GHTDB.products - ADD CONSTRAINT ck_products_unitprice CHECK ( - UnitPrice >= 0 - ) -/ - -ALTER TABLE GHTDB.products - ADD CONSTRAINT ck_reorderlevel CHECK ( - ReorderLevel >= 0 - ) -/ - -ALTER TABLE GHTDB.products - ADD CONSTRAINT ck_unitsinstock CHECK ( - UnitsInStock >= 0 - ) -/ - -ALTER TABLE GHTDB.products - ADD CONSTRAINT ck_unitsonorder CHECK ( - UnitsOnOrder >= 0 - ) -/ - -CREATE INDEX GHTDB.categoryid - ON GHTDB.products ( - categoryid - ) -/ - -CREATE INDEX GHTDB.productname - ON GHTDB.products ( - productname - ) -/ - -CREATE INDEX GHTDB.suppliersproducts - ON GHTDB.products ( - supplierid - ) -/ - -CREATE TABLE GHTDB.region ( - regionid NUMBER(10,0) NOT NULL, - regiondescription CHAR(100) NOT NULL -) - TABLESPACE USERS -/ - - -CREATE TABLE GHTDB.Results ( - employeeid NUMBER(10,0) NOT NULL, - lastname VARCHAR2(20) NOT NULL, - firstname VARCHAR2(10) NOT NULL, - title VARCHAR2(30) NULL, - titleofcourtesy VARCHAR2(25) NULL, - birthdate DATE NULL, - hiredate DATE NULL, - address VARCHAR2(60) NULL, - city VARCHAR2(15) NULL, - region VARCHAR2(15) NULL, - postalcode VARCHAR2(10) NULL, - country VARCHAR2(15) NULL, - homephone VARCHAR2(24) NULL, - extension VARCHAR2(4) NULL, - notes CLOB NULL, - reportsto NUMBER(10,0) NULL, - photopath VARCHAR2(255) NULL, - mycolumn NUMBER(10,0) NULL -) - TABLESPACE USERS -/ - - -CREATE TABLE GHTDB.shippers ( - shipperid NUMBER(10,0) NOT NULL, - companyname VARCHAR2(80) NOT NULL, - phone VARCHAR2(48) NULL -) - TABLESPACE USERS -/ - - -CREATE TABLE GHTDB.shoppingcart ( - recordid NUMBER(10,0) NOT NULL, - cartid VARCHAR2(100) NULL, - quantity NUMBER(10,0) DEFAULT (1) NOT NULL, - productid NUMBER(10,0) NOT NULL, - datecreated DATE DEFAULT (SYSDATE) NOT NULL -) - TABLESPACE USERS -/ - - -CREATE TABLE GHTDB.suppliers ( - supplierid NUMBER(10,0) NOT NULL, - companyname VARCHAR2(80) NOT NULL, - contactname VARCHAR2(60) NULL, - contacttitle VARCHAR2(60) NULL, - address VARCHAR2(120) NULL, - city VARCHAR2(30) NULL, - region VARCHAR2(30) NULL, - postalcode VARCHAR2(20) NULL, - country VARCHAR2(30) NULL, - phone VARCHAR2(48) NULL, - fax VARCHAR2(48) NULL, - homepage CLOB NULL -) - TABLESPACE USERS -/ - - -CREATE INDEX GHTDB.companyname - ON GHTDB.suppliers ( - companyname - ) - TABLESPACE USERS -/ - -CREATE INDEX GHTDB.postalcode - ON GHTDB.suppliers ( - postalcode - ) - TABLESPACE USERS -/ - -CREATE TABLE GHTDB.territories ( - territoryid VARCHAR2(40) NOT NULL, - territorydescription CHAR(100) NOT NULL, - regionid NUMBER(10,0) NOT NULL -) - TABLESPACE USERS -/ - - -CREATE SEQUENCE GHTDB.s_463_1_reviews - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE SEQUENCE GHTDB.s_464_1_shoppingcart - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE SEQUENCE GHTDB.s_59_1_products - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE SEQUENCE GHTDB.s_60_1_orders - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE SEQUENCE GHTDB.s_62_1_suppliers - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE SEQUENCE GHTDB.s_63_1_shippers - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE SEQUENCE GHTDB.s_65_1_employees - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE SEQUENCE GHTDB.s_68_1_categories - MINVALUE 1 - MAXVALUE 999999999999999999999999999 - INCREMENT BY 1 - NOCYCLE - NOORDER - CACHE 20 -/ - -CREATE OR REPLACE TRIGGER GHTDB.tr_s_464_1_shoppingcart -BEFORE INSERT -ON GHTDB.shoppingcart -FOR EACH ROW -BEGIN - IF (:new.RECORDID IS NULL) THEN - SELECT S_464_1_SHOPPINGCART.nextval - INTO :NEW.RECORDID - FROM dual; - END IF; -END; -/ - -CREATE OR REPLACE TRIGGER GHTDB.tr_s_59_1_products -BEFORE INSERT -ON GHTDB.products -FOR EACH ROW -BEGIN - IF (:new.PRODUCTID IS NULL) THEN - SELECT S_59_1_PRODUCTS.nextval - INTO :NEW.PRODUCTID - FROM dual; - END IF; -END; -/ - -CREATE OR REPLACE TRIGGER GHTDB.tr_s_62_1_suppliers -BEFORE INSERT -ON GHTDB.suppliers -FOR EACH ROW -BEGIN - IF (:new.SUPPLIERID IS NULL) THEN - SELECT S_62_1_SUPPLIERS.nextval - INTO :NEW.SUPPLIERID - FROM dual; - END IF; -END; -/ - -CREATE OR REPLACE TRIGGER GHTDB.tr_s_63_1_shippers -BEFORE INSERT -ON GHTDB.shippers -FOR EACH ROW -BEGIN - IF (:new.SHIPPERID IS NULL) THEN - SELECT S_63_1_SHIPPERS.nextval - INTO :NEW.SHIPPERID - FROM dual; - END IF; -END; -/ - -CREATE OR REPLACE TRIGGER GHTDB.tr_s_68_1_categories -BEFORE INSERT -ON GHTDB.categories -FOR EACH ROW -BEGIN - IF (:new.CATEGORYID IS NULL) THEN - SELECT S_68_1_CATEGORIES.nextval - INTO :NEW.CATEGORYID - FROM dual; - END IF; -END; -/ - -CREATE OR REPLACE VIEW GHTDB.current_product_list ( - productid, - productname -) AS -SELECT Product_List.ProductID, Product_List.ProductName - FROM Products Product_List - WHERE - ( - (( Product_List.Discontinued ) = 0)) -/ - -CREATE OR REPLACE VIEW GHTDB.customer_and_suppliers_by_cit ( - city, - companyname, - contactname, - relationship -) AS -SELECT City, CompanyName, ContactName, 'Customers' Relationship - FROM Customers UNION SELECT City, CompanyName, ContactName, 'Suppliers' - FROM Suppliers -/ - -CREATE OR REPLACE VIEW GHTDB.products_above_average_price ( - productname, - unitprice -) AS -SELECT Products.ProductName, Products.UnitPrice - FROM Products - WHERE Products.UnitPrice > ( - SELECT AVG(UnitPrice) - FROM Products ) -/ - -CREATE OR REPLACE PACKAGE BODY "GHTDB"."GHTPKG" AS - ---Procedure declerations: - procedure ghsp_pkgAmbig(res out rct1) is - begin - --Return a value which indocates that the procedure 'ghsp_pkgAmbig' was called not from within GHTPKG. - declare IN_GHTPKG varchar2(4) := 'TRUE'; - begin - open res for - select IN_GHTPKG as IN_PKG from dual; - end; - end; - - procedure ghsp_inPkg(CustomerIdPrm in char, result out rct1) is - begin - OPEN result FOR - SELECT * FROM Customers where CustomerId=CustomerIdPrm; - end; - -END; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GH_MULTIRECORDSETS" ( - RCT_Employees OUT GHTPKG.RCT1, - RCT_Customers OUT GHTPKG.RCT1, - RCT_Orders OUT GHTPKG.RCT1 - ) - IS - -- Declare cursor - m_RCT_Employees GHTPKG.RCT1; - m_RCT_Customers GHTPKG.RCT1; - m_RCT_Orders GHTPKG.RCT1; - BEGIN - OPEN m_RCT_Employees FOR - SELECT EmployeeId, LastName FROM Employees where EmployeeId in (1,2) ORDER BY EMPLOYEEID ASC; - - OPEN m_RCT_Customers FOR - SELECT CustomerId, CompanyName,ContactName FROM Customers where CustomerId in ('MORGK','NORTS') ORDER BY CustomerId ASC; - -- return empty result set - OPEN m_RCT_Orders FOR - SELECT OrderId, ShipAddress,ShipVia, ShipCity FROM Orders where OrderId=-1 ; - - RCT_Employees := m_RCT_Employees; - RCT_Customers := m_RCT_Customers; - RCT_Orders := m_RCT_Orders; -END; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GH_INOUT1" ( -INPARAM IN VARCHAR DEFAULT NULL, -OUTPARAM OUT INTEGER -) -AS -L_INPARAM VARCHAR(30) := INPARAM; -BEGIN -OUTPARAM := 100; -END; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GH_CREATETABLE" AS -Begin - --craete a temporary table - execute immediate 'Create global temporary Table ghtdb.temp_tbl (Col1 int,Col2 int)'; - --insert values to the table - execute immediate 'insert into ghtdb.temp_tbl values (11,12)'; - execute immediate 'insert into ghtdb.temp_tbl values (21,22)'; - execute immediate 'insert into ghtdb.temp_tbl values (31,32)'; - --execute select on the created table - execute immediate 'select col1 as Value1, col2 as Value2 from ghtdb.temp_tbl'; - execute immediate 'drop table temp_tbl'; -end; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GH_REFCURSOR1" ( - RCT_Employees OUT GHTPKG.RCT1 - ) - IS - m_RCT_Employees GHTPKG.RCT1; - BEGIN - OPEN m_RCT_Employees FOR - SELECT EmployeeId, LastName FROM GHTDB.Employees where EmployeeId = 1; - - RCT_Employees := m_RCT_Employees; -END; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GH_REFCURSOR2" ( - IN_EMPLOYEEID INTEGER, - RCT_Employees OUT GHTPKG.RCT1 - ) - IS - m_RCT_Employees GHTPKG.RCT1; - BEGIN - OPEN m_RCT_Employees FOR - SELECT EmployeeId, LastName FROM GHTDB.Employees where EmployeeId = IN_EMPLOYEEID; - - RCT_Employees := m_RCT_Employees; -END; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GH_REFCURSOR3" ( - IN_LASTNAME VARCHAR2, - RCT_Employees OUT GHTPKG.RCT1 - ) - IS - m_RCT_Employees GHTPKG.RCT1; - BEGIN - OPEN m_RCT_Employees FOR - SELECT EmployeeId, LastName FROM GHTDB.Employees where LastName = IN_LASTNAME; - - RCT_Employees := m_RCT_Employees; -END; -/ - - -CREATE OR REPLACE PROCEDURE GHTDB.GHSP_TYPES_SIMPLE_1 ( -T_NUMBER IN NUMBER, -T_LONG IN LONG, -T_FLOAT IN FLOAT, -T_VARCHAR IN VARCHAR2, -T_NVARCHAR IN NVARCHAR2, -T_CHAR IN CHAR, -T_NCHAR IN NCHAR, -RESULT OUT GHTPKG.RCT1 -) -AS -BEGIN -OPEN RESULT FOR -SELECT T_NUMBER AS T_NUMBER, T_LONG AS T_LONG, T_FLOAT AS T_FLOAT, T_VARCHAR AS T_VARCHAR, T_NVARCHAR AS T_NVARCHAR, T_CHAR AS T_CHAR, T_NCHAR AS T_NCHAR FROM DUAL; -RETURN; -END; -/ - - - -CREATE OR REPLACE PROCEDURE GHTDB.GHSP_TYPES_SIMPLE_2 ( -T_NUMBER IN OUT NUMBER, -T_LONG IN OUT LONG, -T_FLOAT IN OUT FLOAT, -T_VARCHAR IN OUT VARCHAR2, -T_NVARCHAR IN OUT NVARCHAR2, -T_CHAR IN OUT CHAR, -T_NCHAR IN OUT NCHAR -) -AS -BEGIN -T_NUMBER := T_NUMBER * 2; -T_LONG := UPPER(T_LONG); -T_FLOAT := T_FLOAT * 2; -T_VARCHAR := UPPER(T_VARCHAR); -T_NVARCHAR := UPPER(T_NVARCHAR); -T_CHAR := UPPER(T_CHAR); -T_NCHAR := UPPER(T_NCHAR); -END; -/ - - - -CREATE OR REPLACE PROCEDURE GHTDB.GHSP_TYPES_SIMPLE_3 ( -ID1 IN CHAR, -P_NUMBER OUT NUMBER, -P_LONG OUT LONG, -P_FLOAT OUT FLOAT, -P_VARCHAR OUT VARCHAR2, -P_NVARCHAR OUT NVARCHAR2, -P_CHAR OUT CHAR, -P_NCHAR OUT NCHAR -) -AS -BEGIN -SELECT T_NUMBER, T_LONG, T_FLOAT, T_VARCHAR, T_NVARCHAR, T_CHAR, T_NCHAR INTO P_NUMBER, P_LONG, P_FLOAT, P_VARCHAR, P_NVARCHAR, P_CHAR, P_NCHAR FROM TYPES_SIMPLE WHERE ID = ID1; -RETURN; -END; -/ - - - -CREATE OR REPLACE PROCEDURE GHTDB.GHSP_TYPES_SIMPLE_4 ( -ID1 IN CHAR, -RESULT OUT GHTPKG.RCT1, -RESULT1 OUT GHTPKG.RCT1, -RESULT2 OUT GHTPKG.RCT1 -) -IS - -m_result GHTPKG.RCT1; -m_result1 GHTPKG.RCT1; -m_result2 GHTPKG.RCT1; - -BEGIN - -insert into TYPES_SIMPLE(ID,T_NUMBER) values (ID1,50); - -OPEN m_result FOR -SELECT * FROM TYPES_SIMPLE where ID = ID1; - -update TYPES_SIMPLE set T_NUMBER=60 where Id = ID1; - -OPEN m_result1 FOR -SELECT * FROM TYPES_SIMPLE where ID = ID1; - -delete from TYPES_SIMPLE where ID = ID1; - -OPEN m_result2 FOR -SELECT * FROM TYPES_SIMPLE where ID = ID1; - -RESULT := m_result; -RESULT1 := m_result1; -RESULT2 := m_result2; - -END; -/ - - - -CREATE OR REPLACE PROCEDURE GHTDB.GHSP_TYPES_SIMPLE_5( -RESULT OUT GHTPKG.RCT1 -)AS - -BEGIN - DECLARE - T_NUMBER NUMBER(10) := 21; - T_LONG LONG := 'abcdefghijklmnopqrstuvwxyz1234567890~!@#$%^&*()_+-=[]\|;:,./<>? '; - T_FLOAT FLOAT(10) := 1.234; - T_VARCHAR VARCHAR2(10) := 'qwertasdfg'; - T_NVARCHAR NVARCHAR2(10) := 'qwertasdfg'; - T_CHAR CHAR(10) := 'abcdefghij'; - T_NCHAR NCHAR(10) := 'abcdefghij'; - - BEGIN - OPEN RESULT FOR - SELECT T_NUMBER,T_LONG, T_FLOAT, T_VARCHAR, T_NVARCHAR, T_CHAR, T_NCHAR FROM DUAL; - RETURN; - END; - -END; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GHSP_PKGAMBIG" ( - res out ghtpkg.rct1 -) -as -begin - --Return a value which indocates that the procedure 'ghsp_pkgAmbig' was called not from within GHTPKG. - declare IN_GHTPKG varchar2(5) := 'FALSE'; - begin - open res for - select IN_GHTPKG as IN_PKG from dual; - end; -end; -/ - -CREATE OR REPLACE PROCEDURE "GHTDB"."GH_DUMMY" ( - EmployeeIDPrm in NUMBER, - result OUT GHTPKG.RCT1 -) -as -begin - OPEN result FOR - SELECT EMPLOYEEID, - LASTNAME, - FIRSTNAME, - TITLE, - TITLEOFCOURTESY, - BIRTHDATE, - HIREDATE, - ADDRESS, - CITY, - REGION, - POSTALCODE, - COUNTRY, - HOMEPHONE, - EXTENSION, - REPORTSTO, - PHOTOPATH, - MYCOLUMN - FROM GHTDB.EMPLOYEES where EmployeeID > EmployeeIDPrm; -RETURN; -end; -/ - -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.categories TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.current_product_list TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.customercustomerdemo TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.customerdemographics TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.customers TO PUBLIC; -GRANT EXECUTE ON GHTDB.GH_MULTIRECORDSETS TO PUBLIC; ---GRANT EXECUTE ON GHTDB.custordersorders TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.employees TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.employeeterritories TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.products TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.products_above_average_price TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.region TO PUBLIC; ---GRANT EXECUTE ON salesbycategory TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.suppliers TO PUBLIC; ---GRANT EXECUTE ON ten_most_expensive_products TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB.territories TO PUBLIC; - ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- -DROP TABLE GHTDB_EX.CUSTOMERS; -DROP TABLE GHTDB_EX.categories; - -DROP USER GHTDB_EX CASCADE; - -CREATE USER "GHTDB_EX" - IDENTIFIED BY "GHTDB_EX" DEFAULT TABLESPACE "USERS" - TEMPORARY TABLESPACE "TEMP" ; - -GRANT CONNECT, RESOURCE TO "GHTDB_EX"; - -CREATE OR REPLACE PACKAGE GHTDB_EX.GHTPKG -AS - TYPE RCT1 IS REF CURSOR; - IDENTITY INTEGER; -END; -/ - -CREATE TABLE GHTDB_EX.CUSTOMERS ( - customerid CHAR(10) NOT NULL, - companyname VARCHAR2(80) NULL, - contactname VARCHAR2(60) NULL, - contacttitle VARCHAR2(60) NULL, - address VARCHAR2(120) NULL, - city VARCHAR2(30) NULL, - region VARCHAR2(30) NULL, - postalcode VARCHAR2(20) NULL, - country VARCHAR2(30) NULL, - phone VARCHAR2(48) NULL, - fax VARCHAR2(48) NULL, - picture VARCHAR2(48) NULL -) - TABLESPACE USERS -/ - -CREATE TABLE GHTDB_EX.categories ( - categoryid VARCHAR2(20) NOT NULL, - categoryname VARCHAR2(30) NOT NULL, - description CLOB NULL -) - TABLESPACE USERS -/ - - -CREATE OR REPLACE PROCEDURE "GHTDB_EX"."GH_DUMMY" - (CustomerIdPrm IN CHAR, -result OUT GHTPKG.RCT1) -AS -BEGIN -OPEN result FOR -SELECT * FROM Customers where CustomerID=CustomerIdPrm; -RETURN; -END; -/ - -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB_EX.CUSTOMERS TO PUBLIC; -GRANT DELETE,INSERT,SELECT,UPDATE ON GHTDB_EX.CATEGORIES TO PUBLIC; -GRANT EXECUTE ON GHTDB_EX.gh_dummy TO PUBLIC; - - - -COMMIT; - -exit |