Home » SQL & PL/SQL » SQL & PL/SQL » Need Oracle Proc to return mult rows
Need Oracle Proc to return mult rows [message #245788] Mon, 18 June 2007 15:50 Go to next message
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 #245790 is a reply to message #245788] Mon, 18 June 2007 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW posting guidelines as enumerated in the STICKY posts at top of this forum.

http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Re: Need Oracle Proc to return mult rows [message #245818 is a reply to message #245790] Mon, 18 June 2007 21:46 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: ORA 29285 Write Access Error
Next Topic: CONNECT BY PRIOR
Goto Forum:
  


Current Time: Fri Dec 02 16:52:22 CST 2016

Total time taken to generate the page: 0.21005 seconds