Home » SQL & PL/SQL » SQL & PL/SQL » insert using table in pl/sql
insert using table in pl/sql [message #236770] Thu, 10 May 2007 23:44 Go to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
Hi,
Actualaly i am trying to write a procedure for inserting into a table column wise value from another table,here is the procedure

CREATE OR REPLACE PROCEDURE P1 IS
TYPE col_tab IS TABLE OF VARCHAR2(100) ;
col_name col_tab;
CURSOR C IS SELECT DISTINCT column_name FROM user_tab_cols WHERE table_name='CCYGROP'AND Column_name NOT LIKE ('BANK_CODE') ORDER BY column_name ;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO col_name ;
FORALL i IN col_name.FIRST..col_name.LAST
INSERT INTO CCYGROP(col_name(i)) (SELECT col_name(i) FROM CCYGROP@lalitdb);UPDATE CCYGROP SET brn_code=(SELECT brn_code FROM BRNSTATUS@lalitdb);
COMMIT;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END P1

when I AM EXECUTING THIS PROCEDURE , IT IS SHOWING ME PL/SQL: ORA-00917: missing comma IN INSERT STATEMENT
cAN ANYONE TELL ME HOW TO SOLVE THIS PROBLEM




Re: insert using table in pl/sql [message #236776 is a reply to message #236770] Fri, 11 May 2007 00:17 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
in the insert statement where is the VALUES clause:

Quote:
INSERT INTO CCYGROP(col_name(i)) values (SELECT col_name(i) FROM CCYGROP@lalitdb);


you can try this.
Ashu

[Updated on: Fri, 11 May 2007 00:18]

Report message to a moderator

Re: insert using table in pl/sql [message #236779 is a reply to message #236776] Fri, 11 May 2007 00:28 Go to previous messageGo to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
By using values clause also, it gives the same error PL/SQL: ORA-00917: missing comma
Re: insert using table in pl/sql [message #236783 is a reply to message #236779] Fri, 11 May 2007 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is so totally wrong, I hardly know where to start.
First:
Insert into CCYGROP(col_name(i))
and
SELECT col_name(i) FROM CCYGROP@lalitdb
You cannot use a variable name for object_names like tables or columns.

Second: Other than your code-layout suggests, the update is NOT part of the FORALL. This means that the update will very likely return an error. (too many rows)

Third: Why the distinct column_name? Are you afraid you have duplicate column_names in your table?

Fourth: The end result of this code would be a table with rows in which each row has a value in one and only onw column.

Fifth: You don't combine bulk-collects without a limit-clause with a loop. That just doesn't make sense.
Re: insert using table in pl/sql [message #236789 is a reply to message #236783] Fri, 11 May 2007 00:52 Go to previous messageGo to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
Thanks a lot 4 ur suggstion , but what i want to do is i want to get all the columns of a table from user_tab_cols and insert in these columns of the tables , the same column values from other table,
so i made columns as a variable,can u tell me other way to do so
Re: insert using table in pl/sql [message #236792 is a reply to message #236770] Fri, 11 May 2007 00:56 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Still not clear: do you want to insert rows with just one filled column or do you want to insert rows with all columns filled (instead of BANK_CODE)?

In both cases, you have to use dynamic sql. Create a VARCHAR2 variable and fill it with SQL string containing the INSERT command you want to run. Then execute it by EXECUTE IMMEDIATE.

As for your UPDATE statement, do you want to update all rows in CCYGROP? If no, add the WHERE condition to both UPDATE and subquery.

By the way, are you sure you use the NOT LIKE condition with correct pattern (as now it is acts the same as the simple comparition).
Re: insert using table in pl/sql [message #236808 is a reply to message #236792] Fri, 11 May 2007 01:21 Go to previous messageGo to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
Thanks again for ur suggestion
actually wht i want is
table 1
a b c
table 2
b c

i want to insert in table 2 values of columns b and c from table 1, i cant hardcode column names, i have to get them for user_tab_cols

I think nw u can understand wht i want
Re: insert using table in pl/sql [message #236811 is a reply to message #236808] Fri, 11 May 2007 01:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Can you *please* write in proper English:
nw = now
u = you
wht = what

MHE
Re: insert using table in pl/sql [message #236812 is a reply to message #236808] Fri, 11 May 2007 01:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
drop table table2;
create or replace view table2 as select b,c from table1@other_db
Re: insert using table in pl/sql [message #236815 is a reply to message #236770] Fri, 11 May 2007 01:29 Go to previous messageGo to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
I Cant write b,c in my query, i cant hardcode the names of columns, this is the problem
Re: insert using table in pl/sql [message #236846 is a reply to message #236815] Fri, 11 May 2007 02:29 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
meeta wrote on Fri, 11 May 2007 08:29
I Cant write b,c in my query, i cant hardcode the names of columns...

Why?

Regards
Michel

Previous Topic: not a valid month error
Next Topic: Query return question
Goto Forum:
  


Current Time: Tue Dec 06 08:39:03 CST 2016

Total time taken to generate the page: 0.08603 seconds