We help IT Professionals succeed at work.

How to refer columns with lower case, columns of blob/clob datatype in a trigger?

sakthikumar
sakthikumar asked
on
I am creating a trigger for a table

where in the columns are in lower case like
description
title
even when I refer as :new.description or :new.title
I am getting error bad bind variable.
How to refer these in triggers.

Also, Is it possible to get new or old blob columns in a trigger.

Thanks,
Sakthi.




Comment
Watch Question

Top Expert 2011

Commented:
- use doublequote to wrap the lowercase columnnames eg:

SELECT "description", "title" FROM tablename
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
Can you give the full trigger code here to review ?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>Also, Is it possible to get new or old blob columns in a trigger.

What makes you think you cannot?

Check out the complete test case below.

Note: It also shows the answer to your lower case question provided by OP_Zaharin above.
drop table tab1 purge;
create table tab1("col1" blob);

insert into tab1 values(rawtohex('A'));
commit;

create or replace trigger tab1_trig
before update on tab1
for each row
begin
	dbms_output.put_line('Before len: ' || dbms_lob.getlength(:old."col1"));
	dbms_output.put_line('After len: ' || dbms_lob.getlength(:new."col1"));
end;
/

show errors

update tab1 set "col1" = rawtohex('AA');

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.