Solved

ORDER BY in MERGE statement USING clause

Posted on 2014-09-29
38
176 Views
Last Modified: 2015-02-18
Populate into relational (physical)table via nested table (collection).  
The collection has a field ITR_ORDER (iteration order) which is basically a sequence so need to read this collection in order and insert (if not matched) into table in order.
Somehow in the MERGE statement USING clause the ORDER BY is not working (compiles, but optimizer seems to ignore ORDER BY clause) as expected.., how to enforce it??

Alternative is looping and updated 0 rows then insert... Don''t want to use this approach.... want to do it via MERGE...
 
    MERGE INTO vendors dest
     USING (SELECT tt.*
            FROM TABLE(CAST(v_vendors_tbl AS vendors_tbl)) tt
            ORDER BY ITR_ORDER) src
     ON (dest.vendors_id = src.vendors_id)
     WHEN MATCHED THEN
        UPDATE ;
     WHEN NOT MATCHED THEN
        INSERT ;
0
Comment
Question by:loginboy
  • 17
  • 12
  • 3
  • +2
38 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
How do you know that is not working?
There's any cluster index on vendors_dest? If so, the records will be automatically ordered by the cluster index and not by the way rows are inserted.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>  how to enforce it??

you can't.  The merge is a set-based operation.  Ordering is (or at least should be) irrelevant to the functionality.

If you think the order should matter - please explain why/how it might affect the results.
0
 

Author Comment

by:loginboy
Comment Utility
Hii sdstuber,

Order matters since this destination table has self reference (emp-mgr) upto N-levels...
The rows in the collection are provided to the stored procedure with itR_order column populated as the order in which it needs be populated/stored....
0
 

Author Comment

by:loginboy
Comment Utility
@ Vitor Montalvão

2 ways, I added the trigger on the table, and the other, one of the user validation failing...
For example, a emp should have mgr and it din't get created.... as emp rec was inserted first and mgr later on...
0
 

Author Comment

by:loginboy
Comment Utility
@sdstuber and @ Vitor Montalvão

Sorry, One thing i missed in the code sample, please see the insert clause.... it will explain why order is important...
 

    MERGE INTO vendors dest
     USING (SELECT tt.*
            FROM TABLE(CAST(v_vendors_tbl AS vendors_tbl)) tt
            ORDER BY ITR_ORDER) src
     ON (dest.vendors_id = src.vendors_id)
     WHEN MATCHED THEN
        UPDATE ;
     WHEN NOT MATCHED THEN
        INSERT(ID, VAL, parent_id )  VALUES (sequence, tt.val, get_vendor_id(tt.parent_val));
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> Order matters since this destination table has self reference (emp-mgr) upto N-levels...

