Skip navigation.

The Anti-Kyte

Syndicate content The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 5 hours 13 min ago

APEX and Privileges Granted through Roles

Sat, 2014-11-15 15:33

The mystery has finally been solved. England’s surrendering of the Ashes last winter was nothing to do with Australia being a much better cricket team. Thanks to Kevin Pietersen’s recently published Autobiography, we now know that the problem was that there were rather too many silly points in the England dressing room.
Moving swiftly on from that weak pun, the subject at hand can also be rather mystifying at first glance.

In a “traditional” Oracle Forms application, you would have one database user per application users.
Connections via the Application to the database would be done as the individual users.
It’s quite likely that database roles would be used to grant the appropriate privileges.

For applications using other web technologies, the application may interact with the database via a single account, often that of the Application Owner. Whether or not this is a good idea is probably a discussion for another time.

For now though, the question we’re asking is, how an APEX application connect to the database ?
On the face of it, it would seem that it’s pretty similar to the second of the two approaches above. APEX connects as the Parsing Schema (usually the application owner).
As Kevin will tell you, appearances can be deceiving…

The Environment

For the purposes of this post, I’ll be using a simple APEX application that’s been created in it’s own workspace.
The application is called NEW_HR and uses the default APEX Authentication Scheme.
The parsing schema is defined as HR.
At this point the application consists of a login screen and a blank Home Page.
I’ve also created a Workspace Admin user called…well…let’s call it Kevin.
The database version is Oracle 11g Express Edition and the APEX version is 4.2.
This environment uses the embedded PL/SQL Gateway to manage database connections from APEX. This is the default setup on Oracle 11g XE.

Who am I ? No, really

Now, I know that there is no user called KEVIN in my database….

select count(*) 
from dba_users 
where username = 'KEVIN'
/
  COUNT(*)
----------
         0

SQL> 

…so I’d like to know who the database thinks I am when I login through my APEX app. I’d also like to check who the APEX itself thinks I am.

The first step then, is to add a couple of fields to the application Home Page…

First of all, I’ve add an HTML Region called whoami. Apart from the name I’ve just accepted the defaults.

Now to add a field to display the Application User – i.e. who APEX thinks I am.

This is a Display Only Item called P1_APEX_USER in the whoami region.
The source settings for this item are the defaults except for :

Source Used : Always, replacing any existing value in session state
Source value or expression : APP_USER

apex_user_source

Next up is to add a field to display the database user.

The field is defined in the same way as P1_APEX_USER, except for :

Source Type : SQL Query (return single value)

and the source itself which is the following query :

select user from dual

db_user_source

Now, if we connect as Kevin….

login

…we can start to resolve our identity crisis….

whoami

So, as expected, APEX knows that Kevin is the Application user. However, the database user is not HR, rather it’s something called ANONYMOUS.

NOTE – If you’re using the Embedded PL/SQL Gateway ( the default setup for Express Edition) then you’ll be connected as ANONYMOUS. If you have the APEX Listener setup then, unless you’ve changed the default, you’ll be connected as APEX_PUBLIC_USER.
For our current purposes we can treat these accounts as synonymous from a database standpoint.
I’ll continue to refer to ANONYMOUS from here on because (a) I’m running this on XE and (b) the name has slightly more comedic potential.

Let’s find out a bit more about this user whilst trying not to worry that our application has been visited by hacktivists.
Hmmm, maybe not so much comedic potential.

The ANONYMOUS User

Looking in the database, we can confirm that ANONYMOUS is indeed a database user :

select account_status, profile, authentication_type
from dba_users
where username = 'ANONYMOUS'
/

ACCOUNT_STATUS                   PROFILE                        AUTHENTI
-------------------------------- ------------------------------ --------
OPEN                             DEFAULT                        PASSWORD

Doesn’t seem to be anything out of the ordinary there.
Now let’s see what ANONYMOUS has granted to it. For good measure, we can see what objects it owns ( if any).
The query looks like this :

select 'SYSTEM PRIVILEGE' as priv_type,
    null as db_object,
    privilege
from dba_sys_privs
where grantee = 'ANONYMOUS'
union
select 'ROLE GRANTED' as priv_type,
    granted_role as db_object,
    null as privilege
