Skip navigation.

The Anti-Kyte

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

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

ANSI Joins and Uppercase Keywords – making PL/SQL look less like COBOL

Wed, 2014-07-30 06:12

The month-long festival of football has finally come to an end.
A tournament that was supposed to be about “No 10s” and the coronation of the host nation has lived up to expectations. Ironically, by defying them.

Where to start ? Well, it seems that goalkeepers had something to say about just who the star players were going to be.
Ochoa, Navas and Neuer were all outstanding, not to mention Tim Howard. I wonder if he could save me money on my car insurance ?
Those number 10s were also in evidence. However, in the end, it wasn’t Neymar, Messi, or even Mueller who shone brightest in the firmament. That honour belonged to one James Rodriguez, scorer of the best goal, winner of the Golden Boot, and inspiration to a thrilling Columbia side that were a bit unlucky to lose out to Brazil in a gripping Quarter Final.
Now, usually a World Cup finals will throw up the odd one-sided game. One of the smaller teams will end up on the wrong end of a good thrashing.
This tournament was no exception…apart from the fact that it was the holders, Spain, who were on the wrong-end of a 5-1 defeat by the Netherlands.
Then things got really surreal.
Brazil were taken apart by a team wearing a kit that bore more than a passing resemblence to the Queens Park Rangers away strip.
The popular terrace chant “It’s just like watching Brazil” may well require a re-think after Germany’s 7-1 win.
So, Germany (disguised as QPR) advanced to the final to play a side managed by a former Sheffield United winger.
Eventually, German style and attacking verve triumphed.
Through the course of the tournament, O Jogo Bonito seems to have metamorphosed into Das Schöne Spiel.
The stylish Germans are what provide the tenuous link to this post. I have once again been reviewing my SQL and PL/SQL coding style.
What follows is a review of some of the coding conventions I (and I’m sure, many others) have used since time immemorial with a view to presenting PL/SQL in all it’s glory – a mature, powerful, yet modern language rather than something that looks like a legacy from the pre-history of computing.

Hopefully, the changes discussed here will help my code to become more readable ( and therefore maintainable) as well as looking a bit nicer.
William Robertson (from whom I plaigarised the title for this post) has some interesting things to say on PL/SQL coding style.

In this post, I’ve attempted to focus on stylistic topics that may affect code readability rather than just well established practices that I find annoying. Believe me, it was a bit of a struggle.

What I will be looking at is :

  • Uppercase Keywords
  • The use of camel case
  • The vexed question of ANSI Join syntax

Inevitably, what follows is likely to be highly subjective so feel free to disagree vehemently.

Uppercase Keywords

I started using SQL back in 1993, when the world was black and white.
My first experiences with Oracle was coding on a terminal emulator using vi.
Sure, you could change the screen colour if you were so minded, but syntax highlighting for PL/SQL was the stuff of science fiction.
The accepted way of distinguishing keywords was therefore, to type them in upper case.

--
-- Old style...
-- Keywords and built in packages are in uppercase.
-- brackets in the cursor for loop are not aligned, but the identation is consistent
--
DECLARE
    l_message VARCHAR2(50) := 'Happy New Millenium!';
BEGIN
    FOR r_emps IN (  
        SELECT first_name,  
        FROM hr.employees) 
    LOOP
        DBMS_OUTPUT.PUT_LINE('Message for '||r_emps.first_name||' - '||l_message);
    END LOOP;
END;
/

Needless to say, things have changed a bit since then. Not only do you have multiple Oracle IDEs to choose from, all but the most basic text editors will have syntax highlighting as standard. Of course many of them will have built-in highlighting for SQL rather than PL/SQL, but many such as Textpad and Gedit can easily be customised to suit.

One additional tweak to the next code example is that I’ve aligned the brackets in the way that you’d expect to see in other 3GLs. Apart from making bracket matching a bit easier, I think it looks a bit nicer…

--
-- All keywords and built-in packages are lowercase.
-- Syntax highlighting means that the keywords are still distinguishable...
--
declare
    l_message varchar2(50) := 'Happy New Millenium!';
begin
    for r_emps in
    (
        select first_name
        from hr.employees
    ) 
    loop
        dbms_output.put_line('Message for '||r_emps.first_name||' - '||l_message);
    end loop;
end;
/

…OK, WordPress doesn’t really do PL/SQL, so I’ll try to illustrate with a screenshot from Gedit :

My code in glorious technicolour

My code in glorious technicolour

We could make this code look even more funky and modern if, for example, we were to consider using…

Camel Case

Camel Case has never really gained wide acceptance in Oracle programming circles. I would suggest that the main reason for this is that Oracle stores object names, column names etc in the data dictionary in UPPERCASE.
Therefore, if you were to create a table with the following script…

Create Table Regions
(
    regionId Number,
    regionName Varchar2(25),
    longRegionDescription Varchar2(4000)
)
/

You may well be a bit surprised by the result of the following query…

select column_name, data_type
from user_tab_columns
where table_name = 'Regions'
/

no rows selected

Yes, in order to find the column details we need to specify the table name in upper case…

select column_name, data_type
from user_tab_columns
where table_name = 'REGIONS'
/

COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------
REGIONID                       NUMBER
REGIONNAME                     VARCHAR2
LONGREGIONDESCRIPTION          VARCHAR2

Unless you want to go down the long and winding road of quoted identifiers, the underscore remains – in my opinion at least – the best way of separating words in identifiers.

Hang on a second, that’s probably fair enough as far as object names go, but what about variables ?

At this point, I have a confession to make. If I were to see something like this…

Declare
    lMessage varchar2(50) := 'Happy New Millenium!';
Begin
    For rEmps In
    (
        select first_name
        from hr.employees
    ) 
    Loop
        dbms_output.put_line('Message for '||rEmps.first_name||' - '||lMessage);
    End Loop;
End;
/

…my first reaction would be to assume that it was written by someone who normally codes in a language other than PL/SQL. Of course, this says rather more about me than the author of the code or, indeed, the code itself.

My justification for persisting with underscores in variable names can be reduced to two rather small fig leaves.

First of all, when declaring a variable for use to compare to a column value, I tend to keep to the name of the target column. For example :

create or replace function get_employee_name_fn
( 
    i_employee_id employees.employee_id%type
)
    return varchar2	
as
    l_last_name varchar2(100);
begin
    select last_name
    into l_last_name
    from employees
    where employee_id = i_employee_id;
    return l_last_name;
end;
/

The second is that, If I’m using underscores for some variables, it would look a bit odd if I then used Camel Case for others.

In an attempt to rid you of the unfortunate mental image you may have acquired with the fig leaves reference, I’ll move swiftly on to something that seems to ellicit strong opinions in the Oracle world…

ANSI Join syntax

In the vast majority of cases, if you’re writing PL/SQL then you will, at some point, need to write some SQL.

Back in Oracle 6, there was no Cost Based Optimizer. For the first several years of my using Oracle, tuning of SQL was done by understanding the 14 rules applied by the Rule Base Optimizer.
One key point was that a query was evaluated from the bottom up, so the ordering of the predicate was important.
For this reason, it became common practice to specify join conditions first, ahead of other, more restrictive conditions.
Of course, in those days, ANSI syntax was not available in Oracle, but it didn’t matter too much as you could be reasonably certain of where to look for join conditions in SQL. For example :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and dept.location_id = loc.location_id
and loc.country_id = coun.country_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and reg.region_name = 'Europe'
order by coun.country_name, dept.department_name
/

In the period of time between the Cost Based Optimizer becoming de rigeur and the bugs in the ANSI syntax being ironed out in Oracle, a less structured coding style seems to have become prevalant.
Join conditions can now appear anywhere in the predicate list without any impact on performance…

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and reg.region_name = 'Europe'
and dept.location_id = loc.location_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and loc.country_id = coun.country_id
order by coun.country_name, dept.department_name
/
Inner Joins – ANSI Style

The ANSI syntax offers a solution to this by enforcing the separation of join conditions into a discreet clause in the query :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept
join employees emp
    on dept.manager_id = emp.employee_id
join locations loc
    on dept.location_id = loc.location_id
join countries coun
    on loc.country_id = coun.country_id
join regions reg
    on coun.region_id = reg.region_id
where reg.region_name = 'Europe'
and coun.country_name != 'Switzerland'
order by coun.country_name, dept.department_name
/

In terms of readability, unless you’re an old fogey like me, this would seem to be a significant improvement.

The ANSI syntax offers several variations on the theme of Inner Join. You can explicitly use the INNER keyword with your inner joins, although this would seem to be a bit superfluous as other join types would have to be stated explicitly in the code.

As well as the tradional…

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    on dept.department_id = emp.department_id
/

…you could specify a join between tables that have the same column name like this :

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    using(department_id)
/

Slightly less appealing is the NATURAL JOIN syntax, which assumes that you want to join on any and all matching column names between two tables.
If you run either of the previous two queries in the HR schema, they will return 106 rows.
However, the following query returns only 32 rows…

select dept.department_name, emp.last_name
from departments dept
natural join employees emp
/

As well as DEPARTMENT_ID, the tables also both contain a MANAGER_ID column. The above statement is therefore not, as you might think, the equivalent of the previous two, but is rather more …

select dept.department_name, emp.last_name
from departments dept
join employees emp
    using( department_id, manager_id)
/

With this in mind, I think I’ll steer clear of having my joins au naturel.

Outer Joins

This is one area where the ANSI syntax has a distinct advantage over it’s more venerable Oracle counterpart.
I’ve previously explored how it allows you to outer join a table multiple times in the same query.

Apart from this, it’s main distinguishing feature over the more traditional Oracle syntax is it’s sense of direction.

For a left outer join, tables are read left-to-right – i.e. the table you’re outer joining to is specified second :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept
left outer join employees emp
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

For a right outer join, the reverse is true, the table specified first is the one you’re outer joining to :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from employees emp
right outer join departments dept
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

Whilst having to know your left from right is something new to contend with, either of the above examples is more readable to someone familiar with SQL ( although not necessarily Oracle) than :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept, employees emp
where dept.department_id = emp.department_id(+)
group by dept.department_name
order by 2 desc, dept.department_name
/

In the past, there have been one or two issues with Oracle’s implementation of the ANSI join syntax. However, it now seems to be fairly stable and consistent.
Add this to it’s undoubted advantage in terms of readability and I’ve really rather run out of excuses not to use it.


