3 Replies Latest reply on Aug 21, 2015 9:03 AM by Manjusha Muraleedas

    Issue while executing procedure using run oprtion - SQL developer Version 4.1.1.19

    Manjusha Muraleedas

      hi,

       

      I am getting the following error when trying to call the following procedure through SQL developer Version 4.1.1.19.

       

      Database version :Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

       

      This error will come only when we run the procedure using RUN option(CNTRL+F10) of package/procedure.

      When the procedure is executed by writing stub in SQL WORKSHEET , it will run successfully and return the result.

      Please let me know whether any work around is available for this issue.

       

      Error:

      Connecting to the database LLLD.

      Invalid column index

      Process exited.

      Disconnecting from the database LLLD.

       

       

      CREATE PROCEDURE new_procedure           (
      
                  O_LIST_REF             OUT  NOCOPY SYS_REFCURSOR,
      
                   O_RTRN_CD                     OUT NOCOPY number,
      
                   O_ERR_NBR                     OUT NOCOPY VARCHAR2,
      
                   O_ERR_DESC                    OUT NOCOPY VARCHAR2,
      
                   O_ERR_OBJECT_NAME             OUT NOCOPY VARCHAR2
      
                 )
      
                 as
         BEGIN
      
                 OPEN O_LIST_REF FOR SELECT ......;        
      
        EXCEPTION  
      
              WHEN NO_DATA_FOUND  THEN
      
                   O_RTRN_CD         := 7;
      
                   O_ERR_NBR         := SQLCODE;
      
                   O_ERR_DESC        := SUBSTR (SQLERRM, 1, 100);
      
                   O_ERR_OBJECT_NAME := 'new_procedure';  
      
             WHEN OTHERS         THEN
      
                   O_RTRN_CD         := 4;
      
                   O_ERR_NBR         := SQLCODE;
      
                   O_ERR_DESC        := SUBSTR (SQLERRM, 1, 100);
      
                   O_ERR_OBJECT_NAME := 'new_procedure';
      
        END new_procedure;
      
      

       

       

        Stub:

        DECLARE
         O_LIST_REF SYS_REFCURSOR;
          O_RTRN_CD NUMBER;
          O_ERR_NBR NUMBER;
          O_ERR_DESC VARCHAR2(200);
          O_ERR_OBJECT_NAME VARCHAR2(200);
        BEGIN
      
         new_procedure(
              O_LIST_REF =>   O_LIST_REF ,
            O_RTRN_CD => O_RTRN_CD,
            O_ERR_NBR => O_ERR_NBR,
            O_ERR_DESC => O_ERR_DESC,
            O_ERR_OBJECT_NAME => O_ERR_OBJECT_NAME
          );
          DBMS_SQL.RETURN_RESULT(   O_LIST_REF );
          dbms_output.put_line( O_RTRN_CD);
          dbms_output.put_line(O_ERR_NBR);
           dbms_output.put_line(O_ERR_DESC);
            dbms_output.put_line( O_ERR_OBJECT_NAME );
        
        END;
      
      

       

      Thank you.

      Manjusha