Skip navigation.

The Anti-Kyte

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

Getting a File Listing from a Directory in PL/SQL

Sat, 2015-04-25 12:32

It’s General Election time here in the UK.
Rather than the traditional two-way fight to form a government, this time around we seem to have a reasonably broad range of choice.
In addition to red and blue, we also have purple and – depending on where you live in the country, multiple shades of yellow and green.
The net effect is to leave the political landscape looking not so much like a rainbow as a nasty bruise.

The message coming across from the politicians is that everything that’s wrong in this country is down to foreigners – Eastern Europeans…or English (once again, depending on your location).
Strangely, the people who’ve been running our economy and public services for the last several years tend not to get much of a mention.
Whatever we end up choosing, our ancient electoral system is not set up to cater for so many parties attracting a significant share of support.

The resulting wrangling to cobble together a Coalition Government will be hampered somewhat by our – equally ancient – constitution.

That’s largely because, since Magna Carta, no-one’s bothered to write it down.

In olden times, if you wanted to find out what files were in a directory from inside the database, you’re options were pretty undocumented as well.
Fortunately, times have changed…

What I’m going to cover here is how to use an External Table pre-process to retrieve a file listing from a directory from inside the database.
Whilst this technique will work on any platform, I’m going to focus on Linux in the examples that follow…

An Overview of the Solution

First of all we’re going to write a shell script to give us a file listing on Linux.
This will include all of the details supplied by ls -l listing, in a comma delimited format.
The directory we’re going to get a list of files for is the DATA_PUMP_DIR.
Then we’ll create an External Table to read the output of the script.
Here goes then….

Getting a useable file listing on Linux

The first thing we need here is a shell script for the external table to execute in it’s pre-processor directive.
The output needs to be in a format that can be easily loaded by the external table.

Let’s have a look then…

/u01/app/oracle/admin/XE/dpdump $ ls -l
total 322320
-rw-r--r-- 1 oracle dba     87816 Aug 22  2014 export.log
-rw-r--r-- 1 oracle dba         0 Apr 16 13:13 file_list_dummy.txt
-rw-r----- 1 oracle dba 327385088 Aug 22  2014 full220814.dmp
-rw-r--r-- 1 oracle dba      2889 Jan  9 15:29 hr_dev_imp.log
-rw-r----- 1 oracle dba    524288 Jan  9 15:16 hr_export.dmp
-rw-r--r-- 1 oracle dba      2171 Jan  9 15:16 hr_export.log
-rw-r----- 1 oracle dba   1560576 Feb 12 11:46 ossca_pre_050_export.dmp
-rw-r--r-- 1 oracle dba      3331 Feb 12 11:46 ossca_pre_050_export.log
-rw-r----- 1 oracle dba    462848 Mar  5 13:44 test_schemas.dmp
-rw-r--r-- 1 oracle dba      1759 Mar  5 13:44 test_schemas_exp.log

There are a few things to note from here.
Firstly, there are up to eight distinct columns in the output. You’ll notice that files over 6 months old are reported with a date and then a year. Newer files just have a date.

Secondly, the widths of the fields themselves vary in length between files, most notably the file sizes.
We need to tidy up this output a bit if we’re going to be able to load it easily into the External Table.

After some trial and error, it looks like the following will meet our needs :

ls -l --time-style=long-iso | awk 'BEGIN {OFS = ",";} {print $1, $2, $3, $4, $5, $6" "$7, $8}'
total,322320,,,, ,
-rw-r--r--,1,oracle,dba,87816,2014-08-22 13:30,export.log
-rw-r--r--,1,oracle,dba,0,2015-04-16 13:13,file_list_dummy.txt
-rw-r-----,1,oracle,dba,327385088,2014-08-22 13:30,full220814.dmp
-rw-r--r--,1,oracle,dba,2889,2015-01-09 15:29,hr_dev_imp.log
-rw-r-----,1,oracle,dba,524288,2015-01-09 15:16,hr_export.dmp
-rw-r--r--,1,oracle,dba,2171,2015-01-09 15:16,hr_export.log
-rw-r-----,1,oracle,dba,1560576,2015-02-12 11:46,ossca_pre_050_export.dmp
-rw-r--r--,1,oracle,dba,3331,2015-02-12 11:46,ossca_pre_050_export.log
-rw-r-----,1,oracle,dba,462848,2015-03-05 13:44,test_schemas.dmp
-rw-r--r--,1,oracle,dba,1759,2015-03-05 13:44,test_schemas_exp.log
The Shell Script

There are a few things we need to bear in mind for this script.
As it’s going to be called from the external table, we cannot assume that any environment variables have been set (even $PATH).
Therefore, the commands need to be called using their absolute path, rather than just their name.

Additionally, the script will be passed the fully qualified path and filename of the location of our external table as the first argument. This is going to be useful as we’ll need to direct the shell script to the appropriate directory to list.
We can strip the filename from this argument and use the directory by using basename…

If you want to find out what the paths for these commands are ( and they can differ between Linux Distros), then you can do the following :

which ls
/bin/ls
which awk
/usr/bin/awk
which basename
/usr/bin/basename

The final script looks like this :

#!/bin/sh
#
# Generate a comma separated ls -l for the directory in which the external table's
# location file resides
#
targetDir=`/usr/bin/dirname $1`
/bin/ls -l --time-style=long-iso $targetDir | /usr/bin/awk 'BEGIN {OFS = ",";} {print $1, $2, $3, $4, $5, $6" "$7, $8}'
exit 0

I’ve saved this file as list_files.sh

Now for…

The External Table

The output we’ll be reading starts with the Total number of blocks in the directory, so we’ll want to skip that.
As for the files themselves, each file record will have the following attributes :

  • File Permissions
  • Number of Hard Links
  • File Owner
  • Group of the file owner
  • File size in bytes
  • Date the file was last modified
  • The file name

As we’re using a pre-processor in our external table, the usual rules will apply.

The External Table needs to have a location file specified at runtime, even though it won’t actually be reading the contents of that file in this instance.

So, our table will look something like this :

create table list_files_xt
(
    permissions varchar2(15),
    hard_links number,
    file_owner varchar2(32),
    group_name varchar2(32),
    size_bytes number,
    last_modified date,
    file_name varchar2(255)
)
    organization external
    (
        type oracle_loader
        default directory data_pump_dir
        access parameters
        (
            records delimited by newline
            nologfile
            preprocessor pre_proc_dir : 'list_files.sh'            
            skip 1
            fields terminated by ','            
            ( 
                permissions,
                hard_links,
                file_owner,
                group_name,
                size_bytes,
                last_modified date 'YYYY-MM-DD HH24:MI',
                file_name
            )
        )
        location('list_files_dummy.txt')
    )
/
Deploying the Components

First, we need to put the script in place. To work out where, check the path of the directory object that we’re referencing in the External Table definition ( in this case, PRE_PROC_DIR).
Then we’ll need to create the external table’s dummy file in the directory specified as the Default Directory ( DATA_PUMP_DIR).

So, we need to know the OS location of these directories…

select directory_name, directory_path
from all_directories
where directory_name in ('PRE_PROC_DIR', 'DATA_PUMP_DIR')
/

DIRECTORY_NAME	DIRECTORY_PATH
--------------- ----------------------------------------
PRE_PROC_DIR	/u01/app/oracle/pre_proc
DATA_PUMP_DIR	/u01/app/oracle/admin/XE/dpdump/

Now to make sure that the shell script is in the right place and has the appropriate permissions :

sudo cp list_files.sh /u01/app/oracle/pre_proc/.
sudo chmod o+x /u01/app/oracle/pre_proc/list_files.sh

If we now check the permissions on the file, we can see that all users have execute :

ls -l /u01/app/oracle/pre_proc/list_files.sh
-rw-r--r-x 1 root root 281 Apr 23 13:02 /u01/app/oracle/pre_proc/list_files.sh

Next we need to create the dummy file for the External table. The oracle os user needs to have read and write permissions on this file.
The simplest way to achieve this is to create the file as the oracle os user :

sudo su oracle
touch /u01/app/oracle/admin/XE/dpdump/list_files_dummy.txt

Now we can see that we’ve got an empty file ….

ls -l /u01/app/oracle/admin/XE/dpdump/list_files_dummy.txt
-rw-r--r-- 1 oracle dba 0 Apr 23 13:05 /u01/app/oracle/admin/XE/dpdump/list_files_dummy.txt

Finally, we just need to login to the database and create the table using the script above.
Now let’s see what export files I’ve got :

select *
from list_files_xt
where file_name like '%.dmp'
order by last_modified
/

