Home » SQL & PL/SQL » SQL & PL/SQL » Insert/Update with cyclic sequence ID (merged 2)
Insert/Update with cyclic sequence ID (merged 2) [message #388878] Thu, 26 February 2009 08:19 Go to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

Hello,

i am looking for the 'right' way to handle an insert/update on a table with a cyclic sequence as an id from within a procedure.

So far i have not found any hints on how to deal with it.

Solutions i could think of are:

-inserting all available IDs from the sequence so i will only use updates on the table. << do not like it at all.

-use an update statement with exception handling. the exception then contains the insert statement in case of the update failing due to the missing id.

How would you suggest i handle this?
Thanks in advance.





Re: Insert/Update with cyclic sequence ID [message #388880 is a reply to message #388878] Thu, 26 February 2009 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MERGE?

Regards
Michel
Re: Insert/Update with cyclic sequence ID (merged 2) [message #389072 is a reply to message #388878] Fri, 27 February 2009 03:27 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

It looks like merge needs a table to merge into and a table/view/query to merge with. I should have mentioned i get the record values over an interface. One record at a time. So when i want to do the insert i have the values in seperate variables.

Simplified it should look like this:

DECLARE
  vIdSQ    number;
  vValSq   number;
BEGIN
  vIdSQ:= TEST_SQ.NEXTVAL;
  vValSq:= 5;
  MERGE INTO SQTEST
      USING (vIdSQ, vValSq)
      ON (IDTEST=vIdSQ)
      WHEN MATCHED THEN
         UPDATE
            SET VALTEST =vValSq;
      WHEN NOT MATCHED THEN
         INSERT
            (IDTEST, VALTEST)
         VALUES
            (vIdSQ,vValSq);
END;


The USING line is the one where i have the problem. A select is expected.

[Updated on: Fri, 27 February 2009 03:28]

Report message to a moderator

Re: Insert/Update with cyclic sequence ID (merged 2) [message #389074 is a reply to message #389072] Fri, 27 February 2009 03:31 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
isthisadagger wrote on Fri, 27 February 2009 10:27

The USING line is the one where i have the problem. A select is expected.


I had the same problem, till someone gave me this:

      USING ( SELECT vIdSQ, vValSq FROM DUAL )
...
Re: Insert/Update with cyclic sequence ID (merged 2) [message #389095 is a reply to message #389074] Fri, 27 February 2009 04:52 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

That change leads to ORA-00904 "VIDSQ" invalid identifier.
Am i missing something?
Re: Insert/Update with cyclic sequence ID (merged 2) [message #389097 is a reply to message #389095] Fri, 27 February 2009 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect you've got a typo somewhere.

Re: Insert/Update with cyclic sequence ID (merged 2) [message #389103 is a reply to message #389097] Fri, 27 February 2009 05:11 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

My thought exactly but there is no typo. It still looks like the problem is those are variables and not columns in the SELECT statement.

DECLARE
  vIdSQ    number;
  vValSq   number;
BEGIN
  SELECT TEST_SQ.NEXTVAL INTO vIdSQ FROM DUAL;
  vValSq:= 5;

  MERGE INTO SQTEST
      USING (SELECT vIdSQ, vValSq FROM DUAL)
      ON (IDTEST=vIdSQ)
      WHEN MATCHED THEN
         UPDATE
            SET VALTEST =vValSq
      WHEN NOT MATCHED THEN
         INSERT
            (VALTEST)
         VALUES
            (vValSq);
END;

[Updated on: Fri, 27 February 2009 05:14]

Report message to a moderator

Re: Insert/Update with cyclic sequence ID (merged 2) [message #389104 is a reply to message #389103] Fri, 27 February 2009 05:16 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I could be wrong and as I don't have a test environment at hand I can't try it myself.

Maybe you should 'alias' them so you avoid ambiguity

      USING (SELECT vIdSQ as v_IdSD, vValSq as v_ValSq FROM DUAL)

Re: Insert/Update with cyclic sequence ID (merged 2) [message #389108 is a reply to message #389104] Fri, 27 February 2009 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's weird and freaky. So freaky, that it's got to be a piece of documented behaviour that I've forgotten about.

I get exactly the same error, unless I make vIdSq and vValSq part of c.alls to functions.
This, for example, works fien (nb you missed a column off the INSERT part of the merge):
 create table sqtest (idtest  number, valtest number);
 
 create sequence test_sq;
 
DECLARE
  vIdSQ    number;
  vValSq   number;
BEGIN
  SELECT TEST_SQ.NEXTVAL INTO vIdSQ FROM DUAL;
  vValSq:= 5;

  MERGE INTO SQTEST
      USING (SELECT cast(vIdSQ as number) , cast(vValSq as number) FROM dual)
      ON (IDTEST=vIdSQ)
      WHEN MATCHED THEN
         UPDATE
            SET VALTEST =vValSq
      WHEN NOT MATCHED THEN
         INSERT
            (IDTEST,VALTEST)
         VALUES
            (vIdSq, vValSq);
END; 
/
Re: Insert/Update with cyclic sequence ID (merged 2) [message #389124 is a reply to message #389108] Fri, 27 February 2009 06:01 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

Thank you very much. I definitely could not have done it without your help.
Although the mandatory typecast/function call leaves with some questionmarks this is much better than what i thought of before Very Happy

[Updated on: Fri, 27 February 2009 06:13]

Report message to a moderator

Re: Insert/Update with cyclic sequence ID (merged 2) [message #389232 is a reply to message #389124] Fri, 27 February 2009 13:07 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Interesting. It appears that, when the select statement is nested within the merge statement, it does not know where to look outside of sql to see what type it is, and does not look for variables within the calling block or procedure or package. If you either tell it what type it is, such as by using CAST or TO_NUMBER or tell it where to look, such as by preceding the variable name with the procedure or package name, then it finds the correct type. I have provided additional demos for to_number and for referencing the variables with their procedure or package names.

SCOTT@orcl_11g> create table sqtest (idtest  number, valtest number);

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> create sequence test_sq;

Sequence created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> DECLARE
  2    vIdSQ	number;
  3    vValSq	number;
  4  BEGIN
  5    SELECT TEST_SQ.NEXTVAL INTO vIdSQ FROM DUAL;
  6    vValSq:= 5;
  7  
  8    MERGE INTO SQTEST
  9  	   USING (SELECT TO_NUMBER (vIdSQ) , TO_NUMBER (vValSq) FROM dual)
 10  	   ON (IDTEST=vIdSQ)
 11  	   WHEN MATCHED THEN
 12  	      UPDATE
 13  		 SET VALTEST =vValSq
 14  	   WHEN NOT MATCHED THEN
 15  	      INSERT
 16  		 (IDTEST,VALTEST)
 17  	      VALUES
 18  		 (vIdSq, vValSq);
 19  END;
 20  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> select * from sqtest
  2  /

    IDTEST    VALTEST
---------- ----------
         1          5

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE test_proc
  2  AS
  3    vIdSQ	number;
  4    vValSq	number;
  5  BEGIN
  6    SELECT TEST_SQ.NEXTVAL INTO vIdSQ FROM DUAL;
  7    vValSq:= 5;
  8  
  9    MERGE INTO SQTEST
 10  	   USING (SELECT test_proc.vIdSQ , test_proc.vValSq FROM dual)
 11  	   ON (IDTEST = test_proc.vIdSQ)
 12  	   WHEN MATCHED THEN
 13  	      UPDATE
 14  		 SET VALTEST = test_proc.vValSq
 15  	   WHEN NOT MATCHED THEN
 16  	      INSERT
 17  		 (IDTEST, VALTEST)
 18  	      VALUES
 19  		 (test_proc.vIdSq, test_proc.vValSq);
 20  END test_proc;
 21  /

Procedure created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> EXEC test_proc

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> select * from sqtest
  2  /

    IDTEST    VALTEST
---------- ----------
         1          5
         2          5

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE test_pkg
  2  AS
  3    vIdSQ	number;
  4    vValSq	number;
  5    PROCEDURE test_proc;
  6  END test_pkg;
  7  /

Package created.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY test_pkg
  2  AS
  3    PROCEDURE test_proc
  4    IS
  5    BEGIN
  6  	 SELECT TEST_SQ.NEXTVAL INTO test_pkg.vIdSQ FROM DUAL;
  7  	 test_pkg.vValSq:= 5;
  8  
  9  	 MERGE INTO SQTEST
 10  	     USING (SELECT test_pkg.vIdSQ , test_pkg.vValSq FROM dual)
 11  	     ON (IDTEST = test_pkg.vIdSQ)
 12  	     WHEN MATCHED THEN
 13  		UPDATE
 14  		   SET VALTEST = test_pkg.vValSq
 15  	     WHEN NOT MATCHED THEN
 16  		INSERT
 17  		   (IDTEST, VALTEST)
 18  		VALUES
 19  		   (test_pkg.vIdSq, test_pkg.vValSq);
 20    END test_proc;
 21  END test_pkg;
 22  /

Package body created.

SCOTT@orcl_11g> EXEC test_pkg.test_proc

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> select * from sqtest
  2  /

    IDTEST    VALTEST
---------- ----------
         1          5
         2          5
         3          5

SCOTT@orcl_11g> 



[Updated on: Fri, 27 February 2009 13:08]

Report message to a moderator

Previous Topic: NEWB TO PL/SQL
Next Topic: A few simple questions
Goto Forum:
  


Current Time: Sun Dec 04 10:25:24 CST 2016

Total time taken to generate the page: 0.11205 seconds