1 Reply Latest reply: Apr 3, 2014 10:39 AM by rp0428 RSS

    Working with OUT Parameter of record type in procedure

    1037673

      Hi,

       

      My procedure is not displaying the out param(Record type) values while debugging the procedure. I am using SQL developer, Please advise me on the same....

       

      Procedure code is as :

      create or replace PACKAGE SimplePackage AS

       

        TYPE REC_ACCT IS RECORD(

          STOCK_LOCATION varchar2(50),

          PART_NO VARCHAR2(50 BYTE),

          STATION VARCHAR2(50 BYTE));

        --------------------------------------------------------------------

        TYPE TAB_ACCT IS TABLE OF REC_ACCT INDEX BY BINARY_INTEGER;

        ---------------------------------------------------------------------

        ---------------------------------

        TYPE V_TAB_ACCT IS RECORD(

          ACCT            VARCHAR(50 BYTE),

          ACCTRLSHP       TAB_ACCT);

       

            PROCEDURE proc1

            (   o_id           OUT  V_TAB_ACCT

            );

       

          END SimplePackage;

      /

      create or replace PACKAGE BODY SimplePackage AS

       

           

           PROCEDURE  proc1

              (

                  o_id           OUT  V_TAB_ACCT

              )

            AS

            BEGIN

            o_id.ACCT := '01';

            o_id.ACCTRLSHP(1).STOCK_LOCATION := 'MUM';

            o_id.ACCTRLSHP(1).PART_NO := 'P1';

            o_id.ACCTRLSHP(1).STATION := 'STATION1';

            END proc1;

       

          END SimplePackage;

        • 1. Re: Working with OUT Parameter of record type in procedure
          rp0428

          Thanks for posting the sample code but you always need to provide your full version for sql developer and tell us what platform you are on.

          My procedure is not displaying the out param(Record type) values while debugging the procedure. I am using SQL developer

          What does 'not displaying' mean?

           

          By default there will be no output from the execution but if you uncomment and modify the DBMS_OUTPUT line when you run/debug the code you will get output.

          DECLARE
            O_ID SCOTT.SIMPLEPACKAGE.V_TAB_ACCT;
          BEGIN

            SIMPLEPACKAGE.PROC1(
              O_ID => O_ID
            );
          DBMS_OUTPUT.PUT_LINE('O_ID = ' || O_ID.acct);
          END;

          I modified the anonymous block sql developer generates to output the 'acct' value.

          Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()

          O_ID = 01

          That output shows up in the LOG window and NOT the dbms_output window.

           

          Show us what is being executed.