PERMISSION HARD_LINKS FILE_OWNER GROUP_NAME  SIZE_BYTES LAST_MODIFIED      FILE_NAME
---------- ---------- ---------- ----------- ---------- ------------------ --------------------------------------------------
-rw-r-----          1 oracle     dba          327385088 22-AUG-14          full220814.dmp
-rw-r-----          1 oracle     dba             524288 09-JAN-15          hr_export.dmp
-rw-r-----          1 oracle     dba            1560576 12-FEB-15          ossca_pre_050_export.dmp
-rw-r-----          1 oracle     dba             462848 05-MAR-15          test_schemas.dmp

NOTE – no minorities were persecuted in the writing of this post.


Filed under: Linux, Oracle, SQL Tagged: awk, basename, chmod, external table, external table preprocessor, ls, ls --time-style = long-iso, OFS, touch, which

SQLCL – The New SQL*Plus

Sun, 2015-04-12 10:33

To borrow a well-known saying, One-Day International Cricket is a game played by two sides for 100 overs…and then the Aussies win the World Cup.
Something else that doesn’t seem to change much over time is SQL*Plus. The command line interface to Oracle Databases has been around, in it’s current guise, since 1985.
Whilst there have been some changes here and there, it’s basic functionality has remained largely unchanged over the intervening 30 years.
Now, however, it looks like things are about to change as Oracle lavish some attention on the noble CLI.

You may be wondering how this is in any way relevant in the modern world of GUI development.
Well, there are still some things that you need the command line for.
Interactive connection to a database from a server that’s not running a desktop environment would be one.

More common though, are those jobs that need to run unattended. These will include batch jobs managed by a scheduling tool external to the Oracle RDBMS, such as Autosys, or even good old CRON.
Increasingly, it will also include jobs that are initiated as part of Continuous Integration or Release Management testing.

SQL*Plus for the 21st Century is currently going by the name of SQLCL (SQL Command Line). It has also been known as SDSQL (SQLDeveloper SQL) and even SQL*Plus++ (my personal favourite).

Whilst the currently available versions of SQLCL are very much in the Early Adopter stage, there is enough there to show the direction in which things are moving.
Whilst the decision has been taken to ensure that SQLCL is fully backward-compatible with the current SQL*Plus, some of the new features may well have significant implications in the way that the Oracle CLI is used in the future.

What I’m going to cover here is :

  • How SQLCL differs “structurally” from SQL*Plus
  • Improvements in command-line interaction incorporating Linux-like and IDE features, including glorious technicolour!

All of which leaves the ageing geek in me unashamedly excited.

Before I go on, I should take this opportunity to say thanks to Jeff Smith and the team for answering the questions I had about some of the new features.

Download and Installation

The first thing you’ll notice about SDSQL is that, unlike the Oracle Client of which SQL*Plus is a component, it is a single file.
This is a pretty good start as you don’t have to figure out which files you need to download before you get going.

Instead, you simply need to head over to the SQLDeveloper Download Page and download Command Line SDSQL – All Platforms.
You are rewarded with posession of 11MB worth of :

sqlcl-4.1.0.15.067.0446-no-jre.zip

Once you’ve unzipped the tool, go to the bin directory. In my case :

cd /opt/sqlcl/bin

Here you will find the following files :

  • sql.bat – a Windows batch script
  • sql – a bash script
  • sql.exe – a Windows executable

On a Windows client, you can just run sql.exe and be on your way. The bash script provided ultimately executes this command :

java  -Djava.awt.headless=true -Dapple.awt.UIElement=true -jar /opt/sqlcl/sqlcl/lib/oracle.sqldeveloper.sqlcl.jar

In order for SQLCL to run whether on Windows or Linux, you need Java installed. To be more precise, you need a Java Runtime Environmnent (JRE) of version 1.7 or higher.

This in itself is not a major issue. However, it’s worth bearing this dependency in mind if you’re working in a large organization with a separate Server Admin team as you’ll probably need to negotiate some process hurdles to get a suitable JRE onto your servers if there isn’t one already in place.

Database Connections

As SQLCL isn’t part of the client, it does not assume that there is a tnsnames.ora hanging around for it to read (although it will happily read one that’s pointed to by the TNS_ADMIN environment variable).

I think it’s fair to say that the “native” connection method is to use the EZConnect syntax which has the format :

host:port/sid

So, connecting to my local XE database interactively can look something like this :

SQLcl: Release 4.1.0 Beta on Fri Apr 10 15:49:47 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.


Username? (''?) mike
Password? (**********?) ********
Database? (''?) localhost:1526/XE
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production 

SQL> 

…where my database is on the current machine, the TNS Listener is on port 1526 and the SID is XE.

Of course, having an Oracle Client installed and a TNS entry for my database specified, I can also still do this :

SQLcl: Release 4.1.0 Beta on Fri Apr 10 15:49:47 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.


Username? (''?) mike
Password? (**********?) ********
Database? (''?) XE
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production 

SQL> 

There are other connection methods available. Barry McGillin has an example of connecting via LDAP.

Of course, using the EZConnect syntax all the time, especially when you open another connection from within your session could involve a fair amount of typing. Fortunately, The developers have thought of this…

NET

The NET command allows you to “save network details and assign it a shortcut command”.
For example, to create an alias for the database running on a Developer Day VirtualBox image :

net alias dday=0.0.0.0:1521/orcl;

NOTE – you need to terminate the string with a semi-colon at the end of the line.

To test it, we first need to enable NET in our SQLCL session (it’s set to OFF by default) …

SQL> set net on
SQL> show net
net: ON

So, whilst connected to XE as mike, I can use the NET alias I’ve just created to connect to the Developer Day database :

SQL> conn hr@dday
Password? (**********?) ************
Connected

SQL> show user
USER is "HR"

SQL> select name from v$database;

NAME    
---------
CDB1     

SQL> 
The Command Line, but not as we know it

When it comes to command-line editing and scrollback, SQL*Plus is, well, a bit basic.
If you’re on Windows, you can scroll back through previous commands. Unfortunately, even this is not a feature on Linux.
There have been efforts to make the SQL*Plus a bit more friendly over the years, notably the RLWRAP project.
If you haven’t discovered the joys of that particular software, you are about to have a new experience at the SQL prompt…

Tab Completion

Type the following…

select * from hr.reg

when you hit the TAB key, the table name is automagically completed for you.
OK, it uppercases the table name to REGIONS but hey, if you’re that worried about code formatting…well, we’ll get to that in a bit.

In the meantime, just marvel at the fact that it’s almost like being at the $ prompt.

Multi-Line Console Editing

If you think that’s good…

select * 
from hr.regions
/

I run this and then realise I really should have added a predicate to avoid having to wade through a massive result set ( OK, it’s only 4 rows, but just bear with me here).
Rather than having to fiddle about with the arcane SQL*Plus editing commands, I can simply use the list command to show me what’s currently in the buffer ( i.e. the query I’ve just run) and then hit the up arrow.

I’m now magically transported to the end of the last line of the statement. I can add a new line, simply by hitting RETURN and then add my predicate (NOTE – it’s probably a good idea to hit back arrow, forward arrow if you’re doing this as otherwise SQLCL thinks you want to cycle through the previous commands you’ve issued).

SQL> select *
  2  from hr.regions
  3* where REGION_ID = 1
  4  /

 REGION_ID REGION_NAME             
---------- -------------------------
         1 Europe                   

SQL> 

The uppercase REGION_ID indicates that I got this column name using tab completion.
Barry McGillin has a demonstration of this feature here.

HISTORY

Rather than simply remembering the last SQL or PL/SQL that was executed, SQLCL retains a history of the last 100 commands executed. Note that this is the last 100 commands for any user connecting on the current client.
This includes SQL*Plus commands, such as describe.

You can scroll back and forward through the previous commands using the arrow keys – nothing new for Windows clients but it is for Linux.

Alternatively, you can get a listing by typing :

history

This will give you a numbered listing of the commands in the history.

Incidentally, if you use the built-in SQLCL help command for history, you’ll get this listing :

SQL>help history
history [<index> | FULL | USAGE | HELP ]

SQL>history full
1  select 1 from dual;
2  select 2
>  from dual;
3  select 3 from dual
>  where 1=1;

SQL>history usage
1  (2) select 1 from dual; 
2  (11) select 2 from dual; 
3  (2) select 3 from dual where 1=1; 

SQL>history 3
1  select 3 from dual
2* where 1=1;

However, if you try the help option of the history command, you get something slightly different …

SQL> history help 

history [<index> | FULL | USAGE | TIME | HELP | CLEAR]

SQL> 

The full option appears to simply list all of the commands in the history ( the same as simply issuing the HISTORY command on it’s own).

When we look at some of the other options, it gets a bit interesting :

SQL> history
  1  select name from v$database
  2  select sysdate from dual
SQL> history usage
  1  (1) select name from v$database
  2  (4) select sysdate from dual
SQL> history time
  1  (00.176) select name from v$database
  2  (05.415) select sysdate from dual
SQL> 

The USAGE option keeps a count of the number of times a particular command has been executed.
The TIME option shows the total execution time for the statement (thanks Jeff).