Filed under: Oracle, PL/SQL, SQL Tagged: ansi join syntax, camel case, inner join, join using, LEFT OUTER JOIN, natural join, right outer join, uppercase keywords

DBMS_DATAPUMP – why quote delimiters are your friend

Mon, 2014-06-30 05:06

So, the World Cup is in full swing.
Now the lesser teams have fallen by the wayside ( England), we can get on with enjoying a feast of footie.
As well as a glut of goals, the current tournament has given us a salutory reminder of the importance of diet for elite athletes.
After predictably (and brilliantly) destroying England single-handedly, Luis Suaraz found himself a bit peckish and nipped out for an Italian. Now the whole world seems to be commenting on his eating habits.
Like Luis, you may find yourself thinking that you’ve bitten off more than you can chew when confronted by DBMS_DATAPUMP.

The documentation does offer some help…to an extent. However, the whole thing can seem a bit fiddly, especially if you’re used to the more traditional command-line interface for Datapump.

What follows is a tour through DBMS_DATAPUMP based on my own (sometimes painful) experience, broken down into bite-sized chunks.
Much of the functionality to filter object types and even data is common to both Exports and Imports.
So, the approach I’ve taken is to cover the Export first, with a view to finally producing a Full Database export.
I’ve then used the Import process against this to demonstrate some of the package’s filtering capabilities.
So, what’s on the menu today ?

  • Privileges required to run a DBMS_DATAPUMP job from your current schema and for the whole database
  • Running a consistent export
  • Running datapump jobs in the background
  • Monitoring running jobs
  • Importing from one schema to another
  • Specifying the types of objects to include in Exports and Imports
  • Specifying subsets of data
  • DDL only Jobs
  • How to Kill a Datapump Job

The full code examples have all been written and tested on Oracle XE 11gR2.
I’ve tried to maximise the use of in-line hard-coded values and minimise the number of variables in an attempt to make the code easier to follow.
Also, in these examples I’ve made use of the default DATA_PUMP_DIR directory object, but you can use any directory object to which you have the appropriate privileges.

For dessert, there are a couple of other DBMS_DATAPUMP features that I have found useful that are specific to Enterprise Edition ( in one case, with the Partitioning Option) ;

  • Including specific table partitions
  • Parallel processing

So, a fair bit to get through then. I hope you have an apetite…

Privileges required for using DBMS_DATAPUMP

Obviously, the first thing you need is execute privileges on DBMS_DATAPUMP. By default, this is granted to PUBLIC, although the more security conscious DBAs will have instituted more rigorous controls over just who gets access to this package.
The other thing you’ll need is READ and WRITE privileges on a database Directory Object.

DATA_PUMP_DIR

The DATA_PUMP_DIR directory object is created by default (but any old Directory Object will do)…

select directory_path
from dba_directories
where directory_name = 'DATA_PUMP_DIR'
/
DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/admin/XE/dpdump/

The HR user, for example, would need to be granted…

grant read, write on data_pump_dir to hr
/

That’s pretty-much it. With just these privileges( along with those for creating/altering the relevant object types), you can run DBMS_DATAPUMP to export/import objects and data in your current schema.

Once again, assuming we’re connected as HR :

set serveroutput on size unlimited
declare
--
-- Just export the current schema (HR)
--
    l_dph number; -- The datapump job handle
    l_status varchar2(4000); -- terminating status of the job
    
begin
    -- create the job...
    l_dph := dbms_datapump.open
    (
        operation => 'EXPORT',
        job_mode => 'SCHEMA', -- schema level export will use the current user by default
        job_name => 'HR_SCHEMA_EXPORT' -- appropriate job name
    );
    -- Specify the name and location of the export dump file we want to create...
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_export.dmp',
        directory => 'DATA_PUMP_DIR', -- can use any database directory object
        filetype => dbms_datapump.ku$_file_type_dump_file,
        reusefile => 1 -- if this file already exists, overwrite it
    );
    -- ...and a log file to track the progress of the export
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_export.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- Kick off the export
    dbms_datapump.start_job( handle => l_dph);

    -- ...and wait for the job to complete
    dbms_datapump.wait_for_job( handle => l_dph, job_state => l_status);

    dbms_output.put_line('Job done - status '||l_status);
end;
/

It’s probably worth noting that dbms_datapump jobs contain four main components as a rule.
First DBMS_DATAPUMP.OPEN creates a kind of container to hold the definition of the job.
Next, you specify any particular requirements for the job. This must always include reference to a dump file.
Then, use DBMS_DATAPUMP.START_JOB to kick-off the job you’ve defined.
Finally, you can either wait for the job to finish (as in this case), or leave it to run in the background. We’ll come onto background execution in a bit.

Roles required for Full Export/Import

If you want the God-Like powers to export/import the entire database then you will need the roles :

  • DATAPUMP_EXP_FULL_DATABASE
  • DATAPUMP_IMP_FULL_DATABASE

These roles are granted to the DBA role and the SYS user by default.

Incidentally, the fact that roles play quite a significant part in DBMS_DATAPUMP priviliges means that, if you do intend to wrap this functionality in a package, it would probably be wise to create it with Invoker Rights – e.g.

create or replace package my_datapump_package
    authid current_user as
...

Now that’s all sorted…

set serveroutput on size unlimited
declare
--
-- Do a no-nonsense full export 
--
    l_dph number; 
    l_status varchar2(4000); 
    
begin
    l_dph := dbms_datapump.open
    (
        operation => 'EXPORT',
        job_mode => 'FULL',
        job_name => 'MIKE_FULL_EXPORT' 
    );
    -- Specify the dump file we're going to create
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.dmp',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file,
        reusefile => 1
    );
    -- ... and a log file
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- Now press the big red shiny button...
    dbms_datapump.start_job( handle => l_dph);
    -- wait around for a bit...
    dbms_datapump.wait_for_job( handle => l_dph, job_state => l_status);
    dbms_output.put_line('Job done - status '||l_status);
end;
/

Pretty much the same as with the schema export we’ve already done. However, this time, we’ve specified the job_mode as FULL rather than SCHEMA.

With this script saved as full_exp_simple.sql…

SQL> @full_exp_simple.sql
Job done - status COMPLETED

PL/SQL procedure successfully completed.

SQL> 

Depending on the size of your database ( and the capacity of the server you’re running on), this may take a while.
When it’s all finished, you can wander over to the DATA_PUMP_DIR on the OS and you’ll see the fruit of your labours…

cd /u01/app/oracle/admin/XE/dpdump
ls -l my_full_export.*

-rw-r----- 1 oracle dba 321515520 Jun 18 19:55 my_full_export.dmp
-rw-r--r-- 1 oracle dba     84957 Jun 18 19:55 my_full_export.log

If we look at the logfile, we can see it starts like this :

Starting "MIKE"."MIKE_FULL_EXPORT":
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 349.9 MB
Processing object type DATABASE_EXPORT/TABLESPACE
...

… and ends with …

. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "MIKE"."MIKE_FULL_EXPORT" successfully loaded/unloaded
******************************************************************************
Dump file set for MIKE.MIKE_FULL_EXPORT is:
  /u01/app/oracle/admin/XE/dpdump/my_full_export.dmp
Job "MIKE"."MIKE_FULL_EXPORT" successfully completed at 19:55:33

We now have an export. If, like me, you’ve just run this from a database where you know that there are no other active sessions, then you should have a nice consistent export. Unfortunately, this isn’t always the case…

Making your Export Read Consistent…and other tweaks

To adress this issue of consistency, we’ll need to change our script a bit. Whilst we’re at it, it would also be nice to have some record in the logfile of when the job started so we can work out how long it actually ran for.
Finally, we’d like to be able to run the export in the background so we don’t have to hang around waiting for it.

Consistent = Y – the DATAPUMP equivalent

Those of you old enough to remember the original export utility will recall that you could ensure that data in an export was referrentially consistent by the simple expedient of specifying consistent = Y in the exp command.
The equivalent in DBMS_DATAPUMP is to specify a value for FLASBACK_SCN.

NOTE – in order for this to work, your database must be running in ARCHIVELOG mode. This is especially relevant if you’re playing along on XE, which runs in NOARCHIVELOG by default.

To check the current status of archiving on the database :

select log_mode
from v$database
/

LOG_MODE
------------
ARCHIVELOG

If the query comes back NOARCHIVELOG then you need to enable archiving.
To do this, you need to connect as SYSDBA, shutdown and then mount the database, before starting archiving and then re-opening the database.
These steps can be achieved as follows once connected to the database as sysdba :

shutdown immediate;
startup mount;
alter database archivelog
/
alter database open
/

NOTE – these steps are intended if you are messing around in your own database ( e.g. XE on a laptop, like I am here). If you’re on a controlled environment, then you need to get your friendly neighbourhood DBA to do the honours.

Assuming the database is now in archivelog mode, the next thing we need to do is find SCN that we can pass to DBMS_DATAPUMP for it to use as a reference point for what we mean by consistent.
The SCN – System Change Number – is incremented every time a transaction completes in the database.
A quick demonstration is probably in order….

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774525

SQL> create table snacks( snack_name varchar2(30), description varchar2(4000));

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774562

SQL> insert into snacks( snack_name, description)
  2  values( 'CHIELLINI', 'Lite bite')
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774581

SQL> drop table snacks purge;

Table dropped.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774608

SQL> 

The fact that the SCN increments by more than one after each transaction completes in this session is explained by the transactions being run by the oracle background processes on my database.

Anyway, if we do want to find the current SCN, or even an SCN for a given time and date, we can simply use the
SQL TIMESTAMP_TO_SCN function :

select timestamp_to_scn( systimestamp)
from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP)
------------------------------
                       2647783

SQL> 

In order to pass this information to DBMS_DATAPUMP, we need to use the SET_PARAMETERS procedure.
This should do the trick :

...
dbms_datapump.set_parameter
(
    handle => the_datapump_job_handle,
    name => 'FLASHBACK_SCN',
    value => timestamp_to_scn( systimestamp)
);
...
Adding a message to the logfile

Next on our to-do list to improve our export is a message in the logfile recording when the export job started.
Oh look, there’s a package member called LOG_ENTRY. I wonder if that will help …

...
dbms_datapump.log_entry
(
    handle => the_datapump_job_handle,
    message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
);
...

Incidentally, we could include the SCN we’re specifying in the message as well ( although it would need to be captured in a variable before use in both the SET_PARAMETER call above and the LOG_ENTRY call).
One point to note – any entry in the logfile that results from a call to this procedure always starts with “;;;”.

