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;
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;
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.