Home » SQL & PL/SQL » SQL & PL/SQL » Insert into table in PL/SQL
Insert into table in PL/SQL [message #257259] Tue, 07 August 2007 22:05 Go to next message
anjalihans
Messages: 2
Registered: August 2007
Junior Member
I am trying to insert into table check_count after every data migration/conversion step
but then it adds a new row so i have given the condition where Row_ID=1 Mad
and then its not working and giving the error ORA-00933: SQL command not properly ended

the structure of the table is
TABLE CHECK_COUNT
Name Type
-----------------------
ROW_ID NUMBER
G_R_L NUMBER

it was working fine without this condition Razz

The code i am using is like this
declare
count1 number;
cursor C1 is select count(*) from GM_R_L ;

begin
open C1;
fetch C1 into count1;
if C1%notfound
then count1 :=0;
end if;

EXECUTE IMMEDIATE 'insert into CHECK_COUNT (G_R_L) values ('||count1||') where Row_ID=1' ;

close C1;
end;

commit;
Re: Insert into table in PL/SQL [message #257263 is a reply to message #257259] Tue, 07 August 2007 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>but then it adds a new row so i have given the condition where Row_ID=1
By what twisted logic did you arrive at this nonsensical conclusion?

Why are you using EXECUTE IMMEDIATE to do a simple INSERT using a bind variable?

If you insist on (ab)using EXECUTE IMMEDIATE, you should construct the whole SQL statement is a string variable.
Print the string before EXECUTE IMMEDIATE.
After it fails, use CUT & PASTE to drop it into SQL*Plus to see where the error occurs.
Re: Insert into table in PL/SQL [message #257284 is a reply to message #257259] Wed, 08 August 2007 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert into ... values ... WHERE ...
The last part is invalid. You either:
- insert values, so can't have a where clause
- insert select, you may have a where clause on the select
- update columns where some rows, then you may have a where clause.

In your case, you either:
insert into check_count (row_id, g_r_l) select 1, count(*) from gm_r_l;
or
update check_count set g_r_l = (select count(*) from gm_r_l) where row_id=1;

Regards
Michel
Re: Insert into table in PL/SQL [message #257370 is a reply to message #257259] Wed, 08 August 2007 03:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
anjalihans wrote on Wed, 08 August 2007 05:05
I am trying to insert into table check_count after every data migration/conversion step
but then it adds a new row so i have given the condition where Row_ID=1

Last time I checked, adding a new row is the whole point of an insert!
My magic bat whispered that you might be looking for an update of an existing row, for which you want to add a value for a null-field. You mistakenly call this an insert..
But, then again, my bat may be wrong
Re: Insert into table in PL/SQL [message #257631 is a reply to message #257259] Wed, 08 August 2007 17:02 Go to previous message
anjalihans
Messages: 2
Registered: August 2007
Junior Member
hello friends

you all are right
and i have corrected my piece of code
that it needed an update stmt rather than insert

thanks to all for ur time and effort
Previous Topic: Dynamic Variable Names
Next Topic: Updating table from anpther table ...Issue
Goto Forum:
  


Current Time: Sun Dec 11 04:05:01 CST 2016

Total time taken to generate the page: 0.06505 seconds