Page 1 of 1
OraScriptProcessor and PL/SQL Types
Posted: Tue 23 Jan 2018 20:54
by 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?
Re: OraScriptProcessor and PL/SQL Types
Posted: Wed 24 Jan 2018 08:52
by 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
Re: OraScriptProcessor and PL/SQL Types
Posted: Wed 24 Jan 2018 19:37
by 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.
Re: OraScriptProcessor and PL/SQL Types
Posted: Wed 21 Feb 2018 10:03
by 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 '/' :
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
Re: OraScriptProcessor and PL/SQL Types
Posted: Sun 25 Feb 2018 19:59
by 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.
Re: OraScriptProcessor and PL/SQL Types
Posted: Tue 27 Feb 2018 14:15
by 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.