Home » SQL & PL/SQL » SQL & PL/SQL » help needed in simple pl/sql procedure
help needed in simple pl/sql procedure [message #321884] Wed, 21 May 2008 13:34 Go to next message
sri2005_05
Messages: 4
Registered: May 2008
Junior Member
Can any one tell me whats wrong in below procedure..i just want to extract from one table and print in the output..

CREATE OR REPLACE PROCEDURE HYSXM.qa_packages_proc
IS


type ACCOUNT_TYPE is table of STG_ACCOUNT%ROWTYPE;
ACCOUNT_TYPE1 ACCOUNT_TYPE;



BEGIN




select * BULK COLLECT INTO ACCOUNT_TYPE1
from STG_ACCOUNT;
forall x in ACCOUNT_TYPE1.FIRST..ACCOUNT_TYPE1.LAST
LOOP
dbms_output.put_line(x);
--INSERT INTO QA_ACCOUNT2 VALUES(x);
END LOOP
/

END qa_packages_proc;
/
Re: help needed in simple pl/sql procedure [message #321886 is a reply to message #321884] Wed, 21 May 2008 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Always copy and paste your session
2/ read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
3/ always post your Oracle version (4 decimals)
4/ FORALL is not an open loop. There are only 3 FORALL statements: FORALL ... INSERT, FORALL ... UPDATE and FORALL DELETE and no FORALL ... LOOP <something>
Read http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/forall_statement.htm

Regards
Michel
Re: help needed in simple pl/sql procedure [message #321899 is a reply to message #321886] Wed, 21 May 2008 14:52 Go to previous messageGo to next message
sri2005_05
Messages: 4
Registered: May 2008
Junior Member
Thanks,

But i want to print the data...Can any one tell how to do that

I tried below sql

select * BULK COLLECT INTO ACCOUNT_TYPE1
from STG_ACCOUNT;
forall x in ACCOUNT_TYPE1.FIRST..ACCOUNT_TYPE1.LAST
LOOP
dbms_output.put_line(x);
--INSERT INTO QA_ACCOUNT2 VALUES(x);
END LOOP

as


select * BULK COLLECT INTO ACCOUNT_TYPE1
from STG_ACCOUNT;

LOOP
dbms_output.put_line(ACCOUNT_TYPE1);

END LOOP
But its not working

Re: help needed in simple pl/sql procedure [message #321900 is a reply to message #321899] Wed, 21 May 2008 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ You didn't read what I posted
2/ You didn't read what I posted
3/ You didn't read what I posted
4/ You didn't read what I posted
5/ Remove useless empty lines

Regards
Michel

Re: help needed in simple pl/sql procedure [message #321901 is a reply to message #321884] Wed, 21 May 2008 15:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
why do you insist upon using BULK COLLECT?
Is using BULK COLLECT an integral part of this home work assignment?


http://asktom.oracle.com has many fine coding examples

>But its not working
my car is not working.
Please tell me how to make my car go.

[Updated on: Wed, 21 May 2008 15:22] by Moderator

Report message to a moderator

Re: help needed in simple pl/sql procedure [message #321905 is a reply to message #321899] Wed, 21 May 2008 15:31 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
sri2005_05 wrote on Wed, 21 May 2008 15:52

select * BULK COLLECT INTO ACCOUNT_TYPE1
from STG_ACCOUNT;
forall x in ACCOUNT_TYPE1.FIRST..ACCOUNT_TYPE1.LAST
LOOP
dbms_output.put_line(x);
--INSERT INTO QA_ACCOUNT2 VALUES(x);
END LOOP

as


select * BULK COLLECT INTO ACCOUNT_TYPE1
from STG_ACCOUNT;

LOOP
dbms_output.put_line(ACCOUNT_TYPE1);

END LOOP



This is invalid syntax all over the place. At least have the decency to post something that doesn't give oracle errors.
Previous Topic: Reorganize values in columns
Next Topic: Foreign Key Dependency Query
Goto Forum:
  


Current Time: Sat Feb 15 09:56:08 CST 2025