Q

ORA-01422 error when procedure returns more than one row

I have the following Oracle procedure that returns one or more rows depending upon the input into the SQL statement. When there is greater than one row returned I get the ORA-01422 error.

Mr. Williams, I have the following Oracle procedure that returns one or more rows depending upon the PO ID input

into the SQL statement. When there is greater than one row returned I get the ORA-01422 error. How do I recode this procedure to allow for one 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. The proc is supposed to return all vouchers associated with this PO. There may be one voucher, there may be five vouchers.

Please be advised I am not a DBA, I am a pure novice at Oracle procedures, but willing to learn. Thank you for any assistance you may provide.

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;

The procedure you have created is using a SQL that should only be used if you are expecting to return only a single row. If you are expecting to return more then one row, you should use a cursor. I have added a cursor called c1 (see the second procedure). The second procedure uses a loop to read through the selected records.
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';
   close;
       
   DBMS_OUTPUT.put_line (po);
   DBMS_OUTPUT.put_line (batch);
   DBMS_OUTPUT.put_line (voucher);
   DBMS_OUTPUT.put_line (invoice);
END testpnet;

/************* Cursor **************/

CREATE OR REPLACE PROCEDURE testpnet (
   po        OUT   VARCHAR,
   batch     OUT   VARCHAR2,
   voucher   OUT   VARCHAR2,
   invoice   OUT   VARCHAR2
)
AS

cursor c1 is
   select po_id, grp_ap_id, voucher_id, invoice_id
   from ps_voucher
   where po_id = '0700185'; 

BEGIN

   open c1;
   loop
       fetch c1 into po, batch, voucher, invoice;
       exit when c1%NOTFOUND;
       <do something>
   end loop;
   close;
       
   DBMS_OUTPUT.put_line (po);
   DBMS_OUTPUT.put_line (batch);
   DBMS_OUTPUT.put_line (voucher);
   DBMS_OUTPUT.put_line (invoice);
END testpnet;
This was first published in July 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close