Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO USE :NEW AND :OLD IN EXECUTE IMMEDIATE (ORACLE9I)
HOW TO USE :NEW AND :OLD IN EXECUTE IMMEDIATE [message #307306] Tue, 18 March 2008 07:26 Go to next message
sunilkumarpal
Messages: 1
Registered: March 2008
Junior Member
following triger throwing error like :
ORA-01008: not all variables bound
please help
CREATE OR REPLACE TRIGGER TRG_AUDIT_TRL 
 AFTER UPDATE OR DELETE 
 ON HRMS_TRADE 
    FOR EACH ROW
DECLARE
CURSOR cursor1 IS 
Select column_name from user_tab_cols 
 where table_name ='HRMS_TRADE';
 name_col VARCHAR(50);
 name_table VARCHAR(50);
 name_old varchar(5) := NULL;
 sql_query VARCHAR(500);
BEGIN 
	 OPEN cursor1;
IF Auditpackage.Reason IS NULL THEN
	  AUDITPACKAGE.MyProcedure(10); 
END IF;
name_old :=':old';
LOOP
	name_col :='';
	FETCH cursor1
	INTO name_col;
	name_table := 'HRMS_TITLE';
	IF name_col IS NOT NULL THEN 
	   DBMS_OUTPUT.put_line('data'|| name_col);
	  sql_query := 'INSERT INTO HRMS_TABLE_BACKUP (BACKUP_TABLE_NAME,BACKUP_COL_NAME,BACKUP_OLD_VALUE,BACKUP_NEW_VALUE,BACKUP_DATE,BACKUP_MOD_BY)'
	     ||'VALUES(:1,:2,'||name_old||'.'||name_col||','||name_old||'.'||name_col||',SYSDATE,Auditpackage.Reason)';
	EXECUTE IMMEDIATE sql_query  USING name_table,name_col ;
	END IF;
	EXIT WHEN cursor1%NOTFOUND;
END LOOP;
 CLOSE cursor1; 
 END;


[mod-edit] code tags added. next time do it yourself!

[Updated on: Tue, 18 March 2008 07:44] by Moderator

Report message to a moderator

Re: HOW TO USE :NEW AND :OLD IN EXECUTE IMMEDIATE [message #307359 is a reply to message #307306] Tue, 18 March 2008 10:34 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055
Previous Topic: procedure execution halt
Next Topic: not equal (!=) in trigger
Goto Forum:
  


Current Time: Sun Dec 11 03:59:18 CST 2016

Total time taken to generate the page: 0.16788 seconds