Procedure to Merge : SELECT from Table Of Record

1003374

Hi there,

I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)

Basically, the procedure receives a Talbe Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.

I have difficulties to merge these data. Below is what I tried

CREATE or REPLACE PACKAGE BIZ_xxx_MERGE
IS
TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER;

PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
);
END BIZ_xxx_MERGE;

CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
) AS
BEGIN
MERGE INTO MyTbl
USING (MyLP) S -- <= WHAT TO DO HERE
ON (MyTbl.xxx_ACTCD = S.xxx_ACTCD)
WHEN NOT MATCHED THEN
INSERT (xxx_ACTCD, xxx_LIBL)
VALUES (S.xxx_ACTCD, s.xxx_LIBL);

END MERGE_xxx_ACTIVITE_SP;
END BIZ_xxx_MERGE;

Any idea? Is there any better way?

Thanks

Edited by: BizTalk Guy on 28 mai 2013 07:37

user1983440

Hmmm...have you read the documentation for merge? I'm not sure what Oracle version you're using, but I'm fairly certain you can't reference a PL/SQL table inside the USING() clause.

From http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_9016.htm:

"USING Clause

Use the USING clause to specify the source of the data to be updated or inserted. The source can be a table, view, or the result of a subquery"

myOra_help

You cannot use local/packaged type in SQL statement directly. Type must be known to SQL engine to process the SQL statement. Read TABLE function, CAST (if using <10g) for how to use collections in SQL statements.

Also you can't use associative arrays in sql statements. Consider Nested/varrays for this.

1003374

Use the USING clause to specify the source of the data to be updated or inserted. The source can be a table, view, or the result of a subquery.

OK perfect, then how do I make my Table of Records look like a table? do I have to create one temporary?
I can do anything with the package

My question would be then what is the best way?

Regards,

user1983440

This is likely not the best answer (see below), but perhaps it will help clarify the concept.
Pay particlular attention to the the USING clause.

CREATE TABLE mytbl (xxx_ACTCD number, xxx_LIBL number)
Table created.

INSERT INTO mytbl
  SELECT 1,12345 FROM DUAL UNION ALL 
  SELECT 5,44444 FROM DUAL UNION ALL
  SELECT 20,999 FROM DUAL
3 rows created.

SELECT * FROM mytbl

 XXX_ACTCD   XXX_LIBL
---------- ----------
         1      12345
         5      44444
        20        999

3 rows selected.

MERGE INTO MyTbl
USING (SELECT 10 xxx_ACTCD, 54321 xxx_LIBL FROM DUAL) S -- <= WHAT TO DO HERE
ON (MyTbl.xxx_ACTCD = S.xxx_ACTCD) 
WHEN NOT MATCHED THEN
INSERT (xxx_ACTCD, xxx_LIBL)
VALUES (S.xxx_ACTCD, s.xxx_LIBL)
1 row merged.

SELECT * FROM mytbl

 XXX_ACTCD   XXX_LIBL
---------- ----------
         1      12345
         5      44444
        20        999
        10      54321

4 rows selected.

1003374

Thanks but I am calling the stored procedure from an external system.
So the parameter of the stored procedure is TableOfRecord (or any other useful type of parameter)

My need is : I want to merge with an existing table

Do I have to create a temp table then insert with a loop record by record into my temp table
Then use my temp table in the merge?

If this is the solution then it is really silly !
In SQL Server we could define Table Types and do a merge, how is this possible with Oracle ?

user1983440

You'll need to define your types as schema-level objects, rather than as transient types within your package definition.

CREATE TABLE mytbl (xxx_ACTCD number, xxx_LIBL number)
Table created.

INSERT INTO mytbl
  SELECT 1,12345 FROM DUAL UNION ALL 
  SELECT 5,44444 FROM DUAL UNION ALL
  SELECT 20,999 FROM DUAL
3 rows created.

SELECT * FROM mytbl

 XXX_ACTCD   XXX_LIBL
---------- ----------
         1      12345
         5      44444
        20        999

3 rows selected.

CREATE OR REPLACE TYPE xxx_ACTIVITE_Type_obj FORCE IS OBJECT 
(
  xxx_ACTCD NUMBER,
  xxx_LIBL  NUMBER
);
Type created.

CREATE OR REPLACE TYPE xxx_ACTIVITE_Type IS TABLE OF xxx_ACTIVITE_Type_obj;
Type created.

CREATE or REPLACE PACKAGE BIZ_xxx_MERGE 
IS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
); 
END BIZ_xxx_MERGE;
Package created.

CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
) AS
BEGIN
MERGE INTO MyTbl
USING (SELECT * FROM TABLE(MyLP)) S -- <= WHAT TO DO HERE
ON (MyTbl.xxx_ACTCD = S.xxx_ACTCD) 
WHEN NOT MATCHED THEN
INSERT (xxx_ACTCD, xxx_LIBL)
VALUES (S.xxx_ACTCD, s.xxx_LIBL);
END MERGE_xxx_ACTIVITE_SP;
END BIZ_xxx_MERGE;
Package body created.

BEGIN
  BIZ_xxx_MERGE.MERGE_xxx_ACTIVITE_SP(xxx_ACTIVITE_Type(xxx_ACTIVITE_Type_obj(8888888,2)));
END;
PL/SQL procedure successfully completed.

SELECT * FROM mytbl

 XXX_ACTCD   XXX_LIBL
---------- ----------
         1      12345
         5      44444
        20        999
   8888888          2

4 rows selected.

Edited by: user1983440 on May 28, 2013 11:31 AM

1003374

OK I'll try that and let you know

1003374

I worked perfectly thanks ... but...

I am accessing the Stored Procedure from BizTalk Server using the ODP.NET, unfortunately the use of objects implies the use of UDT (User Defined Types) which is causing me some troubles.

So In this case I don't know what are the alternatives I have?
Use Table of Record and then loop?

Regards,

BEDE

What about the following:

CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
 PROCEDURE MERGE_xxx_ACTIVITE_SP (
 MyLP IN xxx_ACTIVITE_Type
 ) AS
 BEGIN
forall i in mylp.first..mylp.last  
MERGE INTO MyTbl
 USING (
   select MyTbl(i).xxx_ACTCD xxx_ACTCD 
    ,mylp(i).col1 alias1
     , mylp(i).col2 alias2
   from dual

) S -- <= WHAT TO DO HERE
 ON (MyTbl.xxx_ACTCD = S.xxx_ACTCD) 
 WHEN NOT MATCHED THEN
 INSERT (xxx_ACTCD, xxx_LIBL)
 VALUES (S.xxx_ACTCD, s.xxx_LIBL);

 END MERGE_xxx_ACTIVITE_SP;
 END BIZ_xxx_MERGE;

1003374

The idea looks nice indeed, I'll test it and let you know