In some circumstances, this could prove handy, especially when you realise that you need to set timing on…just after you’ve kicked off your script.

If you want to cleardown the history…

SQL> history clear
History Cleared

One point to note – as with it’s Linux counterpart, HISTORY retains any command executed using that client, not simply any command when connected as a particular user. This is something you may need to be aware of on servers where the Oracle “client” is shared by multiple users.

CD

You know how much fun it is when you have a master release script, either for deployment to an environment or, just as common these days, to refresh a Continuous Integration environment.

Such a script may well look something like this :

prompt Deploying HR_DEV...

prompt Sequences
prompt ---------

@sequences/locations_seq.sql

prompt Tables
prompt ------

@tables/countries_tab.sql
@tables/locations_tab.sql

prompt Deployment complete

With SQLCL, the script becomes somewhat simpler :

prompt Deploying HR_DEV...

prompt Sequences
prompt ---------

cd sequences
@locations_seq.sql

prompt Tables
prompt ------

cd ../tables
@countries_tab.sql
@locations_tab.sql

prompt Deployment complete

Yes, you can now change directory from SQLCL, in the same way that you would at the OS level. As well as being rather convenient, this should serve to save a fair bit of typing, especially for scripts such as this one.

If all that isn’t quite enough Linux-like goodness for you then you’d probably be quite interested in…

ALIAS

As you may guess from the name, ALIAS allows you to save and invoke SQL or PL/SQL blocks under a pre=defined name.
There are already some aliases defined. You can find them by simply typing :

SQL> alias
locks
tables
tables2
SQL> 

If you want to see the definition of the tables alias….

SQL> alias list tables
tables
------
 select table_name "TABLES"from user_tables
SQL> 

You can run this simply by typing the alias name at the prompt. For example, if you connect as the HR user …

SQL> tables
Command=tables

TABLES                       
------------------------------
REGIONS                       
LOCATIONS                     
DEPARTMENTS                   
JOBS                          
EMPLOYEES                     
JOB_HISTORY                   
COUNTRY_EXT                   
COUNTRIES                     

 8 rows selected 

SQL> 

Of course, you can add your own alias. If you’re an inveterate clock-watcher, for example….

SQL> alias current_time=select to_char(sysdate, 'HH24:MI') from dual;

The alias is now saved so, whenever I want to know if it’s nearly hometime….

SQL> current_time
Command=current_time

TO_CHAR(SYSDATE,'HH24:MI')
--------------------------
19:53                     

SQL> 

According to the help, you can define aliases for PL/SQL blocks, and even reference bind variables.

Choosing you’re output format with SQLFORMAT

The new SQLFORMAT parameter deserves particular attention. Before I get into some of it’s more obvious benefits, take a look at this :

set sqlformat ansiconsole
with ts_details as
(
    select rpad(tablespace_name,30, ' ')||' '||lpad(floor(used_percent), 3, ' ') as ts_line,
        used_percent
    from dba_tablespace_usage_metrics
)
select 
    case 
        when used_percent > 70 then '@|bg_red '||ts_line||'|@'
        when used_percent < 1 then '@|bg_green '||ts_line||'|@'
        else '@|bg_yellow '||ts_line||'|@' 
    end as ts_usage_percentage
from ts_details
/

Now at first glance this script looks rather odd. Have I had an attack of fat-fingers ? Well, let’s run it and see…

Contrived example to show traffic-lighting

Contrived example to show traffic-lighting

Yep, SQL*Plus now does colour. OK, so I set the threshold values to make sure I got records to show in all three colours, but you get the idea.

There’s a rather more comprehensive script by Kris Rice here, which details many colour options.

One of the neat features in SQLDeveloper has always been the ability to generate the output from a query in a pre-defined format.
This ability is still there in SQLCL. To use a fashionable example…

SQL> select /*json*/ * from hr.regions;

{"items":[
{"region_id":1,"region_name":"Europe"},{"region_id":2,"region_name":"Americas"},{"region_id":3,"region_name":"Asia"},{"region_id":4,"region_name":"Middle East and Africa"},{}]}

SQL> 

SQLFORMAT allows you to specify the output format separately from the code. For example :

SQL> set sqlformat csv
SQL> select * from hr.regions;

"REGION_ID","REGION_NAME"
1,"Europe"
2,"Americas"
3,"Asia"
4,"Middle East and Africa"

SQL> 

The fact that the output format is separated from the script means that you can use the same script to generate multiple formats.
Ultimately, this means less code, not to mention less complex (or, at least, fiddly) code.

For example, instead of :

select employee_id||','||first_name||','||last_name
    ||'.'||email||','||phone_number
from hr.employees
/

… you can now have…

set sqlformat csv
select employee_id, first_name, last_name,
    email, phone_number
from hr.employees
/

…and if you need to create an additional feed file in a different format, you simply need to type…

set sqlformat xml
...

…and execute exactly the same code again.

There are several SQLFORMAT options. The ones that I’ve tested are :

  • default
  • ansiconsole
  • csv
  • insert – lists resuts as an insert statement
  • loader – pipe-delimited
  • delimited – same as csv
  • xml
  • html
  • fixed – fixed width
  • text
  • json
INFORMATION = KNOWLEDGE = POWER = ENERGY = MATTER = MASS

This heading is a slight misquote of Terry Pratchett’s L-Space equation.
The theory of L-space is that a large collection of accumulated knowledge, such as a library, can act like a black hole.
If you want to have a go at bending the fabric of space and time armed with nothing more than a command line…

INFORMATION

The humble DESCRIBE command in SQL*Plus has been around for a while. I’d guess it’s probably as old as the tool itself.
In that time, things have changed a bit.
It’s now possible to have comments on columns. There’s also this new-fangled PL/SQL, together with it’s associated packages.
If, like me, you’ve hankered after a describe that’s just a bit more able to handle these more modern developments, well, you’re in luck.

First off, let’s issue a good old DESCRIBE :

SQL> desc employees
Try the new information command: info employees


Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    
SQL> 

Interesting message, let’s give it a go (note, it’s probably a good idea to set the SQLFORMAT to ansiconsole at this point) :

SQL> set sqlformat ansiconsole
SQL> info employees
Columns 
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*EMPLOYEE_ID     NUMBER(6,0)         No                   Primary key of employees table.
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  First name of the employee. A not null column.
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Last name of the employee. A not null column.
 EMAIL           VARCHAR2(25 BYTE)   No                   Email id of the employee
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  Phone number of the employee; includes country code and area code
 HIRE_DATE       DATE                No                   Date when the employee started on this job. A not null column.
 JOB_ID          VARCHAR2(10 BYTE)   No                   Current job of the employee; foreign key to job_id column of the
                                                                    jobs table. A not null column.
 SALARY          NUMBER(8,2)         Yes                  Monthly salary of the employee. Must be greater
                                                                    than zero (enforced by constraint emp_salary_min)
 COMMISSION_PCT  NUMBER(2,2)         Yes                  Commission percentage of the employee; Only employees in sales
                                                                    department elgible for commission percentage
 MANAGER_ID      NUMBER(6,0)         Yes                  Manager id of the employee; has same domain as manager_id in
                                                                    departments table. Foreign key to employee_id column of employees table.
                                                                    (useful for reflexive joins and CONNECT BY query)
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  Department id where employee works; foreign key to department_id
                                                                    column of the departments table

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION  
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             


References
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED  
DEPARTMENTS  DEPT_MGR_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
EMPLOYEES    EMP_MANAGER_FK   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
JOB_HISTORY  JHIST_EMP_FK     NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  

SQL> 

INFORMATION can give you even more in-depth information:

SQL> info+ employees
Columns 
NAME             DATA TYPE           NULL  DEFAULT    LOW_VALUE             HIGH_VALUE            NUM_DISTINCT   HISTOGRAM  
*EMPLOYEE_ID     NUMBER(6,0)         No                   100                   206                   107            NONE       
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  Adam                  Winston               91             NONE       
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Abel                  Zlotkey               102            NONE       
 EMAIL           VARCHAR2(25 BYTE)   No                   ABANDA                WTAYLOR               107            NONE       
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  011.44.1343.329268    650.509.4876          107            NONE       
 HIRE_DATE       DATE                No                   2001.01.13.00.00.00   2008.04.21.00.00.00   98             NONE       
 JOB_ID          VARCHAR2(10 BYTE)   No                   AC_ACCOUNT            ST_MAN                19             FREQUENCY  
 SALARY          NUMBER(8,2)         Yes                  2100                  24000                 58             NONE       
 COMMISSION_PCT  NUMBER(2,2)         Yes                  .1                    .4                    7              NONE       
 MANAGER_ID      NUMBER(6,0)         Yes                  100                   205                   18             FREQUENCY  
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  10                    110                   11             FREQUENCY  

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION  
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             


