Home » SQL & PL/SQL » SQL & PL/SQL » Cursor reffering tablename (Oracle SQL*Plus 10.1.0.4.2)
Cursor reffering tablename [message #563250] Fri, 10 August 2012 08:30 Go to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi All,

My objective is to select the table names from all_tables table where the number of rows is greater than zero.
Use this table name and choose a single record from those tables.
Below is the code i am trying. but it throws me an error as
Quote:

ERROR at line 9:
ORA-06550: line 9, column 24:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 2:
PL/SQL: SQL Statement ignored


DECLARE 
    CURSOR c1 IS 
      SELECT owner 
             ||'.' 
             ||table_name table_name 
      FROM   all_tables 
      WHERE  owner = 'ARCHIVE' 
             AND num_rows > 0 
             AND ROWNUM <= 4; 
    cur_rec c1%ROWTYPE; 
BEGIN 
    OPEN c1; 

    FETCH c1 INTO cur_rec; 

    dbms_output.Put_line(cur_rec.table_name); 

    SELECT * 
    FROM   cur_rec.table_name 
    WHERE  ROWNUM = 1; 

    CLOSE c1; 
END; 
/


Much appreciate your quick response.

Thanks in advance
Re: Cursor reffering tablename [message #563253 is a reply to message #563250] Fri, 10 August 2012 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member

The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.
Re: Cursor reffering tablename [message #563258 is a reply to message #563250] Fri, 10 August 2012 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you use "SELECT *", you don't know the number and type of columns, you have to use DBMS_SQL.

Quote:
Use this table name and choose a single record from those tables.


And what do you want to do with this row?

Regards
Michel
Re: Cursor reffering tablename [message #563261 is a reply to message #563253] Fri, 10 August 2012 10:23 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi Thanks Swan,

With respect to your reply tried the same way and the error was the same as in my previous message.

BlackSwan wrote on Fri, 10 August 2012 21:11

The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.


i tried the code in a different way as below but got the error as below
Quote:
select 'x' into v from (select cur_rec.table_name from all_tables) a where a.cur_rec.table_name.arch_ts < (sysdate-730) and rownum=1;
*
ERROR at line 17:
ORA-06550: line 17, column 76:
PL/SQL: ORA-00904: "A"."CUR_REC"."TABLE_NAME"."ARCH_TS": invalid identifier
ORA-06550: line 17, column 1:
PL/SQL: SQL Statement ignored

DECLARE 
    CURSOR c1 IS 
      (SELECT owner 
              ||'.' 
              ||table_name table_name 
       FROM   all_tables 
       WHERE  owner = 'ARCHIVE' 
              AND num_rows > 0 
              AND ROWNUM <= 4); 
    cur_rec c1%ROWTYPE; 
    v       VARCHAR2(50); 
    v_sql   VARCHAR2(32767); 
BEGIN 
    FOR a1 IN c1 LOOP 
        dbms_output.Put_line(cur_rec.table_name); 

        select 'x' into v from (select cur_rec.table_name from all_tables) a 
where rownum=1 where a.cur_rec.table_name.arch_ts < (sysdate-730) and rownum=1'; 
        dbms_output.Put_line(v); 

        IF v = 'x' THEN 
          INSERT INTO archive_data_tables 
                      (arch_table_name, 
                       category, 
                       insert_user, 
                       insert_date) 
          VALUES      (cur_rec.table_name, 
                       'ARCHIVE', 
                       'DBA.ADMIN', 
                       SYSDATE); 
        ELSE 
          dbms_output.Put_line('no data found'); 
        END IF; 
    END LOOP; 
END; 
/


please advice.

Thanks
Dhivya

[Updated on: Fri, 10 August 2012 10:55] by Moderator

Report message to a moderator

Re: Cursor reffering tablename [message #563262 is a reply to message #563261] Fri, 10 August 2012 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
EXECUTE IMMEDIATE must be part of the solution!
Please consider to Read The Fine Manual regarding EXECUTE IMMEDIATE
Re: Cursor reffering tablename [message #563350 is a reply to message #563253] Mon, 13 August 2012 02:31 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi Team,

Thanks that did work for some case. but my requirement was not satisfied completely.
I have modified the code but still i couldn achieve what i want.
Please find the code below
DECLARE 
    CURSOR c1 IS 
      (SELECT owner 
              ||'.' 
              ||table_name table_name 
       FROM   all_tables 
       WHERE  owner = 'ARCHIVE' 
              AND num_rows > 0 
              AND ROWNUM <= 4); 
    cur_rec c1%ROWTYPE; 
    v       VARCHAR2(50); 
    v_sql   VARCHAR2(32767); 
BEGIN 
    FOR a1 IN c1 LOOP 
        dbms_output.Put_line(a1.table_name); 

        SELECT 'x' 
        INTO   v 
        FROM   (SELECT a1.table_name 
                FROM   all_tables) a 
        WHERE  [size=5][b][color=orangered]A.arch_ts < [/b]( SYSDATE - 730 )[/color] [/size]
               AND ROWNUM = 1; 

        dbms_output.Put_line(v); 

        IF v = 'x' THEN 
          INSERT INTO archive_data_tables 
                      (arch_table_name, 
                       category, 
                       insert_user, 
                       insert_date) 
          VALUES      (a1.table_name, 
                       'ARCHIVE', 
                       'DBA.ADMIN', 
                       SYSDATE); 
        ELSE 
          dbms_output.Put_line('no data found'); 
        END IF; 
    END LOOP; 
--close c1; 
END; 
/ 


Through the above code i am trying to collect all the table names from ALL_TABLES table which has num_rows>0 and then after fetching all table names am trying to query each table which has arch_ts(Date column) < (sysdate-730).if exists it will insert the table name into the table ARCHIVE_DATA_TABLES.
The problem here is when i use the column ARCH_TS which is available in all the individual tables not in all_tables.
I am not sure how to query the individual tables with the condition ARCH_TS < (SYSDATE-730)

The error message i get when i run the query is

Quote:

select 'x' into v from (select a1.table_name from all_tables) a where A.ARCH_TS < (sysdate-730) and rownum=1;
*
ERROR at line 10:
ORA-06550: line 10, column 73:
PL/SQL: ORA-00904: "A"."ARCH_TS": invalid identifier
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored


Please Advice

Regards,
Dhivya
Re: Cursor reffering tablename [message #563355 is a reply to message #563350] Mon, 13 August 2012 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error is obvious: a.arch_ts does not exists in subquery named "a".

Regards
Michel
Re: Cursor reffering tablename [message #563361 is a reply to message #563355] Mon, 13 August 2012 03:50 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Thanks..But how can i achieve this..

I want to query each individual tables which are stored in the cursor and check for the condition a.arch_ts<sysdate-730?

Regards,
Dhivya
Re: Cursor reffering tablename [message #563362 is a reply to message #563361] Mon, 13 August 2012 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot check this condition as the field/column does not exist.

Regards
Michel
Re: Cursor reffering tablename [message #563363 is a reply to message #563362] Mon, 13 August 2012 04:01 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Thanks for the quick update.

Can you please tell me if i can try any other way to achieve this?

Regards
Dhivya
Re: Cursor reffering tablename [message #563364 is a reply to message #563363] Mon, 13 August 2012 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want to achieve and I have no "archive_data_tables" table that could make me understand and show you.

Regards
Michel
Re: Cursor reffering tablename [message #563366 is a reply to message #563364] Mon, 13 August 2012 04:33 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi Michel,

The problem is not about archive_data_tables.

My requirement is i need to get the table names which has num_rows>0 from all_tables table, using the table names i need to go and query each and every table to check the condition arch_ts< sysdate-730.

Hope this explains.

Regards,
Dhivya
Re: Cursor reffering tablename [message #563369 is a reply to message #563366] Mon, 13 August 2012 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and every table to check the condition arch_ts< sysdate-730.


You cannot check this condition as the field/column does not exist.

Regards
Michel
Re: Cursor reffering tablename [message #563379 is a reply to message #563369] Mon, 13 August 2012 06:30 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
You have to use two seperate queries.
One to get the list of tables
Then a second, dynamic query, to check arch_ts for each table.
Re: Cursor reffering tablename [message #563384 is a reply to message #563379] Mon, 13 August 2012 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But there is no arch_ts, nowhere. Wink

Regards
Michel


[Updated on: Mon, 13 August 2012 07:08]

Report message to a moderator

Re: Cursor reffering tablename [message #563387 is a reply to message #563384] Mon, 13 August 2012 07:11 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
OP says there is a column of that name on each table, I see no reason to doubt him.
Re: Cursor reffering tablename [message #563395 is a reply to message #563379] Mon, 13 August 2012 08:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
cookiemonster wrote on Mon, 13 August 2012 07:30
You have to use two seperate queries.
One to get the list of tables
Then a second, dynamic query, to check arch_ts for each table.


Why? What happened to ALL_TAB_COLUMNS?

SY.
Re: Cursor reffering tablename [message #563411 is a reply to message #563395] Mon, 13 August 2012 10:42 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Hi All,

Tried the code in a different way as below

DECLARE 
    v_sql          VARCHAR2(32767); 
    num_total_rows NUMBER; 
BEGIN 
    FOR c1 IN (SELECT owner 
                      ||'.' 
                      ||table_name table_name 
               FROM   all_tables 
               WHERE  owner = 'ARCHIVE' 
                      AND num_rows > 0 
                      AND ROWNUM < 3 
               ORDER  BY table_name) LOOP 
        dbms_output.Put_line(c1.table_name); 

        v_sql := 'select * from ' 
                 ||c1.table_name 
                 ||' where arch_ts < (sysdate-5)'; 

        EXECUTE IMMEDIATE (v_sql); 

        num_total_rows := SQL%rowcount; 

        dbms_output.Put_line(v_sql); 

        dbms_output.Put_line(num_total_rows); 

        IF num_total_rows > 0 THEN 
          EXECUTE IMMEDIATE 
'Insert into ARCHIVE_DATA_TABLES(ARCH_TABLE_NAME) ('''||c1.table_name||''')'; 
ELSE 
  dbms_output.Put_line('no data found'); 
END IF; 

COMMIT; 
END LOOP; 
END; 

/ 


The above code returns the num_total_rows as zero though it has some value to return. Can you please tell why?
Re: Cursor reffering tablename [message #563413 is a reply to message #563411] Mon, 13 August 2012 10:52 Go to previous messageGo to next message
John Watson
Messages: 4859
Registered: January 2010
Location: Global Village
Senior Member
I would have thought it would be simpler to count the rows and store the result in a variable, like this:
orcl>
orcl> var n number
orcl> begin
  2  execute immediate 'select count(*) from emp' into :n;
  3  end;
  4  /

PL/SQL procedure successfully completed.

orcl> print n

         N
----------
        14

orcl>
Re: Cursor reffering tablename [message #563414 is a reply to message #563411] Mon, 13 August 2012 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59994
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL%rowcount returns the number of rows you FETCHED.
Here you didn't fetch anything.

Good readings for you:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel

[Updated on: Mon, 13 August 2012 10:53]

Report message to a moderator

icon14.gif  Re: Cursor reffering tablename [message #563426 is a reply to message #563414] Mon, 13 August 2012 13:39 Go to previous messageGo to next message
dhivyaenjoy
Messages: 49
Registered: June 2011
Member
Thanks Michel.. will go through the docs... am just a beginner in coding so only found this forum very useful for myself...

Really good job guys..

Thanks John.. that really worked for me...

Thanks for all who contributed for my topic..
Re: Cursor reffering tablename [message #563437 is a reply to message #563395] Mon, 13 August 2012 18:47 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Solomon Yakobson wrote on Mon, 13 August 2012 14:54

Why? What happened to ALL_TAB_COLUMNS?
SY.

What use would it be? OP wants to check the value of the column.
Re: Cursor reffering tablename [message #563439 is a reply to message #563437] Mon, 13 August 2012 18:51 Go to previous message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
16:50:02 SQL> desc ALL_TAB_COL_STATISTICS

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER						    VARCHAR2(30)
 TABLE_NAME					    VARCHAR2(30)
 COLUMN_NAME					    VARCHAR2(30)
 NUM_DISTINCT					    NUMBER
 LOW_VALUE					    RAW(32)
 HIGH_VALUE					    RAW(32)
 DENSITY					    NUMBER
 NUM_NULLS					    NUMBER
 NUM_BUCKETS					    NUMBER
 LAST_ANALYZED					    DATE
 SAMPLE_SIZE					    NUMBER
 GLOBAL_STATS					    VARCHAR2(3)
 USER_STATS					    VARCHAR2(3)
 AVG_COL_LEN					    NUMBER
 HISTOGRAM					    VARCHAR2(15)

16:50:37 SQL> 16:50:37 SQL> 

Previous Topic: Updating Accounts and Displaying Changes
Next Topic: *WITH AS* (alternatives) other methods (2 threads merged by bb)
Goto Forum:
  


Current Time: Sat Dec 20 01:05:39 CST 2014

Total time taken to generate the page: 0.05047 seconds