references have no dependencies on physical ordering (or, if they do, it's a design flaw)

>>> The rows in the collection are provided to the stored procedure with itR_order column populated as the order in which it needs be populated/stored....

since the column indicates the sorting criteria, this makes the physical ordering moot (which is a good thing)

In either case, what you "want" to happen, and what the syntax will "allow" you to do aren't compatible.
You can't do what you want.

If you post some sample data and expected query results perhaps we can help give you some ideas of how to implement a solution that doesn't make assumptions on physical ordering.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>  it will explain why order is important...

I'm sorry, but that revised example still doesn't show me why the ordering within a set-based operation should matter.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
ordering is  only of importance to users
databases (not instances) don't care about the order of things in life
0
 

Author Comment

by:loginboy
Comment Utility
Input (to stored procedure) Collection
Collection:
(ID               NUMBER,
 val              VARCHAR2(50),
parent_val  VARCHAR2(50),
ITR_ORDER NUMBER);

Collection Data:
NULL  A   NULL   1
NULL  X   NULL   2
NULL  B   A         3
NULL  C   B         4
NULL  Y    X        5

Destination TABLE:

Since all 5 rows go into NOT MATCH, need to insert it....
hence need to read the collection in this order using ITR_ORDER and insert in the same order...
For example...B get's created before C, so that while inserting C, another function  get_vendor_id gets ID of B (which just got inserted) and created parent-child relationship.....

Hope this clarifies...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
What is the exact error message you received when trying to use the MERGE?

>>WHEN NOT MATCHED THEN
         INSERT(ID, VAL, parent_id )  VALUES (sequence, tt.val, get_vendor_id
(tt.parent_val));

You cannot reference 'tt' here.  Is this a typo or an example of the actual code you are trying to run?

'tt' needs to be 'src':
WHEN NOT MATCHED THEN
         INSERT(ID, VAL, parent_id )  VALUES (sequence, src.val, get_vendor_id
(src.parent_val));
0
 

Author Comment

by:loginboy
Comment Utility
yes, it's src... tt is type there...

No error messages while merging... merge happens successfully but not the way i wanted....

Since order by is being ignored by oracle... C gets inserted before and B... and obviously get_vendor_id is returns NULL because at that point while inserting C it doesn't know what B is.... The column on the dest table is nullable hence no error there either...
this is why I need to rely on ORDER BY....

Note: For few records it is working fine ...may be by chance... but when given 10's of rows... the order by doesn't help....



I was answering other expert's question.. about how i know it is not working.... because later in the code some user validation is failing....
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>ON (dest.vendors_id = src.vendors_id)

Are you sure this is correct?

I'm thinking the src.vendors_id needs to be src.parent_vendors_id (or whatever the actual column is).
0
 

Author Comment

by:loginboy
Comment Utility
yes yes, that is correct... In early stage of execution path, SP populates this PK... so the MERGE performs somewhat better...

And the volume here is very low... so MERGE was used in the 1st place...

the key here is ORDER BY clause... and get_vendor_id (parent_val) ....  everything compiles and works most of the time... when it doesn't I know ORDER BY is the culprit...
0
 

Author Comment

by:loginboy
Comment Utility
Will this work?

select *
FROM (select *  
           from collection
           order by ITR_ORDER);

Since order by clause is the last clause to be executed by oracle in select statement...

Will test if it works in MERGE...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Will this work?

I doubt it.

By chance does get_vendor_id query the vendors table?

If so I'm thinking this had to be declared as an autonomous transaction which is likely causing the issue.  Until the MERGE has committed, the call to get_vendor_id cannot see the 'new' rows.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
If it is an autonomous transaction issue you'll need to do two merges.

first one grabs all the rows where parent_val is null
commit it
then all the ones where parent_val is not null
0
 

Author Comment

by:loginboy
Comment Utility
You are right, it's not working... just did a test.

get_vendor_id queries the same table it's merging on....

Don't think autonomous transaction will help... as this is happening in the same transaction... it is working fine most of the time... with less rows in collection...
0
 

Author Comment

by:loginboy
Comment Utility
2 merges - autonomous block sol...

>> then all the ones where parent_val is not null

Still the order has to be maintained... inthe example i've given both B's and C's parent is not null... but it will work only if they are populated in order so the B and C can be related....
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Don't think autonomous transaction will help... as

Check the function code.  I'm saying that it pretty much has to be autonomous already.

You cannot query a table in a function or trigger you are performing DML on or you'll get:
ORA-04091: table <table_name> is mutating, trigger/function may not see it
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>inthe example i've given both B's and C's parent is not null...

You are correct.  Missed that.

Anyway: sdstuber has already posted that you cannot ORDER BY in a MERGE.

Let me think about this for a while.  Maybe one of the other Experts will have an idea before I do.
0
 

Author Comment

by:loginboy
Comment Utility
Actually, I din't understand sdstuber answer totally ...  :-(

I thought he wanted more details regarding the question...

Shouldn't there be a way to tell oracle and enforce order by clause (apart from just mentioning it)....
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Above where he asked for data he posted:
In either case, what you "want" to happen, and what the syntax will "allow" you to do aren't compatible.
 You can't do what you want.


I still think the issue is that get_vendor_id is declared autonomous.  Even if you could use an order by in the merge, it wouldn't return the correct id's because the function can't see them until the merged data is committed.

Check the function:
select dbms_metadata.get_ddl('FUNCTION','GET_VENDOR_ID') from dual;

See if it has:
PRAGMA AUTONOMOUS_TRANSACTION;
0
 

Author Comment

by:loginboy
Comment Utility
No Sir, the function doesn't have PRAGMA AUTONOMOUS_TRANSACTION... I've written it from scratch...
The function is in package... not standalone... Thanks though, I learnt something new...
 

yes, sdstuber said that... but i believe he gave 2 reasons (which seemed contradictory to me) and those reasons i din't understand...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I don't see how you can select from the vendors table in the function and merge into the vendors table in the same session without getting an ORA-04091.

Nothing I can set up on my end allows that.

Can you create a small test case complete with stand-alone function that we can create on our systems?  This will allow all of us to work from the same exact model.

The test case doesn't need the vendors_tbl type.  Just provide data for an actual table:
vendors_tbl.

Here is the basics.  You just need to fill in the rest and provide the expected results after the merge.

create table vendors(
	--need columns
);

create or replace function get_vendor_id(p_val in number) return varchar2
is
begin
--need sample code
end;
/

create table vendors_tbl (
ID          NUMBER,
val         VARCHAR2(50),
parent_val  VARCHAR2(50),
ITR_ORDER   NUMBER
);


insert into vendors_tbl values(NULL,'A', NULL,1);
insert into vendors_tbl values(NULL,'X', NULL,2);
insert into vendors_tbl values(NULL,'B','A',3);
insert into vendors_tbl values(NULL,'C','B',4);
insert into vendors_tbl values(NULL,'Y',' X',5);
commit;


merge into vendors
	-- sample merge that 'fails'

--What are the expected results after the merge from
select * from vendors;

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I'm going to explain you in other words.
There's any clustered index in vendors table? If so, please post the definition of that clustered index.
0
 

Author Comment

by:loginboy
Comment Utility
@ Vitor Montalvão

How to check if there are any clustered indexes...

here are the indexes on the table
Unique Index on ID  (Sequence, PK)    -> MERGE join happens on this column
Unique Index on val, parent_GROUP
NON - Unique Index on parent_val_id
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
In Management Studio, check in the Indexes folder of the table. If there's a clustered index no matter  the order you insert the records they will be physically ordered by the clustered index definition.
Indexes.JPG
0
 

Author Comment

by:loginboy
Comment Utility
@slightwv, @sdstuber

I've created the complete model with test data... please see if it helps in providing the ideas...

Bottom Line: Enforce oracle to use ORDER BY in MERGE statement....

-----------------------------------------------------------
DROP TABLE val;
DROP TYPE val_obj;  
DROP TYPE val_tbl; 
DROP FUNCTION get_val_id; 
/
-----------------------------------------------------------
CREATE TABLE val(val_id	          NUMBER,
                 val_cd	          VARCHAR2(50),
                 prnt_val_id	    NUMBER,
                 prnt_grp_val_id	NUMBER,
                 upd_dt           DATE,
                 ins_dt           DATE);
/
-----------------------------------------------------------
CREATE OR REPLACE TYPE val_obj AS OBJECT
( val_id	          NUMBER,
  val_cd	          VARCHAR2(50),
  prnt_val_cd	      VARCHAR2(50), -- To retrieve the val_id for the records which got created as part of same transaction when ordered by itr_order asc.   
  prnt_grp_val_cd	  VARCHAR2(50), -- To retrieve the val_id for the records which got created as part of same transaction when ordered by itr_order asc. 
  itr_order         NUMBER,       -- For iterating/processing the array in order.    
  
STATIC FUNCTION initialize RETURN val_obj);
/

CREATE OR REPLACE TYPE BODY val_obj  AS
 STATIC FUNCTION initialize RETURN val_obj IS
 BEGIN
   RETURN(val_obj(NULL,
                  NULL,
                  NULL,
                  NULL,                        
                  NULL));
 END initialize;
END;
/

CREATE OR REPLACE TYPE val_tbl IS TABLE OF val_obj;
/
-----------------------------------------------------------

  CREATE OR REPLACE FUNCTION get_val_id(in_val_cd           IN VARCHAR2,
                                        in_prnt_grp_val_cd  IN VARCHAR2,
                                        in_val_type         IN VARCHAR2) RETURN val.val_id%TYPE IS -- GROUP, PARENT, VALUE
   --     
   v_val_id      val.val_id%TYPE := NULL;
   --  
  BEGIN
      --
      IF (in_val_type = 'GROUP') THEN    -- in_prnt_grp_val_id IS NULL AND in_prnt_grp_val_cd IS NULL
        --
        SELECT v.val_id
          INTO v_val_id
          FROM val v
        WHERE v.val_cd = in_val_cd
          AND v.prnt_val_id IS NULL
          AND v.prnt_grp_val_id IS NULL;
        --     
      ELSE
        --      
        SELECT v.val_id
          INTO v_val_id
          FROM val v
        WHERE v.val_cd = in_val_cd
          AND v.prnt_grp_val_id = (SELECT v.val_id
                                      FROM val v
                                    WHERE v.val_cd = in_prnt_grp_val_cd
                                      AND v.prnt_val_id IS NULL
                                      AND v.prnt_grp_val_id IS NULL);
          --   
      END IF;
    --    
    RETURN v_val_id;
    --  
  EXCEPTION
    WHEN OTHERS THEN 
    RETURN v_val_id; 
  END get_val_id;
  /
-----------------------------------------------------------
DECLARE
  --
  CURSOR val_cur 
  IS
  SELECT val_obj(tt.val_id, tt.val_cd, tt.prnt_val_cd, tt.prnt_grp_val_cd, tt.itr_order)
  FROM (SELECT DISTINCT y.*
         FROM ( SELECT NULL val_id, 'A' val_cd, NULL prnt_val_cd, 'GRP1' prnt_grp_val_cd, 1 itr_order FROM dual
                UNION ALL
                SELECT NULL val_id, 'X' val_cd, NULL prnt_val_cd, 'GRP2' prnt_grp_val_cd, 2 itr_order FROM dual
                UNION ALL
                SELECT NULL val_id, 'B' val_cd, 'A' prnt_val_cd, 'GRP1' prnt_grp_val_cd, 3 itr_order FROM dual
                UNION ALL
                SELECT NULL val_id, 'C' val_cd, 'B' prnt_val_cd, 'GRP1' prnt_grp_val_cd, 4 itr_order FROM dual
                UNION ALL
                SELECT NULL val_id, 'Y' val_cd, 'X' prnt_val_cd, 'GRP2' prnt_grp_val_cd, 5 itr_order FROM dual
                ORDER BY 5) y) tt;
  --                
  v_val_tbl   val_tbl := val_tbl();      
  --                    
BEGIN
  --
  OPEN val_cur;
  FETCH val_cur BULK COLLECT INTO v_val_tbl;
  CLOSE val_cur;
  --  
  MERGE INTO val dest
   USING (SELECT tt.* 
           FROM TABLE(CAST(v_val_tbl AS val_tbl)) tt
          ORDER BY tt.itr_order) src
   ON (dest.val_id = src.val_id)
   WHEN MATCHED THEN
      UPDATE 
      SET dest.upd_dt = SYSDATE
   WHEN NOT MATCHED THEN
      INSERT (dest.val_id,
              dest.val_cd,
              dest.prnt_val_id,
              dest.prnt_grp_val_id,
              dest.upd_dt,
              dest.ins_dt)
      VALUES (123, -- ideally using sequence
              src.val_cd,
              get_val_id(in_val_cd => src.prnt_val_cd, in_prnt_grp_val_cd => src.prnt_grp_val_cd, in_val_type => NULL),
              get_val_id(in_val_cd => src.prnt_grp_val_cd, in_prnt_grp_val_cd => NULL, in_val_type => 'GROUP'),
              SYSDATE,                
              SYSDATE);
END;
/  
-----------------------------------------------------------

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
OK, look in the get_val_id function you posted.

Comment out:
EXCEPTION
    WHEN OTHERS THEN
    RETURN v_val_id;


Run your test.

You should get:
ORA-04091: table <user>.VAL is mutating, trigger/function may not see it

So, with the exception in the function, it will always return a null.
0
 

Author Comment

by:loginboy
Comment Utility
Yes, I'm able to reproduce the ORA-04091 error...
1) So, ORDER BY is working correctly?? Because the trigger (not in the sample code) on table says the order by is not working correctly...
2) Also, Why do we get mutating error  here?
3) Using for loop and function without exception is working, why not mutating error here... see attachment
4) What are my options now!?!!
abc.sql
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>1) So, ORDER BY is working correctly??