References
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED  
DEPARTMENTS  DEPT_MGR_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
EMPLOYEES    EMP_MANAGER_FK   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
JOB_HISTORY  JHIST_EMP_FK     NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  

SQL> 

Whilst all of this additional functionality is very welcome, the best bit, for me at least, is this…

SQL> info dbms_utility.get_time
Package

/* Package SYS.DBMS_UTILITY */
/*  FUNCTION  SYS.DBMS_UTILITY.GET_TIME  */
    /*   RETURN NUMBER   */
     v_ret := SYS.DBMS_UTILITY.GET_TIME( );


SQL> info dbms_metadata.get_ddl
Package

/* Package SYS.DBMS_METADATA */
/*  FUNCTION  SYS.DBMS_METADATA.GET_DDL  */
    /*   RETURN CLOB   */
     v_ret := SYS.DBMS_METADATA.GET_DDL(   OBJECT_TYPE   =>  p_IN_param0  /*   VARCHAR2   */,
                                           NAME          =>  p_IN_param1  /*   VARCHAR2   */,
                                           SCHEMA        =>  p_IN_param2  /*   VARCHAR2   */,
                                           VERSION       =>  p_IN_param3  /*   VARCHAR2   */,
                                           MODEL         =>  p_IN_param4  /*   VARCHAR2   */,
                                           TRANSFORM     =>  p_IN_param5  /*   VARCHAR2   */);


SQL> 

Yes, no longer will I have to scroll through the 86 members of DBMS_METADATA to find out the signature for GET_DDL.
I can now simply describe (OK, INFO) the package member directly. Just like going through a wormhole directly to the information I need (to stretch the L-space metaphor).

DDL

As the name suggests, DDL outputs the DDL for a given object. For example…

SQL> ddl regions

  CREATE TABLE "HR"."REGIONS" 
   (	"REGION_ID" NUMBER CONSTRAINT "REGION_ID_NN" NOT NULL ENABLE, 
	"REGION_NAME" VARCHAR2(25), 
	 CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

You also have the option of writing the output to a file. This command …

ddl regions regions_tab.sql

…writes the output to a file called regions_tab.sql, as well as the command line.

Whilst we’re on the subject of Time (well, saving some, at least)…

CTAS

CTAS – Create Table as Select – without all of that typing…

CTAS regions new_regions

… generates DDL to create a new table called new_regions…

  CREATE TABLE "HR"."NEW_REGIONS" 
   (	"REGION_ID",
	"REGION_NAME",
	 CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 

as 
select * from REGIONS

As CTAS does use DBMS_METADATA, you can amend the output using that package’s SET_TRANFORM_PARAM procedure. For example :

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false)
anonymous block completed

SQL> ctas regions new_regions

  CREATE TABLE "HR"."NEW_REGIONS" 
   (	"REGION_ID",
	"REGION_NAME",
	 CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" 
as 
select * from REGIONS
SQL> 

UPDATE – Jeff has posted a much better example of this feature here.

Remember, CTAS simply generates the DDL command to create the table and writes it to the buffer (and a file, if specified). It does not actually execute the command.

LOAD

The LOAD command offers an alternative way of populating a table from a CSV file.

Let’s try using a topical example…

create table icc_wc_winners
(
    year number(4),
    country varchar2(30)
)
/

The csv file is in a sub-directory called cricket :

YEAR,COUNTRY
1975,WEST INDIES
1979,WEST INDIES
1983,INDIA
1987,AUSTRALIA
1992,PAKISTAN
1996,SRI LANKA
1999,AUSTRALIA
2003,AUSTRALIA
2007,AUSTRALIA
2011,INDIA
2015,AUSTRALIA

At present, LOAD has a bit of a problem recognising that you’ve chnaged directory using the CD command. However, the developers have fixed this and it should be available in a future release.
In the meantime though, we can get this to work by specifying the relative path for the csv file :

SQL> load icc_wc_winners cricket/wc_winners.csv
--Number of rows processed: 11
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors
SQL>

NOTE – you need to issue a commit for these records to be saved permanently.

FORMAT

Being a rather particular breed, developers tend to like their code formatted in a certain way. The trouble is, if you ask ten developers what that way should be, you’re likely to get ten different answers.
It is for this reason that Coding Standards documents tend to illicit an inordinate amount of controversy.
As I’ve noted previously, one possible solution to this is to let developers format their code in whatever way they see fit and then just run the code through a formatting tool that converts it to whatever the standard is.
All the main IDEs have their own formatting solutions. However, you can now do this at the command line.

For example, let’s try this query :

select dept.department_name, emp.first_name||' '||emp.last_name as empname
from employees emp
inner join departments dept
  on emp.department_id = dept.department_id
order by 1,2  
/

Using the new FORMAT command, we can, now apply the default SQLDeveloper formatting…

SQL> format buffer
  1  SELECT
  2    dept.department_name,
  3    emp.first_name
  4    ||' '
  5    ||emp.last_name AS empname
  6  FROM
  7    employees emp
  8  INNER JOIN departments dept
  9  ON
 10    emp.department_id = dept.department_id
 11  ORDER BY
 12*   1,2
SQL> 

Better still, we can even take the source file for the query, run it through the formatter and save the output to a new file :

SQL> format file hr.sql hr_yuk.sql
SQL> 

The resulting file looks like this :

SELECT
  dept.department_name,
  emp.first_name
  ||' '
  ||emp.last_name AS empname
FROM
  employees emp
INNER JOIN departments dept
ON
  emp.department_id = dept.department_id
ORDER BY
  1,2 /

As you can probably tell by the output filename I’ve chosen, I’m not particularly keen on the default formatting.
I’ll admit, this is largely subjective on my part. However, I would like the code to be formatted according to my preferences rather than the default.
Once again, the developers have a solution for this which should be available in the near future.
With this functionality in place, you could potentially leave the contentious business of formatting code to the standard until just before deployment. Running each program through a formatter setup to meet your coding standards may be an appealing option.

BRIDGE

One of the major advantages IDE’s have over the command line is the ability to compare two schemas in different databases.
Up until now, this has not been possible from SQL*Plus without the use of a database link.
The BRIDGE command may well change all that.

At present, it’s functionality is restricted to creating a table in your current connection based on the results of a query run against a second connection that you specify. For example, to create a table in my current schema with the details of the tables in a remote hr schema….

SQL> bridge hr_tabs as "jdbc:oracle:thin:hr/the_hr_password@0.0.0.0:1521/orcl"(select table_name from user_tables);
Table hr_tabs : insert succeeded 
SQL> select * from hr_tabs;

TABLE_NAME                                                                     
--------------------------------------------------------------------------------
JOB_HISTORY                                                                     
EMPLOYEES                                                                       
JOBS                                                                            
DEPARTMENTS                                                                     
LOCATIONS                                                                       
REGIONS                                                                         
COUNTRIES                                                                       

 7 rows selected 

SQL> 

…where the_hr_password is the password for the HR user on the target database.

One point to note here is that bridge executes the remote connection and the Create Table independently of each other.
Therefore, it’s quite possible for the connection to fail and the table to be created anyway.

NOTE – it should be possible to get around this behaviour by specifiying WHENEVER SQLERROR EXIT. I’ve not managed to get this to work as I’d expect, but this could be because I’m doing something silly.

Another thing to be aware of is that the BRIDGE command you’ve issued will be retained in the HISTORY, complete with uid/password.
This is currently on the developers’ to-do list.

APEX

The last of the new commands to cover is APEX.

If you simply issue this command without any arguments, it will list all of the APEX Applications where the user you are currently connected as is defined as the owning schema.
The exception to this is if you’re connected as SYS as SYSDBA where you’ll get a full list of all APEX applications.

So, connected as user OSSCA :

SQL> apex
WORKSPACE  APPLICATION_ID  APPLICATION_NAME             BUILD_STATUS     LAST_UPDATED_ON        
OSSCA2     100             Sample Database Application  Run and Develop  26-FEB-15              
OSSCA2     101             OSSCA UI                     Run Only         26-FEB-15              

APEX also provides the facility to export an application. By spooling the output, you can create the appropriate file :

spool my_apex_export.sql
apex export 100
spool off

…creates the APEX export file my_apex_export.sql, as well as outputting to the screen.

Conclusion

Whilst it’s still very early days for SQLCL, it’s potential impact is significant. I make no apologies for being excited about it.

Where to go for more SQLCL stuff

There’s a fair amount of information out there about SQLCL.

A good starting point would be Jeff’s presentation, which is available here.

Both Kris Rice and Barry McGillin are members of the development team and post regularly on the subject of SQLCL.

If you find any glitches, or have any questions, then the place to go is the SQLDeveloper OTN Forum.

Finally, as it’s around Easter Time, try entering the following at the SQLCL command line :

show sqldev2

Filed under: Oracle, SQL Tagged: alias, APEX, bridge, cd, colours in sqlcl, ctas, dbms_metadata.set_transform_param, ddl, format, history, information, load, multi-line editing in sqlcl, net, sdsql, sqlcl, sqlformat, tab completion in sqlcl

SQLDeveloper XML Extensions and auto-navigation

Sun, 2015-03-22 09:42

It’s official, England are now the second best cricket team in the British Isles !
After all, Scotland were dispatched with ease and as for Wales…they didn’t even make it to the Cricket World Cup.
OK, technically they did because they’re part of England for the purposes of cricket…although you’d be hard pressed to get them to admit it.
Ireland are, of course, some way in front having actually managed to actually win the odd game against Test Playing Nations.
Whilst it takes quite some effort to find silver lining in the cloud of English Cricket’s latest debacle, the same cannot be said if SQLDeveloper is your Oracle Database IDE of choice …

Why I use SQLDeveloper

Now, I’m well aware that each developer has their own favourite IDE. TOAD and PL/SQL Developer are fine tools in their own right, each with their own strengths. Which of these tools you swear by, or swear at, is always likely to be based on some fairly subjective criteria.

One of the main reasons I have for using SQLDeveloper is that it’s so easy to extend by means of a judicious application of XML, combined with a bit of SQL and/or PL/SQL.

Fun with Foreign Keys

As with all of the mainstream IDE’s, SQLDeveloper displays a table’s constraints as standard. However, what it doesn’t show is which tables have Foreign Keys to the table you’re looking at.

If, for example, you want to find out which tables have a Foreign Key to HR.COUNTRIES, you need to run a query like this :

select owner, table_name, constraint_name
from all_constraints
where constraint_type = 'R'
and (r_owner, r_constraint_name) in 
(
    select owner, constraint_name
    from all_constraints
    where constraint_type in ('U', 'P')
    and owner = 'HR'
    and table_name = 'COUNTRIES'
)
order by owner, table_name, constraint_name
/

OWNER                          TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
HR                             LOCATIONS                      LOC_C_ID_FK

SQL> 

Wouldn’t it be handy if you could somehow add a tab onto the table view in SQLDeveloper and have this information
pop-up for the table you’re looking at.
Well, funny you should say that….

<items>
	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Child Tables]]></title>
		<query>
			<sql>
				<![CDATA[
					select owner, table_name, constraint_name
					from all_constraints 
					where constraint_type = 'R' 
					and (r_owner, r_constraint_name) in
					(
						select owner, constraint_name 
						from all_constraints 
						where constraint_type in ('U', 'P') 
						and owner = :OBJECT_OWNER 
						and table_name = :OBJECT_NAME 
					) 
					order by owner, table_name, constraint_name 
				]]>
			</sql>
		</query>
	</item>
