Random Oracle Posts

Tuesday, March 15, 2011

Multiset Union ALL operation on NESTED tables built on OBJECT Types

This blog is to share some difficulties when we try to use MULTISET UNION ALL command on NESTED TABLES
built on OBJECT TYPES.In our example the nested table test_obj_array is built on a type called test_obj.

I will then mention a small workaround which is not perfect but workable by creating a map member function

SQL> select col1 from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya

SQL> create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100));
2 /

Type created.

SQL> create type test_obj_array as table of test_obj;
2 /

Type created.

SQL> select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

It gets more complicated when we use MULTISET UNION ALL command when are dealing with NESTED TABLES built on OBJECT TYPES

SQL> DECLARE
2   x test_obj_array := test_obj_array();
3   y test_obj_array := test_obj_array();
4   z test_obj_array := test_obj_array();
5 BEGIN
6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
7     INTO x from dual;
8 y:=x;
9 z:=x multiset union all y;
10
11 END;
12 /
z:=x multiset union all y;
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2079; Type
0x87ffffffbf3eb9a0 has no MAP method.; _anon__c0000000381e5be8__AB[9, 1]]

SQL> drop type test_obj_array;

Type dropped.

SQL> drop type test_obj;

Type dropped.

create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100),map member function test_function return varchar2);
/

create type test_obj_array as table of test_obj;
/

SQL> create or replace type body test_obj
2 as
3 map member function test_function return varchar2 is
4 begin
5   return col1||col2||col3;
6 end;
7 end;
8 /

Type body created.

SQL> select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

SQL> DECLARE
2   x test_obj_array := test_obj_array();
3   y test_obj_array := test_obj_array();
4   z test_obj_array := test_obj_array();
5 BEGIN
6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
7     INTO x from dual;
8 y:=x;
9 z:=x multiset union all y;
10 for i in 1 .. z.count() loop dbms_output.put_line(z(i).col1); end loop;
11
12 END;
13 /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>

Monday, March 14, 2011

Shell Script to map datafiles on source to target filesystem before doing RMAN duplicate/restore

Many a times we DBA's are asked to refresh the DEV/QA database with PROD.
The biggest headache is rarely do the file system names match and even if they match the size varies and therefore we can rarely do a 1 to 1 mapping.

For example , a DB file /oracle/oradata01/proddb/data/users01.dbf might have to go to /oracle/oradata02 filesystem as either there might not be a filesystem called /oracle/oradata01 on the dev/qa box or it might be too small to accomodate this file after the other datafiles go there.

Therefore I have developed a utility called mapper.sh (a shell script written in ksh) which does this very nicely.

This utility has 2 arguments
Parameter 1 to shell script- Mandatory- name of the file which has information about the file system on destination server (1st field), file size in MB (2nd field) and Utilization factor in % (optional 3rd field).
For example
/oracle/oradata01/devdb/data 10000 72
/oracle/oradata02/devdb/data 5000
This means our candidates on the dev/qa box are
1./oracle/oradata01/devdb/data which has 10000M free space but we want to use only 72% of it.
2./oracle/oradata02/devdb/data which has 5000M but no utilization factor is mentioned.That means the tool will take a default utilization factor of 90% unless it is over-rided by the 2nd parameter to the shell script which is explained just below.

Parameter 2 to shell script -
this is an optional parameter which takes effect for file systems (inside the file of parameter 1) which have the 3rd field missing.For example in the above file /oracle/oradata02/devdb/data file system will use this parameter (if passed) or if not passed as 2nd argument will use the default 90% utilization factor.

Things might be a bit confusing but let us jump to the demo shown below which will make things clearer
**caution**PLEASE CHECK THIS SCRIPT IN A TEST ENV BEFORE IMPLEMENTING
At the production DB server ,login as DBA account and check the datafile sizes

SQL> select file_name||'->'||(bytes/1024/1024)bytes_in_mb from dba_data_files;