No.  We've already answered that:  Order by does NOT work with MERGE.

>>2) Also, Why do we get mutating error  here?

I've already stated this:
You cannot query the same table you are performing DML on.

>>3) Using for loop and function without exception is working, why not mutating error

Not sure.  However, if you change the exception to a NO_DATA_FOUND exception, you will see that the test case as posted, still always returns a NULL value form the function because the selects don't actually return data.

...
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
    dbms_output.put_line('GOT NO DATA');
    return null;
  END get_val_id;

Open in new window


>>4) What are my options now!?!!

Looks like you have a logic bug.  Even using the LOOP code you posted, get_val_id doesn't return values.
0
 

Accepted Solution

by:
loginboy earned 0 total points
Comment Utility
1)  >> No.  We've already answered that:  Order by does NOT work with MERGE.
    Please help me understand the reason(s) or please point me to some article/forum

2) >> You cannot query the same table you are performing DML on
Apparently, this is valid only in MERGE statement and TRIGGER(S). Since the FOR LOOP approach doesn't give any errors.

3) The function didn't work because of some other bug and not EXCEPTION BLOCK...
I re-wrote the function to keep very simple and yes, the function is working fine when exec from insert statement on the same table...
CREATE SEQUENCE val_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 ;

DECLARE
  --
  v_val_tbl   val_tbl := val_tbl();      
  --                    
