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 ;
/

On Sep 7, 9:55*am, Knickerless Parsons <knickerlesspars... (AT) gmail (DOT) com>
wrote:
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

On Sep 7, 6:55*am, Knickerless Parsons <knickerlesspars... (AT) gmail (DOT) com>
wrote:
....

This kind of thing is out of my comfort zone, but see
http://www.morganslibrary.com/hci/hci003.html

jg
--
@home.com is bogus.
http://www.marketwatch.com/story/ora...irm-2010-09-07

On Sep 7, 5:45*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Thanks for the pointers, I've had a look at the article and it looks
like it'll do what I want but it only applies to Oracle 11g and I'm
stuck with 10! So I'll probably shelve this until we upgrade as I'm in
danger of spending more time trying to be sexy than if I wasn't!

Thanks again for your input.

Dave.