Category Archives: PL/SQL

Definer vs. invoker rights – check out SYS_CONTEXT

Leave a reply

The introduction
This is showing the difference between definer and invoker rights in terms of current user and current schema – what context is the code running in.

The setup
In schema JOHN create two stored procedures one with definer rights the other with invoker rights both printing the same SYS_CONTEXT parameters.

1
2
3
4
5
6
7
8
9
SQL> create or replace procedure print_user_info_def
is
begin
  dbms_output.put_line('USER: '||user);
  dbms_output.put_line('SESSION_USER: '||sys_context('userenv', 'session_user'));
  dbms_output.put_line('CURRENT_USER: '||sys_context('userenv', 'current_user'));
  dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', 'current_schema'));
end print_user_info_def;
/
1
2
3
4
5
6
7
8
9
10
SQL> create or replace procedure print_user_info_inv
authid current_user
is
begin
  dbms_output.put_line('USER: '||user);
  dbms_output.put_line('SESSION_USER: '||sys_context('userenv', 'session_user'));
  dbms_output.put_line('CURRENT_USER: '||sys_context('userenv', 'current_user'));
  dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', 'current_schema'));
end print_user_info_inv;
/

The result
Log-in with user JANE and run the two stored procedures owned by JOHN

1
2
3
4
5
SQL> exec john.print_user_info_def
USER: JANE
SESSION_USER: JANE
CURRENT_USER: JOHN
CURRENT_SCHEMA: JOHN
1
2
3
4
5
SQL> exec john.print_user_info_inv
USER: JANE
SESSION_USER: JANE
CURRENT_USER: JANE
CURRENT_SCHEMA: JANE

Foot note: All tests have been run on a 10g 10.2.0.4 database.

DBMS_LOB and Character Sets

Leave a reply

Running the database with a multi byte database character set (fixed or varying), e.g. AL32UTF8, has some implications on default behaviour of certain parts of the database you just have to be aware of. Of what I can see those are mostly well documented in the Oracle documentation and is just another case that proves the importance of reading them ;-).

Here the relevant NLS parameter config for the case/scripts following:

1
2
3
4
5
6
SQL> select * from nls_session_parameters where parameter in ('NLS_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');
 
PARAMETER                      VALUE
----------------------------------------------------------
NLS_CHARACTERSET               AL32UTF8
NLS_LENGTH_SEMANTICS           CHAR

Create the table and populate it:

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> create table t1 (id number(1), text clob);
 
SQL> declare
  cval clob;
begin
  dbms_lob.createtemporary(cval, false);
  for idx in 0 .. 9 loop
    dbms_lob.writeappend(cval, 4000, lpad(to_char(idx), 4000, to_char(idx)));
  end loop;
  insert into t1 values (1, cval);
  commit;
end;
/

Illustrate the behaviour of DBMS_LOB.SUBSTR:

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> declare
  cval clob;
begin
  select text into cval from t1 where id = 1;
  --
  dbms_output.put_line('Original length: '||dbms_lob.getlength(cval));
  dbms_output.put_line('Substr 10000 length: '||length(dbms_lob.substr(cval, 10000, 1)));
  dbms_output.put_line('Substr 1000 length: '||length(dbms_lob.substr(cval, 1000, 1)));
end;
/
Original length: 40000
Substr 10000 length: 8191
Substr 1000 length: 1000

Although we know the entire string is 40’000 characters long a the sub-string function supposed to return the first 10’000 characters only returns 8191. Getting the first 1’000 characters just work s as expected. Here’s why:
AL32UTF8 character set uses variable-width multi byte encoding scheme which means depending on the character it uses one to maximum four bytes to store it. As by definition DBMS_LOB.SUBSTR returns 32767/n characters whereas n = number of bytes used to store a character for fixed-width character sets and n = max number of bytes used to store a character for variable-width character sets. This means in our case 32767 / 4 = 8191.75.