BEGIN
  --
  FOR rec in (SELECT NULL val_id, 'A' val_cd, NULL prnt_val_cd, 'GRP1' prnt_grp_val_cd, 1 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'X' val_cd, NULL prnt_val_cd, 'GRP2' prnt_grp_val_cd, 2 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'B' val_cd, 'A' prnt_val_cd, 'GRP1' prnt_grp_val_cd, 3 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'C' val_cd, 'B' prnt_val_cd, 'GRP1' prnt_grp_val_cd, 4 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'Y' val_cd, 'X' prnt_val_cd, 'GRP2' prnt_grp_val_cd, 5 itr_order FROM dual
              ORDER BY 5)
  LOOP
    --
    UPDATE val v
    SET v.upd_dt = SYSDATE
    WHERE v.val_id = rec.val_id;
    --
    IF (SQl%ROWCOUNT =0 ) THEN
      --
      INSERT INTO val(val_id,
                      val_cd,
                      prnt_val_id,
                      prnt_grp_val_id,
                      upd_dt,
                      ins_dt)
      VALUES (val_seq.nextval, -- ideally using sequence
              rec.val_cd,
              get_val_id(in_val_cd => rec.prnt_val_cd, in_prnt_grp_val_cd => rec.prnt_grp_val_cd, in_val_type => NULL),
              get_val_id(in_val_cd => rec.prnt_grp_val_cd, in_prnt_grp_val_cd => NULL, in_val_type => 'GROUP'),
              SYSDATE,                
              SYSDATE);
      --
    END IF;
    --    
  END LOOP;
  --  
