0

I try to call a procedure from another procedure which returns a cursor as OUT and tries to insert the cursor data into a temp table without success.

create or replace PROCEDURE test_sp_1
(
    IN_LGVID            VARCHAR2,
    IN_CHNID            NUMBER,
    IN_PGMERCHANTID     VARCHAR2,
    IN_FROMDATE         DATE,
    IN_TODATE           DATE,
    IN_LEGALNAME        VARCHAR2,
    IN_DBANAME          VARCHAR2,
    IN_USERID           NUMBER,


  NEW_OUT_TXNREPOCURSOR OUT SYS_REFCURSOR,
  OUT_STATUSCODE      OUT NUMBER,
  OUT_STATUSDESC      OUT VARCHAR2 
)
AS

l_rec  tmp_emp%rowtype;
l_cur1 sys_refcursor;

BEGIN
   DBMS_OUTPUT.PUT_LINE('Testing');
   PRC_GET_AUTH_TXNS(IN_LGVID,IN_CHNID,NULL,TRUNC(IN_FROMDATE),TRUNC(IN_TODATE),NULL,NULL,'10870',NEW_OUT_TXNREPOCURSOR ,OUT_STATUSCODE,OUT_STATUSDESC);

  l_cur1:=NEW_OUT_TXNREPOCURSOR;

   loop
    fetch l_cur1 into l_rec;
    exit when l_cur1%notfound;
    INSERT INTO TMP_EMP(awlmcc,bsftaxamt) values(l_rec.awlmcc,l_rec.bsftaxamt);
    dbms_output.put_line(l_rec.awlmcc||'='||l_rec.bsftaxamt||'=');
    COMMIT;
   end loop;

END;
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 4
    Your procedure is committing, which you probably don't actually want; but is the GTT defined to [preserve rows on commit](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6)? I'm guessing not... (Your OUT ref cursor is going to be empty as you're consuming it, but you might not care...) – Alex Poole May 29 '19 at 11:10
  • [Possible duplicate](https://stackoverflow.com/q/36314084/266304) pending confirmation of the GTT definition. – Alex Poole May 29 '19 at 11:50

0 Answers0