Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Fetching TABLE_NAME

    I try to make a select statement based on the results of another request, but it seems to me, that Oracle don't use the information gathered.

    I can output the result into a dbms_output, but if I remove the -- in the end, the SQL fails. How do I solve that problem?

    Example:

    set serveroutput on;

    declare
    cursor table_cur is
    select
    table_name
    from
    sys.all_tables
    where
    owner = 'SCOTT' and
    rownum < 5;

    const_table varchar2(30) ;
    count_tables number(10) ;

    begin
    select
    count(*) into count_tables
    from
    sys.all_tab_columns
    where
    owner = 'SCOTT' and
    rownum < 5;

    open table_cur;
    for tab in 1..count_tables loop
    fetch table_cur into const_table;

    dbms_output.put_line ('TABLE = ' ||const_table);


    -- select count(*) from const_table ;

    end loop; -- tab

    end;
    /

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by kfc@vd.dk
    I can output the result into a dbms_output, but if I remove the -- in the end, the SQL fails. How do I solve that problem?/
    const_table is obviously not the name of a table but the name of your variable.

    You need to create a String that contains your SELECT count(*) and then use dynamic SQL (EXECUTE IMMEDIATE) to run that statement.

Posting Permissions

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