from dba_role_privs
where grantee = 'ANONYMOUS'
union
select 'OBJECT PRIVILEGE' as priv_type,
    owner||'.'||table_name as db_object,
    privilege
from dba_tab_privs
where grantee = 'ANONYMOUS'
union
select 'OWNED OBJECT' as priv_type,
    object_name as db_object,
    null as privilege
from dba_objects
where owner = 'ANONYMOUS'
order by 1,2
/ 

When we run it we get variations on the theme of :

PRIV_TYPE            DB_OBJECT                                                    PRIVILEGE
-------------------- ------------------------------------------------------------ ------------------------------
OBJECT PRIVILEGE     APEX_040000.WWV_FLOW_EPG_INCLUDE_MODULES                     EXECUTE
OBJECT PRIVILEGE     APEX_040200.WWV_FLOW_EPG_INCLUDE_MODULES                     EXECUTE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           ALTER
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           DELETE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           FLASHBACK
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           INDEX
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           INSERT
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           ON COMMIT REFRESH
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           QUERY REWRITE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           REFERENCES
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           SELECT
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           UPDATE
SYSTEM PRIVILEGE                                                                  CREATE SESSION

Now, the Object Privileges listed here are probable the result of some of the sample APEX applications I’ve installed.
By default, the only thing granted to ANONYMOUS is the CREATE SESSION privilege.

More pertinent here though is that it has no permissions at all on any objects owned by HR. This begs the question as to how our APEX application will work. Remember, our parsing schema ‎( essentially the Application Owner) is HR. Therefore, it’s reasonable to assume that we’ll want to interact with the tables in that schema.

NOTE – at this point I should add that, of course, ANONYMOUS does have additional privileges – i.e. everything granted to PUBLIC in the database. Whilst th‎is is not strictly relevant to the matter at hand, it’s probably worth bearing in mind when you look at how you implement security around this user.

Anyway, let’s put it to the test…

The Regions Report

In our application we’re going to create a new page – a Report on the HR.REGIONS table so…

In the Application Builder, click on Create Page :

create_page

Select Report and click Next

Select Interactive Report and click Next

Accept the defaults for Page Region Attribute and click Next

In Tab Options choose Use an existing tab set and create a new tab within the existing set
New Tab Label is Regions :

tab_options

Click Next

For the SQL Query :

select region_id, region_name
from regions

Note – we’re not specifying the table owner in this query, even though ANONYMOUS does not have a synonym on the HR.REGIONS table ( let alone any privileges)

query

Click Next

…and click Create

create

When we now connect to the application as Kevin and click on the Regions tab….

regions

So, the report has worked without error, despite the lack of privileges and synonyms. So what’s happening ?

Session Privileges in APEX

To answer this, we’ll need to tweak our earlier privileges query. This time, we’ll use the USER_ version of the views.
We can then it to the Application Home Page in a new reports region to see what ANONYMOUS can actually do when connected via APEX.

First, the new query, using USER_ versions of the views and without the order by clause.

select 'SYSTEM PRIVILEGE' as priv_type,
    null as db_object,
    privilege
from user_sys_privs
union
select 'ROLE GRANTED' as priv_type,
    granted_role as db_object,
    null as privilege
from user_role_privs
union
select 'OBJECT PRIVILEGE' as priv_type,
    owner||'.'||table_name as db_object,
    privilege
from user_tab_privs
union
select 'OWNED OBJECT' as priv_type,
    object_name as db_object,
    null as privilege
from user_objects
where object_type != 'INDEX'
/

Spoiler Alert – the reason I’m not using the SESSION_PRIVS view here is because it will list privileges granted via roles. The distinction between these and directly granted privileges will shortly become apparent.

We now simply create a new interactive reports region called User Privileges on the Home Page, using the above query.
If we now filter on PRIV_TYPE = ‘OWNED OBJECT’, we can see that we’ve magically acquired ownership of all the HR objects…

owned_objects

If we filter on PRIV_TYPE = ‘SYSTEM PRIVILEGE’, we can see that we also seem to have inherited HR’s System Privileges…

sys_privs