/oracle/oradata01/prodpr/data/system01.dbf->542
/oracle/oradata02/prodpr/data/undotbs01.dbf->1094
/oracle/oradata02/prodpr/data/sysaux01.dbf->500
/oracle/oradata01/prodpr/data/drsys01.dbf->1024
/oracle/oradata01/prodpr/data/maxdata01.dbf->5049.5
/oracle/oradata02/prodpr/data/users01.dbf->1024

SQL> select sum(bytes/1024/1024) from dba_data_files;

SUM(BYTES/1024/1024)
--------------------
9233.5

SQL>

Assume that we already know the destination server file systems, here is my input file to
the shell script (/tmp/destfs.txt).

contents of /tmp/destfs.txt
==================cut from below==============
/oracle/oradata01/testdv/data 100000 2
/oracle/oradata02/testdv/data 5000 80
/oracle/oradata03/testdv/data 8000
=================cut from above===============

What we are telling the tool is

1.utilize just 2% of the /oracle/oradata01/testdv/data which is almost 100G(approx)i.e use just 2G
2.use 80% of /oracle/oradata02/testdv/data i.e.use 4G out of 5G
3.Since we don't mention anything here it takes the 2nd parameter to the script (if passed) or
if nothing is passed takes default 90% of available 8G.

Demo:

homelinux<oracle>/tmp> cat /tmp/destfx.txt
/oracle/oradata01/testdv/data 100000 2
/oracle/oradata02/testdv/data 5000 80
/oracle/oradata03/testdv/data 8000

homelinux<oracle>/tmp>
homelinux<oracle>/tmp>
homelinux<oracle>/tmp>
homelinux<oracle>/tmp>
homelinux<oracle>/tmp>

Before running please make sure mapper.sh has execute privs and all the oracle env's have been set

as the below script tries to connect as sysdba using the unix account it is being run on.

therefore I recommend running this as oracle user or a unix account in DBA group.

homelinux<oracle>/tmp> ./mapper.sh /tmp/destfx.txt 87
Processing ...
...

Utlization Percentage specified by user as 87
This might change at individual file system level if specified in /tmp/destfx.txt
.....
Utilization limit for /oracle/oradata01/testdv/data is 2
....
.....
Utilization limit for /oracle/oradata02/testdv/data is 80
....
.....
Utilization limit for /oracle/oradata03/testdv/data is 87
....
Sucessfully allocated destination file system to all data files
SET NEWNAME FOR DATAFILE 5 TO '/oracle/oradata03/testdv/data/maxdata0101.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oracle/oradata03/testdv/data/undotbs0101.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/oracle/oradata02/testdv/data/users0101.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/oracle/oradata02/testdv/data/drsys0101.dbf';
SET NEWNAME FOR DATAFILE 1 TO '/oracle/oradata03/testdv/data/system0101.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/oracle/oradata02/testdv/data/sysaux0101.dbf';
****
Above information is stored in /tmp/datafile_map.log
Please refer to tables - file_placement and dest_fsize_tab for other useful information
homelinux<oracle>/tmp>

Let us see the output if the o/p file which can be copied and pasted
into our rman duplicate /restore file
homelinux<oracle>/tmp> cat /tmp/datafile_map.log
SET NEWNAME FOR DATAFILE 5 TO '/oracle/oradata03/testdv/data/maxdata0101.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oracle/oradata03/testdv/data/undotbs0101.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/oracle/oradata02/testdv/data/users0101.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/oracle/oradata02/testdv/data/drsys0101.dbf';
SET NEWNAME FOR DATAFILE 1 TO '/oracle/oradata03/testdv/data/system0101.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/oracle/oradata02/testdv/data/sysaux0101.dbf';
homelinux<oracle>/tmp>

Please note 2 tables called file_placement and dest_fsize_tab are created in the DB account
which can be dropped or left around which will be re-used the next time the script is run.

SOURCE CODE FOR MAPPER.SH

*************************************************

#/bin/ksh

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++

#+++++++ AUTHOR :GAUTHAM CHANDRASEKARAN 

#File name: mapper.sh 

#mapper.sh <file name> [ <optional utilization capacity>

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

echo "Processing ..."

echo "..."

echo

echo

