Hi All,
i'm facing an issue with dynamic merge query.i'm performing all these inside a oracle procedure package to delete data from all schema tables with a config value.i'm using a loop
CREATE OR REPLACE EDITIONABLE PACKAGE BODY PKG_PACKAGE_1 As PROCEDURE TEST_PROCEDURE (
p_owner IN VARCHAR2,
p_tablename IN VARCHAR2,
p_retaindays INT,
p_position_flag IN CHAR,
p_active_run IN CHAR
)
AS
sqlstr VARCHAR2(2000);
test_id VARCHAR(1000);
test_name VARCHAR(1000);
testid_atribute VARCHAR(100);
testname_attribute VARCHAR(100);
data_exists INT;
v_partition INT;
BEGIN
SELECT count INTO data_exists
FROM test_table
where upper(owner) = upper(p_owner)
and upper(tablename) = case when p_tablename is null or p_tablename <>'' then upper(tablename) else upper(p_tablename) END
and active_flag ='y'
and position_flag = case when p_position_flag = 'y' THEN 'y' else 'N' END;
IF data_exists = 0 THEN
DBMS_OUTPUT.PUT_LINE ('no data found')
RETURN;
ELSE
FOR dat IN (
SELECT DISTINCT owner,tablename,columnname,retaindays,active_run
FROM test_table where upper(owner) = upper(p_owner) and upper(tablename) = case when p_tablename is null or p_tablename <>'' then upper(tablename) else upper(p_tablename) END and active_flag ='y'
)LOOP
---- Few more operations i do ------
---test_id-- it will be in mutliple tables--
SELECT COUNT(1) INTO v_partition
FROM all_tab_partitions
WHERE table_name = dat.object_name AND table_owner = dat.owner;
testid_atribute := '';
testname_attribute := '';
BEGIN
SELECT column_name INTO testid_atribute
from TABLES and with few conditions
END;
BEGIN
SELECT column_name INTO testname_attribute
from TABLES and with few conditions;
END;
FOR dates IN (
select datevalue from date_table )
LOOP
sqlstr := 'performing a select query'
EXECUTE IMMEDIATE sqlstr into test_id;
sqlstr := 'performing a select query'
EXECUTE IMMEDIATE sqlstr into test_name;
-----Here comes my error on the merge---
i need to insert the record if its a new entry with active_run flag . if the entry is already ther for owner,tablename,retaindays then i need to update the active_run either 'Y' or 'N'.
basically getting the config from test_table on owner,tablename,retaindays and storing the result in another tables.
RESULT TABLE
OWNER TABLENAME DATE_VALUE TEST_ID TEST_NAME COUNT active_run
XXX EMPLOYEE 12-MAR-2022 NULL NULL 445 Y
XXX STUDENT 20-JAN-2022 123 ABC 500 Y
my goal is to be take the count of the table EMPLOYEE and STUDENT and send the report. if they confirm i can delete then i will delete the records and update the active_run flag as 'N'
sqlstr:= 'MERGE INTO result_table d
USING (select dat.owner,dat.tablename,dates.datevalue,test_id,test_name FROM dual) s
ON (d.owner = s.owner AND d.object_name = s.object_name AND d.date_value = s.datevalue)
WHEN MATCHED THEN
UPDATE SET d.active_run = '|| p_active_run ||'
WHEN NOT MATCHED THEN
INSERT
(
d.owner
,d.tablename
,d.datevalue
,d.test_id
,d.test_name
,d.active_run
)
VALUES (
s.owner
,s.tablename
,s.datevalue
,s.test_id
,s.test_name
,'|| p_active_run ||'
)';
COMMIT;
EXECUTE IMMEDIATE sqlstr;
END;
-- some update scripts--
when i excute this merge is showing an error as invalid identifier on testname and test_id. my active_run flag is getting updated correctly since i pass as direct parameter.
but test_id and test_name and rest columns as i'm using it from EXECUTE IMMEDIATE sqlstr into test_id and EXECUTE IMMEDIATE sqlstr into test_name.
what wrong i'm doing here.What ever the column i giv here
USING (select dat.owner,dat.tablename,dates.datevalue,test_id,test_name FROM dual) s
its throwing error as invalid identifier
sorry for the long post.
thanks in advance