Skip to Main Content
  • Questions
  • 'BEFORE CREATE ON SCHEMA' trigger apparently not firing before Create Table

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, OracleLearner.

Asked: November 29, 2019 - 1:32 pm UTC

Answered by: Chris Saxon - Last updated: December 05, 2019 - 5:28 pm UTC

Category: Database Administration - Version: Version 19.5.0.0.0

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Shameless, I know. But it feels so good...

You Asked

In Oracle 8.1.7 instance set up with characterset US7ASCII

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> create table t1 (c1 varchar2(3));

Table created.

SQL> create table t2 (c1 varchar2(3));

Table created.

SQL>REM simulate legacy application inserting 8bit characters into these tables i.e. characters outwith the definition of the configured 7bit character set

SQL> insert into t1 values(chr(128)||chr(129)||chr(130));

1 row created.

SQL> insert into t2 values(chr(128)||chr(129)||chr(130));

1 row created.



Run original exp for tables t1 and t2.


Running original imp on the target database 19C database (defined with a character set AL32UTF8) with the above export file taken from the source database - tables and rows created and populated with data as shown - should result in 2 tables on the target database, each with one row containing 3 3-byte replacement characters (so 9 bytes per row) provided NLS_LENGTH_SEMANTICS is set to CHAR.

By default the import will execute 'ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE' during the run.

To try and get round this create a schema level trigger in a privileged user executing 'ALTER
SESSION SET NLS_LENGTH_SEMANTICS = CHAR'


create or replace trigger test_user_before_create
before create on test_user.schema 
begin
     execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR';
end;
/


Run original imp on 19C...


C:\dmp>set NLS_LANG=ENGLISH_UNITED KINGDOM.US7ASCII

C:\dmp>imp test_user@al32pdb file=test_user.00000 log=test_user_imp.log

Import: Release 19.0.0.0.0 - Production on Fri Nov 29 11:44:39 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

Export file created by EXPORT:V08.01.07 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses US7ASCII NCHAR character set (possible ncharset conversion)
. importing TEST_USER's objects into TEST_USER
. importing TEST_USER's objects into TEST_USER
. . importing table                           "T1"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST_USER"."T1"."C1" (actual: 9, maximum: 3)
Column 1 ...          0 rows imported
. . importing table                           "T2"          1 rows imported
Import terminated successfully with warnings.

C:\dmp>sqlplus test_user@al32pdb

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 29 11:47:42 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Nov 29 2019 11:44:44 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(3)

SQL> desc t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(3 CHAR)

SQL> select vsize(c1) from t2;

 VSIZE(C1)
----------
         9

SQL> select dump(c1,1016) from t2;

DUMP(C1,1016)
--------------------------------------------------------------------------------
Typ=1 Len=9 CharacterSet=AL32UTF8: ef,bf,bd,ef,bf,bd,ef,bf,bd

SQL>


If the trigger is set to alter the character set 'BEFORE CREATE', why does t1 not get the CHAR qualification on the column name?

When tried with a production schema with a number of tables, some containing thousands of rows, every VARCHAR2 type table column was created as CHAR - this was repeated a number of times with the same result, no CHAR missing on any column.

Seems as if there is some synchronization/timing issue with the trigger that may kick in when the export is small?

Thanks,






and we said...

The trigger is firing. But after PARSING your statement. So the first statement is parsed with NLS_LENGTH_SEMANTICS = BYTE. And thus executes with this setting.

So, as you find, the first statement is "skipped".

To ensure you use CHAR semantics, use a logon trigger instead:

create or replace trigger test_user_logon
after logon on test_user.schema 
begin
  execute immediate 'ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR';
end;
/

and you rated our response

  (1 rating)

Reviews

December 05, 2019 - 4:12 pm UTC

Reviewer: OracleLearner from Scotland

Thanks for considering my question and responding.

I have tried the logon trigger, however it doesn't meet the need as the import explicitly sets NLS_LENGTH_SEMANTICS=BYTE after the logon trigger has fired and before the first CREATE TABLE statement.

The lines from a trace done on the import:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2 NLS_LENGTH_SEMANTICS = 'BYTE' 
  PLSQL_CODE_TYPE = INTERPRETED PLSQL_WARNINGS = 'DISABLE:ALL' PLSQL_CCFLAGS =
   ''


Thanks.
Chris Saxon

Followup  

December 05, 2019 - 5:28 pm UTC

Ahhh... import preserves the NLS_LENGTH_SEMANTICS from the source table.

I think you'll have to precreate the table. Then load the data in.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.