Re: dynamic sql problem

Hi,

Gary M. Greenberg wrote:
>
> 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);

When you try to print what you will parse to the cursor like this ... (with the declaration... help varchar2(100);)

help := 'select COLUMN_NAME, DATA_TYPE, NULLABLE ' ||   'from USER_TAB_COLUMNS where table_name = '||upper('''tname'''); dbms_output.put_line(help);

you will see ...

select COLUMN_NAME, DATA_TYPE, NULLABLE from USER_TAB_COLUMNS where table_name = 'TNAME'

what should not be as you probably want. So try this...

help := 'select COLUMN_NAME, DATA_TYPE, NULLABLE ' ||   'from USER_TAB_COLUMNS where table_name = upper('''||tname||''')'; dbms_output.put_line(help);
dbms_sql.parse(cur_id, help,dbms_sql.native);

and the result will be

select COLUMN_NAME, DATA_TYPE, NULLABLE from USER_TAB_COLUMNS where table_name = upper('sales')

> 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||'.');

According this number V_num ... you probably expect number of fetched row... But only if the SQL statement is an INSERT, UPDATE or DELETE, the execution command returns the number of processed rows. So in your case it is not a number of selected rows.  

> 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 Tue Jan 12 1999 - 03:08:35 CST