Home » SQL & PL/SQL » SQL & PL/SQL » Table name in cursor
Table name in cursor [message #200965] Thu, 02 November 2006 01:33 Go to next message
sinwei_chee
Messages: 5
Registered: November 2006
Junior Member
HI, all

I am writing a PL-SQL to help me to get data from differnt tables and insert to a new table. But I keep getting error saying "invalid table name", Can someone be so kind enlighten me?
Cheers

DECLARE
CURSOR cur_table IS
SELECT table_name FROM user_tables
WHERE USER = 'TEST'
AND table_name LIKE 'TBL_2005_OCT_%'
ORDER BY table_name;
BEGIN
FOR v_cur IN cur_table LOOP
INSERT INTO analysis_temp_for_october
(direction, owner)
SELECT direction , owner FROM v_cur.table_name
WHERE direction = 'IN' AND owner NOT LIKE '%JOHN%';
END LOOP;
END;
/

[Updated on: Thu, 02 November 2006 01:40]

Report message to a moderator

Re: Table name in cursor [message #200983 is a reply to message #200965] Thu, 02 November 2006 02:18 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll have to use the table name dynamically, something like this:
DECLARE
   CURSOR cur_table
   IS
      SELECT   table_name
          FROM user_tables
         WHERE USER = 'TEST' AND table_name LIKE 'TBL_2005_OCT_%'
      ORDER BY table_name;
	  
   str VARCHAR2(1000) := 'INSERT INTO ANALYSIS_TEMP_FOR_OCTOBER
                            (direction, owner)
                          SELECT direction, owner
                          FROM ';  
BEGIN
   FOR v_cur IN cur_table
   LOOP
      str := str || v_cur.table_name
                 || ' WHERE direction = ''IN'' AND owner NOT LIKE ''%JOHN%''';
      EXECUTE IMMEDIATE str;
   END LOOP;
END;
/
Re: Table name in cursor [message #200985 is a reply to message #200965] Thu, 02 November 2006 02:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is with this line:
SELECT direction , owner FROM v_cur.table_name

You're trying to pass the table name in as a parameter, and that won't work for two reasons.
First, the compiler checks all the SQL statements for validity when you compile the code, and at compile time this statement is invalid.
Second, at execution time, the optimiser needs to know the name of the tables it's selecting from at the start of the process, not part way through.

You can't do what you're trying to do without using dynamic sql in some fashion.
Re: Table name in cursor [message #201176 is a reply to message #200983] Thu, 02 November 2006 21:10 Go to previous messageGo to next message
sinwei_chee
Messages: 5
Registered: November 2006
Junior Member
Hi,

Thanks for the guidnace.

I got the str1 correct as I did a dbms_output. When I cut & paste the str1 and execute, if work ok.... but if i run the script then "invalid charater found" error.

do I need to include "/" at teh end of the statement?
Many thanks in advance
Re: Table name in cursor [message #201222 is a reply to message #201176] Fri, 03 November 2006 03:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to make sure that you don't have a ';' at the end of the statement that you're executing.
Previous Topic: How do i set default database/user for statements?
Next Topic: Testing whether rows exist from multiple tables with one PL/SQL-SQL context switch?
Goto Forum:
  


Current Time: Mon Dec 05 14:56:03 CST 2016

Total time taken to generate the page: 0.15661 seconds