But heck, I really need the first 10’000 characters…so I make use DBMS_LOB.READ:

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> declare
  cval clob;
  vval varchar2(10000);  -- session level lenght semantic is CHAR
  nof_chars integer := 10000;
begin
  select text into cval from t1 where id = 1;
  --
  dbms_lob.read(cval, nof_chars, 1, vval);
  dbms_output.put_line('Read 10000 length: '||length(vval));
end;
/
 
Read 10000 length: 10000

This rule also applies to DBMS_LOB.COMPARE function.
Now I just wanted to mention something else that I always miss when checking DBMS_LOB function/procedure definitions in the PL/SQL Packages and Types Reference documentation. Procedures CONVERTTOBLOB, CONVERTTOCLOB and LOADCLOBFROMFILE require a character set ID but nowhere it points out how you can figure out the ID for a given character set (or vice versa). The SQL Reference documentation gives you the clue when looking for the functions NLS_CHARSET_ID and NLS_CHARSET_NAME:

1
2
3
4
5
SQL> select nls_charset_id('AL32UTF8') cs_id, nls_charset_name(873) cs_name from dual;
 
CS_ID         CS_NAME
--------      --------------
873           AL32UTF8

Foot note: All tests have been run on a 10g 10.2.0.4 database.

Looking forward to 11g sequence expressions in PL/SQL

Leave a reply

Although behind the scenes Oracle will perform a “select .nextval from dual” and therefore really doesn’t improve performance or anything I’m looking forward to write 11g PL/SQL code where you can use the sequence pseudo-columns CURRVAL and NEXTVAL in expressions. First off, I always prefer to use the .NEXTVAL in the SQL statement itself and use the RETURNING INTO clause to assign the new value to a PL/SQL variable. BUT, if the case requires for some reason to get the sequence value in PL/SQL without going through a SQL statement first just imagine how beautiful and elegant your code will look like without having “select .netxval into from dual;” spread all over the place…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> create sequence seq;
 
-- the 10g and prior way
SQL> declare
  nextval number;
begin
  select seq.nextval
  into nextval
  from dual;
end;
/
 
-- the 11g way
SQL> declare
  nextval number;
begin
  nextval := seq.nextval;
end;
/

You can basically use the sequence pseudo-columns everywhere where a number value is valid in the expression, e.g. “…if seq.currval > 10 then…”

Qualified names

Leave a reply

Have you ever looked at a big long package/procedure and wondered what the scope of a variable might be in this web of nested named and unnamed PL/SQL blocks? I’m sure all of you have.
There are a few naming convention proposals out there that suggest using prefixes like “l_” for local, “g_” for global variables, etc which is some kind of Systems Hungarian notation (I’m not convinced of the usefulness of System Hungarian whereas I can see some benefit of App Hungarian if properly applied on the variable scope – read more about it at Joel’s).
The problem rises when there are multiple nested blocks, what’s now the scope of this “l_” prefixed variable again? You simply don’t know until you back track it to the declaration of that variable.
That’s where qualified names come in pretty handy, let’s examine:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create or replace package pck
as
  procedure p1 (
     text in varchar2
  );
end pck;
/
 
create or replace package body pck
as
  text varchar2(10);
  
  procedure p1 (
     text in varchar2
  )
  is
  begin
    pck.text := p1.text;
  end p1;
end pck;
/

Note that in procedure P1 we use qualifiers for the variable and parameter TEXT. Even though they have the same name, the scope is very clear. We could even qualify the parameter with the package name which would make it a bit long, e.g. “pck.text := pck.p1.text;”
Isn’t this beautiful? Absolutely, but as soon as we use long package, procedure and variable names it can make the code somewhat cumbersome to read as you end up with very long expressions. In my opinion I think the benefits of qualified names far outweigh the disadvantages of having a bit longer code lines – you can look at one (maybe big) line of code and you have all the information, no need for scrolling up to the declaration to find out the scope.