-
SQL query
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,
Giani
-
Adjust you query as follows:
Code:
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
Last edited by jovery; 03-05-2003 at 11:15 AM.
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
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
Giani
-
You are getting that error because you table list exceeds the maximum size of the string you have defined.
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
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
Giani
-
The following should get round the problem (a bit sloppy I'm afraid)
Code:
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;
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
That's better than nothing - Thanks again Jim for your help.
Regards,
Giani
-
i wonder if "varchar2(30000)" is part of the problem. varchar2 only goes to 4000.
-
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)
Giani
-
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...
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|