END;
/  
select * from val;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Below is my complete test case and my results.

I still get NULL values from get_val_id.

Please post what the expected results are supposed to be.

Setup:
-----------------------------------------------------------
DROP TABLE val purge;
DROP FUNCTION get_val_id; 
DROP TYPE val_tbl; 
DROP TYPE val_obj;  
/
-----------------------------------------------------------
CREATE TABLE val(val_id	          NUMBER,
                 val_cd	          VARCHAR2(50),
                 prnt_val_id	    NUMBER,
                 prnt_grp_val_id	NUMBER,
                 upd_dt           DATE,
                 ins_dt           DATE)
/
-----------------------------------------------------------
CREATE OR REPLACE TYPE val_obj AS OBJECT
( val_id	          NUMBER,
  val_cd	          VARCHAR2(50),
  prnt_val_cd	      VARCHAR2(50), -- To retrieve the val_id for the records which got created as part of same transaction when ordered by itr_order asc.   
  prnt_grp_val_cd	  VARCHAR2(50), -- To retrieve the val_id for the records which got created as part of same transaction when ordered by itr_order asc. 
  itr_order         NUMBER,       -- For iterating/processing the array in order.    
  
STATIC FUNCTION initialize RETURN val_obj);
/

drop SEQUENCE val_seq;
CREATE SEQUENCE val_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 ;

CREATE OR REPLACE TYPE BODY val_obj  AS
 STATIC FUNCTION initialize RETURN val_obj IS
 BEGIN
   RETURN(val_obj(NULL,
                  NULL,
                  NULL,
                  NULL,                        
                  NULL));
 END initialize;
END;
/

CREATE OR REPLACE TYPE val_tbl IS TABLE OF val_obj;
/
-----------------------------------------------------------

