Re: sqlplus dbms_output

From: <fitzjarrell_at_cox.net>
Date: 19 Sep 2006 11:22:48 -0700
Message-ID: <1158690168.708692.105150@k70g2000cwa.googlegroups.com>

soalvajavab1_at_yahoo.com wrote:
> Why I do not see the output of the following procedure in sqlplus??? As
> you see I execute serveroutput on command at beginnig but still it does
> not display the result, Please advise>
>
> set serveroutput on
> set serveroutput on size 200000
> declare
> begin
> for tab in (select table_name from user_tables where table_name =
> '\"BMA_Chemical\"')
> loop
> dbms_output.put_line(' create table
> '||tab.table_name||'(');
> for col in (select column_name,data_type,data_length
> from user_tab_columns where table_name = tab.table_name)
> loop
> dbms_output.put_line(col.column_Name||'
> '||col.data_type||' ('||col.data_length || '),');
> end loop;
> dbms_output.put_line('dummy date)');
> end loop;
> end;

Double quotes aren't necessary when querying the table name from USER_TABLES:

SQL> set serveroutput on
SQL> set serveroutput on size 200000
SQL> declare
  2  begin
  3     for tab in (select table_name from user_tables where table_name
= 'BMA_Chemical')
  4        loop
  5               dbms_output.put_line(' create table
'||tab.table_name||'(');
  6               for col in (select column_name,data_type,data_length
  7               from user_tab_columns where table_name =
tab.table_name)
  8                   loop
  9                         dbms_output.put_line(col.column_Name||'
'||col.data_type||' ('||col.data_length || '),');
 10                  end loop;
 11       dbms_output.put_line('dummy date)');
 12        end loop;

 13 end;
 14 /
create table BMA_Chemical(
CAS_NO VARCHAR2 (20),
IUPAC_NAME VARCHAR2 (400),
MOLWT NUMBER (22),
CHEM_DESC VARCHAR2 (400),
FORMULA VARCHAR2 (400),
dummy date)

PL/SQL procedure successfully completed.

SQL> Get rid of the " and the escape backslashes; they're only causing no records to be returned, as Sybrand has already mentioned.

David Fitzjarrell Received on Tue Sep 19 2006 - 13:22:48 CDT