Running the job in the background

Just in case you don’t fancy the idea of hanging around waiting for the job to finish, you can replace the call to WAIT_FOR_JOB with this…

...
dbms_datapump.detach( handle => the_datapump_job_handle);
...
The New, Improved Datapump Export Script

If we now apply all of these changes to our original script, it should look something like ….

set serveroutput on size unlimited
declare

    l_dph number; 
    l_scn number; -- The SCN from which the export will run    
begin
    -- setup the job
    l_dph := dbms_datapump.open
    (
        operation => 'EXPORT',
        job_mode => 'FULL',
        job_name => 'MIKE_FULL_EXPORT'
    );
    
    -- define the files
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.dmp',
        directory => 'DATA_PUMP_DIR', 
        filetype => dbms_datapump.ku$_file_type_dump_file,
        reusefile => 1 
    );
    
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    
    --
    -- Specify the SCN number to make sure that the export
    -- is a consistent copy
    --
    l_scn := timestamp_to_scn(systimestamp);
    dbms_datapump.set_parameter
    (
        handle => l_dph,
        name => 'FLASHBACK_SCN',
        value => l_scn
    );
        
    -- log the start time of the job
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')||' for SCN '||l_scn
    );
    
    -- start the job
    dbms_datapump.start_job( handle => l_dph);
   
    -- ...and leave it to run
    dbms_datapump.detach( handle => l_dph);
end;
/
Monitoring the job

The USER_DATAPUMP_JOBS view (there is also a DBA_ version available) contains details of any currently defined datapump jobs.

SQL> select job_name, state, attached_sessions
  2  from user_datapump_jobs;

JOB_NAME                       STATE                          ATTACHED_SESSIONS
------------------------------ ------------------------------ -----------------
MIKE_FULL_EXPORT               EXECUTING                                      1

SQL> 

Additionally, during the export, DATAPUMP will create a temporary table with the same name as the job.
In this table, you can see which object (if any) it’s currently working on…

select object_schema, object_name,
    work_item, 
    to_char(last_update, 'hh24:mi:ss') last_updated
from mike_full_export
where state = 'EXECUTING'
and object_schema is not null
and object_name is not null
/

There is an example of how to get “real-time” updates in the Oracle Documentation.
Unfortunately, it relies on DBMS_OUTPUT so the messages don’t get echoed to the screen until after the job is completed.

The simplest way to find out what’s happening right now is to check the logfile.
On Linux, for example, you could simply do this :

cd /u01/app/oracle/admin/XE/dpdump
tail -f my_full_export.log

If you haven’t got command line access to the database server, or simply prefer to keep everything in the database, then you could just create an external table based on the logfile. After all, you already have the required privileges on the directory …

create table datapump_log_xt
(
    line number,
    text varchar2(4000)
)
    organization external
    (
        type oracle_loader
        default directory data_pump_dir
        access parameters
        (
            records delimited by newline
            nologfile
            fields terminated by whitespace
            (
                line recnum,
                text position(1:4000)
            )
        )
        location('')
    )
    reject limit unlimited
/

If we want to check progress, we can simply “tail” the file like this :

alter table datapump_log_xt location ('my_full_export.log')
/

select text
from datapump_log_xt
where line > ( select max(line) - 10 from datapump_log_xt)
order by line;

TEXT
--------------------------------------------------------------------------------
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

10 rows selected.

SQL> 

So far, we’ve looked exclusively at Exporting everything either from a given schema or an entire database.
Datapump also allows you to filter the data. Generally speaking, these filters apply whether you are importing or exporting.

Importing data from one schema to another

One of the many reasons that you may want to take a DataPump export may be to refresh a development environment.
It’s possible that the schema you’re refreshing on the Development database has a different name from the one on your Production environment. Let’s say, for the sake of argument, that we have a HR_DEV user in our development environment…

create user hr_dev identified by pwd
/

grant connect, resource, unlimited tablespace,
    create view, create sequence, create session,
    alter session, create synonym, create table, 
    create procedure
    to hr_dev
/

alter user hr_dev default tablespace users temporary tablespace temp
/

Fortunately, importing the HR objects in the export file into the HR_DEV user is fairly simple :

...
dbms_datapump.metadata_remap
(
    handle => the_datapump_job_handle,
    name => 'REMAP_SCHEMA'
    old_value => 'HR'
    value => 'HR_DEV'
);
...
Specifying schemas and object types

At this point, let’s say that we only want to import the HR schema. The rest of the objects in the export file aren’t really relevant to us.
Furthermore, let’s assume we only want to import Tables and Sequences as we’ll re-create all of our PL/SQL stored program units, views etc from source code files.

Filtering by schema

Allow me to introduce the METADATA_FILTER procedure :

...
dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'SCHEMA_EXPR',
    value => q'[= 'HR']'
);
...

Once again, not as painful as it might appear. However, it’s here where we begin to see the benefits of quote delimiters.

Filtering by object types

Here’s where things get a bit more interesting.
Remember the export logfile. At the beginning, there were a number of entries like :

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
...

These paths are the basis for how datapump determines what to export.
Fortunately they are stored in some publicly available views :

  • DATABASE_EXPORT_OBJECTS
  • SCHEMA_EXPORT_OBJECTS
  • TABLE_EXPORT_OBJECTS

We’re doing a SCHEMA import so we can check to see that the relevant object_path is available to us by :

select object_path, comments
from schema_export_objects
where object_path in ('TABLE', 'SEQUENCE')
/

OBJECT_PATH	     COMMENTS
-------------------- --------------------------------------------------------------------------------
SEQUENCE	     Sequences in the selected schemas and their dependent grants and audits
TABLE		     Tables in the selected schemas and their dependent objects

The Path Map looks to be a flattened hierarchy ( possibly an XML representation). The point here is that, by specifying a node in this hierarchy, you can persuade DBMS_DATAPUMP to do your bidding.

As both TABLE and SEQUENCE are nodes in the object_path, we should be able to use that here…

...
dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'INCLUDE_PATH_EXPR',
    value => q'[IN ('TABLE', 'SEQUENCE')]'
);
...

This will give us everything at the level at and below the TABLE and SEQUENCE nodes. Therefore, you will also get INDEXES for the tables…as well as Triggers.

select object_path, comments
from schema_export_objects
where object_path like '%TABLE%TRIGGER%'
/

OBJECT_PATH                    COMMENTS
------------------------------ ------------------------------
SCHEMA_EXPORT/TABLE/TRIGGER    Triggers
TABLE/TRIGGER                  Triggers

Remember, we don’t want any PL/SQL program units, so we need to filter these out. Fortunately, calls to the METADATA_FILTER procedure seem to be addative. As TRIGGER appears below TABLE in the hierarchy, we can filter them out with an additional call to the procedure :

...
dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'EXCLUDE_PATH_EXPR',
    value => q'[= 'TRIGGER']'  
);  
...
The finished HR Import Script

Here it is then, a script to Import Tables and Sequences from HR to HR_DEV :

set serveroutput on size unlimited
declare
--
-- Import of HR objects to HR_DEV
-- Only importing Tables and Sequences
--
    l_dph number;
    l_predicate varchar2(4000);
begin
    -- Setup the job "context" as usual
    l_dph := dbms_datapump.open
    (
        operation => 'IMPORT',
        job_mode => 'SCHEMA',
        job_name => 'IMP_HR_DEV'
    );
    -- We only want objects in the HR schema
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'SCHEMA_EXPR',
        value => q'[= 'HR']'
    );
    -- and then only Sequences and Tables
	dbms_datapump.metadata_filter
	(
	    handle => l_dph,
	    name => 'INCLUDE_PATH_EXPR',
	    value => q'[IN ('TABLE', 'SEQUENCE')]'
    );
    -- ...but no triggers...
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'EXCLUDE_PATH_EXPR',
        value => q'[= 'TRIGGER']'  
    );  
    -- and we want to import these objects into the HR_DEV schema
    dbms_datapump.metadata_remap
    (
        handle => l_dph,
        name => 'REMAP_SCHEMA',
        old_value => 'HR',
        value => 'HR_DEV'
    );
    --
    -- If we find a table that already exists in the target
    -- schema then overwrite it..
    --
	dbms_datapump.set_parameter
	(
		handle => l_dph,
		name => 'TABLE_EXISTS_ACTION',
		value => 'REPLACE'
	);
	-- Now point to the export dump file to take this from
	dbms_datapump.add_file
	(
	    handle => l_dph,
	    filename => 'my_full_export.dmp',
	    directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file
    );
    -- ...and make sure we log what's going on...
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_dev_imp.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- log the start time...
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
    );
    -- start the job...
    dbms_datapump.start_job( handle => l_dph);
    -- and detach...
    dbms_datapump.detach(handle => l_dph);
end;
/

Now to give it a whirl ( saved as hr_dev_imp.sql)….

SQL> @hr_dev_imp.sql

PL/SQL procedure successfully completed.

SQL> 

Looking at the logfile, all appears well…

;;; Job starting at 18:15:29
Master table "MIKE"."IMP_HR_DEV" successfully loaded/unloaded
Starting "MIKE"."IMP_HR_DEV":  
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "HR_DEV"."COUNTRIES"                        6.367 KB      25 rows
. . imported "HR_DEV"."DEPARTMENTS"                      7.007 KB      27 rows
. . imported "HR_DEV"."EMPLOYEES"                        16.80 KB     107 rows
. . imported "HR_DEV"."JOBS"                             6.992 KB      19 rows
. . imported "HR_DEV"."JOB_HISTORY"                      7.054 KB      10 rows
. . imported "HR_DEV"."LOCATIONS"                        8.273 KB      23 rows
. . imported "HR_DEV"."REGIONS"                          5.476 KB       4 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "MIKE"."IMP_HR_DEV" successfully completed at 18:16:15

If we now connect as HR_DEV and check the objects we have in our schema :

