Forum Stats

  • 3,769,359 Users
  • 2,252,957 Discussions
  • 7,875,002 Comments

Discussions

ORA-00942, table does not exist

User_Q6JRK
User_Q6JRK Member Posts: 44 Blue Ribbon
edited Aug 24, 2021 9:30AM in SQL & PL/SQL

Hi,

I'm trying to do a pl/sql with two loops. Inside second loop it gives me error:

ORA-00942.

I do the query manually and I don't have any error. Someone could help me please?

Thanks



begin

         DBMS_OUTPUT.PUT_line('begin' );

 for i in ( select table_name from user_tables where table_name like 'A%002_AJOB') loop

         dbms_output.put_line(i.table_name);

         DBMS_OUTPUT.PUT_LINE('La tabla es ... ' || i.table_name );

    for j in ( select job from i.table_name where status='Wait' ) loop

       dbms_output.put_line('Job is....' || j.job);

    end loop;

 end loop;

end;

/

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 675 Silver Trophy
    create or replace function fu(some_uniq number) return varchar2 sql_macro as
     qy varchar2(32766);
    begin
     select listagg('select '''|| replace(tc.table_name,'''','''''') ||''' tbl, count(*) from "'||tc.table_name||'"', ' union all ')
            within group (order by tc.table_name)
       into qy
       from all_tab_columns tc
       where tc.owner='SYS' and tc.column_name='JOB_NAME'
         and table_name not like '%LOG' and table_name not like '%RUN%'
         and rownum <= 10;
     return qy;
    end;
    /
    
    select * from fu(123);
    
    TBL                            COUNT(*)
    ----------------------------- ----------
    ALL_QUEUE_SCHEDULES                   0
    ALL_REFRESH                           0
    ALL_REFRESH_CHILDREN                  0
    ALL_SCHEDULER_JOBS                   23
    ALL_SCHEDULER_JOB_ARGS                0
    ALL_SCHEDULER_JOB_DESTS              23
    ALL_SCHEDULER_NOTIFICATIONS           0
    ALL_SCHEDULER_REMOTE_JOBSTATE         0
    CDB_QUEUE_SCHEDULES                   0
    USER_DATAPUMP_JOBS                    0
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond
    edited Aug 24, 2021 10:15AM


    What User_H3J7U hasn't explained to you is that your query doesn't work because you are expecting PL/SQL to bind the value of your i.table_name into another query, but bind variables only work for "values" , not for "objects" and the table name of the select query is an object, not a value. i.e. you can't reference objects dynamically in static SQL.

    See the community document: https://community.oracle.com/tech/developers/discussion/4417551/pl-sql-101-substitution-vs-bind-variables

    So, User_H3J7U has used one of the new features of SQL which is a SQL Macro function designed to allow a dynamic query to be generated from which you can then select and Oracle will apply the resultant query as if it were a subquery.

    If you don't have a recent version of the database that supports SQL Macros, then there are other techniques, which is why it's important you specify what version of the database you are using when you ask questions.

    One of the old methods was to do something like:

    SQL> select
      2    table_name,
      3    to_number(
      4      extractvalue(
      5        xmltype(
      6 dbms_xmlgen.getxml('select count(*) c from '||table_name))
      7        ,'/ROWSET/ROW/C')) count
      8  from user_tables
      9 where iot_type != 'IOT_OVERFLOW'
     10 or    iot_type is null;
    
    
    TABLE_NAME                      COUNT
    ------------------------------ ------
    DEPT                                4
    EMP                                14
    BONUS                               0
    SALGRADE                            5
    

    Though this does use some deprecated functionality such as "extractvalue".

    A more recent version would be something like:

    select table_name, t.cnt as cnt
    from   user_tables,
           xmltable('/ROWSET/ROW'
                    passing dbms_xmlgen.getxmltype('select count(*) as cnt from '||table_name)
                    columns cnt number path '/ROW/CNT'
                   ) t
    


  • User_H3J7U
    User_H3J7U Member Posts: 675 Silver Trophy
    select table_name,
      xmlcast(xmlquery(('count(collection("oradb:/'||user||'/'||table_name||'"))')
              returning content) as number) cnt
    from user_tables
    where iot_name is null and temporary='N' and secondary ='N' and nested = 'NO'
      and num_rows < 1000;
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,916 Red Diamond
    edited Aug 24, 2021 12:49PM

    @BluShadow: Why overcomplicate things with all that overhead of using XML. All OP needs it cursor variable. Source data:

    SQL> select * from A1002_AJOB;
    
    JOB       STAT
    --------- ----
    PRESIDENT
    MANAGER   Wait
    CLERK     Wait
    
    SQL> select * from A2002_AJOB;
    
    JOB       STAT
    --------- ----
    ANALYST   Wait
    MANAGER
    CLERK
    CLERK     Wait
    
    SQL>
    

    Now:

    set serveroutput on
    declare
        v_job varchar2(20);
        v_refcur sys_refcursor;
        cursor v_cur
          is
            select  table_name
              from  user_tables
              where table_name like 'A%002_AJOB';
    begin
         for v_rec in v_cur loop
           dbms_output.put_line(v_rec.table_name);
           open v_refcur for 'select job from ' || v_rec.table_name || q'[ where status = 'Wait']';
           loop
             fetch  v_refcur
               into v_job;
             exit when v_refcur%notfound;
             dbms_output.put_line('Job is....' || v_job);
           end loop;
           close v_refcur;
        end loop;
    end;
    /
    A1002_AJOB
    Job is....MANAGER
    Job is....CLERK
    A2002_AJOB
    Job is....ANALYST
    Job is....CLERK
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    SY,

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond

    Indeed Solomon, there are many ways for it to be done.

    The point is for the OP to understand why their original query wasn't working, and how "dynamic" queries are needed, but do vary by version etc.. 😉