Accessing a weak refcursor
Date: Tue, 7 Sep 2010 06:55:47 -0700 (PDT)
Message-ID: <e2d7c66b-de7d-4ef0-98e5-85dd9e59af89_at_h7g2000yqn.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 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 ;
- Get the next row
FETCH c_get_cols INTO w_get_cols ;
END LOOP ;
--
- 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 ;
-- -- 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 ;
/
Received on Tue Sep 07 2010 - 08:55:47 CDT