select object_name, object_type
from user_objects
order by object_type, object_name
/

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
COUNTRY_C_ID_PK                INDEX
DEPT_ID_PK                     INDEX
DEPT_LOCATION_IX               INDEX
EMP_DEPARTMENT_IX              INDEX
EMP_EMAIL_UK                   INDEX
EMP_EMP_ID_PK                  INDEX
EMP_JOB_IX                     INDEX
EMP_MANAGER_IX                 INDEX
EMP_NAME_IX                    INDEX
JHIST_DEPARTMENT_IX            INDEX
JHIST_EMPLOYEE_IX              INDEX
JHIST_EMP_ID_ST_DATE_PK        INDEX
JHIST_JOB_IX                   INDEX
JOB_ID_PK                      INDEX
LOC_CITY_IX                    INDEX
LOC_COUNTRY_IX                 INDEX
LOC_ID_PK                      INDEX
LOC_STATE_PROVINCE_IX          INDEX
REG_ID_PK                      INDEX
DEPARTMENTS_SEQ                SEQUENCE
EMPLOYEES_SEQ                  SEQUENCE
LOCATIONS_SEQ                  SEQUENCE
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE

29 rows selected.

SQL> 
Sequences are special

There is one thing to be aware of with importing sequences.
Most database objects ( procedure, packages, triggers, views etc), can be overwritten by using CREATE OR REPLACE.
Like tables, you cannot do this with Sequences.
Unlike tables, DBMS_DATAPUMP does not have an option to re-create existing sequences.
This means that, if we were to refresh the HR_DEV schema again using the same script, we’d be likely to run into a bit of a problem, as you can see from this log file entry :

...
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"HR_DEV"."LOCATIONS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR_DEV"."DEPARTMENTS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR_DEV"."EMPLOYEES_SEQ" already exists
...

With this in mind, it’s probably a good idea to drop any sequences prior to refreshing with an import…

set serveroutput on size unlimited
begin
    for r_seq in
    (
        select sequence_name
        from dba_sequences
        where sequence_owner = 'HR_DEV'
    )
    loop
        --
        -- check the sequence name is "clean"
        --
        if regexp_instr( replace( r_seq.sequence_name, '_'), '[[:punct:]]|[[:space:]]') > 0
        then
            raise_application_error( -20000, 'Sequence name contains dodgy characters.');
        end if;
        -- drop the sequence
        execute immediate 'drop sequence hr_dev.'||r_seq.sequence_name;
        dbms_output.put_line('Sequence '||r_seq.sequence_name||' dropped.');
    end loop;
end;
/

If we run this we’ll get…

SQL> @drop_sequences.sql
Sequence DEPARTMENTS_SEQ dropped.
Sequence EMPLOYEES_SEQ dropped.
Sequence LOCATIONS_SEQ dropped.

PL/SQL procedure successfully completed.

SQL> 
Specifying a subset of data

Sometimes you don’t want to export/import everything. You might just want a few tables with a subset of data.
In our HR_DEV environment we want to focus on the EMPLOYEES table. We don’t want all of the rows – let’s just have one department…

Specifying which tables to use

Once again, METADATA_FILTER can be used here :

dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'NAME_LIST',
    value => q'['EMPLOYEES']',
    object_path => 'TABLE'
);

NAME_LIST tells datapump to look for object names rather than in object type paths.
Specifying the object type path as TABLE means that datapump will only look for a table called EMPLOYEES and not any other type of object with the same name.

Getting a subset of data

Here, we need to use the DATA_FILTER procedure. Unlike it’s METADATA counterpart, you need to provide a syntactically correct predicate for it to work…

...
dbms_datapump.data_filter
(
    handle => the_datapump_job_handle,
    name => 'SUBQUERY',
    value => 'where department_id = 20',
    table_name => 'EMPLOYEES',
    schema_name => 'HR'
);
...
The Data Sub-set Import Script

Before running this, I’ve taken the precaution of dropping all of the objects from the HR_DEV schema as I don’t want to run into any pesky constraint errors…

set serveroutput on size unlimited
declare
--
-- import Subset of employees into HR_DEV ( having cleared down the schema first)
--
    l_dph number;
begin
    l_dph := dbms_datapump.open
    (
        operation => 'IMPORT',
        job_mode => 'SCHEMA',
        job_name => 'IMP_EMPLOYEES'
    );
    -- We only want objects in the HR schema
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'SCHEMA_EXPR',
        value => q'[= 'HR']'
    );
    -- only TABLES...
	dbms_datapump.metadata_filter
	(
	    handle => l_dph,
	    name => 'INCLUDE_PATH_EXPR',
	    value => q'[= 'TABLE']'
    );
    -- and then only the EMPLOYEES table
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'NAME_LIST',
        value => q'['EMPLOYEES']',
        object_path => 'TABLE'
    );
    -- without any triggers or ref constraints
    	dbms_datapump.metadata_filter
	(
	    handle => l_dph,
	    name => 'EXCLUDE_PATH_EXPR',
	    value => q'[IN ('TRIGGER', 'REF_CONSTRAINT')]'
    );

    -- subset of EMPLOYEES
    dbms_datapump.data_filter
    (
        handle => l_dph,
        name => 'SUBQUERY',
        value => 'where department_id = 20',
        table_name => 'EMPLOYEES',
        schema_name => 'HR'
    );
        dbms_datapump.metadata_remap
    (
        handle => l_dph,
        name => 'REMAP_SCHEMA',
        old_value => 'HR',
        value => 'HR_DEV'
    );
    --
    -- If we find a table that already exists in the target
    -- schema then overwrite it..
    --
	dbms_datapump.set_parameter
	(
		handle => l_dph,
		name => 'TABLE_EXISTS_ACTION',
		value => 'REPLACE'
	);
	--
	-- Now point to the export dump file to take this from
	--
	dbms_datapump.add_file
	(
	    handle => l_dph,
	    filename => 'my_full_export.dmp',
	    directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file
    );
    --
    -- ...and make sure we log what's going on...
    --
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_dev_employees_imp.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- log the start time...
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
    );
    --
    -- start the job...
    --
    dbms_datapump.start_job( handle => l_dph);
    --
    -- and detach...
    --
    dbms_datapump.detach(handle => l_dph);
end;
/	

Notice that I’ve also specifically excluded the REF_CONSTRAINTS and TRIGGERS from the import.
Run this and we get the following output in the logfile :

;;; Job starting at 18:35:49
Master table "MIKE"."IMP_EMPLOYEES" successfully loaded/unloaded
Starting "MIKE"."IMP_EMPLOYEES":  
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "HR_DEV"."EMPLOYEES"                        16.80 KB       2 out of 107 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "MIKE"."IMP_EMPLOYEES" successfully completed at 18:36:33

We can see that only the EMPLOYEES table and it’s associated indexes have been imported :

select object_name, object_type
from user_objects
order by object_type, object_name
  4  /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
EMP_DEPARTMENT_IX              INDEX
EMP_EMAIL_UK                   INDEX
EMP_EMP_ID_PK                  INDEX
EMP_JOB_IX                     INDEX
EMP_MANAGER_IX                 INDEX
EMP_NAME_IX                    INDEX
EMPLOYEES                      TABLE

7 rows selected.

SQL> 

…and no Foreign Key constraints :

SQL> select table_name, constraint_name, constraint_type
  2  from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
EMPLOYEES                      EMP_LAST_NAME_NN               C
EMPLOYEES                      EMP_EMAIL_NN                   C
EMPLOYEES                      EMP_HIRE_DATE_NN               C
EMPLOYEES                      EMP_JOB_NN                     C
EMPLOYEES                      EMP_EMP_ID_PK                  P
EMPLOYEES                      EMP_EMAIL_UK                   U
EMPLOYEES                      EMP_SALARY_MIN                 C

7 rows selected.

SQL> 

and only those EMPLOYEES in DEPARTMENT_ID 20…

select last_name, department_id
from employees
/

LAST_NAME                 DEPARTMENT_ID
------------------------- -------------
Hartstein                            20
Fay                                  20

SQL> 
DDL_ONLY Datapump operations

Once again the DATA_FILTER procedure comes in here. However, this time, the call is a bit different :

...
dbms_datapump.data_filter
(
	handle => the_datapump_job_handle,
	name => 'INCLUDE_ROWS',
	value => 0
);
...

Here the value is effectively a boolean – 0 = false i.e. don’t include rows. The default is 1 – do include rows.
Incidentally, this time I’ve dropped the user HR_DEV altogether before importing as it will be re-created by the Import if it does not already exist.
This next script will import all of the DDL from HR to HR_DEV, but will not import any data…

set serveroutput on size unlimited
declare
--
-- Import of HR objects to HR_DEV
-- This time all DDL, but no data
--
    l_dph number;
begin
    l_dph := dbms_datapump.open
    (
        operation => 'IMPORT',
        job_mode => 'SCHEMA',
        job_name => 'IMP_DDL_HR_DEV'
    );
    -- We only want objects in the HR schema
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'SCHEMA_EXPR',
        value => q'[= 'HR']'
    );
    -- but no data
    dbms_datapump.data_filter
    (
	    handle => l_dph,
	    name => 'INCLUDE_ROWS',
	    value => 0
    );
    --
    -- and we want to import these objects into the HR_DEV schema
    --
    dbms_datapump.metadata_remap
    (
        handle => l_dph,
        name => 'REMAP_SCHEMA',
        old_value => 'HR',
        value => 'HR_DEV'
    );
    -- If we find a table that already exists in the target
    -- schema then overwrite it..
	dbms_datapump.set_parameter
	(
		handle => l_dph,
		name => 'TABLE_EXISTS_ACTION',
		value => 'REPLACE'
	);
	-- Now point to the export dump file to take this from
	dbms_datapump.add_file
	(
	    handle => l_dph,
	    filename => 'my_full_export.dmp',
	    directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file
    );
    -- ...and make sure we log what's going on...
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_dev_ddl_imp.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- log the start time...
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
    );
    -- start the job...
    dbms_datapump.start_job( handle => l_dph);
    -- and detach...
    dbms_datapump.detach(handle => l_dph);
end;
/

After running this we find that the HR_DEV user has been created. However, you will need to connect using the password of the HR user included in the export.

We can see now that all of the HR objects have been imported into HR_DEV :

