Home » SQL & PL/SQL » SQL & PL/SQL » FORALL statement error while compiling
FORALL statement error while compiling [message #261260] Wed, 22 August 2007 04:17 Go to next message
vinodhere
Messages: 9
Registered: August 2007
Location: Mumbai
Junior Member
FORALL x IN 1..24
INSERT INTO RPT_TABLE_DAILY_DT VALUES(val_fdr(1)(x), val_fdr(2)(x), x);
END;


I am not able to insert 'x' in my ‘RPT_TABLE_DAILY_DT’ table(last column), its showing "PLS-00430: FORALL iteration variable x is not allowed in this context"
Could you please suggest me how I can insert the value of 'x' in my table.

If I could use this its compiling properly:

FORALL x IN 1..24
INSERT INTO RPT_TABLE_DAILY_DT VALUES(val_fdr(1)(x), val_fdr(2)(x), null);
END;

Please suggest me any solution for inserting 'x' in my table.
Re: FORALL statement error while compiling [message #261277 is a reply to message #261260] Wed, 22 August 2007 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
Create a new array with a(x)=x and use this array in your statement.

Regards
Michel
Re: FORALL statement error while compiling [message #261362 is a reply to message #261277] Wed, 22 August 2007 06:59 Go to previous messageGo to next message
vinodhere
Messages: 9
Registered: August 2007
Location: Mumbai
Junior Member
Thanks for your prompt reply.. I am using Oracle 10g.

it still not working you are directly using x variable in the code and still which is not allowed in this context.


FORALL x IN 1..24 
   var_new_cnt(x)=[B]x[/B];
   INSERT INTO RPT_TABLE_DAILY_DT VALUES(val_fdr(1)(x), val_fdr(2)(x), x);

END;



the bold 'x' is not allowed here to use directly.

[mod-edit] removed all illiterate IM words

[Updated on: Wed, 22 August 2007 09:52] by Moderator

Report message to a moderator

Re: FORALL statement error while compiling [message #261367 is a reply to message #261362] Wed, 22 August 2007 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create and fill the array BEFORE the FORALL part. Something like:
for x in 1..24 loop var_new_cnt(x)=x; end loop;
FORALL x IN 1..24 
   INSERT INTO RPT_TABLE_DAILY_DT VALUES(val_fdr(1)(x), val_fdr(2)(x), var_new_cnt(x));

Regards
Michel

[Updated on: Wed, 22 August 2007 07:08]

Report message to a moderator

Re: FORALL statement error while compiling [message #261517 is a reply to message #261367] Wed, 22 August 2007 22:34 Go to previous messageGo to next message
vinodhere
Messages: 9
Registered: August 2007
Location: Mumbai
Junior Member
Yes that’s right, it works but I am in the stage of improving the performance of my stored procudure.

Previously I written the same code for inserting the records with FOR LOOP, but for improving the performance I preferred FORALL.

Waiting for your tunned suggestion if any.

Thanks,
Vinod


Re: FORALL statement error while compiling [message #261568 is a reply to message #261517] Thu, 23 August 2007 01:14 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not improving the performances?
I doubt this is not the case.
The main cost in loop is loop containing SQL not pure PL/SQL loop.

Regards
Michel
Previous Topic: Max statement
Next Topic: Using cursor within a cursor
Goto Forum:
  


Current Time: Sun Dec 04 02:34:51 CST 2016

Total time taken to generate the page: 0.09158 seconds