Oracle ORA-01008: not all variables bound

[di99lipe]

Hello gurus!
I get ORA-01008: not all variables bound error when trying to execute immidiate function in a trigger. This is the code


FUNCTION****************
CREATE OR REPLACE FUNCTION table2rowtype ( prefix IN VARCHAR2, tableName IN VARCHAR2) RETURN VARCHAR2
IS
CURSOR v_cursor IS
SELECT column_name FROM user_tab_columns
WHERE TABLE_NAME = tableName
ORDER BY column_id;

v_sql VARCHAR2(8000):= NULL;
BEGIN
FOR v_rec IN v_cursor
LOOP
IF v_sql iS NOT NULL
THEN
v_sql := v_sql || ',';
END IF;
v_sql := v_sql || prefix || v_rec.column_name;
END LOOP;
v_sql := 'SELECT ' || v_sql || ' FROM DUAL';
RETURN v_sql;

END ;
/

TRIGGER*****************
CREATE OR REPLACE TRIGGER CHARGES_ArchiveTrigger
BEFORE INSERT or UPDATE or DELETE ON CHARGES
REFERENCING OLD as old NEW as new
FOR EACH ROW
DECLARE
archiveRec CHARGES%ROWTYPE ;
BEGIN
EXECUTE IMMEDIATE table2rowtype(':new.','CHARGES') INTO archiveRec;


-- call to package/procedure to process archiving: archive(archiveRec) ;

END ;

[davide++]

Hi all

If I've figured out correctly what you are trying to do, the code may be as follow


CREATE OR REPLACE FUNCTION table2rowtype ( prefix IN VARCHAR2, tableName IN VARCHAR2) RETURN VARCHAR2
IS
CURSOR v_cursor IS
SELECT column_name FROM user_tab_columns
WHERE TABLE_NAME = tableName
ORDER BY column_id;

v_sql VARCHAR2(8000):= NULL;
BEGIN
FOR v_rec IN v_cursor
LOOP
IF v_sql iS NOT NULL
THEN
v_sql := v_sql || ',';
END IF;
v_sql := v_sql || prefix || '.' || v_rec.column_name;
END LOOP;
v_sql := 'SELECT ' || v_sql || ' FROM DUAL';
RETURN v_sql;

END ;


You should add the dot in the concatenation, so you'll get a SELECT like


SELECT :new.FIELD1,
:new.FIELD2,
:new.FIELD3
FROM DUAL


At the moment when the stored procedure is executed you get


SELECT :newFIELD1,
:newFIELD2,
:newFIELD3,
FROM DUAL


that is wrong.

In any case, I'm not sure that this will work fine...

[di99lipe]

Hello davide++!
Thanx for the answer...and that you look into my problem :D

But the dot is sent into the function with the prefix parameter ':new.' already so it does not solve my problem unfortunetly.


CREATE OR REPLACE TRIGGER CHARGES_ArchiveTrigger
BEFORE INSERT or UPDATE or DELETE ON CHARGES
REFERENCING OLD as old NEW as new
FOR EACH ROW
DECLARE
archiveRec CHARGES%ROWTYPE ;
BEGIN
EXECUTE IMMEDIATE table2rowtype(':new.','CHARGES') INTO archiveRec;


-- call to package/procedure to process archiving: archive(archiveRec) ;

END ;

[davide++]

Ok.

You're right, I'm sorry, I haven't read well your code.

Probably the problem is that the query you're trying to execute with direct SQL is


SELECT :new.FIELD1,
:new.FIELD2,
:new.FIELD3
FROM DUAL


but DUAL has one field , DUMMY, so the error 'not all variables bound'.

Try to concatenate the table name at the end of 'table2rowtype'.

[jcdarshana]

Hi...gd morning.
I want 2 know how conect Oracal dbase with VB6.Please give me some project with codes.
Thank you.

[KrisSimonis]

Lookup the Oracle knowledge base and help files. They should give you plenty of examples how to set up a connection. I would also recommend an English Dictionary, and the 'read this before you post'-post at the top of all forums. Then you'd know how to attempt to write proper English, and that you shouldn't try and hijak threads if you have a question, but make a thread of your own.
In fact, Looking up things in helpfiles first tends to in the very least help you phrase your question properly so people can easily understand the situation and what is needed. ( which is not nescessarily what you say you want )