07 April 2014

Current_Schema and the Data Dictionary

Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.

The regular install script for Logger looks something like the following (parts removed and table names are changed):

set verify off serveroutput on

begin
   execute immediate 'create table new_table (x int)';
end;
/

prompt ****
prompt *** Query user_tab_columns
prompt ****
declare
    l_nullable varchar2(32767);
begin
   select nullable
    into l_nullable
    from user_tab_columns
   where table_name = 'NEW_TABLE'
     and column_name = 'X'
   ;
   dbms_output.put_line('Found the NEW_TABLE, do some stuff to it');
end;
/
The first lines make sure that the user is not prompted for input and to see output from DBMS_OUTPUT.
The "Create table" is done using dynamic SQL after which some more action is taken. To determine which action needs to be taken is handled in the PL/SQL block - now simply replace by some DBMS_OUTPUT. In this PL/SQL block the datadictionary view USER_TAB_COLUMNS is queried.
This install script is usually run in the schema where the objects need to be installed.

This time, however, the install script needed to be adjusted for the DBA to run the script. As the DBA didh't want to log into different schema's during the complete install, the files needed to include the following at the top of the scripts:

alter session set current_schema = &1;
What this does is best explained in the Oracle documentation:
This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.
The install script was now called (by the DBA) as follows:
@install.sql SCOTT

Adding the ALTER SESSION command at the top of the install script produced this output

Session altered.


PL/SQL procedure successfully completed.

****
*** Query user_tab_columns
****
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
I would have expected that changing the CURRENT_SCHEMA would also query the USER_TAB_COLUMNS related to the schema that we changed into. This is not the case.

New install script

The install script needed to be adjusted to query the DBA_TAB_COLUMNS instead of the USER_TAB_COLUMNS:

set verify off serveroutput on

alter session set current_schema = &1;

begin
   execute immediate 'create table new_table (x int)';
end;
/

prompt ****
prompt *** Query dba_tab_columns
prompt ****
declare
    l_nullable varchar2(32767);
begin
   select nullable
    into l_nullable
    from dba_tab_columns
   where owner = upper ('&1')
     and table_name = 'NEW_TABLE'
     and rownum = 1;
   dbms_output.put_line('Found the NEW_TABLE, do some stuff to it');
end;
/
Now the install script works as desired:
****
*** Query dba_tab_columns
****
Found the NEW_TABLE

PL/SQL procedure successfully completed.

Difference between SESSION_USER and CURRENT_SCHEMA

SQL> col session_user format a35
SQL> col current_schema format a35
SQL> select sys_context ('userenv', 'session_user') session_user
  2        ,sys_context ('userenv', 'current_schema') current_schema
  3    from dual
  4  /

SESSION_USER                        CURRENT_SCHEMA
----------------------------------- -----------------------------------
SYS                                 SCOTT

Not so strange

Knowing what you know after reading the above, the following is not so strange anymore:

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 STR                                                VARCHAR2(25)


SQL> select column_name
  2        ,data_type
  3    from user_tab_columns
  4   where table_name = 'T'
  5  /

COLUMN_NAME                    DATA_TYPE
------------------------------ -------------------------
ID                             NUMBER

These scripts were run on the following version:

SQL> select *
  2    from v$version
  3  /

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Links