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…
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…
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 :
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 :
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.
- saves the existing SQL*Plus session settings
- sets the environment up to display the query output
- uses an anonymous PL/SQL block evaluates the argument string passed in
- executes the query using variables declared in SQL*Plus and populated in the block
- 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 :
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.