utils/eclipselink.dbws.builder.test.oracle/src/it/resources/sql/dbsetup_alltests.sql

--
-- Copyright (c) 2018, 2020 Oracle and/or its affiliates. All rights reserved.
--
-- This program and the accompanying materials are made available under the
-- terms of the Eclipse Public License v. 2.0 which is available at
-- http://www.eclipse.org/legal/epl-2.0,
-- or the Eclipse Distribution License v. 1.0 which is available at
-- http://www.eclipse.org/org/documents/edl-v10.php.
--
-- SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
--

CREATE OR REPLACE PROCEDURE BOOL_IN_TEST (X IN BOOLEAN) AS
BEGIN
  NULL;
END BOOL_IN_TEST;
|
CREATE OR REPLACE PROCEDURE TESTECHO(T IN VARCHAR2, U OUT VARCHAR2) AS
BEGIN
  U := CONCAT('test-' , T);
END;
|
CREATE OR REPLACE PACKAGE SOMEPACKAGE AS
  TYPE TBL1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
  TYPE TBL2 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE ARECORD IS RECORD (
    T1 TBL1,
    T2 TBL2,
    T3 BOOLEAN
  );
  TYPE TBL3 IS TABLE OF ARECORD INDEX BY PLS_INTEGER;
  TYPE TBL4 IS TABLE OF TBL2 INDEX BY PLS_INTEGER;
  PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2);
  PROCEDURE P2(OLD IN TBL2, NEW IN TBL2);
  PROCEDURE P3(RECARRAY IN TBL3);
  PROCEDURE P4(REC IN ARECORD);
  PROCEDURE P5(OLDREC IN ARECORD, NEWREC OUT ARECORD);
  PROCEDURE P6(BAR IN TBL4);
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2);
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2);
  PROCEDURE P8(FOO IN VARCHAR2);
  PROCEDURE P8(FOO IN VARCHAR2, BAR IN VARCHAR2);
  FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD;
  FUNCTION F2(OLD IN TBL2, SIMPLARRAY IN TBL1) RETURN TBL2;
  FUNCTION F3(SIMPLARRAY IN TBL1, OLDVAR IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION F4(RECARRAY IN TBL3, OLDREC IN ARECORD) RETURN TBL3;
END SOMEPACKAGE;
|
CREATE OR REPLACE PACKAGE BODY SOMEPACKAGE AS
  PROCEDURE P1(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
  BEGIN
    NULL;
  END P1;
  PROCEDURE P2(OLD IN TBL2, NEW IN TBL2) AS
  BEGIN
    NULL;
  END P2;
  PROCEDURE P3(RECARRAY IN TBL3) AS
  BEGIN
    NULL;
  END P3;
  PROCEDURE P4(REC IN ARECORD) AS
  BEGIN
    NULL;
  END P4;
  PROCEDURE P5(OLDREC IN ARECORD, NEWREC OUT ARECORD) AS
  BEGIN
    NEWREC.T1 := OLDREC.T1;
    NEWREC.T2 := OLDREC.T2;
    NEWREC.T3 := OLDREC.T3;
  END P5;
  PROCEDURE P6(BAR IN TBL4) AS
  BEGIN
    NULL;
  END P6;
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2) AS
  BEGIN
    NULL;
  END P7;
  PROCEDURE P7(SIMPLARRAY IN TBL1, FOO IN VARCHAR2, BAR IN VARCHAR2) AS
  BEGIN
    NULL;
  END P7;
  PROCEDURE P8(FOO IN VARCHAR2) AS
  BEGIN
    NULL;
  END P8;
  PROCEDURE P8(FOO IN VARCHAR2, BAR IN VARCHAR2) AS
  BEGIN
    NULL;
  END P8;
  FUNCTION F1(OLDREC IN ARECORD, FOO IN VARCHAR2) RETURN ARECORD IS arec ARECORD;
  BEGIN
    arec.T1 := OLDREC.T1;
    arec.T2 := OLDREC.T2;
    arec.T3 := OLDREC.T3;
    RETURN arec;
  END F1;	  
  FUNCTION F2(OLD IN TBL2, SIMPLARRAY IN TBL1) RETURN TBL2 IS
  BEGIN
    RETURN OLD;
  END F2;     
  FUNCTION F3(SIMPLARRAY IN TBL1, OLDVAR IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN OLDVAR;
  END F3;
  FUNCTION F4(RECARRAY IN TBL3, OLDREC IN ARECORD) RETURN TBL3 IS
  BEGIN
	RETURN RECARRAY;
  END F4;
END SOMEPACKAGE;
|
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL1 AS TABLE OF VARCHAR2(111)|
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL2 AS TABLE OF NUMBER|
CREATE OR REPLACE TYPE SOMEPACKAGE_ARECORD AS OBJECT (
  T1 SOMEPACKAGE_TBL1,
  T2 SOMEPACKAGE_TBL2,
  T3 INTEGER
)|
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL3 AS TABLE OF SOMEPACKAGE_ARECORD|
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL4 AS TABLE OF SOMEPACKAGE_TBL2|
CREATE OR REPLACE TYPE SOMEPACKAGE_CRECORD AS OBJECT (
  C1 SOMEPACKAGE_ARECORD,
  C2 SOMEPACKAGE_TBL2
)|
CREATE OR REPLACE TYPE SOMEPACKAGE_TBL5 AS TABLE OF DATE|
CREATE OR REPLACE FUNCTION SF_TBL1(NUM IN INTEGER) RETURN SOMEPACKAGE_TBL1 AS
    L_DATA SOMEPACKAGE_TBL1 := SOMEPACKAGE_TBL1();
  BEGIN
      FOR I IN 1 .. NUM LOOP
         L_DATA.EXTEND;
         L_DATA(I) := CONCAT('entry ', TO_CHAR(i));
      END LOOP;
     RETURN L_DATA;
END;
|
CREATE OR REPLACE FUNCTION BUILDTBL2(NUM IN INTEGER) RETURN SOMEPACKAGE_TBL2 AS
    L_DATA SOMEPACKAGE_TBL2 := SOMEPACKAGE_TBL2();
    BEGIN
	FOR I IN 1 .. NUM LOOP
	 L_DATA.EXTEND;
	 L_DATA(I) := I;
	END LOOP;
    RETURN L_DATA;
END BUILDTBL2;
|
CREATE OR REPLACE FUNCTION BUILDTBL5(NUM IN INTEGER) RETURN SOMEPACKAGE_TBL5 AS
    L_DATA SOMEPACKAGE_TBL5 := SOMEPACKAGE_TBL5();
    BEGIN
	FOR I IN 1 .. NUM LOOP
	 L_DATA.EXTEND;
	 L_DATA(I) := SYSDATE;
	END LOOP;
    RETURN L_DATA;
END BUILDTBL5;
|
CREATE OR REPLACE FUNCTION BUILDARECORD(NUM IN INTEGER) RETURN SOMEPACKAGE_ARECORD AS
    L_DATA SOMEPACKAGE_ARECORD := SOMEPACKAGE_ARECORD(NULL, NULL, NULL);
    BEGIN
     L_DATA.T1 := SF_TBL1(NUM);
     L_DATA.T2 := BUILDTBL2(NUM);
     L_DATA.T3 := NUM;
    RETURN L_DATA;
END BUILDARECORD;
|
CREATE OR REPLACE FUNCTION BUILDTBL4(NUM IN INTEGER) RETURN SOMEPACKAGE_TBL4 AS
    L_DATA SOMEPACKAGE_TBL4 := SOMEPACKAGE_TBL4();
    BEGIN
	FOR I IN 1 .. NUM LOOP
	 L_DATA.EXTEND;
	 L_DATA(I) := BUILDTBL2(I);
	END LOOP;
    RETURN L_DATA;
END BUILDTBL4;
|
CREATE OR REPLACE FUNCTION BUILDCRECORD(NUM IN INTEGER) RETURN SOMEPACKAGE_CRECORD AS
    L_DATA SOMEPACKAGE_CRECORD := SOMEPACKAGE_CRECORD(NULL, NULL);
    BEGIN
     L_DATA.C1 := BUILDARECORD(NUM);
     L_DATA.C2 := BUILDTBL2(NUM);
    RETURN L_DATA;
END BUILDCRECORD;
|
CREATE OR REPLACE TYPE EMP_INFO AS OBJECT (
 ID       NUMBER(5),
 NAME     VARCHAR2(50)
)|
CREATE OR REPLACE TYPE EMP_INFO_ARRAY AS VARRAY(3) OF EMP_INFO|
CREATE OR REPLACE PACKAGE ANOTHER_ADVANCED_DEMO
AS
  FUNCTION BUILDEMPARRAY(NUM IN INTEGER) RETURN EMP_INFO_ARRAY;
END;
|
CREATE OR REPLACE PACKAGE BODY ANOTHER_ADVANCED_DEMO AS
  FUNCTION BUILDEMPARRAY(NUM IN INTEGER) RETURN EMP_INFO_ARRAY AS
    L_DATA EMP_INFO_ARRAY := EMP_INFO_ARRAY();
  BEGIN
      FOR I IN 1 .. NUM LOOP
         L_DATA.EXTEND;
         L_DATA(I) := EMP_INFO(I, CONCAT('entry ', TO_CHAR(i)));
      END LOOP;
     RETURN L_DATA;
  END BUILDEMPARRAY;
END;
|
CREATE OR REPLACE TYPE REGION AS OBJECT (
 REG_ID       NUMBER(5),
 REG_NAME     VARCHAR2(50)
)|
CREATE OR REPLACE TYPE EMP_ADDRESS AS OBJECT (
 STREET       VARCHAR2(100),
 SUBURB       VARCHAR2(50),
 ADDR_REGION  REGION,
 POSTCODE     INTEGER
)|
CREATE OR REPLACE TYPE EMP_OBJECT AS OBJECT (
 EMPLOYEE_ID   NUMBER(8),
 ADDRESS       EMP_ADDRESS,
 EMPLOYEE_NAME VARCHAR2(80),
 DATE_OF_HIRE  DATE
)|
CREATE OR REPLACE PACKAGE ADVANCED_OBJECT_DEMO AS
  FUNCTION ECHOREGION(AREGION IN REGION) RETURN REGION;
  FUNCTION ECHOEMPADDRESS(ANEMPADDRESS IN EMP_ADDRESS) RETURN EMP_ADDRESS;
  FUNCTION ECHOEMPOBJECT(ANEMPOBJECT IN EMP_OBJECT) RETURN EMP_OBJECT;
END;
|
CREATE OR REPLACE PACKAGE BODY ADVANCED_OBJECT_DEMO AS
  FUNCTION ECHOREGION(AREGION IN REGION) RETURN REGION AS
  BEGIN
     RETURN AREGION;
  END ECHOREGION;
  FUNCTION ECHOEMPADDRESS(ANEMPADDRESS IN EMP_ADDRESS) RETURN EMP_ADDRESS AS
  BEGIN
    RETURN ANEMPADDRESS;
  END ECHOEMPADDRESS;
  FUNCTION ECHOEMPOBJECT(ANEMPOBJECT IN EMP_OBJECT) RETURN EMP_OBJECT AS
  BEGIN
    RETURN ANEMPOBJECT;
  END ECHOEMPOBJECT;
END;
|
CREATE OR REPLACE PACKAGE TEST_TYPES AS
   FUNCTION ECHO_INTEGER (PINTEGER IN INTEGER) RETURN INTEGER;
   FUNCTION ECHO_SMALLINT(PSMALLINT IN SMALLINT) RETURN SMALLINT;
   FUNCTION ECHO_NUMERIC (PNUMERIC IN NUMERIC) RETURN NUMERIC;
   FUNCTION ECHO_DEC (PDEC IN DEC) RETURN DEC;
   FUNCTION ECHO_DECIMAL (PDECIMAL IN DECIMAL) RETURN DECIMAL;
   FUNCTION ECHO_NUMBER (PNUMBER IN NUMBER) RETURN NUMBER;
   FUNCTION ECHO_VARCHAR(PVARCHAR IN VARCHAR) RETURN VARCHAR;
   FUNCTION ECHO_VARCHAR2 (PINPUTVARCHAR IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION ECHO_CHAR (PINPUTCHAR IN CHAR) RETURN CHAR;
   FUNCTION ECHO_REAL (PREAL IN REAL) RETURN REAL;
   FUNCTION ECHO_FLOAT (PINPUTFLOAT IN FLOAT) RETURN FLOAT;
   FUNCTION ECHO_DOUBLE (PDOUBLE IN DOUBLE PRECISION) RETURN DOUBLE PRECISION;
   FUNCTION ECHO_DATE (PINPUTDATE IN DATE) RETURN DATE;
   FUNCTION ECHO_TIMESTAMP (PINPUTTS IN TIMESTAMP) RETURN TIMESTAMP;
   FUNCTION ECHO_CLOB (PINPUTCLOB IN CLOB) RETURN CLOB;
   FUNCTION ECHO_BLOB (PINPUTBLOB IN BLOB) RETURN BLOB;
   FUNCTION ECHO_LONG (PLONG IN LONG) RETURN LONG;
   FUNCTION ECHO_LONG_RAW (PLONGRAW IN LONG RAW) RETURN LONG RAW;
   FUNCTION ECHO_RAW(PRAW IN RAW) RETURN RAW;
END;
|
CREATE OR REPLACE PACKAGE BODY TEST_TYPES AS
   FUNCTION ECHO_INTEGER (PINTEGER IN INTEGER) RETURN INTEGER IS
   BEGIN
      RETURN PINTEGER;
   END ECHO_INTEGER;
   FUNCTION ECHO_SMALLINT(PSMALLINT IN SMALLINT) RETURN SMALLINT IS
   BEGIN
      RETURN PSMALLINT;
   END ECHO_SMALLINT;
   FUNCTION ECHO_NUMERIC (PNUMERIC IN NUMERIC) RETURN NUMERIC IS
   BEGIN
      RETURN PNUMERIC;
   END ECHO_NUMERIC;
   FUNCTION ECHO_DEC (PDEC IN DEC) RETURN DEC IS
   BEGIN
      RETURN PDEC;
   END ECHO_DEC;
   FUNCTION ECHO_DECIMAL (PDECIMAL IN DECIMAL) RETURN DECIMAL IS
   BEGIN
      RETURN PDECIMAL;
   END ECHO_DECIMAL;
   FUNCTION ECHO_NUMBER (PNUMBER IN NUMBER) RETURN NUMBER IS
   BEGIN
      RETURN PNUMBER;
   END ECHO_NUMBER;
   FUNCTION ECHO_VARCHAR(PVARCHAR IN VARCHAR) RETURN VARCHAR IS
   BEGIN
      RETURN PVARCHAR;
   END ECHO_VARCHAR;
   FUNCTION ECHO_VARCHAR2 (PINPUTVARCHAR IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      RETURN PINPUTVARCHAR;
   END ECHO_VARCHAR2;
   FUNCTION ECHO_CHAR (PINPUTCHAR IN CHAR) RETURN CHAR IS
   BEGIN
      RETURN PINPUTCHAR;
   END ECHO_CHAR;
   FUNCTION ECHO_REAL (PREAL IN REAL) RETURN REAL IS
   BEGIN
      RETURN PREAL;
   END ECHO_REAL;
   FUNCTION ECHO_FLOAT (PINPUTFLOAT IN FLOAT) RETURN FLOAT IS
   BEGIN
      RETURN PINPUTFLOAT;
   END ECHO_FLOAT;
   FUNCTION ECHO_DOUBLE (PDOUBLE IN DOUBLE PRECISION) RETURN DOUBLE PRECISION IS
   BEGIN
      RETURN PDOUBLE;
   END ECHO_DOUBLE;
   FUNCTION ECHO_DATE (PINPUTDATE IN DATE) RETURN DATE IS
   BEGIN
      RETURN PINPUTDATE;
   END ECHO_DATE;
   FUNCTION ECHO_TIMESTAMP (PINPUTTS IN TIMESTAMP) RETURN TIMESTAMP IS
   BEGIN
      RETURN PINPUTTS;
   END ECHO_TIMESTAMP;
   FUNCTION ECHO_CLOB (PINPUTCLOB IN CLOB) RETURN CLOB IS
   BEGIN
      RETURN PINPUTCLOB;
   END ECHO_CLOB;
   FUNCTION ECHO_BLOB (PINPUTBLOB IN BLOB) RETURN BLOB IS
   BEGIN
      RETURN PINPUTBLOB;
   END ECHO_BLOB;
   FUNCTION ECHO_LONG (PLONG IN LONG) RETURN LONG IS
   BEGIN
      RETURN PLONG;
   END ECHO_LONG;
   FUNCTION ECHO_LONG_RAW (PLONGRAW IN LONG RAW) RETURN LONG RAW IS
   BEGIN
      RETURN PLONGRAW;
   END ECHO_LONG_RAW;
   FUNCTION ECHO_RAW(PRAW IN RAW) RETURN RAW IS
   BEGIN
      RETURN PRAW;
   END ECHO_RAW;
END;
|
CREATE TABLE LTBL ( 
  EMPNO NUMBER(4) NOT NULL, 
  FNAME VARCHAR2(40), 
  LNAME VARCHAR2(40),  
  PRIMARY KEY (EMPNO) 
)|
CREATE TYPE LTBL_PKG_LTBL_REC AS OBJECT (
      EMPNO NUMBER(4),
      FNAME VARCHAR2(40),
      LNAME VARCHAR2(40)
)|
CREATE TYPE LTBL_PKG_LTBL_TAB AS TABLE OF LTBL_PKG_LTBL_REC|
CREATE PACKAGE LTBL_PKG AS
  TYPE LTBL_REC IS RECORD(
    EMPNO LTBL.EMPNO%TYPE,
    FNAME LTBL.FNAME%TYPE,
    LNAME LTBL.LNAME%TYPE
  );
  TYPE LTBL_TAB IS TABLE OF LTBL_REC INDEX BY BINARY_INTEGER;
  PROCEDURE LTBL_QUERY(BLOCK_DATA IN OUT LTBL_TAB, P_EMPNO IN NUMBER);
END;
|
CREATE PACKAGE BODY LTBL_PKG AS
  PROCEDURE LTBL_QUERY(BLOCK_DATA IN OUT LTBL_TAB, P_EMPNO IN NUMBER) IS
  BEGIN
    BLOCK_DATA(2).EMPNO := 789;
    BLOCK_DATA(2).FNAME := 'John';
    BLOCK_DATA(2).LNAME := 'Blair';
  END;
END;
|
CREATE OR REPLACE TYPE TESMAN_TYPE1 AS OBJECT (
 ACCT          NUMBER,
 COUNTRY       VARCHAR2(30),
 ADDR_DIVISION VARCHAR2(30),
 STATE         VARCHAR2(30)
)
|
CREATE OR REPLACE TYPE TESMAN_TYPE2 AS OBJECT (
 PNR     NUMBER,
 COMPANY VARCHAR2(30),
 SE      VARCHAR2(30),
 SCRIP   VARCHAR2(30),
 TT      TESMAN_TYPE1
)
|
CREATE OR REPLACE TYPE TESMAN_TYPE3 AS VARRAY(2) OF TESMAN_TYPE2
|
CREATE TABLE TESMAN_TABLE1 (
  IDE NUMBER,
  IDTT TESMAN_TYPE1
)
|
CREATE TABLE TESMAN_TABLE2 (
  SRNO NUMBER,
  DETAIL TESMAN_TYPE2
)
|
CREATE TABLE TESMAN_TABLE3 (
  ID INTEGER,
  TT3 TESMAN_TYPE3
)
|
CREATE OR REPLACE PACKAGE TESMANPACK AS
  FUNCTION TESMANFUNC17(PARAM1 IN INTEGER) RETURN TESMAN_TABLE2%ROWTYPE;
  PROCEDURE TESMANPROC17(PARAM1 IN INTEGER, REC OUT TESMAN_TABLE2%ROWTYPE);
  PROCEDURE TESMANPROC17b(OLDREC IN TESMAN_TABLE3%ROWTYPE, NEWREC OUT TESMAN_TABLE3%ROWTYPE);
END TESMANPACK;
|
CREATE OR REPLACE PACKAGE BODY TESMANPACK AS

  FUNCTION TESMANFUNC17(PARAM1 IN INTEGER) RETURN TESMAN_TABLE2%ROWTYPE AS
    L_DATA1 TESMAN_TABLE2%ROWTYPE;
    CURSOR C_EMP(PARAMTEMP IN INTEGER) IS SELECT * FROM TESMAN_TABLE2 TE WHERE TE.SRNO=PARAMTEMP;
  BEGIN
      OPEN C_EMP(PARAM1);
      LOOP
        FETCH C_EMP INTO L_DATA1;
        EXIT WHEN C_EMP%NOTFOUND;
      END LOOP;
      RETURN L_DATA1;
  END;

  PROCEDURE TESMANPROC17( PARAM1 IN INTEGER, REC OUT TESMAN_TABLE2%ROWTYPE) AS
  BEGIN
    REC := TESMANFUNC17(PARAM1);
  END;

  PROCEDURE TESMANPROC17b(OLDREC IN TESMAN_TABLE3%ROWTYPE, NEWREC OUT TESMAN_TABLE3%ROWTYPE) AS
  BEGIN
    NEWREC := OLDREC;
  END;

END TESMANPACK;
|