| URGENT Syntax question Dynamic SQL. [message #370776] |
Sat, 05 February 2000 01:48 |
Laert
Messages: 20
Registered: January 2000
|
Junior Member |
|
|
Hi,
Can someone help me to fix following problem:
From stored procedure I'm doing follows
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id, cTBCrtString || cIndex, dbms_sql.native);
nRows := DBMS_SQL.EXECUTE(cursor_id);
DBMS_SQL.CLOSE_CURSOR(cursor_id);
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id, cTrigHeader || cColumns || ' ON ' || ctable || ' FOR EACH ROW DECLARE nCount INTEGER;' || Chr(13) || Chr(10) || 'BEGIN' || Chr(13) || Chr(10) || cTrigBody || Chr(13) || Chr(10) || 'END;' || Chr(13) || Chr(10) || '/', dbms_sql.native);
nRows := DBMS_SQL.EXECUTE(cursor_id);
DBMS_SQL.CLOSE_CURSOR(cursor_id);
Procedure is compiling without errors, but on execution
I'm reciving following errors on second DBMS_SQL.PARSE command :
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "My_proc", line 227
ORA-06512: at line 1
SQL*Plus internal error state 2087, context 47:0:0
Unsafe to proceed
The Execution string is:
CREATE TRIGGER My_table_IUTrig BEFORE INSERT OR UPDATE OF ID ON My_table FOR EACH ROW DECLARE nCount INTEGER;
BEGIN
IF INSERTING OR UPDATING ('ID') THEN
SELECT Count(*) INTO nCount FROM ref_table WHERE ID = :new.ID;
IF nCount = 0 THEN
raise_application_error( -20501, 'Cannot add or change record. Referential integrity rules require a related record in table ''ref_table''.');
END IF;
END IF;
END;
The execution of this string from SQL*Plus is processing without errors.
I'm using
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
Thanx in advance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
