dynamic sql problem

From: Gary M. Greenberg <garyg_at_gator.net>
Date: Mon, 11 Jan 1999 19:48:53 -0500
Message-ID: <369A9BF5.5AF18943@gator.net>

I'm trying to create a stored procedure that is similar to the SQL*PLUS command describe; my code compiles error-free but fails to produce the desired output. Here's the code (sans comments):

create or replace procedure desctab
(tname in user_tab_columns.column_name%TYPE) is
cur_id integer;
colname varchar2(30);
cdatatype varchar2(30);
cnullable varchar2(1);
v_num integer;
begin
cur_id := dbms_sql.open_cursor;
dbms_sql.parse(cur_id, 'select COLUMN_NAME, DATA_TYPE, NULLABLE ' ||   'from USER_TAB_COLUMNS where table_name = '||upper('''tname'''),   dbms_sql.native);

dbms_sql.define_column(cur_id,1,colname,30);
dbms_sql.define_column(cur_id,2,cdatatype,30);
dbms_sql.define_column(cur_id,3,cnullable,8);

v_num := dbms_sql.execute(cur_id);
LOOP
    if dbms_sql.fetch_rows(cur_id) = 0 then

        dbms_output.put_line('Empty cursor');
        exit;
    else
        dbms_sql.column_value(cur_id,1,colname);
        dbms_sql.column_value(cur_id,2,cdatatype);
        dbms_sql.column_value(cur_id,3,cnullable);
        dbms_output.put_line('Line: '||colname

||','||cdatatype||','||cnullable);

    end if;
END LOOP;
dbms_sql.close_cursor(cur_id);
dbms_output.put_line(upper(tname)||', '||'V_num: '||v_num||'.'); exception
when others then

dbms_sql.close_cursor(cur_id);
dbms_output.put_line('cursor '||cur_id||' closed in X-handler');
dbms_output.put_line('error ' ||SQLERRM || ' code '|| SQLCODE);

end;

When I execute from SQL*PLUS, the results look like this:

SQL> execute desctab('sales');
Empty cursor
SALES, V_num: 0.

PL/SQL procedure successfully completed.

SQL> This is my first attempt to use the dbms_sql package; anyone seeing what's wrong with the code wish to advise.

Replies by post &&|| email at your pleasure.

Thanks,
Gary

            -=- Avenue Programmers' Reference -=-    Class Hierarchy, Requests, and Hyperlinked Sample Scripts:

             http:/www.gator.net/~garyg/aveclass.htm Received on Mon Jan 11 1999 - 18:48:53 CST