Column name contains # error in trigger, but normal in stored procedure

Shenk

Hello everyone.

This is a problem that occurs when importing 9i data to the next 12c. For example, there is a field CC#DD in the A table. In the stored procedure, A.CC#DD can be compiled and run. In the case of OLD.CC#DD or NEW.CC#DD in the trigger, there will be compilation errors for invalid characters and bad bind variable , and the double quotes will be written as "CC#DD" and will no longer be an error.

Questions are as follows:

1. Why does the stored procedure not report an error, and the trigger will report an error?

2. The 9i trigger does not use CC#DD with double quotes. Why is there no error?

Thanks

BluShadow

Upgrading from 9i to 12c is a major step up.

I'm actually surprised that you managed to get it to compile in 9i with a "#" in the name, but perhaps Oracle tightened up on their limitations on names since 9i.  (Not sure how many people out there would be able to test for you as 9i has be unsupported for many years now - so not many people still using it)

Double quotes is the way to specify names such that they can contain some other characters e.g. spaces, some punctuation etc. and it also makes them case sensitive (so if you use lower case characters in the name it will always be lower case and will always have to be referenced with double quotes).

Whoever decided to put such an odd character in the name of a column should be seriously talked to.

If you can, revisit your database, your code and your application to rename such columns to something sensible that doesn't require double quoting.

Shenk

Hey BluShadow

This is a Japanese system, # is a full-width symbol,  so I think this is the reason why there is no error in the 9i database, maybe you are right, after 9i oracle added restrictions on the column name so it will be wrong in 12c

Shenk

Why is there no error in the 12c stored procedure, but an error occurs in the trigger? This makes it hard for me to understand

Cookiemonster76

Because oracle coded it that way.

The discrepancy will almost certainly be an oversight on their part so it's probably technically a bug, though if you raise it with oracle I doubt they'll be in a hurry to do anything about it and will likely introduce the restriction in stored procedures if they do get round to fixing it.

I'd just rename the column and move on.

KayK

Hi 9f,

sorry, but i can't reproduce your issue in 12c.

That's my test and it works

< scott:[email protected] > create table kk1 ( CC#DD number );Table created.< scott:[email protected] > CREATE OR REPLACE TRIGGER tr_insert_kk1  2  before insert ON kk1 FOR EACH ROW  3  begin  4  dbms_output.put_line ('time ' || sysdate);  5  dbms_output.put_line ('new value ' || :new.CC#DD);  6  exception  7  when others then  8    dbms_output.put_line ('error ' || sqlerrm);  9  end;10  /Trigger created.< scott:[email protected] > show errorNo errors.< scott:[email protected] > insert into kk1 values ( 16 );time 13.11.2019 09:47:02new value 161 row created.< scott:[email protected] > create or replace procedure prc_kk1 as  2  result number;  3  begin  4  select CC#DD  5    into result  6    from kk1;  7  dbms_output.put_line (result);  8  end;  9  /Procedure created.< scott:[email protected] > exec prc_kk116PL/SQL procedure successfully completed.

Please show the complete error message.

regards

Kay

btw change the name of your avatar to a more readable name.

Shenk

Hey Cookiemonster76

I think I can only add double quotes to the column names in the trigger   because there is no double quotes in the DMP exported from 9i. Is there any parameter that can automatically add double quotes when exporting data?

Cookiemonster76

I think you need to pay attention to KayK's post first.

Gaz in Oz

This is a Japanese system, # is a full-width symbol

Are you saying the symbol you use is multi-byte character, not just a normal hash or gate or pound character (or what ever the single byte character is callled)?

Shenk

Hey Kayk

This is the column name NEW.あ＃い, the following three are the error message(after my translation):

PLS-00049:Bound variable NEW. あ Error

ORA-00911:Invalid text

SQL Statement ignored

I just modified the name, but I don't know why it is still old here.sorry

Shenk

Hey Gaz in Oz

Yes, as you said, I am using NEW.あ＃い