So, we can infer from this that, although the database connection from APEX is as the ANONYMOUS user, the session will inherit all of the objects and privileges of the parsing schema.
A reasonable assumption, given the evidence, and a correct one…mostly.

Objects not owned by the parsing schema

I’ve created a simple function in my own schema :

create or replace function name_scandal_fn( i_basename varchar2)
    return varchar2
as
begin
    return i_basename||'gate';
end;
/

Next we’re going to create a role and then grant execute on this function to that role. Finally, we’re going to grant the role to hr :

create role hr_role
/

grant execute on name_scandal_fn to hr_role
/

grant hr_role to hr
/

First off, we’ll test this in SQL*Plus. Connect as HR and …

select mike.name_scandal_fn('Twitter') from dual
/

MIKE.NAME_SCANDAL_FN('TWITTER')
--------------------------------------------------------------------------------
Twittergate

SQL> 

So, we should have no problem invoking this function from our application then.

Let’s create a page with a Display Only field that is populated by a call to this function :

Blank Page :

fn_page1

Called Scandal

fn_page2

…With an HTML Region…

fn_page3

…on a new tab…

fn_page4

…and confirm…

fn_page5

Now, add the Item…

fn_item1

…called P3_SCANDAL_NAME…

fn_item2

Accept the defaults for the Item Attributes settings, and Settings…

… and change the Source settings to :

Source Used : Always, replacing any existing value in session state
Source Type : SQL Query (return single value)
Item Source Value – here we put in our call to the function :

select mike.name_scandal_fn('Twitter') from dual

fn_item3

Finally, hit the create button.

No problems so far. Now, let’s try running the page…

fn_err

Hmmm, not quite what we were expecting.

Looking at the error stack, a possible source of the problem emerges.
In the background, it looks as if APEX is calling a package called WWV_FLOW_FORMS, which in turn calls WWV_FLOW_DYNAMIC_EXEC.
Whilst the source for both of these packages is wrapped, there are some notes availble on the next package in the call stack, WWV_DBMS_SQL here.

Putting all together and looking at the package headers, it would seem reasonable to assume that, rather than running the SQL statement directly, APEX does this via a series of package calls which then run the statement as dynamic SQL.
The effect of calling a (presumably) Definer’s Rights package is that any privileges granted via roles are ignored.

In order to test this theory, we can revoke the role from HR and instead, grant execute on the function directly.
So, connected to SQL*Plus as the function owner ( in my case MIKE) :

revoke hr_role from hr
/

grant execute on name_scandal_fn to hr
/

Now a quick sanity check to make sure that HR can see the function.
Connect as HR and :

SQL> select mike.name_scandal_fn('Twitter') from dual
  2  /

MIKE.NAME_SCANDAL_FN('TWITTER')
--------------------------------------------------------------------------------
Twittergate

SQL> 

Now let’s see what APEX makes of this.
Re-run the page and we can see…

itworks

There you have it. APEX, like Kevin, is just a little bit different.


Filed under: APEX, Oracle, SQL Tagged: apex anonymous user, apex_public_user, granting privileges via roles, parsing schema

Going dotty – Generating a Filename containing a parameter value in SQL*Plus

Sat, 2014-10-18 12:01

As I have alluded to previously, I was not born in the UK.
Nope, my parents decided to up-sticks and move from London all the way to the
other side of the world, namely Auckland.
Then they had me. Then they came back.
To this day, they refuse to comment on whether these two events were related.

I went back to New Zealand a few years ago.
As I wandered around places that I hadn’t seen since I was five, it was strange how memories that I had forgotten came flooding back.
That last sentence doesn’t make much sense. It’s probably more accurate to say that memories I hadn’t thought about for years came flooding back.

I recently remembered something else I once knew, and then forgot – namely how to generate a SQL*Plus file name which includes a parameter value.

The scenario

I’ve got a script that lists all of the employees in a given department :

accept deptno prompt 'Enter Department ID : '
spool department.lis

select first_name, last_name
from hr.employees
where department_id = &deptno
order by employee_id
/

spool off

Now, rather than it just creating a file called department.lis, I want to create a file that includes the department number I’m querying.

Obvious…but wrong

You might think the following is a reasonable attempt to do this :

accept deptno prompt 'Enter Department ID : '
spool department_&deptno.lis

