Systems Engineering and RDBMS

Inserting output records from stored procedure into a table in Oracle

Posted by decipherinfosys on July 7, 2009

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.

4 Responses to “Inserting output records from stored procedure into a table in Oracle”

  1. shyam said

    Excellent articles found on this site.. as well as Could you please update with latest and new enhancements in oracle 11g and sql server 2005

  2. Ravi said

    Thank you for detailed explanation of using REF CURSORS

  3. neeraj said

    i have created a procedure and have to insert it’s output in a table. but it’s not doing so with insert statement. there is no compile time error. but select statement says no rows selected.
    please help…..

  4. vivek said

    hi,
    below code is not working:
    create or replace procedure emp_dept_ref_cur(p_dept IN emp.deptno%TYPE)
    as
    lv_sysref sys_refcursor;
    lv_rec emp_dept%rowtype;
    begin
    open lv_sysref for select e.empno,e.ename,e.sal
    from emp e where e.deptno=p_dept;
    loop
    fetch lv_sysref into lv_rec;
    exit when lv_sysref%notfound;
    insert into emp_dept(empno,ename,sal) values(lv_rec.empno,lv_rec.ename,lv_rec.sal);
    end loop;
    close lv_sysref;
    end;

    where as when i take individual variables e.g. lv_empno emp.empno%type;
    lv_ename emp.ename%type; code is working fine…how can i achieve above functionality using %rowtype with refcursor kindly pls help…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: