Oracle SQL Developer – How to Find Where Length Semantics Gets Changed

oracleoracle-11g-r2oracle-sql-developeroracle-xesqlplus

I've code to create tables that doesn't specify length semantics:

CREATE TABLE CHAR_TEST (
    CHAR_TEST_ID NUMBER(*,0) NOT NULL,
    FOO VARCHAR2(6) NOT NULL,
    BAR VARCHAR2(6) NOT NULL,

    CONSTRAINT CHAR_TEST_PK PRIMARY KEY (CHAR_TEST_ID) ENABLE
);

Actual code is fairly large and contains hundreds of objects. I have two versions of the code that correspond to different source control revisions separated by a couple of years.

I've created 2 users/schemas on the same server with identical settings. I then run each revision in each respective schema. To my surprise, the latest revision gets an unexpected CHAR semantic:

Name         Null     Type        
------------ -------- ----------- 
CHAR_TEST_ID NOT NULL NUMBER(38)  
FOO          NOT NULL VARCHAR2(6) 
BAR          NOT NULL VARCHAR2(6)

Name         Null     Type        
------------ -------- ---------------- 
CHAR_TEST_ID NOT NULL NUMBER(38)  
FOO          NOT NULL VARCHAR2(6 CHAR) 
BAR          NOT NULL VARCHAR2(6 CHAR)

I can't figure out where those CHAR come from!

I can't reproduce it if I run CREATE TABLE code isolately
I've been unable to find ALTER TABLE CHAR_TEST statements in my code base that change anything but keys or indexes
SELECT value FROM V$PARAMETER WHERE NAME LIKE '%nls_length_semantics%'; prints BYTE in both schemas

Could you please suggest where to look further?

Best Answer

I forgot the most obvious thing: session parameters can be changed!

I had this on the latest revision:

ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

... but not the older one. I was running the scripts in a different session thus I couldn't spot the value change.

Related Solutions

Oracle 11g .DMP viewer

First of all you need to create a directory on the database.

Create or Replace Directory IMP_DIR as 'Path\Path\';

Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)

GRANT Read, Write ON DIRECTORY IMP_DIR TO user;

Then, include the directory parameter within the import statement

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR

Find a syntax reference for Oracle SQL Developer’s “Generate DB Doc” function

_{Community wiki answer initially based on comments left by thatjeffsmith:}

This is an exhaustive list of what it supports:

(reproduced from http://pldoc.sourceforge.net/maven-site/samples/sample1.sql)

CREATE OR REPLACE
PACKAGE CUSTOMER_DATA
IS
/** 
* Project:         Test Project (<a href="http://pldoc.sourceforge.net">PLDoc</a>)<br/>
* Description:     Customer Data Management<br/>
* DB impact:       YES<br/>
* Commit inside:   NO<br/>
* Rollback inside: NO<br/>
* @headcom
*/

/**
* Record of customer data.
*
* @param id     customer ID
* @param name       customer name
* @param regno      registration number or SSN
* @param language   preferred language
*/
TYPE customer_type IS RECORD (
  id                        VARCHAR2(20),
  name                      VARCHAR2(100),
  regno                     VARCHAR2(50),
  language                  VARCHAR2(10)
);

/** Table of customer records. */
TYPE customer_table IS TABLE OF customer_type INDEX BY BINARY_INTEGER;

/**
* Gets customer by ID.
*
* @param p_id       customer ID
* @param r          record of customer data
* @throws no_data_found if no such customer exists
*/
PROCEDURE get_customer (
  p_id              VARCHAR2,
  customer_rec      OUT customer_type);

/**
* Searches customer by criteria.
*
* @param p_criteria record with assigned search criteria
* @param r_records  table of found customers <b>(may be empty!)</b>
*/
PROCEDURE get_by_criteria (
  p_criteria        customer_type,
  r_records         OUT customer_table);

/**
* Creates a customer record.
*
* @param customer_rec record of customer data
*/
PROCEDURE create_customer (
  customer_rec      customer_type);

/**
* Changes customer data.
*
* @param customer_rec record of updated customer data
*/
PROCEDURE update_customer (
  customer_rec      customer_type);

END;
/

We support everything in PLDOC - we just have a GUI vs a CLI for it. There are three code samples there, you should be able to do anything listed in those samples.

Related Question