select first_name, last_name
from hr.employees
where department_id = &&deptno
order by employee_id
/

spool off

Unfortunately, SQL*Plus insists on being obtuse and outputting the following file :

ls
department_10lis.lst

It is at this point that a colleague came to the rescue ( thanks William)…

Going dotty

This will do the job…

accept deptno prompt 'Enter Department ID : '

spool department_&deptno..lis

select first_name, last_name
from hr.employees
where department_id = &deptno
order by employee_id
/

spool off

Run this and we not only get :

Enter Department ID : 10
old   3: where department_id = &deptno
new   3: where department_id = 10

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

SQL> 

…we get a file, appropriately named :

ls
department_10.lis

The magic here is that the “.” character delimits the variable substitution.
Just to prove the point, we can do the same with a positional parameter :

set verify off

spool department_&1..lis

select first_name, last_name
from hr.employees
where department_id = &1
order by employee_id
/

spool off

…run this and we get :

SQL> @position_param.sql 10

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

SQL> 

…and the appropriate file…

ls
department_10.lis

On that note, I’m off to the pub. Now, where did I leave my keys ?


Filed under: Oracle, SQL Tagged: spool; filename including a variable value, SQL*Plus

Sayonara to Sequences and Trouble for Triggers – Fun and Games in Oracle 12c

Sat, 2014-09-13 08:57

Ah, Nostalgia.
Not only can I remember the Good Old Days, I also remember them being far more fun than they probably were at the time.
Oh yes, and I was much younger….and had hair.
Yes, the Good Old Days, when Oracle introduced PL/SQL database packages, partitioning, and when the sequence became extinct.
Hang on, I don’t remember that last one…

The Good Old Ways

Say we have a requirement for a table to hold details of gadgets through the ages.
This table has been modelled with a synthetic key, and also a couple of audit columns so we can track when a row was created and by whom.
Traditionally, the code to fulfill this requirement would follow a familiar pattern.

The table might look something like this :

create table gadgets
(
    id number constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default user,
    creation_date date default sysdate
)
/

NOTE – you’d normally expect to see NOT NULL constraints on the CREATED_BY and CREATION_DATE columns. I’ve left these off for for the purposes of the examples that follow.

We’ll also want to have a sequence to generate a value for the id…

create sequence gad_id_seq
/

As it stands, this implementation has one or two issues…

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

The first problem becomes apparent when we query the table after these inserts…

SQL> select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   1 Dial-Up Modem	  MIKE		       31-AUG-14
   2 Tablet Computer	  MIKE		       31-AUG-14
   3 Netbook

Yes, although the insert was successful for the Netbook row, the explicit specification of CREATED_BY and CREATION_DATE values as NULL has overidden the default values defined on the table.

What’s more, there’s nothing enforcing the use of the sequence to generate the ID value. This becomes a problem when we go to do the next insert…


-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

insert into gadgets(id, gadget_name, created_by, creation_date)
*
ERROR at line 1:
ORA-00001: unique constraint (MIKE.DINO_PK) violated

Because we didn’t use the sequence for the previous insert, it’s still set to the value it had after it was last invoked…


SQL> select gad_id_seq.currval from dual;

   CURRVAL
----------
	 3

The traditional solution to these problems is, of course, a trigger…

create or replace trigger gad_bir_trg
    before insert on gadgets
    for each row
    --
    -- Make sure that :
    --  - id is ALWAYS taken from the sequence
    --  - created_by and creation date are always populated
begin
	:new.id := gad_id_seq.nextval;
    :new.created_by := nvl(:new.created_by, user);
    :new.creation_date := nvl(:new.creation_date, sysdate);
end;
/

Now, if we re-run our insert…


insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted


SQL> select * from gadgets where gadget_name = 'Smart Phone';

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   5 Smart Phone	  MIKE		       31-AUG-14

Yes, even though we’ve invoked the sequence in the INSERT statement, the trigger invokes it again and assigns that value to the ID column ( in this case 5, instead of 4).
Reassuringly thought, the CREATED_BY and CREATION_DATE columns are now populated.

So, in order to fulfill our requirements, we need to create three database objects :

  • A table
  • a sequence
  • a DML trigger on the table

