OraScriptProcessor and PL/SQL Types

[heidenbluth]

Currently, OraScriptProcessor does not hand PL/SQL types correctly.
This can be fixed by a small change to OraScriptProcessor.pas

Code: Select all

procedure TCustomOraScriptProcessor.CheckLexem(
...
      ST_NORMAL:
        case Code of
          // lxType added 
          lxPROCEDURE, lxFUNCTION, lxPACKAGE, lxTRIGGER, lxTYPE:

Can you please include this fix to your next build?

[MaximG]

Of course, we can make the proposed change to the source code of our product. However, we must check it before release using internal autotests. For this, we will need a
sample of PL/SQL code, with which we will check the correctness of the made changes. Please compile and send us a sample of PL/SQL code, which contains descriptions of the
types you are interested in

[heidenbluth]

You can use any PLSQL type definition, for example

Code: Select all

CREATE OR REPLACE
TYPE T_NUMBERS AS TABLE OF NUMBER;

Please run a script that contains a mix of

• CREATE OR REPLACE PACKAGE
  CREATE OR REPLACE TYPE
  CREATE TABLE
  DML such as any INSERT, UPDATE or DELETE

and run this script using a TOraScript component.

Without the suggested change, TOraScript does not recognise CREATE OR REPLACE TYPE as its own statement. Please monitor SQL patrameter in TOraScript.BeforeExecute event and make sure the TYPE comes in its own event.

[MaximG]

We composed the following SQL script according to your description :

Code: Select all

CREATE OR REPLACE TYPE AMY_NUMBERS AS TABLE OF NUMBER
/
CREATE OR REPLACE PACKAGE AMY_PACK
AS
  intvar NUMBER;
  TYPE TCursor IS REF CURSOR;

  PROCEDURE MY_IN_PARAMS_PROC
  (
    id_p IN NUMBER,
    char_p IN CHAR,
    varchar_p IN VARCHAR2,
    int_p IN NUMBER,
    int64_p IN NUMBER,
    double_p IN NUMBER,
    date_p IN DATE,
    raw_p IN RAW
  );
END AMY_PACK;
/
CREATE OR REPLACE PACKAGE BODY AMY_PACK
IS
  PROCEDURE MY_IN_PARAMS_PROC
  (
    id_p IN NUMBER,
    char_p IN CHAR,
    varchar_p IN VARCHAR2,
    int_p IN NUMBER,
    int64_p IN NUMBER,
    double_p IN NUMBER,
    date_p IN DATE,
    raw_p IN RAW
  ) IS
    new_date DATE;
  BEGIN
    SELECT sysdate into new_date FROM dual;
  END  MY_IN_PARAMS_PROC;

END AMY_PACK;
/
CREATE OR REPLACE TYPE AAsdwsT_NUMBERS AS TABLE OF NUMBER;
/
CREATE TABLE MYDEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13), CONSTRAINT PK_MYDEPT PRIMARY KEY (DEPTNO))
/
INSERT INTO MYDEPT VALUES(10, 'IT', 'BARSELONA')
/
DELETE FROM MYDEPT WHERE DEPTNO=10
/

As a separator we used the character '/' :

Code: Select all

OraScript.Delimiter : = '/';

We have successfully executed the SQL queries specified with this script. Please provide the full text of the SQL script execution of which requires the mentioned changes in the ODAC source code

[heidenbluth]

I am merging this change into each ODAC release for a couple of years now, without telling you. Now I decided to telling you, and unfortunately, I cannot reproduce it with the current ODAC version.

In my case, SQL Delimiter must be set to ';' and not '/'. That allows to work similar to Oracle tools and separate SQL statements by ';' or '/' and PLSQL blocks by '/'.

The PLSQL syntax for TYPES is similar to PACKAGES. From an analytical approach, syntax parsing must be the same. I am using the change for years now, after we had a problem case. I have a feeling in my stomach that there is a case it comes to an error without the change, but I cannot prove that. My guess is the reason is that TYPE definitions do not contain a ';' whereas PACKAGE definitions may do.

[MaximG]

We will add the fixes you specified in the source code of our product. However, to verify the correctness and necessity of these changes, we need an example of the script, which includes the creation of all the database objects you mention. Please create and send us an example of such a script and we will immediately notify you about the results.