SQL Error: ORA-00984: column not allowed here 00984. 00000 - "column not allowed here"

Question

i am getting error: Error at Command Line : 45 Column : 111 Error report - SQL Error: ORA-00984: column not allowed here 00984. 00000 - "column not allowed here"

query:

Insert into BL_DIFF_QUERY_ANL (TABLE_NAME,ISSUSE,ISSUE_CATEGORY,ISSUE_ID,DB_QUERY) 
values ('BILL','TOTAL_BILLED_ADJUST_MISMATCH','TOTAL_BILLED_ADJUST',2,
'DECLARE 
V_DIFF_AMT_chr varchar2(20);
V_DIFF_AMT NUMBER(9,2);
V_SUM_ACTV_AMT NUMBER(9,2);
V_SUM_ACTV_TAXES_AMT NUMBER(9,2);
V_COUNT NUMBER(1);
v_val_Done_by varchar(25) ;
v_ban number(10) := 339339856;
v_comments varchar(20);
v_success varchar(10) := ''SUCCESS'';
v_yesnoind varchar(1) := ''Y'';
v_immediate_Adj varchar (30) := ''IMMEDIATE_ADJUSTMENT'';
v_issue_Desc  varcahr(50) := ''TOTAL_BILLED_ADJUST_MISMATCH'';
BEGIN 
DBMS_OUTPUT.Put_line (''BAN: '' || :1 || ''  ACTV_BILL_SEQ_NO : '' || :2 || '' SUBSCRIBER: '' || :3 
|| ''COLUMN_NAME : '' || :4 || '' COLUMN_DATA: '' || :5 || ''DIFF_DATA : '' || :6 || '' SOC: '' || 
:7 || '' FEATURE_CODE: '' || :8 || '' FTR_REVENUE_CODE: '' || :9 || '' PRIOD_CVRG_ST_DATE: '' || :10 
|| '' PRIOD_CVRG_ND_DATE: '' || :11 || '' ACTV_REASON_CODE: '' || :12 || ''BALANCE_IMPACT_CODE: '' 
|| :13 || ''  SOURCE_APPL_CODE : '' || :14 || '' DISCOUNT_CD: '' || :15 || '' BILL_MEDIA : '' || :16 
|| '' BILL_FORMAT : '' || :17 || '' PRODUCT_TYPE: '' || :18 || '' FTR_TYPE '' || :19 || '' VAL_ID '' 
|| :20 );

select ''comments_''|| :20  into v_comments from dual;
select ''val_done_by_''|| :20  into  v_VAL_DONE_BY from dual;
SELECT COUNT (distinct tax_ind ) into V_COUNT FROM SERVICE_AGREEMENT WHERE BAN = :1 and SERVICE_TYPE 
= ''P'' and EXPIRATION_DATE is NULL and tax_ind = ''TI''; 
DBMS_OUTPUT.Put_line (V_COUNT);
if (V_COUNT = 0) 
then 
SELECT ABS ( (nvl(sum(t1.ACTV_AMT),0) - nvl(sum(t2.ACTV_AMT),0)) + (nvl(sum(t1.TAX_CITY_CUST_AMT),0) 
- nvl(sum(t2.TAX_CITY_CUST_AMT),0)) + (nvl(sum(t1.TAX_COUNTY_CUST_AMT),0) 
nvl(sum(t2.TAX_COUNTY_CUST_AMT),0)) + (nvl(sum(t1.TAX_STATE_CUST_AMT),0) - 
nvl(sum(t2.TAX_STATE_CUST_AMT),0)) + (nvl(sum(t1.TAX_FEDERAL_AMT),0) - 
nvl(sum(t2.TAX_FEDERAL_AMT),0)) + (nvl(sum(t1.TAX_ROAMING_AMT),0) - nvl(sum(t2.TAX_ROAMING_AMT),0)) 
) into V_SUM_ACTV_TAXES_AMT 
from QATAPP50.ADJUSTMENT t1 ,QATAPP55.ADJUSTMENT@abc_test t2 where t1.BAN = :1 and t2.ban=t1.ban and 
t1.ACTV_BILL_SEQ_NO = :2 and t2.ACTV_BILL_SEQ_NO = t1.ACTV_BILL_SEQ_NO 
and t1.balance_impact_code = ''I'' and t2.balance_impact_code = t1.balance_impact_code and 
nvl(t1.CHARGE_SEQ_NO,0) = nvl(t2.CHARGE_SEQ_NO,0);

else 
SELECT ABS( nvl(sum(t1.ACTV_AMT),0) - nvl(sum(t2.ACTV_AMT),0) ) into V_SUM_ACTV_AMT 
from QATAPP50.ADJUSTMENT t1 ,QATAPP55.ADJUSTMENT@abc_test t2 where t1.BAN = :1 
and t2.ban=t1.ban and t1.ACTV_BILL_SEQ_NO = :2 and t2.ACTV_BILL_SEQ_NO = t1.ACTV_BILL_SEQ_NO 
and t1.balance_impact_code = ''I'' and t2.balance_impact_code = t1.balance_impact_code 
and nvl(t1.CHARGE_SEQ_NO,0) = nvl(t2.CHARGE_SEQ_NO,0);
end if;
DBMS_OUTPUT.Put_line (V_SUM_ACTV_TAXES_AMT);
DBMS_OUTPUT.Put_line (V_SUM_ACTV_AMT);

if ( V_SUM_ACTV_AMT = V_DIFF_AMT or V_SUM_ACTV_TAXES_AMT = V_DIFF_AMT ) 
then 

execute immediate ''UPDATE BL_DIFF_CATEGORY SET VALIDATION_STS = :2' ||','|| v_VAL_DONE_BY || '='|| 
:3 ||','||  v_comments || '= :4  where BAN = :1 and  
DIFF_TYPE = :5
'' using v_issue_Desc,v_immediate_Adj,v_yesnoind,v_success,v_ban;

 COMMIT;
 END IF;
END;'
);

