Forum Stats

  • 3,814,956 Users
  • 2,258,936 Discussions
  • 7,892,896 Comments

Discussions

BINARY_CI COLLATION table Trigger error: PLS-00049: bad bind variable 'NEW.C_NAME' Oracle 19c

879152
879152 Member Posts: 135
edited Aug 29, 2020 5:49AM in SQL & PL/SQL

Hi,

I have a table with varchar2 column & table is case insensitive with defaut collation binary_ci Oracle 19c.

When I create trigger it show error. But If I create table without collate binary_ci my trigger works fine.

Details with example:

CREATE TABLE Office_Record

(C_ID    INTEGER,

C_NAME  VARCHAR2(100)  Collate binary_ci,

C_Type    VARCHAR2(100)  Collate binary_ci

)

DEFAULT COLLATION BINARY_CI;

CREATE OR REPLACE TRIGGER TRG_UPD_Office_Record

BEFORE UPDATE ON Office_Record

FOR EACH ROW

BEGIN

    :NEW.C_NAME := (CASE

   WHEN LENGTH(:NEW.C_ID) = 1 THEN '00' || :NEW.C_NAME

   WHEN LENGTH(:NEW.C_ID) = 2 THEN '0' || :NEW.C_NAME

   ELSE :NEW.C_NAME

      END);      

END;

/

Error Shows below:

[Error] PLS-00049 (23: 5): PLS-00049: bad bind variable 'NEW.C_NAME'

[Error] PLS-00049 (25: 44): PLS-00049: bad bind variable 'NEW.C_NAME'

[Error] PLS-00049 (27: 43): PLS-00049: bad bind variable 'NEW.C_NAME'

[Error] PLS-00049 (29: 9): PLS-00049: bad bind variable 'NEW.C_NAME'

Please help

How BINARY_CI COLLATION will work with Trigger oracle 19c?

Thanks

Solaiman

