Value not getting pass from variable in string in PLSQL

Question

I have a For loop in plsql and a procedure. Inside the procedure I have IF block :

 IF c.payer='GCC' and  c.dataset='SAME' then
EMPID:=REGEXP_SUBSTR(c.hitablename,'[^_]+',1,4);
vbquery:='                          
Insert /*parallel(unk_codes)*/ into unk_codes(act_nbr, hitablename, groupid, payer)
select /* parallel(a,4) */ distinct '''||EMPID||'0100'' as  unknown_codes,'''||c.hitablename||''' as hitablename,
REGEXP_SUBSTR('''||c.hitablename||''',''[^_]+'',1,4) as groupid,'''||c.payer||''' as payer  from HI0777777.'||c.hitablename||' a
where '''||EMPID||'0100'' in  (select NVL(b.ACC_NUM,''NOT'') from group_abc b where
'''||EMPID||'0100'' = b.ACC_NUM
        AND
REGEXP_SUBSTR('''||c.hitablename||''',''[^_]+'',1,4)=b.group_id)';
BEGIN                   
EXECUTE IMMEDIATE vbquery;
EXCEPTION WHEN OTHERS THEN       
Dbms_Output.put_line('GCC SAME block'||SQLERRM||vbquery||c.ai_table);
END;
END IF;

I want to pass EMPID inside the vbquery string but I am getting ORA-00936: missing expression error. I tried to debugged and see what is the problem and I found EMPID value not getting replaced inside vbquery.

My vbquery is being printed as:

 ORA-00936: missing expression                                                                     
Insert /*parallel(unk_codes)*/ into unk_codes(act_nbr, hitablename, groupid, payer)
select /* parallel(a,4) */ distinct  unknown_codes,'HI_SAME_GCC_MPD' as hitablename,
REGEXP_SUBSTR('HI_SAME_GCC_MPD','[^_]+',1,4) as groupid,'GCC' as payer  from HI0777777.HI_SAME_GCC_MPD a
where not exists (select 1 from group_abc b where b.acc_num= and
REGEXP_SUBSTR('HI_SAME_GCC_MPD','[^_]+',1,4)=b.group_id)

My EMPID may be 'ABCD'. So my expected output was:

  Insert /*parallel(unk_codes)*/ into unk_codes(act_nbr, hitablename, groupid, payer)
    select /* parallel(a,4) */ distinct  'ABCD0100' unknown_codes,'HI_SAME_GCC_MPD' as hitablename,
    REGEXP_SUBSTR('HI_SAME_GCC_MPD','[^_]+',1,4) as groupid,'GCC' as payer  from HI0777777.HI_SAME_GCC_MPD a
    where not exists (select 1 from group_abc b where b.acc_num='ABCD0100' and
    REGEXP_SUBSTR('HI_SAME_GCC_MPD','[^_]+',1,4)=b.group_id)

Why is that EMPID value not getting passed in string?

In the vbquery example it is not only EMPID missing but '''0100' as" is missing too. Is it just a mistake in the example or they are really missing? — ekochergin
– ekochergin, Commented Mar 17, 2021 at 6:32
@ekochergin yes it is reallt missing. I dont know why it is missing — AB21
– AB21, Commented Mar 17, 2021 at 6:39
ps @AshwinKarki - sorry to mislead, I was wrong having tried it out now, so I have deleted my answer. eg DECLARE v_test VARCHAR2(4000); v_test2 VARCHAR2(4000); BEGIN SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') INTO v_test2 FROM dual; v_test := REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,'); dbms_output.put_line(v_test); dbms_output.put_line(v_test2); END; - both outputs show up fine — Chris
– Chris, Commented Mar 17, 2021 at 14:53

ekochergin · Accepted Answer · 2021-03-17 06:59:40Z

1

I think you're looking in a wrong place in code. In the plsql-example I see the following part in the query you're trying to build

