Forum Stats

  • 3,872,651 Users
  • 2,266,449 Discussions
  • 7,911,276 Comments

Discussions

Procedure to Merge : SELECT from Table Of Record

1003374
1003374 Member Posts: 23
edited Jun 4, 2013 9:02AM in SQL & PL/SQL
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
Tagged:

Answers

  • user1983440
    user1983440 Member Posts: 108
    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
    myOra_help Member Posts: 245
    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
    1003374 Member Posts: 23
    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
    user1983440 Member Posts: 108
    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
    1003374 Member Posts: 23
    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
    user1983440 Member Posts: 108
    edited May 28, 2013 11:31AM
    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
    1003374 Member Posts: 23
    OK I'll try that and let you know
  • 1003374
    1003374 Member Posts: 23
    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
    BEDE Oracle Developer Member Posts: 2,475 Gold Trophy
    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
    1003374 Member Posts: 23
    The idea looks nice indeed, I'll test it and let you know
This discussion has been closed.