| Need Oracle Proc to return mult rows [message #245788] |
Mon, 18 June 2007 15:50  |
jklein
Messages: 1
Registered: June 2007
|
Junior Member |
|
|
I have the following proc that returns 1 to ?? number of rows depending upon the PO ID input into the SQL stmt.
When there is > than 1 row returned I get the ORA-01422 error. How do I recode this proc to allow for 1 to many returned rows of data?
The PO id (input) will be passed into the proc by another program. I have hardcoded the PO id ('0700185') for testing pursposes.
Please be advised I am not a DBA, I am a pure novice at Oracle procs, but willing to learn.
Thank you for any assistance you may provide.
Jim -
CREATE OR REPLACE PROCEDURE TESTPNET(PO OUT VARCHAR, BATCH OUT VARCHAR2, VOUCHER OUT VARCHAR2, INVOICE OUT VARCHAR2)
AS
BEGIN
SELECT PO_ID,GRP_AP_ID, VOUCHER_ID, INVOICE_ID INTO PO, BATCH, VOUCHER, INVOICE FROM PS_VOUCHER
WHERE PO_ID = '0700185';
DBMS_OUTPUT.PUT_LINE(PO);
DBMS_OUTPUT.PUT_LINE(BATCH);
DBMS_OUTPUT.PUT_LINE(VOUCHER);
DBMS_OUTPUT.PUT_LINE(INVOICE);
END TESTPNET;
|
|
|
|
|
|
| Re: Need Oracle Proc to return mult rows [message #245818 is a reply to message #245790] |
Mon, 18 June 2007 21:46  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Your procedure looks a little like you may have written T-SQL procedures in SQL-Server before. Oracle procedures work a bit differently.
It's very hard to answer your question, because there are some fundamental problems with your procedure. I could launch into all sorts of complex functionality like collections, pipelined functions, and persistent-state package cursors, but none of that will help you because you really need to understand the fundamentals of PL/SQL programming first.
I suggest you first read through the PL/SQL Users Guide. Ideally, you should go to a PL/SQL course, otherwise you should search the web for learning materials. I have searched for CBT packages before without any luck, but I have seen non-interactive material designed for the new starter.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
