Why my Oracle script not giving any output?

Question

strong text``Hello All,

I have newly started with Oracle and trying write one script for below problem statement.

Problem Statement : I want to perform reconciliation for Two table Table A and Table B. based on fields present in Mapping Table with flag 1. In this case I am using Dynamic SQL to handle runtime column and join conditions but my query is not giving any output. Please give me solution or else any other method via I can perform this Dynamic Reconciliation in Oracle?

----------------------Reconciliation Query------------------------ **Note- Below query is running without any error but not giving any output. I don't want to use Cursor to print the result because I have Million Records. ** --code

DECLARE
v_sql VARCHAR2(32767);
v_columns VARCHAR2(32767);
v_join_condition VARCHAR2(32767);
v_column_list SYS.ODCIVARCHAR2LIST;
v_result SYS_REFCURSOR;
BEGIN
SELECT LISTAGG(Column_name, ',') WITHIN GROUP (ORDER BY Column_name)
INTO v_columns
FROM MappIng_table
WHERE Flag = 1;
-- Split the column list into individual column names
v_column_list := SYS.ODCIVARCHAR2LIST();
v_column_list.EXTEND(REGEXP_COUNT(v_columns, ',') + 1);
FOR i IN 1..v_column_list.COUNT LOOP
v_column_list(i) := REGEXP_SUBSTR(v_columns, '[^,]+', 1, i);   
END LOOP;

-- Build the join condition dynamically
v_join_condition := '';
FOR i IN 1..v_column_list.COUNT LOOP
IF i > 1 THEN
v_join_condition := v_join_condition || ' AND ';
END IF;
v_join_condition := v_join_condition || 'a.' || v_column_list(i) ||
'=b.' || v_column_list(i);
END LOOP;
v_sql := 'SELECT * FROM Table_A a full outer JOIN Table_B b ON ' || 
v_join_condition;
dbms_output.put_line(v_sql); 
EXECUTE IMMEDIATE v_sql;
END;

There's no "below query", and it is difficult to guess what might be wrong if you don't post code you used. — Littlefoot, Aug 17 '23 at 08:24
The reason why you're not seeing any results is because you're not doing anything with the results. You're attempting to select them (except you're not returning the contents into a variable or anything, so I'm not sure the query will even be run). Then you say you don't want to see the results because there will be millions of rows? Do you want to see the data or not?! Also, is this a one-off task (in which case, run the query output by the procedure yourself) or is it to be repeated? And what do you actually need to do with the results? — Boneist, Aug 18 '23 at 10:49
Hello Boneist, Yes I want to run and see the output of query string because I need that data further to perform Reconciliation. As this is reconciliation process, so it is gonna be repetitive task. Can you please suggest me what I need to do to see the above query result? Thank you. — munde shubhangi, Aug 18 '23 at 13:52
You need to decide what you need to do. Is the reconciliation to be manually done? Are you wanting the code to reconcile it? Perhaps you should make the query a ref cursor and then loop over the ref cursor, doing whatever reconciliation is needed. Perhaps you just need to output the query and run it manually. Perhaps you need to update the query and only return rows where it fails the reconciliation. There isn't enough information in your question for us to know how best to help you. — Boneist, Aug 21 '23 at 09:57

Valentin Tabacaru · Answer 1 · 2023-08-17T14:00:14.570

Same table will be used for the JOIN example. Use your two tables instead.

create table EMPLOYEES as select * from HR.EMPLOYEES;

As I understand, your objective is:

SELECT * FROM EMPLOYEES a full outer JOIN EMPLOYEES b ON a.EMPLOYEE_ID = b.EMPLOYEE_ID AND a.FIRST_NAME = b.FIRST_NAME AND a.LAST_NAME = b.LAST_NAME;

Mapping table:

CREATE TABLE MappIng_table (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 5) PRIMARY KEY,
  Column_name  VARCHAR2(128),
  Flag NUMBER default 0
);

Some Join condition columns:

insert into MAPPING_TABLE (column_name, flag) values ('EMPLOYEE_ID', 1);
insert into MAPPING_TABLE (column_name, flag) values ('FIRST_NAME', 1);
insert into MAPPING_TABLE (column_name, flag) values ('LAST_NAME', 1);
insert into MAPPING_TABLE (column_name) values ('EMAIL');
commit;

Individual Join conditions:

select 'a.' || Column_name || ' = b.' || Column_name as join_condition from MAPPING_TABLE where Flag = 1;

Entire join condition:

SELECT LISTAGG(jc.join_condition, ' AND ') WITHIN GROUP (order by jc.join_condition) as v_join_condition
  FROM (select 'a.' || Column_name || ' = b.' || Column_name as join_condition from MAPPING_TABLE where Flag = 1) jc;

Query returning the full statement:

with v_join_condition as (
    SELECT LISTAGG(jc.join_condition, ' AND ') WITHIN GROUP (order by jc.join_condition) as v_join_condition
        FROM (select 'a.' || Column_name || ' = b.' || Column_name as join_condition 
                from MAPPING_TABLE where Flag = 1) jc)
select 'SELECT * FROM EMPLOYEES a full outer JOIN EMPLOYEES b ON ' || vjc.v_join_condition as v_sql
    from v_join_condition vjc;

Posted. here I have given dummy name for table and column. Thank you — munde shubhangi, Aug 17 '23 at 11:26
Hello Valentin, Thanks for the solution. This logic is going with my problem statement. When I run this It not giving me any result. It jus printing below query. Can you please help me to get the data. Just, All tables contains the data still not giving any output. "SELECT * FROM scfx_so.app_vfr_edcdatadump a full outer JOIN scfx_so.app_vfr_vendordatadump b ON a.UDF1 = b.UDF1 AND a.UDF2 = b.UDF2 AND a.UDF3 = b.UDF3 AND a.UDF4 = b.UDF4" — munde shubhangi, Aug 18 '23 at 07:41

Why my Oracle script not giving any output?

1 Answers1