select object_name, object_type
from user_objects
order by object_type, object_name
/

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
ADD_JOB_HISTORY                PROCEDURE
COUNTRIES                      TABLE
COUNTRY_C_ID_PK                INDEX
DEPARTMENTS                    TABLE
DEPARTMENTS_SEQ                SEQUENCE
DEPT_ID_PK                     INDEX
DEPT_LOCATION_IX               INDEX
EMPLOYEES                      TABLE
EMPLOYEES_SEQ                  SEQUENCE
EMP_DEPARTMENT_IX              INDEX
EMP_DETAILS_VIEW               VIEW
EMP_EMAIL_UK                   INDEX
EMP_EMP_ID_PK                  INDEX
EMP_JOB_IX                     INDEX
EMP_MANAGER_IX                 INDEX
EMP_NAME_IX                    INDEX
JHIST_DEPARTMENT_IX            INDEX
JHIST_EMPLOYEE_IX              INDEX
JHIST_EMP_ID_ST_DATE_PK        INDEX
JHIST_JOB_IX                   INDEX
JOBS                           TABLE
JOB_HISTORY                    TABLE
JOB_ID_PK                      INDEX
LOCATIONS                      TABLE
LOCATIONS_SEQ                  SEQUENCE
LOC_CITY_IX                    INDEX
LOC_COUNTRY_IX                 INDEX
LOC_ID_PK                      INDEX
LOC_STATE_PROVINCE_IX          INDEX
REGIONS                        TABLE
REG_ID_PK                      INDEX
SECURE_DML                     PROCEDURE
SECURE_EMPLOYEES               TRIGGER
UPDATE_JOB_HISTORY             TRIGGER
USER_FK_TREE_VW                VIEW

35 rows selected.

SQL> 

…and just to prove that we’ve imported no data at all…

SQL> select count(*) from regions;

  COUNT(*)
----------
         0

SQL> select count(*) from locations;

  COUNT(*)
----------
         0

SQL> select count(*) from departments;

  COUNT(*)
----------
         0

SQL> select count(*) from jobs;

  COUNT(*)
----------
         0

SQL> select count(*) from job_history;

  COUNT(*)
----------
         0

SQL> select count(*) from employees;

  COUNT(*)
----------
         0

SQL> select count(*) from countries;

  COUNT(*)
----------
         0

SQL> 
Killing a runaway job

There will be times when things don’t quite work as expected. This is especially true if you’re playing around with Datapump parameter settings.
Just occasionally when testing the code in this post I’ve come across some variation on the theme of …

ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5283
ORA-06512: at line 11

Yep, I’v fixed my runtime error, re-executed the scrpt and got this insistent little message.
The first thing to do when you get this ( especially if you’re running in the same session that you started the original job) is to logout and log back in again.

The second thing to do is to check to see if the job is still hanging about…

user
select job_name, state
from user_datapump_jobs
/

If so, then you can attach to the job and stop it.
If I’m connected as the job owner and I’m only running the one job, the following script should do the trick…

set serveroutput on size unlimited
declare
      l_dph number;
      l_job_name user_datapump_jobs.job_name%type;
begin
    select job_name into l_job_name from user_datapump_jobs;
    dbms_output.put_line('Killing '||l_job_name);
    l_dph := dbms_datapump.attach( job_name => l_job_name, job_owner => user);
    dbms_datapump.stop_job( l_dph);
    dbms_output.put_line('Job '||l_job_name||' terminated.');
exception
    when no_data_found then
        dbms_output.put_line('Job has already terminated.');
end;
/

This should work almost every time. On the off-chance it doesn’t, and the job does not appear in the USER_DATAPUMP_JOBS view anymore, then it’s possible that the temporary table created as part of the job has not been tidied up.
Remember, the temporary table is created in the job owner’s schema with the same name as the datapump job itself.
So, if I were to hit this problem whilst running the job IMP_HR_DEV, I could check :

select 1
from user_tables
where table_name = 'IMP_HR_DEV'
/

If the table does still exist and there is no entry in the _DATAPUMP_JOB view, we can simply drop it :

drop table imp_hr_dev
/

This should finally persuade datapump that the job is not in fact running.

A couple of Enterprise Edition Features

For those of you on a richer diet, there are a couple of further morsels which may well be worthy of consideration.

If you want to speed up your Datapump job by making use of the CPU cores available you could try :

...
dbms_datapump.set_parallel
(
	handle => the_datapump_job_handle,
	degree => 32
);
...

If you’ve really pushed the boat out and have the partitioning option, you can tell datapump to take only specific partitions for tables.

For example, say you have a number of tables that are partitioned by date ranges.
The tables may be partitioned by quarter-year, with the partitions following a consistend naming convention (e.g. Q12014, Q22014 etc).
If we only want to export/import the latest partition for each of these tables( say Q22014), we can do something like this :

...
for r_part_tabs in
(
	select table_name
	from user_tab_partitions
	where partition_name = 'Q22014'
)
loop
	dbms_datapump.data_filter
	(
		handle => the_datapump_job_handle,
		name => 'PARTITION_EXPR',
		value => q'[= 'Q22014']',
		table_name => r_part_tabs.table_name
	);
end loop
...

Despite having gorged ourselves on this feast of DBMS_DATAPUMP delights, it’s fair to say that I’ve by no means covered everything.
Doubtless you will want to do things with this API that I’ve not covered here. Indeed, you’ll probably also find better ways of implementing some of the same functionality.
In the meantime, I’m off for a digestif.
Bon Apetite.


Filed under: Oracle, PL/SQL Tagged: archivelog mode, database_export_objects, dba_datapump_jobs, dbms_datapump examples, dbms_datapump.add_file, dbms_datapump.data_filter, dbms_datapump.detach, dbms_datapump.log_entry, dbms_datapump.metadata_filter, dbms_datapump.metadata_remap, dbms_datapump.open, dbms_datapump.set_parallel, dbms_datapump.set_parameter, dbms_datapump.start_job, dbms_datapump.stop_job, dbms_datapump.wait_for_job, DDL Only import, drop sequence, EXCLUDE_PATH_EXPR, external table, flashback_scn, INCLUDE_PATH_EXPR, INCLUDE_ROWS, NAME_LIST, ora-31634, PARTITION_EXPR, REF_CONSTRAINTS, remap_schema, Running a Datapump job in the background, schema_export_objects, SCHEMA_EXPR, scn, SUBQUERY, TABLE_EXISTS_ACTION, table_export_objects, tail a logfile from sql, timestamp_to_scn, user_datapump_jobs, v$database.current_scn, v$database.log_mode

Oracle’s hidden documentation – Commenting the Data Model in Oracle

Sun, 2014-06-08 06:55

The Football (or Soccer, if you prefer) World Cup is almost upon us.
England have absolutely no chance so even the false hope that traditionally accompanies major tournaments won’t be around to spoil the enjoyment.
What makes this World Cup extra special is the fact that it’s taking place in Brazil – the spiritual home of the Beautiful Game.
The only previous occasion that Brazil hosted the tournament was in 1950, and it’s worth a brief look at what went on then, if only to provide the basis of the examples that follow.
Back in 1950, as now, money was a bit scarce.
Brazil agreed to host the tournament on condition that the format would be designed to maiximize the number of games played and therefore the gate revenue generated.
It is for this reason that the 1950 tournament is unique in World Cup history as the only tournament to be decided, not by a final, but by a round-robin “Final Pool”.
Then, as now, England travelled to Brazil. Unlike now there was a fair degree of confidence, not to say arrogance, about the prospects of the national team showing these foreigners how the game should really be played.
The Empire may have been slipping away, but it was still a widely held belief – in England at least – that God was an Englishman.
In the event, England managed to lose to an amatuer USA team 1-0 and then get sent packing by Spain.

OMG, WTF does GD Mean ?

Whilst the English may have begun to wonder whether God wasn’t actually Scottish ( or even Welsh for that matter), the Brazilians swept all before them in the final round robin.
In the last match of the tournament, at the new footballing cathederal of the Maracana, over 200,000 people turned up to watch what the whole nation assumed would be their coronation of the new World Champions.
Apparently, no-one bothered to mention this to Uruguay.
Coming from a goal down, Uruguay won the match 2-1 and with it, the World Cup.
In Brazil this game is still known as Maracanaço – The Maracana Blow.
All of which brings me to the matter at hand.
Abbreviations in both Oracle table names and column names are fairly common. After all, you only get 30 characters to play with for each ( unless you want to go down the long and winding road of quoted identifiers). An example of this might be :

create table final_pool_standings
(
    team varchar2(30) constraint fps_pk primary key,
    pld number(1),
    w number(1),
    d number(1),
    l number(1),
    gf number(2),
    ga number(2),
    pts number(1),
    gd as (gf - ga)
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'URUGUAY', 3, 2, 1, 
    0, 7, 5, 5
)
/
   
insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'BRAZIL', 3, 2, 0,
    1, 14, 4, 4
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'SWEDEN', 3, 1, 0,
    2, 6, 11, 2
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'SPAIN', 3, 0, 1,
    2, 4, 11, 1
)
/

commit;

Now, whilst for a footie fan( well, an English-speaking footie fan), the column names here are pretty much self explainatory, someone who is not quite so familiar with common abbreviations and acronyms associated with football may do a bit of head-scratching when presented with this table.

To be fair, it’s the same when looking at any data structure using abbreviations and acronyms.
In baseball you have GB (Games Back). In cricket, even more confusingly, you have Strike Rate, which means different things in the context of batting averages and bowling averages.
Fortunately, Oracle provides a mechanism to overcome this apparent failure in the QA processes for this particular data model.


Comment on Table

I think the first thing we need to do is to explain what the FINAL_POOL_STANDINGS table is all about :

comment on table final_pool_standings is
    'The 1950 World Cup round-robin group to decide the title. Table Short Name : FPS'
/

Note that the comment itself is a string-literal so it’s not possible to build a concatenated string. On the plus side however, you do get up to 4000 characters to begin to de-mistify your data model.

The _TAB_COMMENTS views provide a means of interrogating table comments :

select comments
from user_tab_comments
where table_name = 'FINAL_POOL_STANDINGS'
/

COMMENTS
------------------------------------------------------------------------------------------
The 1950 World Cup round-robin group to decide the title. Table Short Name : FPS

Unravelling the GD mystery- Column Comments

Just as useful, if not more so, is the ability to comment on individual columns :

comment on column final_pool_standings.team is
    'The national team. Primary Key'
/

comment on column final_pool_standings.pld is
    'Games played in the final pool stage'
/

comment on column final_pool_standings.w is
    'Games won'
/

comment on column final_pool_standings.d is
    'Games drawn'
/

comment on column final_pool_standings.l is
    'Games lost'
/

comment on column final_pool_standings.gf is
    'Goals For - number of goals scored in final pool matches'
/

comment on column final_pool_standings.ga is
    'Goals Against - number of goals conceded in final pool matches'
/

comment on column final_pool_standings.pts is
    'Points gained in final pool matches - two for a win, one for a draw, nothing for a loss'
/

comment on column final_pool_standings.gd is
    'Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column'
/

