In one of our how to articles, we had covered how we can return records from a stored procedure to the calling program. In this blog post, we will extend it further to see how can we insert records returned from stored procedure into a table. We are going to use the same example that we had used in our how to document but for ease of understanding and reading we are showing it here again. Please create the following objects in your test schema.
CREATE TABLE Invoice
(
INVOICE_NUMBER NUMBER(9) NOT NULL,
INVOICE_DATE DATE NOT NULL,
CLIENT_ID NUMBER(9) NOT NULL,
INVOICE_AMT NUMBER(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
/
CREATE SEQUENCE INVOICE_SEQ
START WITH 1
CACHE 100
/
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,101,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,102,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,103,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,104,1100.00);
/* Create package */
CREATE OR REPLACE PACKAGE types
AS
type cursorType is ref cursor;
END;
/
/* Here we have declared cursor variable of type cursorType as an output variable.*/
CREATE OR REPLACE PROCEDURE DEC_RTN_RECORDSET
(
p_InvoiceDate IN DATE,
p_ResultSet OUT TYPES.cursorType
)
AS
BEGIN
OPEN p_ResultSet FOR
SELECT Invoice_Number, Invoice_Date, Client_ID, Invoice_Amt
FROM Invoice
WHERE Invoice_date <= p_InvoiceDate
ORDER BY Invoice_number;
END DEC_RTN_RECORDSET;
/
Since we also want to insert into another table, we will create new table as well to store the returned records.
CREATE TABLE Invoice_New
(
INVOICE_NUMBER NUMBER(9) NOT NULL,
INVOICE_DATE DATE NOT NULL,
CLIENT_ID NUMBER(9) NOT NULL,
INVOICE_AMT NUMBER(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/1 paid
CONSTRAINT PK_INVOICE_NEW PRIMARY KEY(INVOICE_NUMBER)
)
/
Now instead of returning record set from stored procedure to calling program, we will write small pl/sql block to insert the records into new table Invoice_New.
DECLARE
v_resultSet TYPES.CURSORTYPE;
v_Inv_no Invoice.Invoice_number%type;
v_Inv_dt Invoice.Invoice_date%type;
v_Inv_amt Invoice.Invoice_Amt%type;
v_clnt_id Invoice.Client_Id%type;
BEGIN
/* Call the stored procedure*/
DEC_RTN_RECORDSET(sysdate, v_resultSet);
LOOP
FETCH v_resultSet INTO v_Inv_no,v_inv_Dt,v_clnt_id,v_inv_amt;
EXIT WHEN v_Resultset%NOTFOUND;
dbms_output.put_line(‘Invoice # = ‘ || v_inv_no);
/* Now insert it into the table.*/
INSERT INTO INVOICE_NEW(Invoice_Number,Invoice_Date,Client_id,Invoice_Amt)
VALUES (v_Inv_no,v_Inv_dt,v_clnt_id,v_Inv_amt);
END LOOP;
close v_resultset;
commit;
EXCEPTION
WHEN OTHERS THEN
raise_Application_Error(-20001,’Inv_No=’||v_inv_no||’–>’||SQLERRM);
END;
/
In above PL/SQL block, we haven’t opened the cursor. After procedure call was done, we straightaway started fetching records into local variables. This is because, cursor is already opened in the procedure itself using ‘OPEN … FOR’ syntax. Since we are returning multiple records, we are putting it into the loop to fetch records one by one and inserting into new table. Once execution is done, examine invoice_new table. You will find all the records from invoice table into this new table.
Now what we will do if we have to test such a procedure. Let us recap this with the small testing block. Connect to SQL*Plus and execute following block.
VARIABLE resultSet REFCURSOR
EXEC DEC_RTN_RECORDSET(sysdate, :resultSet);
PRINT :resultSet
The above block will return the records and will display it in SQL*Plus session. Procedure can be called from other calling programs as well in such case, data will be returned to the calling program.