Or at least, we did….

12c – the Brave New World

Oracle Database 12c introduces a couple of enhancements which will enable us to do away with our trigger completely.
First of all…

Changes to Default Values Specification

You can now specify a default value for a column that will be used, even if NULL is explicitly specified on Insert.
Furthermore, you can now also use a sequence number as a default value for a column.

If we were writing this application in 12c, then the code would look a bit different….


create sequence gad_id_seq
/

create table gadgets
(
    id number default gad_id_seq.nextval 
        constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user,
    creation_date date default on null sysdate
)
/

We’ve dispensed with the trigger altogether.
The ID column now uses the sequence as a default.
The CREATED_BY and CREATION_DATE columns will now be populated, even if NULL is explicitly specified as a value in the INSERT statement….


-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/




  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

Whilst we can now guarantee that the CREATED_BY and CREATION_DATE columns are populated, we are still left with one issue, or so you might think…

-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted

That’s odd. You’d think that the sequence NEXTVAL would be 3, thus causing the same error as before. However…

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
  21 Smart Phone	  MIKE		 31-AUG-14

Hmmm. Let’s take a closer look at the sequence…

select min_value, increment_by, cache_size, last_number
from user_sequences
where sequence_name = 'GAD_ID_SEQ'  
/

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
	 1	      1 	20	    41

Yes, it looks like, in 12c at least, the default for sequences is a cache size of 20.
If we wanted to create the sequence in the same way as for 11g ( i.e. with no caching), we’d need to do this :

create sequence gad_id_seq
    nocache
/

We can now see that the sequence values will not be cached :

PDB1@ORCL> select cache_size
  2  from user_sequences
  3  where sequence_name = 'GAD_ID_SEQ'
  4  /

CACHE_SIZE
----------
	 0

All of this is a bit of an aside however. The fact is that, as it stands, it’s still quite possible to by-pass the sequence altogether during an insert into the table.
So, we still need to have a trigger to enforce the use of the sequence, right ?
Well, funny you should say that….

Identity Column in 12c

Time for another version of our table. This time however, we’re dispensing with our sequence, as well as the trigger…