</items>

It may not be immediately obvious how this xml code is going to help us.
The answer is that it’s the SQLDeveloper extension that we’ve just written. Yep, just that one xml file.

It’s probably worth taking a closer look at the structure of this file in terms of the XML…

<items>
	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Enter the Tab Name Here]]></title>
		<query>
			<sql>
				<![CDATA[Enter your query here]]>
			</sql>
		</query>
	</item>
</items>

If you want to add a node to a different object type, it seems that you just need to change the node attribute of the item tag as appropriate.
For example, specifying “Viewnode” should enable you to add a Tab to your Views.

Anyway, to add this to SQLDeveloper, we just need to do a bit of pointing and clicking…

To start with, save the above code into an xml file. I’ve called mine deps_fk.xml.

In SQLDeveloper, go to the Tools menu and select Preferences

In the tree on the left hand side expand the Database node. Then click on User Defined Extensions.

sqld_add_ext1

Now click the Add Row button at the bottom of the Right Hand Pane and you should see a new Row appear under the Type and Location headings like this :

sqld_add_ext2

If you now click in the row you’ve just created under the Type heading, you should get a drop-down list of values.
Select Editor from the list :

sqld_add_ext3

In the Location field, you just need to tell SQLDeveloper where your xml file is :

sqld_add_ext4

Finally, hit the OK button at the bottom of the window to save your changes and then re-start SQLDeveloper.

Now, if we open the HR.COUNTRIES table, we can see that there’s now an additional tab called Child Tables.
When we go to this tab, we are rewarded with :

sqld_tab1

This stuff has been done before. As well as this post from Tony Andrews, I also posted something similar several years ago.
I must be getting old, I’m starting to repeat myself !

Whilst all this is quite handy, wouldn’t it be good to be to be able to link directly to the Child table ?

Linking to other objects

Jeff Smith’s post here might be just what we’re looking for.

Using Jeff’s linking technique, we can improve our extension a bit…

<items>
	<item type="editor" node="TableNode" vertical="true">
		<title><![CDATA[Child Tables]]></title>
		<query>
			<sql>
				<![CDATA[
					select owner,  
						'SQLDEV:LINK:'
						    ||owner||':TABLE:'||table_name
						    ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' table_name,
						constraint_name
					from all_constraints 
					where constraint_type = 'R' 
					and (r_owner, r_constraint_name) in
					(
						select owner, constraint_name 
						from all_constraints 
						where constraint_type in ('U', 'P') 
						and owner = :OBJECT_OWNER 
						and table_name = :OBJECT_NAME 
					) 
					order by owner, table_name, constraint_name 
				]]>
			</sql>
		</query>
	</item>
</items>

Now when I run this, the table_name appears in blue.

sqld_ext_final1

By clicking it, I can navigate directly to the child table…

sqld_ext_final2

When the code is run, in this example, the string generated for the table_name column is :

SQLDEV:LINK:HR:TABLE:LOCATIONS:oracle.dbtools.raptor.controls.grid.DefaultDrillLink

You can link to all sorts of things using this technique. In fact, it would seem to be that you can link to any object that has a node in the SQLDeveloper Navigator Tree. If you want to play around, just select the string from dual in SQLDeveloper (using F9 to execute).

For example :

select 'SQLDEV:LINK:HR:TRIGGER:UPDATE_JOB_HISTORY:oracle.dbtools.raptor.controls.grid.DefaultDrillLink'
from dual;

…returns a link to the UPDATE_JOB_HISTORY trigger in the results grid.
Note that the link syntax is case sensitive in that the owner, object type and object name all need to be in uppercase for this to work.

Another point worth noting, specifying a type of PACKAGE takes you to the package header.
If you want to link to the body, then specify PACKAGE BODY.

Other SQLDeveloper Extension Types

There’s a whole bunch of stuff you can do to customise SQLDeveloper using nothing more than a bit of boilerplate xml.
In addition to Jeff’s blog, there is an Oracle Wiki which contains, among other things,

It’s definitely worth a look, especially if, like me, you need something to take your mind off the cricket.


Filed under: SQLDeveloper Tagged: adding tabs to sqldeveloper, all_constraints, finding child constraints, linking to tabs in sqldeveloper, oracle.dbtools.raptor.controls.grid.DefaultDrillLink, sqldeveloper xml extensions

Installing Oracle XE on CentOS

Sun, 2015-03-01 11:33

Another Cricket World Cup is underway. England are fulfilling their traditional role of making all of the other teams look like world beaters.
To take my mind off this excruciating spectacle, I’ll concentrate this week on installing Oracle XE 11g on CentOS 7.

Before I get into the nuts and bolts of the installation…

Flavours of Linux

Whilst there are many Linux Distros out there, they all share the same common Linux Kernel. Within this there are a few Distros upon which most others are based.
Debian provides the basis for Ubuntu and Mint among others.
It uses the .deb package format.

Red Hat Linux in contrast uses the RPM file format for it’s packages. Red Hat is the basis for Distros such as Fedora, CentOS…and Oracle Linux.

For this reason, the Oracle Express Edition Linux version is packaged using rpm.
Whilst it is possible to deploy it to a Debian based Distro – instructions for which are available here, deploying on CentOS is rather more straightforward.
More straightforward, but not entirely so, as we will discover shortly…

Getting Oracle Express Edition 11G

Open your web browser and head over the the Oracle Express Edition download page.

You’ll need to register for an account if you don’t already have one but it is free.

The file you need to download is listed under :

Oracle Express Edition 11g Release 2 for Linux x64.

NOTE XE 11G only comes in the 64-bit variety for Linux. If you’re running a 32-bit version of your Distro, then you’re out of luck as far as 11G is concerned.

If you’re not sure whether you’re on 32-bit or 64-bit, the following command will help you :

uname -i

If this returns x86_64 then your OS is 64-bit.

Installing XE

You should now have downloaded the zipped rpm file which will look something like this :

cd $HOME/Downloads
ls -l
-rwxrwx---. 1 mike mike 315891481 Dec 16 20:21 oracle-xe-11.2.0-1.0.x86_64.rpm.zip

The next step is to uncompress…

 unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

When you run this, the output will look like this :

   creating: Disk1/
   creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql  
   creating: Disk1/response/
  inflating: Disk1/response/xe.rsp   
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm 