Most IDE’s actually display column comments in-line with the column definitions so they are easy to spot.
You can also retrieve them from the data dictionary directly :

select column_name, comments
from user_col_comments
where table_name = 'FINAL_POOL_STANDINGS'
/
COLUMN_NAME		       COMMENTS
------------------------------ ------------------------------------------------------------------------------------------
TEAM			       The national team. Primary Key
PLD			       Games played in the final pool stage
W			       Games won
D			       Games drawn
L			       Games lost
GF			       Goals For - number of goals scored in final pool matches
GA			       Goals Against - number of goals conceded in final pool matches
PTS			       Points gained in final pool matches - two for a win, one for a draw, nothing for a loss
GD			       Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column

This sort of information may well be held in whichever modelling tool you may or may not be using. However, there’s no substitute for having these sorts of comments in the database itself, especially if you happen to be the one trying to resolve an urgent support call on a job that’s accessing a data model with which you are unfamiliar.

Amending and deleting comments

If you want to edit a comment, you simply replace it with another string literal.
For example, if I wanted to expand on the comment for the GD column :

comment on column final_pool_standings.gd is
    'Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column. In truth this is slightly anachronistic as Goal Difference was not used to rank teams on the same number of points in this tournament.'
/

If you want to remove a comment altogether, you simply need to set it to a NULL string :

comment on column final_pool_standings.w is 
    ''
/

Even if a table or column has no comment associated with it, it does still have an entry in the _COMMENTS views.

Hidden documentation – Comments on Data Dictionary Views

Oracle is way ahead of you here. It’s been using the comments functionality for tables and views for years.
Not sure what USER_TAB_PRIVS is for (or even, whether or not it’s a table) ?…

select table_type, comments
from dba_tab_comments
where owner = 'SYS'
and table_name = 'USER_TAB_PRIVS'
/

TABLE_TYPE  COMMENTS
----------- --------------------------------------------------------------------------------
VIEW        Grants on objects for which the user is the owner, grantor or grantee

SQL> 

You can also find out more about the differences between the USER, ALL, and DBA versions of a view :

select table_name, comments
from dba_tab_comments
where table_name like '%\_TAB_PRIVS' escape '\'
/

TABLE_NAME	     COMMENTS
-------------------- ------------------------------------------------------------------------------------------
USER_TAB_PRIVS	     Grants on objects for which the user is the owner, grantor or grantee
ALL_TAB_PRIVS	     Grants on objects for which the user is the grantor, grantee, owner,
		      or an enabled role or PUBLIC is the grantee

DBA_TAB_PRIVS	     All grants on objects in the database
ROLE_TAB_PRIVS	     Table privileges granted to roles

Column comments are also included :

select column_name, comments
from dba_col_comments
where owner = 'SYS'
and table_name = 'USER_TAB_PRIVS'
/

COLUMN_NAME                    COMMENTS
------------------------------ --------------------------------------------------------------------------------
GRANTEE                        Name of the user to whom access was granted
OWNER                          Owner of the object
TABLE_NAME                     Name of the object
GRANTOR                        Name of the user who performed the grant
PRIVILEGE                      Table Privilege
GRANTABLE                      Privilege is grantable
HIERARCHY                      Privilege is with hierarchy option

7 rows selected.

SQL> 

There are hundreds of data dictionary objects that are documented in this way.
These do seem to be mainly the USER, ALL and DBA views – V$ views are noticable by their absence. However, the ones that are there do offer a significant amount of useful documentation.

OK. Time to review my World Cup Essentials checklist …

  • World Cup Wall Chart and TV Guide – check
  • Realistic Expectations – check
  • England Penalty shoot-out blind-fold ( just in case) – I’m not sure I’ll be needing that

Filed under: Oracle, SQL Tagged: COMMENT ON COLUMN, comment on table, dba_col_comments, dba_tab_comments, editing table and column comments, escape, escaping a string in a query predicate, user_col_comments, user_tab_comments

Putting the Plus into SQL*Plus – describing a Package member (with added kittens)

Sat, 2014-05-31 10:05

Deb’s been on about getting another cat recently.
“Absolutely not !”, I said, putting my foot down very firmly.
Oh yes, we all know who wears the trousers in this house thank you very much.
So…Cleo the Kitten is now making herself at home.

In fact, she’s on the desk now, sniffing at the key..$#%3″!Q…make that running across the keyboard.
Obviously, she’s not quite mastered the basics of shell scripting yet ( although she does evidently know that it employs most of the keys on the top row of the keyboard).
Maybe we need to start her off with something a little less complicated.
Not however, the sanctity of those cute and fluffy Oracle IDEs, I’ve had quite enough cute and fluffy for one day.
Instead we’re going to have a look at the grizzled old moggy that is SQL*Plus.

In my defence, I will say that the use of SQL*Plus is a something of a necessity for me as I spend a lot of time on the train and so write much of my ramblings on a small, elderly netbook.
I’ve got XE running on it so firing up an IDE will slow things down considerably.
And yes, OK, I did start programming before the World Wide Web and GUI desktops…but no, not before the invention of the internal combustion engine, Deborah. Honestly, haven’t you got a kitten you should be playing with ?

My starting point is simply this – wouldn’t it be great if you could use the DESCRIBE function to lookup a single member of a package in SQL*Plus.

So, what I’m going to cover here is :

  • How to get argument details for package members from the Data Dictionary
  • Building a pipelined function ( using a cursor for loop)
  • Mimicing the DESCRIBE functionality using SQL*Plus structures and formatting

Right, stop sharpening your claws on that sofa and pay attention…

Describing Packages

Say that I want to check the signature of DBMS_METADATA.GET_DDL.
If I’m in an IDE, I can simply type the package name, press the appropriate short-cut key to bring up the package signature and then search for the member of interest ( in this case the GET_DDL function).
In SQL*Plus however, it’s that last bit that’s somewhat problematic…

desc dbms_metadata

Run this and you will get reams of data spooled to the screen. This is not surprising. DBMS_METADATA contains a lot of members :

select count(procedure_name)
from dba_procedures
where owner = 'SYS'
and object_name = 'DBMS_METADATA'
/

COUNT(PROCEDURE_NAME)
---------------------
		   86

It’s by no means the largest package in terms of members ( DBMS_BACKUP_RESTORE has over 300, for example).
If we just want to examine the signature for the GET_DDL function, we’ll need to write some SQL. Fortunately, all of the information we need is contained in DBA_ARGUMENTS :

select argument_name, data_type, in_out, defaulted
from dba_arguments
where package_name = 'DBMS_METADATA'
and object_name = 'GET_DDL'
and data_level = 0
order by sequence
/

ARGUMENT_NAME		       DATA_TYPE		      IN_OUT	D
------------------------------ ------------------------------ --------- -
			       CLOB			      OUT	N
OBJECT_TYPE		       VARCHAR2 		      IN	N
NAME			       VARCHAR2 		      IN	N
SCHEMA			       VARCHAR2 		      IN	Y
VERSION 		       VARCHAR2 		      IN	Y
MODEL			       VARCHAR2 		      IN	Y
TRANSFORM		       VARCHAR2 		      IN	Y

The first argument has a NULL argument_name, but we can see that it’s an OUT parameter. This is, in fact, the return value of the function.

Incidentally, if you have a look at DBA_ARGUMENTS, you’ll notice that there is a DEFAULTED_VALUE column, Unfortunately, up to and including 11gR2, this is always blank. According to the Oracle Documentation, this column is “Reserved for future use”.

Anyway, we now have a relatively simple query that we can use to get the information we want. Now we need to make it a bit more convenient to access. After all, we don’t want to have to type the query out every time if we can help it.

The Pipelined function

In order to make this information a bit easier to get at (i.e. avoid the need to type out a query such as the one above every time), let’s create a pipelined function.

First of all, we’ll define some types :

create or replace type proc_desc_typ as object
(
    object_type varchar2(20),
    object_name varchar2(30),
    overload varchar2(40),
    argument_name varchar2(30),
    data_type varchar2(30),
    in_out varchar2(9),
    defaulted varchar2(1)
)
/

create or replace type proc_desc_tab_typ is table of proc_desc_typ
/

…and then the function itself.
Incidentally, I did write something a while ago on the subject of pipelined functions.
In the course of my reasearch, I noticed that all of the examples of pipelined functions use explicit cursors.
So, just for variety, I’m going to use a cursor for loop here, just to prove that it works.

create or replace function desc_plus_proc
(
    i_package all_arguments.package_name%type,
    i_member all_arguments.object_name%type default null,
    i_owner all_arguments.owner%type default null
)
    return proc_desc_tab_typ pipelined
    authid current_user
is
    l_owner all_arguments.owner%type;
    l_package all_arguments.package_name%type;
    
    cursor c_syns( cp_package all_synonyms.synonym_name%type) is    
        select table_owner, table_name
        from all_synonyms
        where owner in ('PUBLIC', user)
        and synonym_name = cp_package;
    
    l_is_synonym boolean := false;
    
    l_current_member all_arguments.object_name%type := null;
    l_current_overload all_arguments.overload%type := null;
    l_current_type varchar2(20) := null;
    
    l_rtn proc_desc_typ := proc_desc_typ(null, null, null, null, null, null, null);
    
begin
    --
    -- i_package is the only mandatory parameter
    --
    if i_package is null then
        raise_application_error(-20000, 'Must provide a package name ');
    end if;
    --
    -- Work out if we've been passed a synonym
    --
    if i_owner is null then
        open c_syns( upper( i_package));
        fetch c_syns into l_owner, l_package;
        l_is_synonym := c_syns%found;
        close c_syns;
    end if;
    if not l_is_synonym then
        --
        -- make sure the l_owner and l_package variables are
        -- set
        --
        l_owner := i_owner;
        l_package := i_package;
    end if;
        
    for r_args in
    (
        select object_name, argument_name, data_type,
            in_out, defaulted, overload, sequence
        from all_arguments
        where owner = upper(nvl(l_owner, owner))
        and package_name = upper(i_package)
        and object_name = upper(nvl(i_member, object_name))
        and data_level = 0
        order by object_name, subprogram_id, sequence
    )
    loop
        --
        -- We may well be returning details of multiple package members
        -- as either the member specified is overloaded or no member is 
        -- specified. So, we need to see if we're starting to describe a new object.
        --
        if r_args.sequence < 2 
        then
            --
            -- Work out if this is member is :
            -- 
            -- 1) A procedure that takes no arguments - 
            --  - first argument_name is null
            --  - data_type is null
            -- 2) A function 
            --  - first argument_name is null
            --  - data_type is return type
            -- 3) An ordinary procedure
            --
            l_current_member := r_args.object_name;
            if r_args.sequence = 0 then
                --
                -- Procedure with no arguments
                --
                r_args.argument_name := '<NONE>';
                r_args.in_out := null;
                r_args.defaulted := null;
                l_current_type := 'PROCEDURE';
            elsif r_args.sequence = 1 and r_args.argument_name is null
                and r_args.data_type is not null 
            then
                r_args.argument_name := '<RETURN>';
                l_current_type := 'FUNCTION';
            else
                l_current_type := 'PROCEDURE';
            end if;
        end if;
        l_rtn.object_type := l_current_type;
        l_rtn.object_name := r_args.object_name;
        l_rtn.overload := r_args.overload;
        l_rtn.argument_name := r_args.argument_name;
        l_rtn.data_type := r_args.data_type;
        l_rtn.in_out := r_args.in_out;
        l_rtn.defaulted := r_args.defaulted;
        pipe row( l_rtn);
    end loop;
