Forum Stats

  • 3,780,466 Users
  • 2,254,398 Discussions


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

Shenk Member Posts: 14
edited Nov 13, 2019 5:44AM in SQL & PL/SQL

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?




  • BluShadow
    BluShadow Member, Moderator Posts: 41,609 Red Diamond
    edited Nov 13, 2019 3:02AM

    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
    Shenk Member Posts: 14
    edited Nov 13, 2019 3:25AM
    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
    Shenk Member Posts: 14
    edited Nov 13, 2019 3:27AM

    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
    Cookiemonster76 Member Posts: 3,410
    edited Nov 13, 2019 3:42AM

    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
    KayK Member Posts: 1,687 Bronze Crown
    edited Nov 13, 2019 3:58AM

    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.



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

  • Shenk
    Shenk Member Posts: 14
    edited Nov 13, 2019 3:58AM

    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
    Cookiemonster76 Member Posts: 3,410
    edited Nov 13, 2019 4:08AM

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

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Nov 13, 2019 4:22AM
    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
    Shenk Member Posts: 14
    edited Nov 13, 2019 4:25AM

    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
    Shenk Member Posts: 14
    edited Nov 13, 2019 4:27AM

    Hey Gaz in Oz

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