2 Replies Latest reply on Feb 5, 2016 1:38 PM by usman_noshahi

    Debug "Table Function"

    usman_noshahi

      Hi,

      This question might be already on forum But I couldn't find anything regarding it.

       

      My question is: How to debug table function.

       

      For Example I have function in package

       

      Function Get_States (P_Parameter_1 In Number , P_Parameter_2 In Number ) Return Collection_type Pipelined ;
      

       

      And Inside the function I have some row piping algorithm lets say some calculations. When I try to debug it it gives me Error.

       

      Sql_Error.PNG

       

       

      Please guide me how to debug such Function.

        • 1. Re: Re: Debug "Table Function"
          usman_noshahi

          I apologize for incomplete details.

          So, here we are.


          Database Version.

           

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

          PL/SQL Release 12.1.0.2.0 - Production

          "CORE 12.1.0.2.0 Production"

          TNS for 64-bit Windows: Version 12.1.0.2.0 - Production

          NLSRTL Version 12.1.0.2.0 - Production

           

          SQL developer Version.

          Version 4.1.3.20

           

          I want to debug my code through SQL developer.


          My main concern to get know what's happening inside the "Table Function".

           

          I try to explain with Examples.

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

          table Used for Simple Procedure.

           

          CREATE TABLE CAPTURE_OBJECT
                 (
                        OBJ_NAME   VARCHAR2(500 BYTE),
                        SIZE_IN_MB NUMBER
                 ) ; 
          

           

           

          Package Specs

          create or replace PACKAGE DEBUG_TEST_PKG AS 
          
          
            Type T_Object Is Record (Obj_Name Varchar2(500) , Size_In_Mb Number )  ;    
            type t_PipeRow is table of t_object ; 
            
            Procedure Debug_Simple_Proc ( P_Owner In Varchar2 ) ; 
            Function  debug_pipelined_function ( P_Owner In Varchar2 ) return t_PipeRow pipelined ; 
          
          
          END DEBUG_TEST_PKG;
          
          
          
          

           

          Package Body

           

          create or replace PACKAGE BODY DEBUG_TEST_PKG AS
          
          
            Procedure Debug_Simple_Proc ( P_Owner In Varchar2 )  As        
              Type T_Collection Is Table Of Dba_Segments%rowtype Index By Binary_Integer ;
              T_Collections T_Collection ; 
            Begin
              Select  *
              Bulk    Collect
              into    T_Collections
              From   Dba_Segments
              Where  Segment_Type In ('TABLE')
              And Upper(Owner)         = Upper(P_Owner ) ;
               
                For I In 1..T_Collections.Count Loop
                   Insert Into Capture_Object values (T_Collections(I).SEGMENT_NAME , T_Collections(I).bytes) ; 
                end loop ;  
              
              
            END Debug_Simple_Proc;
          
          
            Function  debug_pipelined_function ( P_Owner In Varchar2 ) return t_PipeRow pipelined  AS
              Type T_Collection Is Table Of Dba_Segments%Rowtype Index By Binary_Integer ;
              T_Collections T_Collection ; 
              
              L_Rowout T_Object  ; 
                   
             
            Begin
              Select  *
              Bulk    Collect
              into    T_Collections
              From   Dba_Segments
              Where  Segment_Type In ('TABLE')
              And Upper(Owner)         = Upper(P_Owner ) ;
                        
              For i In 1..T_Collections.count 
              Loop    
                L_Rowout.obj_name   := T_Collections(I).segment_name;
                L_rowout.Size_In_Mb       := T_Collections(i).bytes;
                
                Pipe Row(L_rowout);
              End Loop;
              Return;
            END debug_pipelined_function;
          
          
          END DEBUG_TEST_PKG;
          
          
          
          
          
          

           

           

          Step 1:

           

          Try to debug the "Debug_Simple_Proc" to get know what's happening inside.

          then I get

          Debug_Simple_Proc.PNG

           

          So, in the Data Panel we can see all the Table indexes. we can check the values insides by expending them.

          if we query the table

          Select * From Capture_Object Where 1 = 1 ;
          

           

          We can get desired results.

           

          Step 2:

           

          Now I have some different scenario, I can't capture the object in a table. I have to show the results directly. lets say some Number of days and hours calculations of employees after some business logic.

          so the Function "DEBUG_TEST_PKG.debug_pipelined_function" can do the job for me.

           

          when I run the function

           

          SELECT * FROM TABLE (DEBUG_TEST_PKG.debug_pipelined_function('SYSTEM')) ;
          

           

          it gives me correct results.But I need to know what's happening inside the function. As we Could the see the values in "Debug_Simple_Proc"

          When I try to Debug it Gives me Error that I mention in starting of the post.

           

          thanks.

          • 2. Re: Re: Re: Debug "Table Function"
            usman_noshahi

            Here I found the solution.

            When I start debugging. it shows like this.

            Before.PNG

            So I just modify it


            DECLARE
              P_OWNER VARCHAR2(200);
              v_Return BIAS.DEBUG_TEST_PKG.T_PIPEROW;
            BEGIN
              P_OWNER := NULL;
            
            
                SELECT *
                       bulk collect into v_Return
              FROM TABLE(DEBUG_TEST_PKG.DEBUG_PIPELINED_FUNCTION(
                P_OWNER
              ));
              /* Legacy output: 
            DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
            */ 
              --:v_Return := v_Return;
            --rollback; 
            END;
            
            
            
            
            

             

             

            But Before this I noticed some points.

             

            1 -  When Ever I upgrade to new version, I just import preferences from previous version ( But this time I DID NOT import. just make new installation and configure debugger on Step Over. ). I am not Sure but this importing was somehow giving me error.

            2 -  Just After Creating new function in the package it's not shown in the debugging choice list of functions, So, I have restart SQL developer.

             

            Thanks.