end desc_plus_proc;
/

The functionality is fairly similar to that of DESCRIBE itself in that we first evaluate whether we’ve been passed a synonym and, if so, then resolve it to the underlying object.
We then loop through our query ( using the funky for loop).
As well as functions, procedures that take no arguments can also have null argument_names so we need a bit of logic to determine which is which.
Finally we pipe back the results.
A point to note here is that you can’t pipe the cursor record variable itself. You need to use a variable of the appropriate return type.

In addition, we’re using the ALL_ARGUMENTS view rather than it’s DBA counterpart. This is because we want to make the function available to users who may not have privileges to the DBA views.
We’ve also created the function with Invoker’s Rights (AUTHID CURRENT_USER). This ensures that we don’t override any grants to users and make available database objects that would not otherwise be visible to them.

As the function is potentially going to be used by anyone connecting to the database, we can issue the following grant :

grant execute on desc_plus_proc to public
/

…and possibly even…

create or replace public synonym desc_plus_proc for mike.desc_plus_proc
/

…or possibly not. Public grants and synonyms tend to illicit a fair amount of discussion in database circles. So, maybe you’d want to grant execute explicitly to individual users (or even a role). The same may apply to the synonym – you may prefer to dispense with it altogether ( and call referencing the owner) or create private synonyms as appropriate.
For the purposes of this particular post, I’m going to sit cat-like on the fence and feign complete disinterest.

Anyway, time for a quick test :

SQL> select object_type||' - '||object_name as member, argument_name, data_type, in_out, defaulted
  2  from table( desc_plus_proc('dbms_metadata', 'get_ddl'))
  3  /

MEMBER						      ARGUMENT_NAME		     DATA_TYPE			    IN_OUT    D
----------------------------------------------------- ------------------------------ ------------------------------ --------- -
FUNCTION - GET_DDL				      <RETURN>			     CLOB			    OUT       N
FUNCTION - GET_DDL				      OBJECT_TYPE		     VARCHAR2			    IN	      N
FUNCTION - GET_DDL				      NAME			     VARCHAR2			    IN	      N
FUNCTION - GET_DDL				      SCHEMA			     VARCHAR2			    IN	      Y
FUNCTION - GET_DDL				      VERSION			     VARCHAR2			    IN	      Y
FUNCTION - GET_DDL				      MODEL			     VARCHAR2			    IN	      Y
FUNCTION - GET_DDL				      TRANSFORM 		     VARCHAR2			    IN	      Y

7 rows selected.

SQL> 

Well, it seems to return the required results. However, the formatting could do with a bit of cleaning up for SQL*Plus.
Now, we could go and make some changes to the function itself. However, as it stands, it can be useful in any Oracle client environment, not just SQL*Plus. So, a better solution would be to ….

Unleash the power of SQL*Plus

You can do a fair bit to customise your SQL*Plus environment. As well as getting in on the fluffy kitten theme, William Robertson has a great article on customising your SQL*Plus environment…although his cat looks a bit older and I’m a bit disappointed that it doesn’t seem to have mastered the art of touch-typing. Have a look at his home page and you’ll see what I mean.
If, like William, you want to have your own personal settings (for SQL*Plus that is, not cats), you can create a file called login.sql.

Making yourself at home with login.sql

In order to get this file to run every time you start SQL*Plus, you can define the SQLPATH environment variable on your OS to point to the directory that login.sql is located.

For example, on my Linux machine :

cd $HOME
mkdir sqlplus_scripts

Now to set the SQLPATH environment variable. I’ll do this by editing the .bashrc file in my home directory to add the line :

export SQLPATH=$HOME/sqlplus_scripts

Finally, I can put my login.sql script in the sqlplus_scripts directory and it will always run when I start SQL*Plus.

My login.sql looks like this :

def _editor=/usr/bin/gedit

You may be wondering what exactly this has to do with our pipelined function. Well, now we have the SQLPATH set, SQL*Plus will look in here to find a file that we’re trying to execute.

With this in mind, we can make our describe package member function just that little bit more user-friendly.
In the new sqlplus_scripts directory, I’ve created the file desc_plus_proc.sql, which does several things.

  1. saves the existing SQL*Plus session settings
  2. sets the environment up to display the query output
  3. uses an anonymous PL/SQL block evaluates the argument string passed in
  4. executes the query using variables declared in SQL*Plus and populated in the block
  5. tidies up after itself

…all of which looks something like :

rem
rem Record the current session settings before we start mucking about
rem
store set $SQLPATH/default_settings.sql replace
rem
rem Set the page width and size (no of lines)
rem
set lines 130
set pages 24
rem
rem format the columns that will be output from the query
rem
column member format a40
column ovrld format a5
column argument_name format a30
column data_type format a30
column io format a3
column defaulted format a9

rem
rem declare the SQL*Plus variables to be populated for the call to the function
rem
var sp_owner varchar2(30)
var sp_package varchar2(30)
var sp_member varchar2(30)

rem 
rem supress verify output for the parameter replacement and feedback for the pl/sql block completion
rem
set verify off
set feedback off

declare
    --
    -- sort out the argument we've been passed.
    -- If it's got '.' in it then we need to determine what the
    -- individual elements are.
    -- Once we've established what we've been passed in, initialise
    -- the SQL*Plus variables we've just declared.
    --
    l_argstr varchar2(100):= '&1';
    
    l_count pls_integer;
    
    l_first_end pls_integer;
    l_second_end pls_integer;
    
    l_arg1 varchar2(30);
    l_arg2 varchar2(30);
    
    l_owner varchar2(30);
    l_package varchar2(30);
    l_member varchar2(30);
    
    l_dummy pls_integer;
    cursor c_is_user( cp_user varchar2) is
        select 1
        from all_users
        where username = upper( cp_user);    
begin
    --
    -- '.' + 1 = the number of elements in the argument we've been passed
    --
    l_count := regexp_count(l_argstr, '\.',1) + 1;
    if l_count = 1 then
        l_package := l_argstr;
    elsif l_count = 3 then
        l_first_end := instr( l_argstr, '.',1,1);
        l_second_end := instr( l_argstr, '.',1,2);
        l_owner := substr( l_argstr, 1, l_first_end -1);
        l_package := substr( l_argstr, l_first_end + 1, l_second_end - l_first_end -1);
        l_member := substr( l_argstr, l_second_end + 1);
    elsif l_count = 2 then
        -- this is either owner.package or package.member...
        l_first_end := instr(l_argstr, '.',1,1);
        l_arg1 := substr( l_argstr, 1, l_first_end -1);
        l_arg2 := substr( l_argstr, l_first_end + 1);
        open c_is_user( l_arg1);
        fetch c_is_user into l_dummy;
        if c_is_user%found then
            l_owner := l_arg1;
            l_package := l_arg2;
        else
            l_package := l_arg1;
            l_member := l_arg2;
        end if;    
    end if;
    :sp_owner := l_owner;
    :sp_package := l_package;
    :sp_member := l_member;
end;    
/

break on member on ovrld
select case when object_type is not null then object_type||' - '||object_name end as member,
    overload as ovrld,
    argument_name,
    data_type,
    in_out as io,
    defaulted
from table( desc_plus_proc( i_package => :sp_package, i_member => :sp_member, i_owner => :sp_owner))
/

rem
rem and now to tidy-up...
rem
undefine sp_owner
undefine sp_package
undefine sp_member
clear column
clear break

@$SQLPATH/default_settings.sql

The upshot of all this ? Well, from the SQL*Plus prompt my call to this function is now rather closer to the DESCRIBE syntax :

@desc_plus_proc dbms_metadata.get_ddl;
@desc_plus_proc dbms_output;

The output looks a bit more elegant too :

Output to have you purring like a kitten

Output to have you purring like a kitten

Of course, if you simply must have this functionality back in the comfort of your IDE, you’re free to use the pipelined function and customise the output accordingly.
In the meantime, the command line becomes just that little bit more…well, cute and fluffy.


Filed under: Oracle, PL/SQL, SQL Tagged: all_arguments, authid current_user, break on column, clear break, clear column, column format, create type as object, create type is table of, dba_arguments, dba_arguments.defaulted_value, dba_procedures, describe, login.sql, select from table, set feedback, set lines, set pagesize, set verify, SQL*Plus, sql*plus variable, SQLPATH, store SQL*Plus settings, undefine variable

Long to CLOB conversion – Constraint Conditions and Database D.I.Y

Tue, 2014-05-20 11:50

DIY on a public holiday is something akin to a Great British tradition. Unfortunately, my DIY skills can best be summed up as being able to move heavy stuff about and do a bit of light destruction.
Fortunately, Deb is great at that sort of thing. As a result, I seem to have spent rather a lot of time lately playing Igor to her mad scientist visionary genius.
On an unusually sunny Bank Holiday Monday recently, The Lady of the House sent me packing out into the back garden with instructions to “tidy it up a bit”.
There followed an afternoon of intensive Chemical Warefare on the weeds ( the one thing I do seem to have a talent for growing) followed by judicious application of mower and strimmer.
Needless to say, the end result resembled less the restoration of verdant sward that she was secretly hoping for and more an example of slash-and-burn agriculture.
Fortunately, the next Bank Holiday isn’t until the end of May.
This gives me some time to continue in my attempts to persuade my better half to move into the 21st century garden-wise.
My argument is that, most people will only ever see our back garden on Google Maps. Provided it looks OK from the satellite view then that’s really what matters.
So, all we need to do is concrete the lawn over then paint the light and dark green stripes in.
This way, rather than constantly cutting the grass, we just need to repaint it once a year or so.
I’ve even offered to paint in some flowerbeds.
Ironically, this argument appears to have fallen on stony ground.

