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 4I 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
