Home » SQL & PL/SQL » SQL & PL/SQL » Execute immediate inside a Loop (Merged 4)
Execute immediate inside a Loop (Merged 4) [message #419095] Wed, 19 August 2009 17:25 Go to next message
paburgos
Messages: 3
Registered: August 2009
Junior Member
Hi Everybody,

I have the following cursor in a table Trigger:

DECLARE CURSOR DATACOMPLETER IS
SELECT ADA_NCODE, ADA_CCOLUMN FROM BH_HIS_ADDITIONAL_DATA WHERE ADA_CTABLE = 'RAF_PAGOS' AND ADA_CCOLUMN IS NOT NULL;
BEGIN
FOR R_DATACOMPLETER IN DATACOMPLETER LOOP

dynamicStatement := CONCAT('INSERT INTO BH_HIS_LOG_ADDITIONAL_DATA(HLA_NCODE, HLA_CVALUE, HLO_NCODE, ADA_NCODE)
VALUES(BH_HISLOG_ADDDATA_SEQ.NEXTVAL,:new.', R_DATACOMPLETER.ADA_CCOLUMN);

dynamicStatement := CONCAT(dynamicStatement,' ,Log_Id , ');

dynamicStatement := CONCAT(dynamicStatement, R_DATACOMPLETER.ADA_NCODE);

dynamicStatement := CONCAT(dynamicStatement, ' )');

EXECUTE IMMEDIATE dynamicStatement;

END LOOP;
END;

But when I execute the trigger I have this error:

ORA-01008 not all variables bound

Debugging the trigger I found that the EXECUTE IMMEDIATE instruction is the problem, I checked the dynamic sql but it is OK, so what I´m missing?


[mod-edit] removed font and size.

[Updated on: Thu, 20 August 2009 08:01] by Moderator

Report message to a moderator

Execute Immediate inside a Loop [message #419097 is a reply to message #419095] Wed, 19 August 2009 17:36 Go to previous messageGo to next message
paburgos
Messages: 3
Registered: August 2009
Junior Member
Hi everybody,

I have a table trigger and it contains the following cursor:


DECLARE CURSOR DATACOMPLETER IS
SELECT ADA_NCODE, ADA_CCOLUMN FROM BH_HIS_ADDITIONAL_DATA WHERE ADA_CTABLE = 'RAF_PAGOS' AND ADA_CCOLUMN IS NOT NULL;
BEGIN
FOR R_DATACOMPLETER IN DATACOMPLETER LOOP

dynamicStatement := CONCAT('INSERT INTO BH_HIS_LOG_ADDITIONAL_DATA(HLA_NCODE, HLA_CVALUE, HLO_NCODE, ADA_NCODE)
VALUES(BH_HISLOG_ADDDATA_SEQ.NEXTVAL,:new.', R_DATACOMPLETER.ADA_CCOLUMN);

dynamicStatement := CONCAT(dynamicStatement,' ,Log_Id , ');

dynamicStatement := CONCAT(dynamicStatement, R_DATACOMPLETER.ADA_NCODE);

dynamicStatement := CONCAT(dynamicStatement, ' )');

EXECUTE IMMEDIATE dynamicStatement;

END LOOP;
END;



Debugging the cursor I found that the problem is in the EXECUTE IMMEDIATE instruction, I checked the dynamic sql but everything is ok.

What am I missing?
Re: Execute Immediate inside a Loop [message #419098 is a reply to message #419095] Wed, 19 August 2009 17:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But when I execute the trigger I have this error:
Trigger? I don't see any trigger

>ORA-01008 not all variables bound


>Debugging the trigger I found that the EXECUTE IMMEDIATE instruction is the problem,
>I checked the dynamic sql but it is OK,
Prove SQL is OK.

>so what am I missing?
Either you or Oracle has bug.
Want to bet on which has the bug?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Wed, 19 August 2009 17:38]

Report message to a moderator

Execute Immediate inside a Loop [message #419099 is a reply to message #419095] Wed, 19 August 2009 17:38 Go to previous messageGo to next message
paburgos
Messages: 3
Registered: August 2009
Junior Member
Hi everybody,

I have a table trigger and it contains the following cursor:


DECLARE CURSOR DATACOMPLETER IS
SELECT ADA_NCODE, ADA_CCOLUMN FROM BH_HIS_ADDITIONAL_DATA WHERE ADA_CTABLE = 'RAF_PAGOS' AND ADA_CCOLUMN IS NOT NULL;
BEGIN
FOR R_DATACOMPLETER IN DATACOMPLETER LOOP

dynamicStatement := CONCAT('INSERT INTO BH_HIS_LOG_ADDITIONAL_DATA(HLA_NCODE, HLA_CVALUE, HLO_NCODE, ADA_NCODE)
VALUES(BH_HISLOG_ADDDATA_SEQ.NEXTVAL,:new.', R_DATACOMPLETER.ADA_CCOLUMN);

dynamicStatement := CONCAT(dynamicStatement,' ,Log_Id , ');

dynamicStatement := CONCAT(dynamicStatement, R_DATACOMPLETER.ADA_NCODE);

dynamicStatement := CONCAT(dynamicStatement, ' )');

EXECUTE IMMEDIATE dynamicStatement;

END LOOP;
END;


When I execute the trigger I have this error:
ORA-01008 not all variables bound

Debugging the cursor I found that the problem is in the EXECUTE IMMEDIATE instruction, I checked the dynamic sql but everything is ok.

What am I missing?
Re: Execute Immediate inside a Loop [message #419100 is a reply to message #419099] Wed, 19 August 2009 17:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>What am I missing?
a clue about spamming for answers
Re: Execute Immediate inside a Loop [message #419103 is a reply to message #419099] Wed, 19 August 2009 18:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
@paburgos

Posting the question 4 times won't give you any faster answers. Answer BlackSwans questions and reading the Forum Guide on how to format posts instead would be more productive.

[Updated on: Wed, 19 August 2009 18:10]

Report message to a moderator

Re: Execute Immediate inside a Loop [message #419104 is a reply to message #419103] Wed, 19 August 2009 18:11 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oh, and an general advice:

Don't use dynamic SQL. It breaks horribly all the time, and is impossible to debug.
Re: Execute immediate inside a Loop (Merged 4) [message #419144 is a reply to message #419095] Thu, 20 August 2009 01:16 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
paburgos wrote on Thu, 20 August 2009 00:25
Debugging the trigger I found that the EXECUTE IMMEDIATE instruction is the problem, I checked the dynamic sql but it is OK, so what I´m missing?

What about posting the dynamic sql too? From a brief look to code, it contains :NEW identifier, which is expected to be a bind variable - its value has to be specified in the USING clause. As EXECUTE IMMEDIATE does not contain it, Oracle fails.

It seems that you are trying to dynamically pass the column name. This is not possible, but you may create a code to generate statements for all columns, as described e.g. in this thread on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055.
Previous Topic: Need an Query idea (merged 3)
Next Topic: SQL Statement - Complex Conditions
Goto Forum:
  


Current Time: Sat Dec 10 22:35:56 CST 2016

Total time taken to generate the page: 0.08046 seconds