Hi, Answer for Q2 was not clear to me. Is it any data_dictionary you are hinting to? All_sequences or all_objects? but even in them i just have the name, how can i get the nextval executed from them.
Could you please write an example. For Q1, Tried the way you suggested. and it works. Appreciate it. Thanks On Thu, Sep 8, 2011 at 11:13 PM, Andrej Hopko <[email protected]> wrote: > Hi, > I won't do the script for you, just some hints to think about > > Q2. you have only two ways of solving names of sequences: > either you LOOP through the table TAB_SEQ_NAME as you showed OR you > assign convention (standard) over all project, that sequence for table XYZ > will be XYZ_SEQ (using same suffix for all sequences) > > Q1. why are you using MERGE when you use only UPDATE part? > > I would suggest using LOOP strategy and parametrized CURSOR (with > FOR UPDATE clause) with parameter of table_name column > CURSOR may be ORDERED BY column of your choosing > then just LOOP CURSOR and use UPDATE (where it is necessary) > > regards > > hoppo > > On 9. 9. 2011 7:26, Gayathri wrote: > > Hi All, I have 2 questions related to Merge. > Below is the code related to that: > > CREATE TABLE TAB_SEQ_NAME > (TABLE_NAME VARCHAR2(50), > SEQ_NAME VARCHAR2(50)); > > INSERT INTO TAB_SEQ_NAME VALUES('TAB1', 'TAB1_SEQ'); > INSERT INTO TAB_SEQ_NAME VALUES('TAB2', 'TAB2_SEQ'); > > CREATE TABLE TAB_KEYS > (TABLE_NAME VARCHAR2(50), > SOURCE_KEY NUMBER, > TARGET_KEY NUMBER); > > INSERT INTO TAB_KEYS VALUES('TAB1', 7, NULL); > INSERT INTO TAB_KEYS VALUES('TAB1', 4, NULL); > INSERT INTO TAB_KEYS VALUES('TAB1', 9, NULL); > INSERT INTO TAB_KEYS VALUES('TAB1', 3, NULL); > INSERT INTO TAB_KEYS VALUES('TAB2', 23, NULL); > INSERT INTO TAB_KEYS VALUES('TAB2', 20, NULL); > INSERT INTO TAB_KEYS VALUES('TAB2', 4, NULL); > INSERT INTO TAB_KEYS VALUES('TAB2', 1, NULL); > > CREATE SEQUENCE TAB1_SEQ > MINVALUE 200 > MAXVALUE 999999999999999999999999999 > START WITH 200 > INCREMENT BY 1; > > CREATE SEQUENCE TAB2_SEQ > MINVALUE 100 > MAXVALUE 999999999999999999999999999 > START WITH 100 > INCREMENT BY 1; > > > > SET SERVEROUTPUT ON; > DECLARE > V_SQL VARCHAR2(1000); > BEGIN > FOR I IN (SELECT TABLE_NAME, SEQ_NAME > FROM TAB_SEQ_NAME) LOOP > V_SQL := 'MERGE INTO TAB_KEYS TGT > USING (SELECT A.TABLE_NAME, A.SOURCE_KEY, A.TARGET_KEY, B.SEQ_NAME > FROM TAB_KEYS A, TAB_SEQ_NAME B > WHERE A.TABLE_NAME = B.TABLE_NAME > AND A.TABLE_NAME = ''' || I.TABLE_NAME || > ''' ORDER BY A.SOURCE_KEY) SRC > ON ( TGT.TABLE_NAME = SRC.TABLE_NAME AND TGT.SOURCE_KEY=SRC.SOURCE_KEY) > WHEN MATCHED THEN > UPDATE SET TGT.TARGET_KEY = ' || I.SEQ_NAME || '.NEXTVAL'; > > DBMS_OUTPUT.PUT_LINE(V_SQL); > > END LOOP; > END; > / > Output: > > MERGE INTO TAB_KEYS TGT > USING (SELECT A.TABLE_NAME, A.SOURCE_KEY, A.TARGET_KEY, B.SEQ_NAME > FROM TAB_KEYS A, TAB_SEQ_NAME B > WHERE A.TABLE_NAME = B.TABLE_NAME > AND A.TABLE_NAME = 'TAB1' ORDER BY A.SOURCE_KEY) SRC > ON ( TGT.TABLE_NAME = SRC.TABLE_NAME AND TGT.SOURCE_KEY=SRC.SOURCE_KEY) > WHEN MATCHED THEN > UPDATE SET TGT.TARGET_KEY = TAB1_SEQ.NEXTVAL; > > MERGE INTO TAB_KEYS TGT > USING (SELECT A.TABLE_NAME, A.SOURCE_KEY, A.TARGET_KEY, B.SEQ_NAME > FROM TAB_KEYS A, TAB_SEQ_NAME B > WHERE A.TABLE_NAME = B.TABLE_NAME > AND A.TABLE_NAME = 'TAB2' ORDER BY A.SOURCE_KEY) SRC > ON ( TGT.TABLE_NAME = SRC.TABLE_NAME AND TGT.SOURCE_KEY=SRC.SOURCE_KEY) > WHEN MATCHED THEN > UPDATE SET TGT.TARGET_KEY = TAB2_SEQ.NEXTVAL; > > > > > Here are my questions: > 1. When the statements are executed, I need the seq val to be generated as > per the order of the source_key. > > I got this > > TAB1 7 204 > TAB1 4 205 > TAB1 3 206 > TAB1 9 207 > > but How can i get? > > TAB1 3 204 > TAB1 4 205 > TAB1 7 206 > TAB1 9 207 > > 2. I am using the loop to substitute the seq name which is in the column. > Can the loop be avoided with just one merge statement? > > Thanks in advance. > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
