CODEWITHSUNDEEP
oracle-database
Burhan Khalid Butt
Burhan Khalid Butt

Reputation: 275

Flow does not enter in the loop

Below is a PL/SQL. The problem is that the flow does not enter loop. I am unable to figure out what is the problem. Both the queries return results, i.e the query in the loop and the query within the loop does return results.

DECLARE
p_file_name VARCHAR2(4000) :='GHCPExtract_100_005_2011052218000700.csv';
v_file_name  VARCHAR2(4000) :='' || '''' || p_file_name ||'''';
v_count NUMBER :=0;
v_loop        NUMBER :=0;


begin
                  DBMS_OUTPUT.PUT_LINE( 'BEFORE LOOP');


FOR C IN (
        SELECT 
S.SOURCE_TRX_KEY_SEGMENT1    ,
S.SOURCE_TRX_KEY_SEGMENT2    ,
S.SOURCE_TRX_KEY_SEGMENT3    ,
S.SOURCE_TRX_KEY_SEGMENT4    ,
S.SOURCE_TRX_KEY_SEGMENT5    ,
S.SOURCE_TRX_KEY_SEGMENT6    
FROM DM_RS.STG_GHCP_EXTRACT S
WHERE S.SOURCE_FILE_NAME = v_file_name

)
LOOP
DBMS_OUTPUT.PUT_LINE( 'IN LOOP');
BEGIN

select 
1
into 
v_count
from

(
select 
T.SOURCE_TRX_KEY_SEGMENT1    ,
T.SOURCE_TRX_KEY_SEGMENT2    ,
T.SOURCE_TRX_KEY_SEGMENT3    ,
T.SOURCE_TRX_KEY_SEGMENT4    ,
T.SOURCE_TRX_KEY_SEGMENT5    ,
T.SOURCE_TRX_KEY_SEGMENT6    
from
GTT_SEGMENT_ID t
WHERE 1=1
AND 
T.SOURCE_TRX_KEY_SEGMENT1 = C.SOURCE_TRX_KEY_SEGMENT1
and
T.SOURCE_TRX_KEY_SEGMENT2 = C.SOURCE_TRX_KEY_SEGMENT2
and
T.SOURCE_TRX_KEY_SEGMENT3 = C.SOURCE_TRX_KEY_SEGMENT3
and
T.SOURCE_TRX_KEY_SEGMENT4 = C.SOURCE_TRX_KEY_SEGMENT4
and
T.SOURCE_TRX_KEY_SEGMENT5 = C.SOURCE_TRX_KEY_SEGMENT5
and 
T.SOURCE_TRX_KEY_SEGMENT6 = C.SOURCE_TRX_KEY_SEGMENT6
and t.source_file_name = v_file_name
);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_count := 0;
    END;

IF (v_count = 1)
THEN
V_LOOP := V_LOOP +1;
END IF;

END LOOP;

                  DBMS_OUTPUT.PUT_LINE( V_LOOP);
   END;

DBMS_OUTPUT

BEFORE LOOP

0

Upvotes: 0

Views: 218

Answers (1)

Stawros
Stawros

Reputation: 935

v_file_name  VARCHAR2(4000) :='' || '''' || p_file_name ||'''';
....
WHERE S.SOURCE_FILE_NAME =  '' || '''' || v_file_name ||''''

How many quotes contain SOURCE_FILE_NAME in table?

You can check count rows in query using temporary variable - 

SELECT count(1)
 INTO cnt_
 FROM DM_RS.STG_GHCP_EXTRACT S
WHERE S.SOURCE_FILE_NAME =  '''' || v_file_name ||'''';

DBMS_OUTPUT.put_line('rows count: '||cnt_);

Also, check that data in table commited and available in other session.

EDIT:

It's obvious that query in cycle returns no rows, because 'IN LOOP' doesn't printed in output.

How many rows return this query? - 

SELECT count(1)
  FROM dm_rs.stg_ghcp_extract s
 WHERE s.source_file_name = '' || '''' || 'GHCPExtract_100_005_2011052218000700.csv' || ''''

Btw, if you need only count of compared rows you can use one select with EXISTS instead of cycle.

SELECT count(1)
  into v_count
  FROM dm_rs.stg_ghcp_extract c
 WHERE c.source_file_name = v_file_name
   AND EXISTS
          (SELECT 1
             FROM gtt_segment_id t
            WHERE t.source_trx_key_segment1 = c.source_trx_key_segment1
              AND t.source_trx_key_segment2 = c.source_trx_key_segment2
              AND t.source_trx_key_segment3 = c.source_trx_key_segment3
              AND t.source_trx_key_segment4 = c.source_trx_key_segment4
              AND t.source_trx_key_segment5 = c.source_trx_key_segment5
              AND t.source_trx_key_segment6 = c.source_trx_key_segment6
              AND t.source_file_name = c.source_file_name)

Upvotes: 2

Related Questions