SQL query

[Giani]

Hi DBAs,

In order to take an export of some few tables of a schema I want to write a SQL query or in PL/sql which will give the result like below in one line:

table1, table2, table3, etc...

I have tried something like the following but doesn't seems to work - it returns me the last row only:
SQL>l
1 declare
2 cursor c1 is select table_name from dba_tables where owner='TROPP' ;
3 tname varchar2(30000):= null ;
4 begin
5 for i in c1
6 loop
7 tname:= i.table_name||',';
8 end loop;
9 dbms_output.put_line(tname);
10* end;
SQL>/
W_RATE,

Any idea how to achieve this?

TIA

Regards,

[jovery]

Adjust you query as follows:

declare
  cursor c1 is select table_name from dba_tables where owner='TROPP' ;
  tname varchar2(30000):= null ;
begin
  for i in c1
    loop
      tname:= tname||i.table_name||',';
    end loop;
  dbms_output.put_line(tname);
end;

However, you will need to put a loop in to keep the output line below 256 characters which is the maximum for dbms_output.put_line

HTH

[Giani]

Thanks Jim, but I've tried already that way but I am getting error:

SQL>declare

  2    cursor c1 is select table_name from dba_tables where owner='TROPP' ;

  3    tname varchar2(30000):= null ;

  4  begin

  5    for i in c1

  6      loop

  7        tname:= tname||i.table_name||',';

  8      end loop;

  9    dbms_output.put_line(tname);

 10  end;

 11  /

declare

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "SYS.DBMS_OUTPUT", line 57

ORA-06512: at line 9

[jovery]

You are getting that error because you table list exceeds the maximum size of the string you have defined.

[Giani]

Still no luck :

SQL>declare
2 cursor c1 is select table_name from dba_tables where owner='TROPP' ;
3 tname varchar2(256):= null ;
4 begin
5 for i in c1
6 loop
7 tname:= tname||i.table_name||',';
8 end loop;
9 dbms_output.put_line(tname);
10 end;
11 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7

[jovery]

The following should get round the problem (a bit sloppy I'm afraid)

declare
  cursor c1 is select table_name from dba_tables where owner='TROPP' ;
  tname varchar2(250):= null ;
begin
  for i in c1
    loop
      if length(tname) > 200 then
        dbms_output.put_line(tname);
        tname := null;
      end if;
      tname := tname||i.table_name||',';
    end loop;
  dbms_output.put_line(tname);
end;

[Giani]

That's better than nothing - Thanks again Jim for your help.

Regards,

[slimdave]

i wonder if "varchar2(30000)" is part of the problem. varchar2 only goes to 4000.

[Giani]

Originally posted by slimdave
i wonder if "varchar2(30000)" is part of the problem. varchar2 only goes to 4000.

Dave,

I don't know if you right but here is my test :
1 declare
2 cursor c1 is select table_name from dba_tables where owner='TROPP' ;
3 tname varchar2(50000):= null ;
4 begin
5 for i in c1
6 loop
7 tname:= i.table_name||',';
8 end loop;
9 dbms_output.put_line(tname);
10* end;
SQL>/
tname varchar2(50000):= null ;
*
ERROR at line 3:
ORA-06550: line 3, column 10:
PLS-00215: String length constraints must be in range (1 .. 32767)

[marist89]

Originally posted by slimdave
i wonder if "varchar2(30000)" is part of the problem. varchar2 only goes to 4000.

The limit for VARCHAR2 in PL/SQL is 32K. Yes, it's stupid that the table can only hold 4000 characters and you can manipulate up to 32K in memory, but that's the way it is.

I think that's an OCP Question, which, of course, you wouldn't know with you being from the School of Hard Knocks and all...