You now need to switch to the newly created Disk1 directory and become root

cd Disk1
su

…and then install the package…

rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

If all goes well you should see…

Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-xe-11.2.0-1.0             ################################# [100%]
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
Configuring XE

The configuration will be prompt you for

  1. the APEX http port (8080 by default)
  2. the database (TNS) listener port (1521 by default)
  3. A single password to be assigned to the database SYS and SYSTEM users
  4. whether you want the database to start automatically when the system starts (Yes by default)

Unless you have other software, or Oracle Instances, running elsewhere, the defaults should be fine.

Here we go then, still as root, run :

/etc/init.d/oracle-xe configure

The output, complete with the prompts will be something like :

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8081

Specify a port that will be used for the database listener [1521]:1525

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

Congratulations, you now have a running database. The first thing to do with it, however, is to shut it down.
In fact, we need to do a re-start so that the menu items that have been added as part of the installation are visible.
So, re-boot.

NOTE – from this point on you can stop being root (although you may need to sudo occasionally).

Once the system comes back, you will see the new Menu icons in the Applications menu under others :

oracle_menu

Just to confirm that your database is up and running, you can select the Run SQL Command Line option from this menu
and run the following :


conn system/pwd
select sysdate from dual
/

This should return the current date.

Sorting out the Environment Variables

In the normal run of things, this is the one fiddly bit. There is a bug in one of the scripts Oracle uses to set the environment variables which may cause issues.

To start with, let’s have a look at the main environment script…

cat /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

This script is as follows :

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export ORACLE_BASE=/u01/app/oracle
export PATH=$ORACLE_HOME/bin:$PATH

There is a bug in the nls_lang.sh that is called from here. If you’re NLS_LANG value contains a space, then it will not be configured correctly. A full list of the affected NLS_LANG values is available on the Oracle XE Installation Guide for Debian based systems I mentioned earlier.

The easiest way to fix this is to just edit the script :

sudo gedit /u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh

Right at the bottom of the script where it says :

# construct the NLS_LANG
#
NLS_LANG=${nlslang}.${charset}

echo $NLS_LANG

…amend it so that the $NLS_LANG value is quoted :

# construct the NLS_LANG
#
NLS_LANG="${nlslang}.${charset}"

echo $NLS_LANG

To test the change and make sure everything is now working properly…

cd /u01/app/oracle/product/11.2.0/xe/bin

. ./oracle_env.sh
echo $ORACLE_HOME
echo $ORACLE_SID
echo $NLS_LANG
echo $PATH

You should now see the following environment variable settings :

echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/xe
echo $ORACLE_SID
XE
echo $NLS_LANG
ENGLISH_UNITED KINGDOM.AL32UTF8
$PATH
/u01/app/oracle/product/11.2.0/xe/bin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/mike/.local/bin:/home/mike/bin

NOTE – the $NLS_LANG should have a setting appropriate for your system (in my case ENGLISH_UNITED KINGDOM.AL32UTF8).

The Oracle bin directory is now at the start of $PATH.

Next, we need to ensure that these environment variables are set for all sessions. This can be done by running …

sudo cp /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh /etc/profile.d/.

To check this, you can start a new terminal session and echo the environment variables to make sure they have been set.

Getting the Menu Items to Work

To do this, you simply need to make sure that the oracle user, as well as your own user, is a member of the dba group :

sudo usermod -a -G dba oracle
sudo usermod -a -G dba mike

To check :

sudo grep dba /etc/group
dba:x:1001:oracle,mike
$

The menu items for starting up and shutting down the database etc. should now work.

Enabling the Getting Started Desktop Icon

The final touch. The installation creates a Getting Started icon on the desktop which is designed to open the Database Home Page of the APEX application that comes with XE.

In order to make it work as desired, you simply need to right-click the icon and select Properties.
In the Permissions Tab check the box to “Allow executing file as program”.
Close the window.

You will notice that the icon has transformed into the familiar Oracle beehive and is now called
Get Started With Oracle Database 11g Express Edition.

Clicking on it now will reward you with …

db_home

All-in-all then, this installation is reasonably painless when compared with doing the same thing on a Debian system.
I wish the same could be said of following the England Cricket Team.


Filed under: Linux, Oracle Tagged: CentOS, nls_lang.sh, Oracle 11g Express Edition, oracle_env.sh

SQL*Plus Terminator Torture

Thu, 2015-02-26 05:42

“Leave that jar of Nutella alone, it’s got my name on it !”
The context in which Deb issued this injunction to me probably requires some explanation.
It was Friday evening.
Wales had just…well…come second in the latest installment of their eternal battle with the English through the medium of Rugby.
There was no alcohol left in the house.
And only one source of chocolate.
From the safety of the Cupboard under the stairs, to which I had retreated at kick-off – the Welsh do take their Rugby quite seriously – I wondered about my better half’s change of name.
Shorn of it’s chocolate hazelnut spread connotations, you might think that Nutella was quite an nice name for a girl.
It certainly seems appropriate if the “Girl” in question is slightly unhinged by a combination of wine and wounded national pride.

I was going to write something here about how Rugby players all look like the Terminator and use this as a way of introducting the topic at hand. However, I realise that this would simply be too contrived…even for me.
Instead, I’ll jump straight in…

The Nature of SQL*Plus

SQL*Plus is the command line interface for the Oracle RDBMS. As such, it supports three categories of statement :

  • SQL*Plus commands – which require no terminator
  • SQL commands – terminated by a “/” on a new line
  • PL/SQL blocks – containing statements that are terminated by “;”

…but that’s not the whole story as we are about to discover….

select 'Hasta La Vista' from dual
/

set serveroutput on size unlimited

begin
    dbms_output.put_line(q'[Ill be back...from PL/SQL]');
end;
/

select 'Just you wait until the World Cup!' 
from dual;

Here we can see an example of all three statement types – SQL, SQL*Plus and PL/SQL.
However the final SQL statement is a bit different. The terminator is not the newline “/” as with the first statement, but a “;”.

It is this small quirk that can cause some interesting things to happen and leave you scratching your head for a good while until you figure out exactly what’s happened.

Spot the Terminator

Consider the following, run as an unattended batch job :

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/
    
create table tenuous_links( arnie_quote varchar2(100));
/

Simple enough – drop the table ( ignoring the ORA-00942 error if it doesn’t exist in the first place), then create it again…


TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:12:20


PL/SQL procedure successfully completed.


Table created.

create table tenuous_links( arnie_quote varchar2(100))
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> 

At first glance, it would seem that our PL/SQL block to drop the table didn’t work for some reason.
However, if we dig a bit deeper…

select to_char(created, 'DD-MON-YYYY HH24:MI:SS')
from user_objects
where object_name = 'TENUOUS_LINKS'
and object_type = 'TABLE'
/

TO_CHAR(CREATED,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:12:20

So, the table was created at the time we ran our script. We know it still exists (and is not in the recycle bin) because it’s still in USER_OBJECTS.

If the table was dropped then re-created when we ran our script then why did we get the error ?

Let’s have a look at that CREATE TABLE statement again :

create table tenuous_links( arnie_quote varchar2(100));
/

Notice that “;” at the end of the first line ?
SQL*Plus takes this to mean “execute the last statement in the buffer”.
Then on the next line we have the same directive – expressed using a different syntax – i.e. “/”.

If we correct the script by removing the extraneous “;” all now works as expected :


select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/
    
create table tenuous_links( arnie_quote varchar2(100))
/

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:30:48


PL/SQL procedure successfully completed.


Table created.

Whilst debugging a small script like this is relatively straight forward, you’re likely to be confronted with a much longer script in the real world and spend considerably more time tracking down the problem.

Culling the Terminators

At this point we have a number of questions :

  1. How and why does SQL*Plus allow two terminator characters ?
  2. Can we turn one of them off ?
  3. Can we standardise on one of them and if so, which ?

It’s probably worth taking a closer look at the two terminator characters as they behave slightly differently in
certain circumstances. For example…

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back!]');
end;
/

Function created.

However, we get a bit less success if we try :

create or replace function arnie
    return varchar2
as
    begin
        return(q'[I'll be back!]');
end;;
SQL> 
SQL> 
SQL> 

SQL*Plus doesn’t recognise the second “;” as a terminator. Putting it on a separate line fares no better.
From this then, we can infer that the “/” is mandatory when generating DDL for PL/SQL stored program units.

The next question is how we end up with two terminators, and whether we can switch one of them off.
Well, let’s take a look at the SQL*Plus settings, or one setting in particular :

SQL> show sqlterminator
sqlterminator ";" (hex 3b)
SQL> 

We can use this parameter to set the terminator to another single character…


SQL> set sqlterminator !

SQL> select 'Another Terminator reference'
  2  from dual
  3  !

'ANOTHERTERMINATORREFERENCE'
----------------------------
Another Terminator reference

SQL> 

