1

I am trying to use execute immediate as a special requirement for assigning the value of a variable. I am using the following code and getting the exception as below.

declare
  lv_kyc_main_GBL KYC_GBL_MAIN.KYC_MAIN%rowtype;
  l_str varchar2(400);

  a number(10);
begin
  select *
    into lv_kyc_main_GBL
    from KYC_GBL_MAIN.KYC_MAIN
   where rownum = 1;

  -- l_str:='lv_kyc_main_GBL.legal_name'||':='||'''TEST''';

  l_str := 'lv_kyc_main_GBL.legal_name := ''TEST''';
  dbms_output.put_line(l_str);

  execute immediate (l_str);
end;
/

The exception I'm getting is:

anonymous block completed
lv_kyc_main_GBL.legal_name := 'TEST'

Error starting at line 4 in command:

declare
  lv_kyc_main_GBL KYC_GBL_MAIN.KYC_MAIN%rowtype;
  l_str varchar2(400);

  a number(10);
begin
  select *
    into lv_kyc_main_GBL
    from KYC_GBL_MAIN.KYC_MAIN
   where rownum = 1;

  --l_str:='lv_kyc_main_GBL.legal_name'||':='||'''TEST''';

  l_str := 'lv_kyc_main_GBL.legal_name := ''TEST''';
  dbms_output.put_line(l_str);

  execute immediate (l_str);
end;
/

Error report:
ORA-00900: invalid SQL statement
ORA-06512: at line 14
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:
lv_kyc_main_GBL.legal_name := 'TEST'

I can't understand what is wrong in the following assignment. It just works fine if I do the assignment separately as:

lv_kyc_main_GBL.legal_name := 'TEST'
diziaq
  • 6,881
  • 16
  • 54
  • 96
Aditya Raman
  • 309
  • 8
  • 19

1 Answers1

6

I am not really sure what you are trying to achieve, but to run a PLSQL block (ie not an SQL statement) in execute immediate, you need to wrap it in begin ... end;

Also, you cannot assign to a variable outside the execute immediate block from within it, so in your example, attempting to assign directly to lv_kyc_main_GBL.legal_name will not work. You need to use bind variables for this. I think something like the following will work (I don't have Oracle running here right now):

declare
  lv_kyc_main_GBL KYC_GBL_MAIN.KYC_MAIN%rowtype;
  l_str varchar2(400);

  a number(10);
begin
 select * into lv_kyc_main_GBL
 from KYC_GBL_MAIN.KYC_MAIN where rownum=1;

 l_str := 'begin :b1 := ''TEST''; end';
 dbms_output.put_line(l_str);

 execute immediate (l_str) using out lv_kyc_main_GBL.legal_name;
end;
Stephen ODonnell
  • 4,441
  • 17
  • 19
  • Thanks a ton. This works. If I am using two bind variables b1 and b2 there how should the execute immediate statement look like ? – Aditya Raman Feb 11 '16 at 12:51
  • 2
    Just separate the parameters with comma `using out lv_kyc_main_GBL.legal_name, anotherVar;` – Jorge Campos Feb 11 '16 at 12:53
  • @JorgeCampos: The main objective behind using an assignment like that is I am having a varchar field_name whose value denote the field name of lv_kyc_main_GBL row variable and another varchar field_value which denote the value of lv_kyc_main_GBL.field_name. I need to run something like lv_kyc_main_GBL.field_name=field_value . Where field_name and field_value are both variables. How to solve such a case ? – Aditya Raman Feb 11 '16 at 14:07
  • 1
    `l_str := 'begin :b1 := :b2; end';` and pass both variables as mentioned im my earlier comment. – Jorge Campos Feb 11 '16 at 14:09
  • @JorgeCampos Just saw this comment now, this should work. Thanks a lot – Aditya Raman Jul 01 '20 at 15:16