Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql (code does not return correct data)
pl/sql [message #273840] Thu, 11 October 2007 20:55 Go to next message
Messages: 3
Registered: October 2007
Location: Georgia
Junior Member
I am new for pl/sql. I wrote the following statement to update
a table name ‘LINFO’ whenever there is an upgrade and new version change in the database.
The INFO table column 'dbtype' is empty when the table is new. So the pl/sql statement error out
with 'no data found' when I run the following code. Is there any other way to make this code work...is there
any step that I missed?
thank you for your help
GETDBTYPE varchar(32)
select dbtype into getdbtype from linfo;
if getdbtype='IBCOM' then
update LINFO SET VERSION='V23',lastreorgdate=sysdate
insert into LINFO (dbtype,VERSION,upgDATE)VALUES('IBCOM','V23',sysdate);
end if;
Re: pl/sql [message #273842 is a reply to message #273840] Thu, 11 October 2007 21:31 Go to previous messageGo to next message
Messages: 24963
Registered: January 2009
Location: SoCal
Senior Member
What should occur when "no data found" happens?

Why did you not use MERGE statement?
Re: pl/sql [message #273847 is a reply to message #273840] Thu, 11 October 2007 23:52 Go to previous message
Messages: 1832
Registered: November 2006
Senior Member
select dbtype into getdbtype from linfo;
update LINFO SET VERSION='V23',lastreorgdate=sysdate

Are you aware these commands work for ALL rows in the table as no WHERE condition is given.
Prepare for TOO_MANY_ROWS exception and/or update of ALL rows, after you somehow succeed and INSERT the second row.

There are many ways. I like this one (first you have to create unique constraint on DBTYPE, if you do not have it yet):
   INSERT INTO linfo (dbtype, version, upgdate)
   VALUES('IBCOM', 'V23', sysdate);
  WHEN dup_val_on_index THEN
    UPDATE linfo
      SET version = 'V23',lastreorgdate = sysdate
      WHERE dbtype = 'IBCOM';

MERGE statement will work well in 9i and above.
Previous Topic: how we create index
Next Topic: Problem in Sql
Goto Forum:

Current Time: Wed Oct 26 00:02:38 CDT 2016

Total time taken to generate the page: 0.09359 seconds