Home » SQL & PL/SQL » SQL & PL/SQL » inserting into table row-by-row.
inserting into table row-by-row. [message #191957] Sat, 09 September 2006 04:14 Go to next message
tridentadm
Messages: 142
Registered: March 2006
Senior Member
whats wrong in this sql code.

14:33:25 SCOTT@ORA90> begin
14:33:41 2 for x in (select * from big_table)
14:33:58 3 loop
14:33:59 4 insert into t2 values x.*;
14:34:09 5 end loop;
14:34:11 6 commit;
14:34:15 7 end;
14:34:18 13 /
insert into t2 values x.*;
*
ERROR at line 4:
ORA-06550: line 4, column 24:
PL/SQL: ORA-00906: missing left parenthesis
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored

i already have a table called big_table existing, but i am missing out something

structure of t2 is as below

14:34:21 SCOTT@ORA90> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

Re: inserting into table row-by-row. [message #191966 is a reply to message #191957] Sat, 09 September 2006 06:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> insert into t2 values x.*;
You think this is the right sql syntax to insert a row?
hint
>>PL/SQL: ORA-00906: missing left parenthesis
Re: inserting into table row-by-row. [message #191967 is a reply to message #191966] Sat, 09 September 2006 07:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Wierd. I was not even getting the same error you got.
Anyhow, a simplest method is;
scott@9i > begin
  2  insert into e (select * from emp);
  3  commit;
  4  end;
  5  /

PL/SQL procedure successfully completed.

Re: inserting into table row-by-row. [message #191998 is a reply to message #191967] Sat, 09 September 2006 13:57 Go to previous messageGo to next message
tridentadm
Messages: 142
Registered: March 2006
Senior Member
Mahesh Rajendran wrote on Sat, 09 September 2006 17:42

Wierd. I was not even getting the same error you got.
Anyhow, a simplest method is;



boss...thats exactly what i DONT want.

i know the easy method...

how can i do the same using a FOR LOOP. ???
Re: inserting into table row-by-row. [message #192002 is a reply to message #191957] Sat, 09 September 2006 14:11 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>how can i do the same using a FOR LOOP. ???
by using valid syntax for INSERT which can be found in the fine SQL Reference Manual at http://tahiti.oracle.com
Re: inserting into table row-by-row. [message #192038 is a reply to message #191957] Sun, 10 September 2006 03:03 Go to previous messageGo to next message
oigor
Messages: 8
Registered: September 2006
Location: Athens, GREECE
Junior Member
The problem is here:

insert into t2 values [B]x.*[/B];


You are inside the CURSOR LOOP, and you cannot refer to the whole record by saying x.*, because this is PL/SQL and x is a record fetched from the cursor.

I mean, the star is valid in SQL, but not in record type of PL/SQL.

-------

Igor Obradovic
Re: inserting into table row-by-row. [message #192136 is a reply to message #191998] Mon, 11 September 2006 03:03 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
The upshot of all this is... use your code, only without the .*
However, if this is the actual example of what you want to do, the only way you should be using it is if it is purely an academic execise. This is (as your subject says) row by row processing. AKA slow by slow. The example given by Mahesh is (as usual) the best way to go if you possibly can. The fact that you don't want to use this method, implies that it is a far more complex situation than you have actually alluded to.


Jim
Previous Topic: how to know whether an update or insert happened due to an insert or update statement
Next Topic: SubQuery not running
Goto Forum:
  


Current Time: Sat Dec 10 12:36:41 CST 2016

Total time taken to generate the page: 0.10926 seconds