Home » RDBMS Server » Server Administration » URGENT Syntax question Dynamic SQL.
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.
Previous Topic: SQL and Upper/Lower case sensitivity
Next Topic: Calling Procedure/Functions dynamically......Urgent !!!!!!!!!!
Goto Forum:
  


Current Time: Fri Mar 29 05:50:17 CDT 2024