going by the line and column no,the error is at v_comments in execute immediate statement. basically i want dynamic column name for v_VAL_DONE_BY and v_Comments. the same execute immediate is working in standalone pl sql block. seems some problem with use of quotes in execute statement could you please help?

On line 57, is your intention to include the :3 in the string? i.e. should it be `':3' ||',' ...` ? — BJones, Dec 06 '19 at 18:28
@BJones: I was just about to mention it, but I'm slow at typing :-) But this wouldn't raise the error, would it? — Thorsten Kettner, Dec 06 '19 at 18:29

Thorsten Kettner · Answer 1 · 2019-12-07T23:56:17.150

Are you running this insert statement in a PL/SQL block? You are using variables in your query that SQL would take for column names (that don't exist) and raise the error. If you are running this code in PL/SQL, then you must of course declare the variables and do something about the bind variable :3 as mentioned by BJones in the comments to your request.

SQL:

Insert into BL_DIFF_QUERY_ANL (TABLE_NAME,ISSUSE,ISSUE_CATEGORY,ISSUE_ID,DB_QUERY)
values ('BILL','TOTAL_BILLED_ADJUST_MISMATCH','TOTAL_BILLED_ADJUST',2,
...
execute immediate ''UPDATE BL_DIFF_CATEGORY SET VALIDATION_STS = :2,' ||
:v_VAL_DONE_BY ||
'='||
:3 ||
','||
:v_comments ||
'= :4  where BAN = :1 and
...
);

PL/SQL

declare
  v_VAL_DONE_BY varchar2(4000);
  v_comments varchar2(4000);
begin
  Insert into BL_DIFF_QUERY_ANL (TABLE_NAME,ISSUSE,ISSUE_CATEGORY,ISSUE_ID,DB_QUERY)
  values ('BILL','TOTAL_BILLED_ADJUST_MISMATCH','TOTAL_BILLED_ADJUST',2,
  ...
  execute immediate ''UPDATE BL_DIFF_CATEGORY SET VALIDATION_STS = :2,' ||
  v_VAL_DONE_BY ||
  '= :3 ,'||
  v_comments ||
  '= :4  where BAN = :1 and
  ...
  );
end;

Update: SQL again

Looking closer at the variable names you are using, I've come to the conclusion that the variables shall be part of the SQL string that you are inserting:

  ...
  execute immediate ''UPDATE BL_DIFF_CATEGORY SET VALIDATION_STS = :2, '' ||
  v_VAL_DONE_BY || '' = :3, '' || v_comments || '' = :4  where BAN = :1 and DIFF_TYPE = :5''
  using v_issue_Desc,v_immediate_Adj,v_yesnoind,v_success,v_ban;

 COMMIT;
 END IF;
END;'
);

Explantion

You want to insert code into your table. This code is a PL/SQL script with variables and strings. This is a small example for such PL/SQL code:

declare
  v_number integer := 5;
begin
  dbms_output.put_line('The number is: ' || v_number);
end;

You want to insert this as a string. But if you merely put single quotes at the start and the end as in

insert into mytable (mycode) values
(
  'declare
     v_number integer := 5;
   begin
     dbms_output.put_line('Number ' || v_number);
   end;'
);

this doesn't work, because the code itself contains single quotes. The DBMS reads the string from the starting quote to the next quote it finds, in this example right before Number. So the DBMS sees a string followed by the word Number and doesn't know what to do with this. Number looks like a column name, but a column name doesn't belong there. (You can see from the syntax highlighting that the DBMS sees two strings with the woprd Number in between.)

The solution is to mask all single quotes with another single quote (i.e. replace ' with '' in your editor):

insert into mytable (mycode) values
(
  'declare
     v_number integer := 5;
   begin
     dbms_output.put_line(''Number '' || v_number);
   end;'
);

All single quote pairs are seen as part of the string now representing a single quote within. The DBMS sees a single string that it can insert into the table now. (You can see from the syntax highlighting that this is one string now.)

In your code you used single quote pairs in some situations but not in all. Maybe you did this just manually instead of search & replace in an editor and thus missed some occurrences.

Thnaks Thorsten Kettner :-). owrks like a charm. but i did not understand why it works. — Ankit Kumar, Dec 07 '19 at 03:06
Do you mean the INSERT statement now succeeds, or the dynamic code runs successfully? — William Robertson, Dec 07 '19 at 10:39
@Ankit Kumar: I've added an explanation. I hope this clarifies things for you. — Thorsten Kettner, Dec 07 '19 at 23:57

SQL Error: ORA-00984: column not allowed here 00984. 00000 - "column not allowed here"

1 Answers1

SQL:

PL/SQL

Update: SQL again

Explantion