Accessing a weak Refcursor

From: Knickerless Parsons <knickerlessparsons_at_gmail.com>
Date: Tue, 7 Sep 2010 06:45:22 -0700 (PDT)
Message-ID: <1d38ccc8-720e-4ada-8222-ff0f3e3ca4a9_at_f42g2000yqn.googlegroups.com>

Hi all,
I'm trying to write a clever stored procedure that I can pass in a table name and a where clause, and it'll display all matching rows in a firendly manner.

For example:

• Row 1 ==== name : fred age : 21
• Row 2 ==== name : John age :35

I've got to the stage where I can generate the required SQL and I'll open a weakly typed refcursor to fetch the data but I'm stuck on how to get the data into variables that I can display or process.

All of the examples I've found seem to assume that the table structure is always the same and they declare variables up front which are used to store the returned data. I need a more dynamic approach.

The anonymous block below is what I currently have, any help would be appreciated:

P.S. I'm aware that all_tables won't work on objects across a DB link and I'm willing to live with that unless there's an easy fix. I'm also expecting specify the where clause as the primary key in most cases so it won't be returning 100s of rows.

DECLARE    --

• Temp routine just to wrap long strings in SQL PLUS. PROCEDURE wrap(p_in IN VARCHAR2, p_wrap IN NUMBER) IS BEGIN FOR i IN 1 .. CEIL(LENGTH(p_in)/p_wrap) LOOP DBMS_OUTPUT.PUT_LINE(SUBSTR(p_in,1+((i-1)*p_wrap),p_wrap)) ; END LOOP ; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Cant render output!!') ; END wrap ;

   --

• Display matching rows from a given table PROCEDURE showme (p_owner IN VARCHAR2, p_table IN VARCHAR2, p_where IN VARCHAR2) IS
  --
  
  • Cursor to get columns for the specified table CURSOR c_get_cols (pc_owner IN VARCHAR2, pc_table IN VARCHAR2) IS SELECT owner, table_name, column_name FROM all_tab_columns WHERE owner = pc_owner AND table_name = pc_table ORDER BY column_id ; w_get_cols c_get_cols%ROWTYPE ;
    --
    
  • Cursor to get the required data w_get_data SYS_REFCURSOR ;
    --
    
  • Somewhere to store generated SQL w_sql VARCHAR2(2000) ; BEGIN
    --
    
  • Try to build the SQL to retrieve the desired row w_sql := 'SELECT ' ;
    --
    
  • Open the cursor to retrieve the column names OPEN c_get_cols(p_owner, p_table) ; FETCH c_get_cols INTO w_get_cols ; IF c_get_cols%FOUND THEN WHILE c_get_cols%FOUND LOOP w_sql := w_sql || w_get_cols.column_name || ', ' ; --
    • Get the next row FETCH c_get_cols INTO w_get_cols ; END LOOP ; --
      • Strip off the trailing comma and space w_sql := SUBSTR(w_sql,1,LENGTH(w_sql) -2) ; --
      • Add the from and where cluases w_sql := w_sql || ' FROM ' || p_owner || '.' || p_table || ' WHERE ' || p_where ;

         --
         -- Diagnostic to display the SQL
         DBMS_OUTPUT.PUT_LINE('Generated the following SQL...') ;
         wrap(w_sql,80) ;

         OPEN w_get_data FOR w_sql ;

         -- ????????????????????
         CLOSE w_get_data ;

      ELSE
         DBMS_OUTPUT.PUT_LINE('Table ' || p_owner || '.' || p_table ||
' not found!') ;
      END IF ;
      CLOSE c_get_cols ;
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('[' || SQLERRM || ']') ;

   END showme ;

BEGIN
   showme('WMS','JOBS','JOB_NUMBER=''123''') ; END ;
/ Received on Tue Sep 07 2010 - 08:45:22 CDT