SQL query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: SQL query

  1. #1
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133

    Unhappy 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

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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 10: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!

  3. #3
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    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

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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!

  5. #5
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    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

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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!

  7. #7
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133

    Thumbs up

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

    Regards,
    Giani

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    i wonder if "varchar2(30000)" is part of the problem. varchar2 only goes to 4000.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    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

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width