There are 2 interesting PRAGMA which can be defined for procedures/functions and packages:

AUTHID CURRENT_USER –  specify CURRENT_USER to indicate that the code executes with the privileges of CURRENT_USER. This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the code resides. All roles for CURRENT_USER are active for dynamic code (EXECUTE IMMEDIATE) and disabled for static compilation.

AUTHID DEFINER – specify DEFINER to indicate that the code executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the code resides. This is the default and creates a definer’s rights package. All roles for DEFINER are disabled for dynamic code(EXECUTE IMMEDIATE) and static compilation.

In this article I want to show specific feature for AUTHID CURRENT_USER enabled roles for dynamic code.

Continue reading →

Sometimes it’s usefull to define NULL columns in views. But there is problem to use such view for CREATE TABLE AS SELECT

--simple view with 3 null columns
create or replace view test_vw
as
select null a, null b, null c 
from dual;

it’s impossible to create table based on such view

desc test_vw
Name Null Type       
---- ---- ---------- 
A         VARCHAR2() 
B         VARCHAR2() 
C         VARCHAR2() 

create table test_tbl
as
select * from test_vw;

SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 -  "zero-length columns are not allowed"

to solve this problem define column size for null columns

create or replace view test_vw
as
select 
  cast( null as varchar2(10) ) a, 
  cast( null as date ) b, 
  cast( null as number ) c
from dual;

desc test_vw

Name Null Type         
---- ---- ------------ 
A         VARCHAR2(10) 
B         DATE         
C         NUMBER     

create table test_tbl
as
select * from test_vw;

table TEST_TBL created

Hope it helps 🙂

Tomasz