CREATE OR REPLACE FUNCTION get_val_id(in_val_cd           IN VARCHAR2,
                                        in_prnt_grp_val_cd  IN VARCHAR2,
                                        in_val_type         IN VARCHAR2) RETURN val.val_id%TYPE IS -- GROUP, PARENT, VALUE
   --     
   v_val_id      val.val_id%TYPE := NULL;
   --  
  BEGIN
      --
      IF (in_val_type = 'GROUP') THEN    -- in_prnt_grp_val_id IS NULL AND in_prnt_grp_val_cd IS NULL
        --
        SELECT v.val_id
          INTO v_val_id
          FROM val v
        WHERE v.val_cd = in_val_cd
          AND v.prnt_val_id IS NULL
          AND v.prnt_grp_val_id IS NULL;
        --     
      ELSE
        --      
        SELECT v.val_id
          INTO v_val_id
          FROM val v
        WHERE v.val_cd = in_val_cd
          AND v.prnt_grp_val_id = (SELECT v.val_id
                                      FROM val v
                                    WHERE v.val_cd = in_prnt_grp_val_cd
                                      AND v.prnt_val_id IS NULL
                                      AND v.prnt_grp_val_id IS NULL);
          --   
      END IF;
    --    
    RETURN v_val_id;
    --  
  --EXCEPTION
    --WHEN OTHERS THEN 
    --RETURN v_val_id; 
  END get_val_id;
  /

  show errors
-----------------------------------------------------------
DECLARE
  --
  v_val_tbl   val_tbl := val_tbl();      
  --                    
BEGIN
  --
  FOR rec in (SELECT NULL val_id, 'A' val_cd, NULL prnt_val_cd, 'GRP1' prnt_grp_val_cd, 1 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'X' val_cd, NULL prnt_val_cd, 'GRP2' prnt_grp_val_cd, 2 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'B' val_cd, 'A' prnt_val_cd, 'GRP1' prnt_grp_val_cd, 3 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'C' val_cd, 'B' prnt_val_cd, 'GRP1' prnt_grp_val_cd, 4 itr_order FROM dual
              UNION ALL
              SELECT NULL val_id, 'Y' val_cd, 'X' prnt_val_cd, 'GRP2' prnt_grp_val_cd, 5 itr_order FROM dual
              ORDER BY 5)
  LOOP
    --
    UPDATE val v
    SET v.upd_dt = SYSDATE
    WHERE v.val_id = rec.val_id;
    --
    IF (SQl%ROWCOUNT =0 ) THEN
      --
      INSERT INTO val(val_id,
                      val_cd,
                      prnt_val_id,
                      prnt_grp_val_id,
                      upd_dt,
                      ins_dt)
      VALUES (val_seq.nextval, -- ideally using sequence
              rec.val_cd,
              get_val_id(in_val_cd => rec.prnt_val_cd, in_prnt_grp_val_cd => rec.prnt_grp_val_cd, in_val_type => NULL),
              get_val_id(in_val_cd => rec.prnt_grp_val_cd, in_prnt_grp_val_cd => NULL, in_val_type => 'GROUP'),
              SYSDATE,                
              SYSDATE);
      --
    END IF;
    --    
  END LOOP;
  --  
END;
/  
-----------------------------------------------------------

select * from val;

Open in new window


My results:
         1 A
09/30/2014 13:03:48 09/30/2014 13:03:48

         2 X
09/30/2014 13:03:48 09/30/2014 13:03:48

         3 B
09/30/2014 13:03:48 09/30/2014 13:03:48

         4 C
09/30/2014 13:03:48 09/30/2014 13:03:48

         5 Y
09/30/2014 13:03:48 09/30/2014 13:03:48

Open in new window

0
 

Author Comment

by:loginboy
Comment Utility
here is my output

6      A                  30-SEP-14      30-SEP-14
7      X                  30-SEP-14      30-SEP-14
8      B      6            30-SEP-14      30-SEP-14
9      C      8            30-SEP-14      30-SEP-14
10      Y      7            30-SEP-14      30-SEP-14
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
That is not what I get running the sample I posted.

My sample should be the same as what you posted.

Run mine including the drop table and confirm your results.

I notice your sequence starts with 6.  This means you already had 5 rows in the table. These 5 were likely committed so the get_val_id function actually has rows to query.
0
 

Author Closing Comment

by:loginboy
Comment Utility
Din't get any alternative solution. Just have been told that it will not work.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now