if [ $# -eq 2 ] 

then

 DEF_UTIL_PCT=$2

 echo "Utlization Percentage specified by user as $DEF_UTIL_PCT"

 echo "This might change at individual file system level if specified in $1"

elif [ $# -eq 1 ]

then

 DEF_UTIL_PCT="90"

 echo "Utlization Percentage assumed as default 90%"

 echo "This might change at individual file system level if specified in $1"

else

 echo "ERROR *** Wrong number of arguments to $0"

 echo "Usage : $0 <filename>  [ <utilization_pct> ]"

 exit 1

fi

if [ ! -f "$1" ]

then

 echo "ERROR *** File $1 does not exist"

 exit 1

fi

sqlplus -s /nolog <<EOF

connect / as sysdba;

set echo off;

set feedback off;

DROP TABLE dest_fsize_tab

/

CREATE TABLE dest_fsize_tab 

(dir_name VARCHAR2(200) primary key,

full_size NUMBER(10), 

avbl_size NUMBER(10), 

utilization_pct NUMBER(3))

/

DROP TABLE file_placement

/

CREATE TABLE file_placement

(file_id NUMBER(5),

file_name VARCHAR2(50),

dir_name VARCHAR2(200),

sequence_num NUMBER(3))

/

CREATE OR REPLACE PACKAGE pk_genutilitypkg

AS

/*

     Generic String Parser: provide a delimiter and it returns an

     index-by table of the individual elements of the string that are

     separated by the specified delimiter.

     Author: "GAUTHAM CHANDRASEKARAN" <gautha@hotmail.com>

*/

    TYPE t_string IS TABLE OF VARCHAR2(2000)

       INDEX BY BINARY_INTEGER;

    m_ctr NUMBER(5);

    m_pos NUMBER(5);

    PROCEDURE sp_parsestring (

       p_string IN VARCHAR2,

       delimiter IN VARCHAR2,

       p_t_string OUT t_string);

 END pk_genutilitypkg;

/

 CREATE OR REPLACE PACKAGE BODY pk_genutilitypkg

AS

    PROCEDURE sp_parsestring (

         p_string IN VARCHAR2,

         delimiter IN VARCHAR2,

         p_t_string OUT t_string)

IS

         m_string VARCHAR2(4000);

      BEGIN

         /* Raise a Error if the length of the delimiter is not 1 */

         IF LENGTH (delimiter) != 1

         THEN

            raise_application_error (-20001,

                'Delimiter should be of only one character');

            RETURN;

         END IF;

         m_string := p_string;

         m_ctr := 1;

         LOOP

            m_pos := INSTR (m_string, delimiter);

            IF m_pos > 1

            THEN

               p_t_string (m_ctr) := SUBSTR (m_string, 1, m_pos - 1);

               IF (m_pos < LENGTH (m_string))

               THEN

                  m_string := SUBSTR (

                                 m_string,

                                 m_pos + 1,

                                 LENGTH (m_string) - m_pos

);

               ELSIF m_pos = LENGTH (m_string)

               THEN

                  m_ctr := m_ctr + 1;

                  p_t_string (m_ctr) := NULL;

                  EXIT;

               END IF;

            ELSIF m_pos = 1

            THEN

               p_t_string (m_ctr) := NULL;

               IF m_pos < LENGTH (m_string)

               THEN

                  m_string := SUBSTR (

                                 m_string,

                                 m_pos + 1,

                                 LENGTH (m_string) - m_pos

);

               ELSIF m_pos = LENGTH (m_string)

               THEN

                  m_ctr := m_ctr + 1;

                  p_t_string (m_ctr) := NULL;

                  EXIT;

               END IF;

            ELSIF m_pos = 0

            THEN

               p_t_string (m_ctr) := m_string;

               EXIT;

            END IF;

            m_ctr := m_ctr + 1;

         END LOOP;

      END;

END pk_genutilitypkg;

/

exit;

EOF

cat $1| while read INPUT_LINE

do

FIELD_CTR=`echo $INPUT_LINE|awk '{print NF}'`

if [ $FIELD_CTR -eq 3 ]

then

  DEST_NAME=`echo $INPUT_LINE|awk '{print $1}'`

  DEST_SIZE=`echo $INPUT_LINE|awk '{print $2}'`

  UTIL_PCT=`echo $INPUT_LINE|awk '{print $3}'`

  echo "....."

  echo "Utilization limit for $DEST_NAME is $UTIL_PCT"

  echo "...."

sqlplus -s /nolog<<EOF

connect / as sysdba;

set feedback off

whenever sqlerror exit sql.sqlcode;

INSERT INTO dest_fsize_tab  values ('$DEST_NAME', $DEST_SIZE,$DEST_SIZE, $UTIL_PCT);

exit;

EOF

if [ $? -ne 0 ]

then

 echo "***Error while inserting into configuration tables from $1"

 echo "Check entries in $1 : $DEST_NAME $DEST_SIZE $UTIL_PCT"

 exit 1

fi

elif [ $FIELD_CTR -eq 2 ]

then

DEST_NAME=`echo $INPUT_LINE|awk '{print $1}'`

  DEST_SIZE=`echo $INPUT_LINE|awk '{print $2}'`

  echo "....."

    echo "Utilization limit for $DEST_NAME is $DEF_UTIL_PCT"

  echo "...."

sqlplus -s /nolog<<EOF

connect / as sysdba;

set feedback off

whenever sqlerror exit sql.sqlcode;

INSERT INTO dest_fsize_tab  values ('$DEST_NAME', $DEST_SIZE,$DEST_SIZE, $DEF_UTIL_PCT);

exit;

EOF

if [ $? -ne 0 ]

then

 echo "***Error while inserting into configuration tables from $1"

 echo "Check entries in $1 : $DEST_NAME $DEST_SIZE $DEF_UTIL_PCT"

 exit 1

fi

else

 echo "Please check the contents of $1 file"

 echo "Wrong number of entries inside file"

 exit 1

fi

done

sqlplus -s /nolog <<EOF

connect / as sysdba;

set feedback off;

set serverout on

whenever sqlerror exit ;

DECLARE

x pk_genutilitypkg.t_string;

v_sequence_num file_placement.sequence_num%TYPE;

formatted_fname pk_genutilitypkg.t_string;

v_dir_name dest_fsize_tab.dir_name%TYPE ;

cursor c1

is select file_id, file_name, (bytes/1024)/1024 fsize from dba_data_files

order by fsize desc;

BEGIN

FOR c2 in c1

LOOP

   BEGIN

   SELECT t1.dir_name

   INTO v_dir_name

   FROM

   (SELECT dir_name 

   FROM   dest_fsize_tab

   WHERE  ( (avbl_size-c2.fsize)*100/full_size) >=(100-utilization_pct)

   order by avbl_size) t1

   where rownum<2 ;

   EXCEPTION

   WHEN no_data_found THEN

   raise_application_error(-20001,'No File System capable of accomodating '||c2.file_name);

   END;

   pk_genutilitypkg.sp_parsestring (c2.file_name,'/',x) ;

   pk_genutilitypkg.sp_parsestring(x(x.count),'.',formatted_fname);

   SELECT NVL(max(sequence_num),0)+1

   INTO v_sequence_num

   FROM file_placement

   WHERE file_name =formatted_fname(1);

   INSERT INTO file_placement

   VALUES

   (c2.file_id,

   formatted_fname(1),

   v_dir_name,

   v_sequence_num);

   UPDATE dest_fsize_tab

   SET  avbl_size=avbl_size-c2.fsize

   WHERE dir_name=v_dir_name;

   commit;

END LOOP;

dbms_output.put_line('Sucessfully allocated destination file system to all data files');

END;

/

whenever sqlerror exit 1;

EOF

if [ $? -ne 0 ]

then

 echo "Please allocate more space or try increasing Utilization Percentage"

 exit 1

fi

sqlplus -s /nolog<<EOF

connect / as sysdba;

set feedback off

set verify off

set pagesize 0

set heading off

spool /tmp/datafile_map.log

select 'SET NEWNAME FOR DATAFILE '||file_id||' TO '''||dir_name||'/'||file_name||trim(to_char(sequence_num,'00'))||'.dbf'';' from file_placement;

spool off

EOF

#cat /tmp/datafile_map.log

echo "****"

echo "Above information is stored in /tmp/datafile_map.log"

echo "Please refer to tables - file_placement and dest_fsize_tab for other useful information"

Break comma-separed values in a table into rows

In this blog, I am going to talk about an interesting way to split a comma-separated list into individual rows.

To achieve this consider a table called junk_tab with a column called names .

SQL> select names from junk_tab;

NAMES
--------------------------------------------------------------------------------
gautham,siddhu,surya
frank,shriram,vimal

SQL> desc junk_tab
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAMES                                              VARCHAR2(4000 CHAR)

What i intend to do is to split the individual names (delimited by comma) into separate rows.

Therefore at the end of the exercise we will have a select query returning 6 rows as
gautham
siddhu
..
..
..
vimal

6 rows selected

There are many ways to achieve this and infact there are several ways to achieve the same end result.

I am going to teach you step by step about what I wanted to do .

Step 1
======

For every row in the table I want to get the number of comma-separated values.

Here to get this the logic is subtract the length of the string after the comma's have ben stripped off from the original
length of the string .The result will give you the count of the commas.Adding 1 to it will give you the number of
comma-separated variables.

SQL> select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab;

NAMES
----------------------------------------------------------------------------------------------------
       XXX
----------
gautham,siddhu,surya
         3

frank,shriram,vimal
         3

Step 2
=======
The below sql is used for generating a sequence of number starting from 1 till what we specify.

SQL> select level num from dual connect by level <=5;

       NUM
----------
         1
         2
         3
         4
         5

In the below query, note the "with tmp as" I have used to include the above query as a temporary view.

Now do the following

SQL> with tmp as ( select level num from dual connect by level <= 100 )
2 select test123.names,tmp.num
3 from
4 (select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab) test123,tmp
5 where tmp.num <=test123.xxx
6 order by test123.names,tmp.num
7 /

NAMES
----------------------------------------------------------------------------------------------------
       NUM
----------
frank,shriram,vimal
         1

frank,shriram,vimal
         2

frank,shriram,vimal
         3

NAMES
----------------------------------------------------------------------------------------------------
       NUM
----------
gautham,siddhu,surya
         1

gautham,siddhu,surya
         2

gautham,siddhu,surya
         3

Step 3
+++++++++

This is easy as we just have to extract the 1st string from row 1, 2nd string from row 2 etc.

SQL> with tmp as ( select level num from dual connect by level <= 100 )
2 select
3 substr(test123.names ,
4    decode(tmp.num,1,1,(instr(test123.names,',',1,(tmp.num-1))+1)),
5    decode(instr(test123.names,',',1,tmp.num),0,length(test123.names)+1,instr(test123.names,',',1
,tmp.num)) - decode(tmp.num,1,1,(instr(test123.names,',',1,(tmp.num-1))+1))
6    )
7 from
8 (select names,length(names)-length(replace(names,',',''))+1 xxx from junk_tab) test123,tmp
9 where tmp.num <=test123.xxx
10 order by test123.names,tmp.num
11 /

SUBSTR(TEST123.NAMES,DECODE(TMP.NUM,1,1,(INSTR(TEST123.NAMES,',',1,(TMP.NUM-1))+1)),DECODE(INSTR(TES
----------------------------------------------------------------------------------------------------
frank
shriram
vimal
gautham
siddhu
surya

6 rows selected.

SQL>

6 rows selected.

Pipelined-Functions - Practical Use

Pipeline functions allow us to call them from a select statement and can be used to return more than 1 row.

The below example would make it more clear.

Say we have a table called employee and the fields are (or rather the most significant fields for our discussion) are

ename and dno .

SQL> select substr(ename,1,25),dno from employee;

SUBSTR(ENAME,1,25) DNO

------------------------- ----------

JACK 10

JILL 10

MASON 20

MARK 20

you are being asked to write a small pl/sql code to return the enames of a particular department, the normal way we would

do is to write a procedure like

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)

CREATE or replace procedure p1(dno_p IN number,ename_p OUT vcarray)

CURSOR c1 is select ename from employee where dno=dno_p;

i number:=1;

BEGIN

ename_p := vcarray();

for c2 in c1

loop

ename_p.extend;

ename_p(ename_p.count) :=c2.ename ;

i:=i+1;

end loop;

END;

To call the above procedure , we are forced to write a pl/sql block like

SQL> declare

2 ename vcarray ;

3 begin

4 p1(10,ename);

5 for i in 1..ename.COUNT

6 LOOP

7 dbms_output.put_line(ename(i));

8 end loop;

9 end;

10 /

JACK

JILL

PL/SQL procedure successfully completed.

So in the above example we are forced to write a pl/sql block to read from the procedure.

The below example uses pipelined functions which will make it possibel for the same

job to be done via a function (eventhough we think of function as only returning

a single value) and the icing on the cake is the function can be referenced as a simple select

from sqlplus prompt itself....

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)

CREATE OR REPLACE FUNCTION get_employees (dno_p IN number)

RETURN vcarray

PIPELINED

CURSOR c1 is select ename from employee where dno=dno_p;

BEGIN

FOR c2 in c1

LOOP

pipe row (c2.ename);

END LOOP;

RETURN;

END;

SQL> select * from TABLE (get_employees(10) );

COLUMN_VALUE

----------------------------------------------------------------------------------------------------

JACK

JILL

Sunday, March 13, 2011

Calling Shell Scripts from Oracle 10gR2

The user requirement was to run a database export on demand from the click of a web browser button and they wanted to hide the nitty gritties of the database export except

Specifying the dump file name , ftp server name etc.

We decided to use the oracle 10g feature of invoking a shell script(which does all the above tasks) from dbms_scheduler built-in package ( which has the capability to call shell scripts and can even pass

command line arguments to them).

Here is the sample code for proof of concept .

contents of /tmp/junk.sh (shell script which takes in 1 argument)

+++++++++++++++++++++++++++++++++++++++++++++++++++

#!/bin/ksh

touch /tmp/$1

contents of the stored procedure which calls dbms_scheduler

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

create or replace procedure p1(abc varchar2)

begin

DBMS_SCHEDULER.CREATE_PROGRAM (

program_name => 'TEST123',

program_type => 'EXECUTABLE',

program_action => '/tmp/junk.sh',

number_of_arguments => 1,

enabled => FALSE,

comments => 'Test Shell Script'

);

DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (

program_name => 'TEST123',

argument_position => 1,

argument_type => 'VARCHAR2'

);

DBMS_SCHEDULER.create_job(job_name => 'TEST_JOB',

program_name => 'TEST123',

start_date => sysdate,

auto_drop => FALSE,

comments => 'testing 123');

dbms_scheduler.set_job_argument_value('TEST_JOB',1,abc);

DBMS_SCHEDULER.enable(NAME => 'TEST123');

DBMS_SCHEDULER.enable(NAME => 'TEST_JOB');

DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB', use_current_session => TRUE);

DBMS_SCHEDULER.drop_job(job_name => 'TEST_JOB');

DBMS_SCHEDULER.DROP_PROGRAM (program_name => 'TEST123');

commit;

end;

By executing procedure p1 , I was able to successfully run a shell script and also pass a argument to it.

The only hitch is the shell script is run as an OS user called nobody and to change it please read the following link

http://forums.oracle.com/forums/thread.jspa?messageID=1748530

Hope this was useful to you.

Multiset Union ALL operation on NESTED tables built on OBJECT Types

This blog is to share some difficulties when we try to use MULTISET UNION ALL command on NESTED TABLES
built on OBJECT TYPES.In our example the nested table test_obj_array is built on a type called test_obj.

I will then mention a small workaround which is not perfect but workable by creating a map member function

SQL> select col1 from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya

SQL> create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100));
2 /

Type created.

SQL> create type test_obj_array as table of test_obj;
2 /

Type created.

SQL> select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

It gets more complicated when we use MULTISET UNION ALL command when are dealing with NESTED TABLES built on OBJECT TYPES

SQL> DECLARE
2   x test_obj_array := test_obj_array();
3   y test_obj_array := test_obj_array();
4   z test_obj_array := test_obj_array();
5 BEGIN
6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
7     INTO x from dual;
8 y:=x;
9 z:=x multiset union all y;
10
11 END;
12 /
z:=x multiset union all y;
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2079; Type
0x87ffffffbf3eb9a0 has no MAP method.; _anon__c0000000381e5be8__AB[9, 1]]

