Re: Accessing a weak refcursor

From: ddf <oratune_at_msn.com>
Date: Tue, 7 Sep 2010 09:29:25 -0700 (PDT)
Message-ID: <d3344736-f6cb-4dfe-9233-5dcb3e0636c5_at_u31g2000pru.googlegroups.com>



On Sep 7, 9:55 am, Knickerless Parsons <knickerlesspars..._at_gmail.com> wrote:
> 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 you'll have to
> change the parameters in the call at the bottom, any help would be
> appreciated, even a link to a good article:
>
> 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('DAVE','JOBS','JOB_NUMBER=''123''') ;
> END ;
> /

AFAIK you cannot implement dynamic variable declaration so as slick as the cursor is you cannot support selecting from it since you have no knowledge of the table columns beforehand.

David Fitzjarrell Received on Tue Sep 07 2010 - 11:29:25 CDT

Original text of this message