create table gadgets
(
    id number generated as identity 
		constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

Let’s see what happens when we try to insert into this table. Note that we’ve modified the insert statements from before as the sequences does not exist ….

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/


-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( null, 'Tablet Computer')
/

The first statement succeeds with no problem. However, the second and third both fail with :

ORA-32795: cannot insert into a generated always identity column

We’ll come back to this in a bit.

In the meantime, if we check the table, we can see the ID column is automagically populated….

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14

Oh, it’s just like being on SQL Server.

How is this achieved ? Well, there are a couple of clues.
First of all, executing the create table statement for this particular version of the table requires that you have the additional privilege of CREATE SEQUENCE.
A further clue can be found by looking once again at USER_SEQUENCES…

select sequence_name, min_value, increment_by, cache_size, last_number
from user_sequences
/

SEQUENCE_NAME	      MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------- ---------- ------------ ---------- -----------
ISEQ$$_95898		      1 	   1	     20 	 21

If we have a look at the column details for the table, we get confirmation that this sequence is used as the default value for the ID column :

  1  select data_default
  2  from user_tab_cols
  3  where table_name = 'GADGETS'
  4* and column_name = 'ID'
PDB1@ORCL> /

DATA_DEFAULT
--------------------------------------------------------------------------------
"MIKE"."ISEQ$$_95898".nextval


It’s worth noting that this sequence will hang around, even if you drop the table, until or unless you purge the table from the RECYCLEBIN.

If you prefer your sequences to be, well, sequential, the good news is that you can use the Sequence Creation syntax when specifying an identity column.
The change in the default number of values cached for sequences created in 12c, compared with 11g and previously, may lead you to consider being a bit more specific in how you create your sequence, just in case things change again in future releases.

Here we go then, the final version of our table creation script….

 create table gadgets
(
    id number generated always as identity
    (
        start with 1
        increment by 1
        nocache
        nocycle
    )
    constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

As we saw earlier, the INSERT statements for this table, now need to change. We can either specify “DEFAULT” for the ID column :

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/

…or simply omit it altogether…

insert into gadgets(gadget_name, created_by, creation_date)
values('Smart Phone', user, sysdate)
/

And, of course, we can also omit the values for the other defaulted columns should we choose….

insert into gadgets(gadget_name)
values('Netbook')
/

If we check the table after these statements, we can see that all is as expected :


select * from gadgets
/

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

As with a “traditional” table/sequence/trigger setup, an erroneous INSERT will cause a gap in the sequence…

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
/

values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
                                   *
ERROR at line 2:
ORA-12899: value too large for column "MIKE"."GADGETS"."CREATED_BY" (actual:
48, maximum: 30)

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', default, default)
/

select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
   5 Psion Series 5	  MIKE		 31-AUG-14
Conclusion

While we can see that 12c hasn’t done away with sequences altogether, it is fair to say that they are now a lot more unobtrusive.
As for the good old DML trigger ? Well, they’ll still be with us, but they may well be a little lighter on the mundane default handling stuff we’ve been through in this post.


Filed under: Oracle, PL/SQL, SQL Tagged: column default value, create sequence, default always, default cache value of sequence, default on null, generated as identity, identity column, insert value into identity column, ORA-32795 : cannot insert into a generated always identity column

Getting Python to play with Oracle using cxOracle on Mint and Ubuntu

Mon, 2014-08-25 12:57

“We need to go through Tow-ces-ter”, suggested Deb.
“It’s pronounced Toast-er”, I corrected gently.
“Well, that’s just silly”, came the indignant response, “I mean, why can’t they just spell it as it sounds ?”
At this point I resisted the temptation of pointing out that, in her Welsh homeland, placenames are, if anything, even more difficult to pronounce if you’ve only ever seen them written down.
Llanelli is a linguistic trap for the unwary let alone the intriguingly named Betws-Y-Coed.
Instead, I reflected on the fact that, even when you have directions, things can sometimes be a little less than straight forward.

Which brings me to the wonderful world of Python. Having spent some time playing around with this language, I wanted to see how easy it is to plug it into Oracle.
To do this, I needed the cxOracle Python library.
Unfortunately, installation of this library proved somewhat less than straightforward – on Linux Mint at least.
What follows are the gory details of how I got it working in the hope that it will help anyone else struggling with this particular conundurum.

My Environment

The environment I’m using to execute the steps that follows is Mint 13 (with the Cinnamon desktop).
The database I’m connecting to is Oracle 11gXE.

In Mint, as with most other Linux Distros, Python is part of the base installation.
In this particular distro version, the default version of Python is 2.7.

If you want to check to see which version is currently the default on your system :

which python
/usr/bin/python

This will tell you what file gets executed when you invoke python from the command line.
You should then be able to do something like this :

ls -l /usr/bin/python
lrwxrwxrwx 1 root root 9 Apr 10  2013 python -> python2.7

One other point to note is that, if you haven’t got it already, you’ll probably want to install the Oracle Client.
The steps you follow to do this will depend on whether your running a 32-bit or 64-bit OS.

To check this, open a Terminal Window and type :

uname -i

If this comes back with x86_64 then you are running 64-bit. If it’s i686 then you are on a 32-bit os.
In either case, you can find the instructions for installation of the Oracle Client on Debian based systems here.

According to the cxOracles’s official SourceForge site, the next bit should be simple.
Just by entering the magic words…

pip install cxOracle

…you can wire up your Python scripts to the Oracle Database of your choice.
Unfortunately, there are a few steps required on Mint before we can get to that point.

Installing pip

This is simple enough. Open a Terminal and :

sudo apt-get install python-pip

However, if we then run the pip command…

pip install cx_Oracle

cx_Oracle.c:6:20: fatal error: Python.h: No such file or directory

It seems that, in order to run this, there is one further package you need…

sudo apt-get install python-dev

Another point to note is that you need to execute the pip command as sudo.
Even then, we’re not quite there….

sudo pip install cx_Oracle

Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    Traceback (most recent call last):
      File "<string>", line 14, in <module>
      File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>
        raise DistutilsSetupError("cannot locate an Oracle software " \
    distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):

  File "<string>", line 14, in <module>

  File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>

    raise DistutilsSetupError("cannot locate an Oracle software " \

distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

----------------------------------------
Command python setup.py egg_info failed with error code 1
Storing complete log in /home/mike/.pip/pip.log

So, whilst we now have all of the required software, it seems that sudo does not recognize the $ORACLE_HOME environment variable.

You can confirm this as follows. First of all, check that this environment variable is set in your session :

echo $ORACLE_HOME
/usr/lib/oracle/11.2/client64

That looks OK. However….

sudo env |grep ORACLE_HOME

…returns nothing.

Persuading sudo to see $ORACLE_HOME

At this point, the solution presented here comes to the rescue.

In the terminal run…

sudo visudo

Then add the line :

Defaults env_keep += "ORACLE_HOME"

Hit CTRL+X then confirm the change by selecting Y(es).

If you now re-run the visudo command, the text you get should look something like this :

#
# This file MUST be edited with the 'visudo' command as root.
#
# Please consider adding local content in /etc/sudoers.d/ instead of
# directly modifying this file.
#
# See the man page for details on how to write a sudoers file.
#
Defaults        env_reset
Defaults        mail_badpass
Defaults        secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:$
Defaults        env_keep += "ORACLE_HOME"
# Host alias specification

# User alias specification

# Cmnd alias specification

# User privilege specification
                               [ Read 30 lines ]
^G Get Help  ^O WriteOut  ^R Read File ^Y Prev Page ^K Cut Text  ^C Cur Pos
^X Exit      ^J Justify   ^W Where Is  ^V Next Page ^U UnCut Text^T To Spell

You can confirm that your change has had the desired effect…

sudo env |grep ORACLE_HOME
ORACLE_HOME=/usr/lib/oracle/11.2/client64
Finally installing the library

At last, we can now install the cxOracle library :

sudo pip install cx_Oracle
Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    
Installing collected packages: cx-Oracle
  Running setup.py install for cx-Oracle
    
Successfully installed cx-Oracle
Cleaning up...

To make sure that the module is now installed, you can now run :

python
Python 2.7.3 (default, Feb 27 2014, 19:37:34) 
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> help('modules')

Please wait a moment while I gather a list of all available modules...

If all is well, you should be presented with the following list :

ScrolledText        copy_reg            ntpath              tty
SgiImagePlugin      crypt               nturl2path          turtle
SimpleDialog        csv                 numbers             twisted
SimpleHTTPServer    ctypes              oauth               types
SimpleXMLRPCServer  cups                opcode              ubuntu_sso
SocketServer        cupsext             operator            ufw
SpiderImagePlugin   cupshelpers         optparse            unicodedata
StringIO            curl                os                  unittest
SunImagePlugin      curses              os2emxpath          uno
TYPES               cx_Oracle           ossaudiodev         unohelper
TarIO               datetime            packagekit    

Finally, you can confirm that the library is installed by running a simple test.
What test is that ?, I hear you ask….

Testing the Installation

A successful connection to Oracle from Python results in the instantiation of a connection object. This object has a property called version, which is the version number of Oracle that the database is running on. So, from the command line, you can invoke Python…

python
Python 2.7.3 (default, Feb 27 2014, 19:58:35) 
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.

… and then run

>>> import cx_Oracle
>>> con = cx_Oracle.connect('someuser/somepwd@the-db-host-machine/instance_name')
>>> print con.version
11.2.0.2.0
>>> 

You’ll need to replace someuser/somepwd with the username and password of an account on the target database.
The db-host-machine is the name of the server that the database is sitting on.
The instance name is the name of the database instance you’re trying to connect to.

Incidentally, things are a bit easier if you have an Oracle client on your machine with the TNS_ADMIN environment variable set. To check this :

env |grep -i oracle
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
ORACLE_HOME=/usr/lib/oracle/11.2/client64

Assuming that your tnsnames.ora includes an entry for the target database, you can simply use a TNS connect string :

>>> import cx_Oracle
>>> con = cx_Oracle.connect('username/password@database')
>>> print con.version
11.2.0.2.0
>>> 
Useful Links

Now you’ve got cxOracle up and running, you may want to check out some rather useful tips on how best to use it :


Filed under: Linux, Oracle Tagged: cxOracle, pip install cxOracle, python, python-dev, uname, visudo, which