SQL> drop type test_obj_array;

Type dropped.

SQL> drop type test_obj;

Type dropped.

create or replace type test_obj as object(col1 varchar2(100),col2 varchar2(100), col3 varchar2(
100),map member function test_function return varchar2);
/

SQL> create or replace type body test_obj
2 as
3 map member function test_function return varchar2 is
4 begin
5   return col1||col2||col3;
6 end;
7 end;
8 /

Type body created.

create type test_obj_array as table of test_obj;
/

SQL> select cast(multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array) from du
al;

CAST(MULTISET(SELECTTEST_OBJ(COL1,COL1,COL1)FROMJUNK_TAB)ASTEST_OBJ_ARRAY)(COL1,
--------------------------------------------------------------------------------
TEST_OBJ_ARRAY(TEST_OBJ('gautham', 'gautham', 'gautham'), TEST_OBJ('siddhu', 'si
ddhu', 'siddhu'), TEST_OBJ('surya', 'surya', 'surya'))

SQL> DECLARE
2   x test_obj_array := test_obj_array();
3   y test_obj_array := test_obj_array();
4   z test_obj_array := test_obj_array();
5 BEGIN
6     SELECT cast( multiset(select test_obj(col1,col1,col1) from junk_tab) as test_obj_array )
7     INTO x from dual;
8 y:=x;
9 z:=x multiset union all y;
10 for i in 1 .. z.count() loop dbms_output.put_line(z(i).col1); end loop;
11
12 END;
13 /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>

Cast/Multiset/Multiset Union Operations using Object Types

In this blog,I have given a small example of how to transfer the contents of a table column into a nested table and then print the contents out using CAST /MULTISET operation.

Also checkout the MULTISET UNION command I have used to merge 2 nested tables.

SQL> create table junk_tab(col1 varchar2(100));

Table created.

SQL> insert into junk_tab values('&1');
Enter value for 1: gautham
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('gautham')

1 row created.

SQL> /
Enter value for 1: siddhu
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('siddhu')

1 row created.

SQL> /
Enter value for 1: surya
old   1: insert into junk_tab values('&1')
new   1: insert into junk_tab values('surya')

1 row created.

SQL> select * from junk_tab;

COL1
--------------------------------------------------------------------------------
gautham
siddhu
surya
SQL> create type string_tab as table of varchar2(100);
2 /

Type created.

SQL> desc string_tab
string_tab TABLE OF VARCHAR2(100 CHAR)
SQL> select cast(multiset(select col1 from junk_tab) as string_tab) from dual;

CAST(MULTISET(SELECTCOL1FROMJUNK_TAB)ASSTRING_TAB)
--------------------------------------------------------------------------------
STRING_TAB('gautham', 'siddhu', 'surya')

SQL> declare
2     x string_tab := string_tab();
3      y string_tab := string_tab();
4
5      z string_tab := string_tab();
6      begin
7      select cast(multiset(select col1 from junk_tab) as string_tab) into x from dual;
8      y := x;
9
10      z := x multiset union all y;
11 for i in 1 .. z.count()
12 loop
13 dbms_output.put_line(z(i));
14
15 end loop;
16 end;
17 /
gautham
siddhu
surya
gautham
siddhu
surya

PL/SQL procedure successfully completed.

One more way to use multiset union all --
SQL> declare
2     x string_tab := string_tab('gautham');
3      y string_tab := string_tab('siddhu','surya');
4
5      z string_tab := string_tab();
6      begin
7     z:=x multiset union all y;
8 for i in 1 .. z.count()
9 loop
10 dbms_output.put_line(z(i));
11
12 end loop;
13 end;
14 /
gautham
siddhu
surya

PL/SQL procedure successfully completed.

SQL>