Answers

  • Paulzip
    Paulzip Member Posts: 8,671 Blue Diamond
    edited Aug 28, 2020 3:54PM

    It could be a bug and this is not the first time I've seem someone mention this issue, I've seen it a few times, and haven't come across a solution - but having read the documentation on collation, I'm wondering if it is by design.

    Triggers have to run with USING_NLS_COMP collation, this is mentioned in the Oracle documentation :

    The default collation of a procedure, function, package, type, or trigger must be USING_NLS_COMP. 

    They also mention that if there is a discrepancy between that and the object's collation, you'll get an error :

    If the resulting default object collation is different from USING_NLS_COMP, the database object is created as invalid with a compilation error.

    Which one could argue is what is occurring.  The table's default collation is binary insensitive, but the trigger's has to be USING_NLS_COMP.  However, I tried with just column collation and the error still occurs.  I also tried some workarounds and couldn't get them to work. Referring to any binary case insensitive collated columns in the trigger seems to give this error. It's almost like they're forbidden because of the collation, which kind of links to the points found in the documentation.  You may need to log a support ticket with Oracle.

  • Paulzip
    Paulzip Member Posts: 8,671 Blue Diamond
    edited Aug 28, 2020 4:19PM

    Here's a workaround.  You create a virtual column, and refer to that for display purposes.

    create table office_record (

      c_id       integer,

      c_name     varchar2(100) collate binary_ci,

      c_type     varchar2(100),

      c_name_fmt varchar2(102) collate binary_ci as (case length(c_id) when 1 then '00' when 2 then '0' end || c_name)

    );

    I tried the same with a view, but it doesn't like applying collate binary to the derived column

  • 879152
    879152 Member Posts: 135
    edited Aug 28, 2020 6:59PM

    I have to use case insensitive table as well as trigger with that table.

    Is there No way to use trigger with case insensitive table i.e. table with binary_ci collation?

    Please reply.

  • Paulzip
    Paulzip Member Posts: 8,671 Blue Diamond
    edited Aug 28, 2020 7:47PM
    879152 wrote:I have to use case insensitive table as well as trigger with that table.Is there No way to use trigger with case insensitive table i.e. table with binary_ci collation?Please reply.

    As I said, I couldn't find a way and others have tried and couldn't either. I offered a solution, ok it's not a trigger, but it will achieve your requirements.

  • 879152
    879152 Member Posts: 135
    edited Aug 29, 2020 5:06AM

    The DML row-level triggers cannot reference fields of OLD, NEW, or PARENT pseudo-records, or correlation names that correspond to columns with Binary_CI collation other than USING_NLS_COMP.

    i.e. In trigger we cannot reference fields of OLD, NEW, or PARENT pseudo-records if column has collation BINARY_CI.

    Than how we can find  OLD, NEW value in update/delete trigger. I have 100+ trigger which was correct before changing

    Table collation to Binary_CI & OLD, NEW value was working Properly.

    Please Reply.

  • Paulzip
    Paulzip Member Posts: 8,671 Blue Diamond
    edited Aug 29, 2020 5:49AM

    I've tried to help you solve the problem but it seems to be a restriction. Whether that's by design or a bug, I can't say, although the documentation does seem to hint that an error would occur if you tried it.

    As I also mentioned, I suggest you raise a support ticket with Oracle in regards to it.

  • user9540031
    user9540031 Member Posts: 172 Silver Badge

    Old thread, but it seems this hasn't been much discussed anyway, so...

    Actually, the Globalization Support Guide provides, not with hints, but certainty. Section 5.10.9 Effect of Data-Bound Collation on PL/SQL Types and User-Defined Types includes the following mentions:

    • The database columns with declared collations other than USING_NLS_COMP can be referenced in embedded SQL, but not in PL/SQL expressions.
    • The DML row-level triggers cannot reference fields of OLD, NEW, or PARENT pseudo-records, or correlation names that correspond to columns with declared collation other than USING_NLS_COMP.
    • The %TYPE attribute is not allowed on character columns with a declared collation other than the pseudo-collation USING_NLS_COMP. Similarly, the %ROWTYPE attribute is not allowed on tables, views, cursors, or cursor variables with at least one character column with a declared collation other than USING_NLS_COMP.

    And the list goes on. This is sad, but fully consistent with the section Data-Bound Collation in the PL/SQL Language Reference manual for 12.2, which states:

    "In this release, the PL/SQL language has limited support for the data-bound collation architecture. All data processed in PL/SQL expressions is assumed to have the compatibility collation USING_NLS_COMP. This pseudo-collation instructs collation-sensitive operators to behave in the same way as in previous Oracle Database releases. That is, the values of the session parameters NLS_COMP and NLS_SORT determine the collation to use. However, all SQL statements embedded or constructed dynamically in PL/SQL fully support the new architecture."

    Apparently there's no improvement in this area in 21c either.

    Bottom line: so far, given this vast array of restrictions, data-bound collation and PL/SQL code don't go very well together. I understand that going beyond "limited support" will be a major enhancement, but I'm having a hard time figuring out what it would be (I haven't tried) to work around these limitations (no %TYPE? no %ROWTYPE? no DML row-level triggers?). Perhaps I lack imagination, or I'm just missing something here. Meanwhile, pending clarification or improvement, I'd stay away from data-bound collation (other than the implicit USING_NLS_COMP) in any application which uses PL/SQL.

  • user9540031
    user9540031 Member Posts: 172 Silver Badge

    Addendum: to put it more positively... Limited support of data-bound collation in PL/SQL means you've got to understand the restrictions and accept the trade-offs.

    no %TYPE? no %ROWTYPE?

    Use carefully crafted, or generated, table-level APIs.

    no DML row-level triggers?

    Actually, we can have DML row-level triggers, it's just we can't reference :OLD.column_name or :NEW.column_name in them if column_name has a declared collation other than USING_NLS_COMP.

    And I did not mention the following restriction: "cursor FOR LOOP statements are not allowed on cursors that return result set columns with collation other than the pseudo-collation USING_NLS_COMP." How painful! But same as %TYPE and %ROWTYPE above: we'll just be missing the convenience.