SQL> select 'Another Terminator reference' from dual !

'ANOTHERTERMINATORREFERENCE'
----------------------------
Another Terminator reference

SQL> 

SQL> select 'Another Terminator reference!' from dual !

'ANOTHERTERMINATORREFERENCE!'
-----------------------------
Another Terminator reference!

SQL> 

…but not multiple characters…

SQL> set sqlterminator runbuffer
string "runbuffer" is too long. maximum size is 1 character.
SQL> 

Interestingly, we can also disable it altogether and then re-enable it…

SQL> set sqlterminator off
SQL> show sqlterminator
sqlterminator OFF
SQL> select sysdate
  2  from dual;
  3  
SQL> 
SQL> select sysdate
  2  from dual
  3  /

SYSDATE
------------------
20-FEB-15

SQL> set sqlterminator ;
SQL> select sysdate
  2  from dual;

SYSDATE
------------------
20-FEB-15

SQL> 

Whilst this can be quite useful, especially if you’re running scripts that contain only DDL for stored program units, it does feel a bit odd having to put the terminator on a new line.
Additionally, you may consider that standardising this would require some serious regression testing of any SQL scripts to make sure that they’re not using the “;” terminator, not to mention any scripts that get generated dynamically.

Missing Terminators

Just as vexing as having too many terminators is not having enough.

consider :

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1	 PLS-00103: Encountered the symbol "CREATE"

SQL> select object_type          
  2  from user_objects
  3  where object_name = 'THE_TERMINATOR'
  4  /

OBJECT_TYPE
-------------------
PACKAGE

SQL> 

Now consider this in a longer script ( which I’ve instrumented with prompt statements) :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/

prompt Creating table TENUOUS_LINKS
    
create table tenuous_links( arnie_quote varchar2(100))
/


prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back !]');
end;

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;
/

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

This time, we’ve missed the terminator at the end of the function.
However, this causes the Package to error as well :

Recording Start Time

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 14:11:39

Dropping TENUOUS_LINKS

PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.


Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its
	 specification

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid
SQL> select object_name, object_type
  2  from user_objects
  3  where object_name in ('ARNIE', 'THE_TERMINATOR')
  4  /

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -------------------
ARNIE			       FUNCTION
THE_TERMINATOR		       PACKAGE
THE_TERMINATOR		       PACKAGE BODY

SQL> 

There are a couple of things to note here. First is that it may well be worth enhancing the instrumentation in the script by including SHOW ERRORS after each stored program unit creation statement. The second is that there is no “smoking gun” error for either too many terminators, or not enough.
With SHOW ERRORS in place, it becomes a bit easier to spot what’s going wrong :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/

prompt Creating table TENUOUS_LINKS
    
create table tenuous_links( arnie_quote varchar2(100))
/


prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back !]');
end;

show error

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;
/

show error


create or replace package body the_terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

show error

When you run this script, the problem is a bit easier to spot :

Recording Start Time

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 14:17:03

Dropping TENUOUS_LINKS

PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.

Errors for FUNCTION ARNIE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1	 PLS-00103: Encountered the symbol "SHOW"

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its
	 specification

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid
SQL> 

There is another SQL*Plus setting that you might consider to at least reduce some of the pain caused by this tangle of terminators.
Remember, the terminator is a directive for SQL*Plus to run what’s in it’s buffer. By default this is the last SQL or PL/SQL statement. SQL*Plus commands are not held in the buffer.
The CLEAR BUFFER command will, well, clear the contents of the buffer…

SQL> select 'I really must watch the movies again'
  2  from dual
  3  /

'IREALLYMUSTWATCHTHEMOVIESAGAIN'
------------------------------------
I really must watch the movies again

SQL> list
  1  select 'I really must watch the movies again'
  2* from dual
SQL> clear buffer
buffer cleared
SQL> list
SP2-0223: No lines in SQL buffer.
SQL> 

So, run a statement and we can then see it’s in the buffer by issuing the list command ( usually abbreviated to “l”).
After the CLEAR BUFFER, there’s nothing to see.

Conclusion

SQL*Plus facilitates the use of two terminator characters which behave in the same way…most of the time.
Whilst it’s possible to disable, or even change the “;”, it may not be practical to go forward using just the “/” as a standard.

Deb has just “asked” me to add a disclaimer to this post.
A certain amount of artistic licence was used in the first couple of paragraphs.
After all, there’s so much junk in the cupboard under the stairs that I’d never fit in.
The Shed, Deb assures me, is an entirely different matter.


Filed under: Oracle, SQL Tagged: clear buffer, prompt, set sqlterminator, show errors, SQL*Plus, statement terminators

Installing and Configuring an Oracle Developer Day VirtualBox Image

Sun, 2015-01-25 15:28

It’s winter. I can tell. First, it’s still dark. Secondly it’s bitterly cold.
Standing on the platform at Milton Keynes Central, it would appear that we now have further evidence that being late is a train driver’s prerogative.
Hiking up the season ticket price is obviously the prerogative of train operators.
The last time I spent this much money to feel this cold, I found myself standing on the top of a mountain in the South Island of New Zealand.

Unlike the “Great” British Railway, installation and configuration of an Oracle Developer Day Appliance is somewhat simpler, not to mention more reliable.

What I’m going to cover here is the installation of a Developer Day Appliance in Virtual Box as well as some settings you might want to tweak to gain access to the Appliance Database from the Host OS.

Environment, Pre-Requisites and terminology

As always, when we’re talking about virtualization, there are going to be mentions of :

  • The Host – the machine and Operating System on which the virtualization software (in this case VirtualBox) is running
  • The Guest – the system that’s running inside the Virtual Machine

These instructions should work, irrespective of the Host OS we’re using.
In my case, I’m using Mint 17 as my Host with VirtualBox 4.3.20 installed.

If you need installation instructions for VirtualBox, you can find them here for Debian based systems or here for Windows.

Alternatively, you can check the fairly comprehensive documentation on the VirtualBox website.

As for the Developer Day Image itself, I’m going to be using the Database Application Development VM.
The system requirements for this are :

  • 2GB RAM for the Guest OS
  • 22GB of disk space (15 GB for the Image plus 5.6 GB for the download file)

One other point to note – the latest incarnation of the Database Application Development VM has port-forwarding for port 1521 enabled by default.
This is the port for the TNS listener on the database in the Image. If you already have an Oracle instance running on your host, you’ll probably want to ensure that either, it’s not using 1521 for TNS connections, or that you disable the port forwarding on the Developer Day Image once you’ve installed it.
We’ll come back to this port-forwarding malarkey in a bit.

Getting the software

Open your browser and head over to Oracle’s Pre-Build Developer VMs page.

This is part of Oracle’s Technet site and you will need a Technet account to download the software, if you don’t already have one. However, registration is free and relatively painless.

Have a look through the available images and select the one that contains the stuff your interested in.
In my case, I want to have a play around with the 12c Database, hence the choice of the Database Application Development VM.

NOTE : this page itself may be a bit out-of-date in terms of what each VM contains, so click the Downloads and Instructions link next to the VM you may be interested in. This will give you a more accurate inventory of what software the VM contains.

Anyway, once you’ve selected your VM, simply hit the Download icon, enter your Technet credentials and – depending on your connection speed – find something else to do for a bit.
Eventually, you will have a file in your Downloads directory called :

Oracle_DB_Developer_VM_new.ova

In case you were wondering, .ova files are Open VirtualBox Format. Creating a VM with this type of file is slightly different to setting one up for a .iso …

Setting up the VM

Start the Oracle VM VirtualBox Manager on your machine and from the menu, select File/Import Appliance

appliance1

Appliance to Import is the file you’ve just downloaded :

appliance2

Appliance Settings are all already completed so just click Import :

…and agree to the License

appliance4

You’ll then see a progress bar….

appliance5

And finally, you’ll see the new VM listed in VirtualBox Manager.

Now, let’s power it up and see what happens….

Once you see the desktop, click the big armoured penguin icon…

vm_start

…and wait…

your patience will be rewarded with the introduction page to the hands-on sessions included in the image :

welcome_page

We can check that the database is up using the copy of SQLDeveloper available in the VM ( a connection for SYSTEM is already there).

Alternatively, we could just open a Terminal and login via SQL*Plus as SYSTEM ( all of the passwords are initially set to oracle).

Actually, that’s probably a point worth reiterating :

All of the accounts that you are likely to want to access on this VM have their password set to “oracle”

Either way connecting as SYSTEM will establish that the database is up. A couple of further queries will confirm the version of the Database and of APEX…

select banner
from v$version
where banner LIKE 'Oracle Database%'
union
select comp_name||' Version '||version as banner
from dba_registry
where comp_id = 'APEX'
/

BANNER
--------------------------------------------------------------------------------
Oracle Application Express Version 4.2.5.00.08
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

… as well as the identity and current status of the database itself :