... where '''||EMPID||'0100'' in  (select NVL(b.ACC_NUM,''NOT'') from group_abc b where...

but in the debug result of your query there is a part which is completely different and shouldn't be there at all

... where not exists (select 1 from group_abc b where b.acc_num= and ...

I'm pretty sure there is another branch in the "if" or something similar to that

answered Mar 17, 2021 at 6:59

ekochergin

4,1592 gold badges14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2021-03-17 07:26:17Z

You didn't say what values are involved so I tried to guess; also, I don't have your tables so I skipped a loop and used local variables instead.

It seems that either

c.hitablename doesn't look as you think it does, or
REGEXP_SUBSTR (which is used to set EMPID's value) doesn't work as you think it does as it returns an empty string, not e.g. ABCD (as you expected)

Because, if hitablename is something like in my example (X_Y_Z_ABCD), then the final insert statement looks OK:

SQL> set serveroutput on
SQL> DECLARE
  2     c_hitablename  VARCHAR2 (20) := 'X_Y_Z_ABCD';   --> this isn't what you think it is ...
  3     c_payer        VARCHAR2 (20) := 'xxxx';
  4     EMPID          VARCHAR2 (20);
  5     vbquery        VARCHAR2 (1000);
  6  BEGIN
  7     EMPID :=                                        --> ... or this doesn't do what you think it does
  8        REGEXP_SUBSTR (c_hitablename,
  9                       '[^_]+',
 10                       1,
 11                       4);
 12     vbquery :=
 13           '
 14  Insert /*parallel(unk_codes)*/ into unk_codes(act_nbr, hitablename, groupid, payer)
 15  select /* parallel(a,4) */ distinct '''
 16        || EMPID
 17        || '0100'' as  unknown_codes,'''
 18        || c_hitablename
 19        || ''' as hitablename,
 20  REGEXP_SUBSTR('''
 21        || c_hitablename
 22        || ''',''[^_]+'',1,4) as groupid,'''
 23        || c_payer
 24        || ''' as payer  from HI0777777.'
 25        || c_hitablename
 26        || ' a
 27  where '''
 28        || EMPID
 29        || '0100'' in  (select NVL(b.ACC_NUM,''NOT'') from group_abc b where
 30  '''
 31        || EMPID
 32        || '0100'' = b.ACC_NUM
 33          AND
 34  REGEXP_SUBSTR('''
 35        || c_hitablename
 36        || ''',''[^_]+'',1,4)=b.group_id)';
 37
 38     DBMS_OUTPUT.put_line (vbquery);
 39  END;
 40  /

Insert /*parallel(unk_codes)*/ into unk_codes(act_nbr, hitablename, groupid,
payer)
select /* parallel(a,4) */ distinct 'ABCD0100' as
unknown_codes,'X_Y_Z_ABCD' as
hitablename,
REGEXP_SUBSTR('X_Y_Z_ABCD','[^_]+',1,4) as groupid,'xxxx' as payer
from HI0777777.X_Y_Z_ABCD a
where 'ABCD0100' in  (select NVL(b.ACC_NUM,'NOT')
from group_abc b where
'ABCD0100' = b.ACC_NUM

AND
REGEXP_SUBSTR('X_Y_Z_ABCD','[^_]+',1,4)=b.group_id)

PL/SQL procedure successfully completed.

SQL>

Formatted:

INSERT                                                 /*parallel(unk_codes)*/
       INTO unk_codes (act_nbr,
                       hitablename,
                       groupid,
                       payer)
   SELECT                                                  /* parallel(a,4) */
          DISTINCT 'ABCD0100' AS unknown_codes,
                   'X_Y_Z_ABCD' AS hitablename,
                   REGEXP_SUBSTR ('X_Y_Z_ABCD',
                                  '[^_]+',
                                  1,
                                  4) AS groupid,
                   'xxxx' AS payer
     FROM HI0777777.X_Y_Z_ABCD a
    WHERE 'ABCD0100' IN (SELECT NVL (b.ACC_NUM, 'NOT')
                           FROM group_abc b
                          WHERE     'ABCD0100' = b.ACC_NUM
                                AND REGEXP_SUBSTR ('X_Y_Z_ABCD',
                                                   '[^_]+',
                                                   1,
                                                   4) = b.GROUP_ID)

Finally: what do you think those /*parallel(unk_codes)*/ thingies do? I bet you thought query will run faster if you executed it with that hint, didn't you? Bad news for you - what you put in here is just a comment, not a hint. Oracle suggests us (developers) not to use hints if we don't know what they do and how to properly use them. You, apparently, don't as you made the same mistake twice (so it wasn't a typo). Therefore, you can remove those comments out of your code (or use a hint if you really want to, but then use proper syntax).

Collectives™ on Stack Overflow

Value not getting pass from variable in string in PLSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related