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
TAB17204
TAB14205
TAB13206
TAB19207
but How can i get?
TAB13204
TAB14205
TAB17206
TAB19207
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