select db.name, ins.host_name,
    ins.status, ins.logins,
    db.log_mode 
FROM v$database db, v$instance ins
/

NAME	  HOST_NAME							   STATUS	LOGINS	   LOG_MODE
--------- ---------------------------------------------------------------- ------------ ---------- ------------
CDB1	  localhost.localdomain 					   OPEN 	ALLOWED    NOARCHIVELOG
Some Housekeeping Time and Date

The first thing you’ll probably notice is that the time and date is defaulted to US Pacific Standard time (I think).
If you want to change this :

Go to the Applicationsmenu and select Utilities/Settings/Time and Date,
Click the Unlock button and then set the time and date that’s appropriate for your location.
Finally, click the Lock button.

Region and Language (as well as Keyboard)

If, like me, you have trouble remembering where the US keyboard layout puts the “@” symbol (not to mention the “|”) then you’ll probably want to re-configure the Keyboard settings to your locality, along with the Region settings :

Go to Applications/System Tools/Settings/Regions & Language.

To change the region settings, Hit the “+” button under Input Sources and select the appropriate region.
To change the Keyboard itself, first highlight the current Input Source – i.e. English (US) – then click add to add the source you require.
Once this is done, Highlight the English (US) entry again and hit the “-” button to remove it.

For Region Changes to take effect, the system will need to be restarted. The keyboard change, however, should take effect immediately.

Setting the APEX user not to Expire

Given that these images were initially created for Developer Day events, it is perhaps not surprising that the expiration of the APEX_PUBLIC_USER password after 6 months was not a primary consideration.
If you want to avoid this little inconvenience, then the simplest way is to change the DEFAULT profile, to which all database accounts in the image are assigned :

alter profile default limit password_life_time unlimited;

NOTE: In a production environment you’d probably want to take a bit more care here. However, the Developer Day Images are purely for research.

Accessing the Database running on the VM from the Host Accessing the Database via TNS

As mentioned previously, port-forwarding of 1521 (The default SQL*Net port) is already pre-configured.
Therefore, assuming that you have an Oracle Client installed, you can connect to the database on the Guest (whilst it’s running) from the Host as follows :

sqlplus system/oracle@0.0.0.0:1521/cdb1

Once you have a SQL prompt, you can check to see that you now have access to Oracle in all it’s 12c splendour…

select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

For convenience, you can always add the following entries to $ORACLE_HOME/network/admin/tnsnames.ora on your host :

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
    )
  )
  
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
Accessing APEX

We can repeat the simple port-forwarding trick to make the APEX home page available on the Host as well.

In Virtual Box Manager, with the Developer Day VM highlighted, click Settings
Select Network
Click the Advanced arrow

network_advanced

Click the Port Forwarding button
Now Click the Add icon on the right-hand side of the window and enter the following values for each of the fields :

  • Name : http
  • Protocol : TCP
  • Host Port : 8080
  • Guest Port : 8080

Note : once again, you’ll probably want to change these values if you have a local instance of Oracle running on the Host.

Anyway, your rules should now look like this :

http

You can now hit the APEX main page from a browser in the host by entering :

http://localhost:8080/apex

apex_login

Note – the credentials for the ADMIN user for APEX are :

Workspace : INTERNAL
Username : ADMIN
Password : oracle

And there you have it, a quick and simple (not to mention, cheap) way to get Oracle 12c installed on your local machine. Now, if only they’d do the equivalent for getting from Milton Keynes to London.


Filed under: Oracle, VirtualBox Tagged: acessing apex, connecting to a guest database from the host, default passwords, Developer Day image, keyboard configuration, port forwarding, region settings, VirtualBox, VM

CREATE USER and ALTER USER – changing passwords and a New Year’s Resolution

Sun, 2015-01-11 08:39

Monday morning. The first day back at work in the New Year.
Still groggy, having been awoken from my slumber at the insistence of my Darth Vader Lego Alarm Clock, I stagger downstairs in search of coffee.
The clock was a Christmas present from Deb. Whilst clinking around the kitchen, I wonder whether it was intended as a subtle reminder of how she likes her coffee with only a little milk. Yes, she prefers it on the Dark Side.
Then I remember. I usually avoid New Year’s resolutions. I find them to be not unlike a cheap plastic toy at Christmas – there in the morning and then broken by the first afternoon.
This year however, is an exception.

In a recent post about APEX Authentication Schemes, I went to great lengths to ensure that a dynamic SQL statement to re-set a users password was safe from the possibility of injection.
Fortunately, Jeff Kemp took the time to point out a couple of issues with my approach.
As a result, this year, my resolution is to :
READ THE MANUAL.

What follows is the result of keeping this resolution ( so far, at least)…

Setting a Password with CREATE USER and ALTER USER

To start with, we’re going to forget about any PL/SQL context for this exercise and simply focus on the ALTER USER command when used to change a user’s password.

First of all, we need a user…

create user marvin identified by 42!
/

create user marvin identified by 42!
                                   *
ERROR at line 1:
ORA-00922: missing or invalid option


That’s a bit surprising. There’s no password verify function on the DEFAULT profile in the database :

select resource_name, limit
from dba_profiles
where resource_type = 'PASSWORD'
and resource_name = 'PASSWORD_VERIFY_FUNCTION'
and profile = 'DEFAULT'
/

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_VERIFY_FUNCTION         NULL

…and even if there were – ORA-00922 ?

At this point, much head-scratching can be saved if we follow Jeff’s recommended link to the relevant documentation.

From this we can see that the password has the same rules applied to it as a database object. So…

  • it must start with a letter
  • …and can’t contain spaces…
  • …or characters other than alphanumeric, $, # or _

…unless…

create user marvin identified by "42!"
/

User created.

Yes, double-quotes around the password will allow other characters to be used, just as with database objects.
So, does the user need to provide the double-quotes when they connect ?

grant create session to marvin
/

…now….

connect marvin/42!@mydb

…works fine. No need to include the double-quotes when connecting.
If the password contains spaces however, then the double-quotes are required :

alter user marvin identified by "Feeling very depressed"
/

conn marvin/Feeling very depressed@XE
/
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]

conn marvin/"Feeling very depressed"@XE
/
Connected.
A Sensible Change Password Procedure

We now come back to the question of how to provide the functionality for a user to change their password by means of a PL/SQL procedure. For the sake of simplicity, we’re going to assume that there is no password verify function in place and therefore the old password does not need to be provided :

create or replace procedure change_pwd_pr
(
    i_new_pwd in varchar2
)
    authid current_user
is
begin
    execute immediate 'alter user '||user||' identified by '||CHR(34)||i_new_pwd||CHR(34);
end;
/

Now, we are dropping user input directly into a dynamic SQL statement, something that rings alarm bells in terms of the potential for injection.
There would appear to be two techniques that are common to injection of such code.

Here, however, we’re only concatenating a password. This is not a database object per se. Once it is set, it will be referenced only as the password hash by Oracle’s internal authentication process and will therefore not be useful as a blind injection string.
Additionally, by enclosing the password in double-quotes, it is simply treated as a string within the dynamic SQL statement itself.

Let’s put this to the test.

First, we need to make the procedure available to MARVIN :

grant execute on change_pwd_pr to marvin;

Now let’s run some tests. Connect as MARVIN and …

set serveroutput on size unlimited
declare
    
    function test_pwd_fn( i_test_no in pls_integer, i_pwd in varchar2)
        return varchar2
    is
    begin
        mike.change_pwd_pr( i_new_pwd => i_pwd);
        return ('Test '||i_test_no||' - Password Changed.');
    exception
        when others then
            return('Test '||i_test_no||'- Password Change Failed : '||substr(sqlerrm,1,100));
    end test_pwd_fn;
begin
    dbms_output.put_line(test_pwd_fn( 1, '42!'));
    dbms_output.put_line(test_pwd_fn( 2, 'silly'||'; grant dba to marvin'));
    dbms_output.put_line(test_pwd_fn( 3, q'[I want you to know that I'm feeling very depressed]'));
    dbms_output.put_line(test_pwd_fn( 4, 'How awful!'));
end;
/    

Run this as Marvin and we get :

SQL> @tests.sql
Test 1 - Password Changed.
Test 2 - Password Changed.
Test 3- Password Change Failed : ORA-00972: identifier is too long
Test 4 - Password Changed.

PL/SQL procedure successfully completed.

SQL> 

If we now check, we can confirm that our attempt at injection in test2 was not successful :

select granted_role 
from user_role_privs
/

no rows selected

SQL> 
Conclusion

Thanks to Jeff, I will henceforth be moderating my paranoia when dealing with programatic password resets.
Oh yes, and I’ll try to remember to read the manual.


Filed under: Oracle, PL/SQL, SQL Tagged: alter user, alter user identified by, create user, create user identified by, dba_profiles, escape new password with double quotes, identified by, ORA-00922, ORA-00972, oracle password rules, password verify function