create table test1(sno number,old Varchar2(15),new varchar2(15),org varchar2(10),entity varchar2(15))
Table created.
create table test2(sno number,name Varchar2(15),org varchar2(10),entity varchar2(15),err_msg varchar2(200))
Table created.
insert all
into test2 values (1,'Donald','ERP','PD',NULL)
into test2 values (2,'Obama','ERP','PD',NULL)
into test2 values (3,'Hillary','ERP','PD',NULL)
into test2 values (4,'John','ERP','PD',NULL)
into test2 values (5,'Hoper','ERP','PD',NULL)
select * from dual
5 row(s) inserted.
insert all
into test1 values (1,'Donald','Trump','ERP','PD')
into test1 values (2,'Obama','Barack','ERP','PD')
into test1 values (3,'Hillary','Clinton','ERP','PD')
into test1 values (4,'John Hoper','JHoper','ERP','PD')
into test1 values (5,'TIM','T Martin','ERP','PD')
select * from dual
5 row(s) inserted.
create table test3(sno number,name Varchar2(15),org varchar2(10),entity varchar2(15),err_msg varchar2(200))
Table created.
select * from test1
SNO | OLD | NEW | ORG | ENTITY | 1 | Donald | Trump | ERP | PD | 2 | Obama | Barack | ERP | PD | 3 | Hillary | Clinton | ERP | PD | 4 | John Hoper | JHoper | ERP | PD | 5 | TIM | T Martin | ERP | PD |
---|
select * from test3
no data found
select * from test2
SNO | NAME | ORG | ENTITY | ERR_MSG | 1 | Donald | ERP | PD | - | 2 | Obama | ERP | PD | - | 3 | Hillary | ERP | PD | - | 4 | John | ERP | PD | - | 5 | Hoper | ERP | PD | - |
---|
select * from test3
no data found
alter table test3 drop column err_msg
Table altered.
insert all
into test3 values (1,'Will Smith','ERP','PD')
into test3 values (2,'Tiger Woods','ERP','PD')
into test3 values (3,'Modi','ERP','PD')
into test3 values (4,'John','ERP','PD')
into test3 values (5,'TIM','ERP','PD')
select * from dual
5 row(s) inserted.
select * from test3
SNO | NAME | ORG | ENTITY | 1 | Will Smith | ERP | PD | 2 | Tiger Woods | ERP | PD | 3 | Modi | ERP | PD | 4 | John | ERP | PD | 5 | TIM | ERP | PD |
---|
select * from test1
SNO | OLD | NEW | ORG | ENTITY | 1 | Donald | Trump | ERP | PD | 2 | Obama | Barack | ERP | PD | 3 | Hillary | Clinton | ERP | PD | 4 | John Hoper | JHoper | ERP | PD | 5 | TIM | T Martin | ERP | PD |
---|
select * from test2
SNO | NAME | ORG | ENTITY | ERR_MSG | 1 | Donald | ERP | PD | - | 2 | Obama | ERP | PD | - | 3 | Hillary | ERP | PD | - | 4 | John | ERP | PD | - | 5 | Hoper | ERP | PD | - |
---|
select * from test3
SNO | NAME | ORG | ENTITY | 1 | Will Smith | ERP | PD | 2 | Tiger Woods | ERP | PD | 3 | Modi | ERP | PD | 4 | John | ERP | PD | 5 | TIM | ERP | PD |
---|
select * from test2
SNO | NAME | ORG | ENTITY | ERR_MSG | 1 | Donald | ERP | PD | - | 2 | Obama | ERP | PD | - | 3 | Hillary | ERP | PD | - | 4 | John | ERP | PD | - | 5 | Hoper | ERP | PD | - |
---|
CREATE OR REPLACE PROCEDURE Update_Prc(p_Templ IN VARCHAR2,
P_ORG IN VARCHAR2) IS
L_TEMPLATE_NAME VARCHAR2(400);
CURSOR CUR_OU IS
SELECT ROWID ROW_ID, XMSI.*
FROM Test2 XMSI
WHERE XMSI.org = P_ORG
-- AND XMSI.entity = p_Templ
;
BEGIN
FOR CUR_GETR IN CUR_OU LOOP
BEGIN
SELECT NEW
INTO L_TEMPLATE_NAME
FROM Test1 LDM
WHERE 1 = 1
AND LDM.old = CUR_GETR.Name
AND LDM.org = CUR_GETR.ORG
AND LDM.ENTITY = 'PD'; --add org conditions
UPDATE Test2
SET TEMPLATE_NAME = L_TEMPLATE_NAME
WHERE ROWID = CUR_GETR.ROW_ID
AND L_TEMPLATE_NAME IS NOT NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT TEMPLATE_NAME
INTO L_TEMPLATE_NAME
FROM test3 MIT
WHERE MIT.name =CUR_GETR.Name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE Test2 XMSI
SET ERR_MsG = 'Template Not found in System'
WHERE ROWID = CUR_GETR.ROW_ID;
END;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The Out sode loop exception');
END;
Error at line: 7More Details: https://docs.oracle.com/error-help/db/ora-24344
select rowid from test1
ROWID | AAM+PkAAXAAAAS1AAA | AAM+PkAAXAAAAS1AAB | AAM+PkAAXAAAAS1AAC | AAM+PkAAXAAAAS1AAD | AAM+PkAAXAAAAS1AAE |
---|