Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    8

    Question Unanswered: how to see results of select query in proc

    i have a database where i want to find out which tables have records where the value for a particular field = X. for example, i want to say, show me all tables that have a field called X with a value of Y. i thought to do this by using a procedure that creates a cursor w/ results from a select statement on all_tab_columns and then a for loop going through and executing a select statement for each table. however, i now see that the results of a select statement don't display when the procedure executes. is there any easy way to get this to work w/ my current approach, or do i need to do something completely different. here's my procedure...

    create or replace procedure check_ptid(ptid number) as

    cursor ptid_tables is select table_name from all_tab_columns where column_name = 'PTID' and table_name like '%99';
    ptid_table ptid_tables%ROWTYPE;
    sqlstr varchar(1000);

    begin
    open ptid_tables;
    loop
    fetch ptid_tables into ptid_table;
    EXIT WHEN ptid_tables%NOTFOUND;
    sqlstr := 'select '''|| ptid_table.table_name ||''', count(*) from ' || ptid_table.table_name || ' where PTID=' || ptid;
    execute immediate (sqlstr);
    end loop;
    end;

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    before running: set serveroutput on

    inside procedure:
    dbms_output.putline
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, a complete service; your procedure was modified a little bit in order to show something in my Scott schema:
    Code:
    DECLARE
       CURSOR ptid_tables
       IS
          SELECT table_name
            FROM user_tab_columns
           WHERE column_name = 'DEPTNO' AND table_name LIKE '%';
    
       ptid_table   ptid_tables%ROWTYPE;
       sqlstr       VARCHAR (1000);
       l_table      tab.tname%TYPE;
       l_cnt        NUMBER;
    BEGIN
       OPEN ptid_tables;
    
       LOOP
          FETCH ptid_tables
           INTO ptid_table;
    
          EXIT WHEN ptid_tables%NOTFOUND;
          sqlstr :=
                'select '''
             || ptid_table.table_name
             || ''', count(*) from '
             || ptid_table.table_name;
    
          EXECUTE IMMEDIATE (sqlstr) INTO l_table, l_cnt;
          dbms_output.put_line(l_table ||': '|| l_cnt);
       END LOOP;
    END;

  4. #4
    Join Date
    Sep 2002
    Posts
    8
    very cool. execute immediate ... into ... was just what i needed. thanks.

Posting Permissions

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