Thanks in Advance TOM
I am Srinadh And i have an issue in my code.
i am using dynamic sql to insert data into some of tables(Cursor C_TABLES ) from Views(Cursor DAIL_VIEWS ).
i am getting the above error and also i'm not sure can we use Dynamic Sql(Execute Immediate) inside the ForAll Insert ?
Could you please provide the solution for the above issue?
PROCEDURE IS_LOAD_LOGICAL_DATA_PR_SRI
IS
CURSOR DAIL_VIEWS IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE 'V_DART_%'
AND SUBSTR(VIEW_NAME, 8, 4) IN
(SELECT DOMAIN
FROM IS_DOMAIN_REVIEW_MASTER
WHERE DEL_FLG <> 'Y'
ORDER BY SUBSTR(VIEW_NAME, 8, 4);
CURSOR C_TABLES IS
SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'IS_DART_%'
ORDER BY SUBSTR(TABLE_NAME, 9, 4);
V_SQL VARCHAR2(1000);
TYPE CRS_IS_DART IS REF CURSOR;
V_CRS_IS_DART CRS_IS_DART;
TYPE CRS_IS_DART1 IS RECORD
(USUBJID VARCHAR2(1999),
STUDY VARCHAR2(1999),
PATIENT VARCHAR2(1999),
LSTCHGTS DATE,
DOCNUM VARCHAR2(1999),
LOGINTS DATE,
CUT_OFF_GROUP VARCHAR2(500),
PATIENT_STATUS VARCHAR2(4000)
);
TYPE CRS_IS_DART_COLL IS TABLE OF CRS_IS_DART1;
V_CRS_IS_DART_COLL CRS_IS_DART_COLL;
LV_DB_NAME VARCHAR2(100);
BEGIN
SELECT UPPER(substr(global_name, 1, (instr(global_name, '.') - 1)))
INTO LV_DB_NAME
FROM global_name;
IF LV_DB_NAME IN ('LSHDEV2', 'LSHTST') THEN
FOR DAIL_COUNT IN DAIL_VIEWS LOOP
FOR TAB_COUNT IN C_TABLES LOOP
IF SUBSTR(DAIL_COUNT.VIEW_NAME, 8, 4) =
SUBSTR(TAB_COUNT.TABLE_NAME, 9, 4) THEN
OPEN V_CRS_IS_DART FOR
'Dynamic Sql Select Statement using View name from Cursor DAIL_VIEWS and Some conditions ';
BEGIN
LOOP
FETCH V_CRS_IS_DART BULK COLLECT
INTO V_CRS_IS_DART_COLL;
FORALL I IN 1..V_CRS_IS_DART_COLL.COUNT
EXECUTE IMMEDIATE 'INSERT INTO '||TAB_COUNT.TABLE_NAME ||' VALUES '||V_CRS_IS_DART_COLL(I);
EXIT WHEN V_CRS_IS_DART%NOTFOUND;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
raise;
END;
END IF;
END LOOP;
END LOOP;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, SUBSTR(SQLERRM, 1, 500));
END IS_LOAD_LOGICAL_DATA_PR_SRI;
EXECUTE IMMEDIATE 'INSERT INTO '||TAB_COUNT.TABLE_NAME ||' VALUES '||V_CRS_IS_DART_COLL(I);
wont work because 'V_CRS_IS_DART_COLL' is a record, and you are trying to do a concatenate operation on a record.
You probably want to recode that as something like:
EXECUTE IMMEDIATE 'INSERT INTO '||TAB_COUNT.TABLE_NAME ||
' VALUES (:1,:2,:3,:4,:5,:6,:7,:8)'
using
V_CRS_IS_DART_COLL(I).USUBJID
,V_CRS_IS_DART_COLL(I).STUDY
,V_CRS_IS_DART_COLL(I).PATIENT
,V_CRS_IS_DART_COLL(I).LSTCHGTS
,V_CRS_IS_DART_COLL(I).DOCNUM
,V_CRS_IS_DART_COLL(I).LOGINTS
,V_CRS_IS_DART_COLL(I).CUT_OFF_GROUP
,V_CRS_IS_DART_COLL(I).PATIENT_STATUS