On top of this, the Oracle database has also required me to do a bit of DIY lately. This takes the form of trying to search the text in a column defined as LONG.
You may be wondering what on earth I’m doing with a LONG column. After all, Oracle introduced the CLOB way back in 8i, before the turn of the century. Surely no-one uses LONGs any more ?
Well, almost no-one…except Oracle.

Long columns in Data Dictionary Views

First of all, let’s see just how widely used LONG columns still are in the Data Dictionary :

select count(*)
from dba_tab_columns
where owner = 'SYS'
and data_type = 'LONG'
/

This comes back with the grand total of 229 on 11gXE. Obviously, this will include multiple views based on a single table. However, it still represents a significant presence for the venerable LONG datatype.
This presence does become particularly apparent when you want to – for example – have a look at the constraints in a given schema.

The Test Schema

Taking inspiration from some of Deb’s shopping lists for our planned trips to the garden centre…

create user igor identified by handy
    default tablespace users
/
 
grant create session, create table to igor
/

alter user igor quota unlimited on users
/

Now to create the tables in the new schema :

create table garden_checklists
(
    gc_name varchar2(50) constraint gc_pk primary key
)
/
create table garden_checklist_items
(
    item_name varchar2(50) constraint gci_pk primary key,
    gc_name varchar2(50) not null constraint gci_gc_fk references garden_checklists( gc_name),
    description varchar2(4000),
    safe_for_mike varchar2(1) not null,
    cost number(9,2) constraint gci_cost_ck check (cost < 50.0)
)
/

So, connected as Igor, we can see that we have the following constraints in place :

select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'GARDEN_CHECKLIST_ITEMS'
/

CONSTRAINT_NAME      CONSTRAINT_TYPE SEARCH_CONDITION
-------------------- --------------- -------------------------------------------------------
SYS_C0010851	     C		     "GC_NAME" IS NOT NULL
SYS_C0010852	     C		     "SAFE_FOR_MIKE" IS NOT NULL
GCI_COST_CK	     C		     cost < 50.0
GCI_PK		     P
GCI_GC_FK	     R

Now, say we want to just list the Check constraints that other than NOT NULL constraints.
Simple enough you might think …

select constraint_name
from user_constraints
where constraint_type = 'C'
and search_condition not like '%IS NOT NULL'
/

However, when you run this you’ll get :

and search_condition not like '%IS NOT NULL'
    *
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

That’s right, the SEARCH_CONDITION column is defined as a long. It looks like we’re going to need a different approach.

TO_LOB or not TO_LOB ? That is the question

The TO_LOB function has been around…well, as long as the LOB datatypes. That should do the job…

select constraint_name
from user_constraints
where constraint_type = 'C'
and to_lob(search_condition) not like '%IS NOT NULL'
/

and to_lob(search_condition) not like '%IS NOT NULL'
    *
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected - got LONG

…or not, as the case may be.

In fact, TO_LOB does work, but only in a CREATE TABLE statement :

create table my_constraints as
    select constraint_name, 
        constraint_type, 
        to_lob( search_condition) as search_condition,
        r_owner, 
        r_constraint_name
    from user_constraints
/

Table created.

SQL> desc my_constraints
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE				    VARCHAR2(1)
 SEARCH_CONDITION				    CLOB
 R_OWNER					    VARCHAR2(120)
 R_CONSTRAINT_NAME				    VARCHAR2(30)

Going back to our problem – how to get a list of Check Constraints other than NOT NULL constraints, things are now a whole lot easier :

select constraint_name
from my_constraints
where constraint_type = 'C'
and search_condition not like '%IS NOT NULL'
/

CONSTRAINT_NAME
------------------------------
GCI_COST_CK

OK, so we do have some kind of solution to our problem. However, creating a table every time we want to check the contents of a long column isn’t ideal.
Fortunately, there is another way of getting DDL for a constraint…

DBMS_METADATA.GET_DDL – Does Exactly What it Says On the Tin ?

Of course, DBMS_METADATA.GET_DDL will return DDL for a given object in the form of a CLOB. As it’s a DDL statement that we’re looking at, we will have to amend our predicate slightly, but the following should be fine :

select constraint_name
from user_constraints
where constraint_type = 'C'
and dbms_metadata.get_ddl
( 
    object_type => 'CONSTRAINT', 
    name => constraint_name, 
    schema => user
) not like '%NOT NULL%'
/

This is where things start getting a little bit silly. Running this query will give you :

and dbms_metadata.get_ddl
    *
ERROR at line 4:
ORA-31603: object "SYS_C003" of type CONSTRAINT not found in schema "IGOR"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

Now, it is true to say that SYS_C003 does not exist in the IGOR schema, it is valid to ask why on earth the function is even attempting to look it up, seeing as it’s being passed in a constraint name from USER_CONSTRAINTS.
If you’re interested, you can lookup this constraint ( as a user with sufficient privileges) :

select owner, table_name, constraint_name, constraint_type, search_condition
from dba_constraints
where constraint_name = 'SYS_C003'
/

You will find that this is a Check Constraint on the FILE# column of SYS.TAB$.

Interestingly, USER_CONSTRAINTS has an OWNER column. If we use this in the predicate, we hit a slightly different issue…

select constraint_name
from user_constraints
where constraint_type = 'C'
and owner = user
and dbms_metadata.get_ddl
( 
    object_type => 'CONSTRAINT', 
    name => constraint_name, 
    schema => user
) not like '%NOT NULL%'
/

and dbms_metadata.get_ddl
    *
ERROR at line 5:
ORA-31603: object "GCI_GC_FK" of type CONSTRAINT not found in schema "IGOR"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

GCI_GC_FK does, in fact, exist in the current schema. The issue here is that retrieving DDL for a Foreign Key constraint requires a slightly different call to the function :

select dbms_metadata.get_ddl( 'REF_CONSTRAINT', 'GCI_GC_FK', user) from dual;

Perhaps a more pertinent question to ask would be, why is the function once again ignoring the query predicate and looking up something that is not a check constraint ?

Now, whilst I do accept that I could just be being as dumb as a bag of hammers ( to continue the D.I.Y theme), I can assure you dear reader, that I have not been drinking. This eliminates at least one of Flavio Casetta’s hypotheses as to what is actually going on here :)

I think we can conclude from this that using the DBMS_METADATA.GET_DDL function in-line in a query is possibly not entirely reliable. However, it seems to behave itself if we enclose it in the straight-jacket of a correlated sub-query :

select cons.constraint_name
from user_constraints cons
where cons.owner = user
and cons.constraint_type = 'C'
and 
(
    select dbms_metadata.get_ddl( 'CONSTRAINT', cons1.constraint_name, user)
    from user_constraints cons1
    where cons1.owner = cons.owner
    and cons1.constraint_type = cons.constraint_type
    and cons1.constraint_name = cons.constraint_name
) not like '%NOT NULL%'
/

CONSTRAINT_NAME
------------------------------
GCI_COST_CK

Once again, we have a solution to our problem of finding all of the Check constraints in the current schema that are not simple NOT NULL constraints.
Once again, the solution we have doesn’t feel entirely satisfactory…

If you can do it in PL/SQL…

Coming toward the end of our LONG and winding road, let’s see what PL/SQL would make of all this…

set serveroutput on size unlimited
begin
    for r_cons in 
    (
        select constraint_name, table_name, search_condition
        from user_constraints
        where constraint_type = 'C'
    )
    loop
        if r_cons.search_condition not like '%IS NOT NULL' then
            dbms_output.put_line(r_cons.constraint_name||' on '||r_cons.table_name||' - '||r_cons.search_condition);
        end if;
    end loop;
end;
/

Now, you might expect to hit the same problem as we started with here, namely ORA-00932. However, running this returns :

GCI_COST_CK on GARDEN_CHECKLIST_ITEMS - cost < 50.0

PL/SQL procedure successfully completed.

So, it seems that PL/SQL happily performs an implicit conversion from LONG to VARCHAR2 or CLOB to allow the string comparison to work without error.

This works even if you declare a scalar variable of type long :

set serveroutput on size unlimited
declare
    cursor c_constraints is
    select constraint_name, table_name, search_condition
    from user_constraints
    where constraint_type = 'C';
    
    l_constraint_name user_constraints.constraint_name%type;
    l_table_name user_constraints.table_name%type;
    l_search_condition user_constraints.search_condition%type;    
begin
    open c_constraints;
    loop
        fetch c_constraints into l_constraint_name, l_table_name, l_search_condition;
        if l_search_condition not like '%IS NOT NULL' then
            dbms_output.put_line(l_constraint_name||' on '||l_table_name||' - '||l_search_condition);
        end if;
        exit when c_constraints%notfound;
    end loop;
    close c_constraints;
end;
/
Other possible solutions

There are other ways of tackling the long problem.

The ever-excellent Adrian Billington has a solution which employs DBMS_XMLGEN, as well as a custom utility, which you may well find useful.
Alternatively, you could take a look at the DBMS_METADATA_UTIL package which contains both a long to varchar2 and long to clob conversion function…although they require you to pass in the name of the base table rather than a view, and supply a rowid…all a bit of a faff for something as simple as the specific problem I’ve looked at here.

Of course, there’s always the option of an open letter to Oracle….

“Dear Larry,

I know you’re probably busy this weekend, sorting out the shed, mowing the lawn, or possibly learning your lines for the next Iron Man movie, but do you think you could take a few moments to get this LONG columns in the Data Dictionary issue sorted ?

I know it’s probably on your “To Do” list and has been for the last 15 years or so.
If it’s going to be too much trouble to solve the problem once and for all, then maybe you might consider an interim solution such as :

  • tweaking the TO_LOB function so that it works against LONG columns in a straight select statement
  • persuading DBMS_METADATA.GET_DDL to behave itself

Any assistance you could offer would be much appreciated.

Love and Kisses,

Mike “


Filed under: Oracle, PL/SQL, SQL Tagged: DBMS_METADATA.GET_DDL, DBMS_METADATA.GET_DDL and Foreign Keys, long datatype in data dictionary, ORA-00932, ORA-31603, PL/SQL implicit conversion of LONG columns, TO_LOB, user_constraints