ReCompile invalid objects using UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:

PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('APPS');
EXEC UTL_RECOMP.recomp_parallel(4, 'APPS');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'APPS');

Reference: Oracle Base

Disable interactive prompts at SQL*Plus

Whenever we have ampersand "&" in our script, SQL Plus prompts for a value to be entered. There are several ways to avoid that prompt as discussed below

1) user chr(38) instead of &

SELECT 'I love SQL '||chr(38)||' PLSQL' from dual; 

2) Use & at the end just below the single quotes

SELECT 'I love SQL &'||' PLSQL' from dual;  

3) SET DEFINE OFF can be use to disable the prompt

SET DEFINE OFF
SELECT 'I love SQL & PLSQL ' from dual; 
SET DEFINE ON

Change SQL prompt oracle SQL*Plus (pre 10g)

Here we discussed how to change prompt for release 10g and up. Now we will discuss how to achieve same in pre 10g releases

Enter following commands in glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory or execute them one by one at SQL Prompt.

col username new_value username
col dbname new_value dbname
set termout off
SELECT lower(user) username,
       substr(global_name, 1, instr(global_name, '.')-1) dbname
FROM   global_name
/
set termout on
set sqlprompt '&&username@&&dbname> '

Change SQL Prompt in oracle SQL*Plus (10g and up)

Below is an example to change the SQL*Plus prompt (10g and up), simple and yet very useful.
SET SQLPROMPT command is used to change the default SQL> prompt

1) Display username

set sqlprompt '_user>' 

2) Display database name

set sqlprompt '_connect_identifier_privilege>' 

Step 2 and 3 can be combined together to display username and database name together.
3) Display username and database name (e.g. apps@dbname> )

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"

4) To set the time at sqlprompt

set time on 

Now the best part is to avoid typing this command everytime you open a new SQL*Plus session, edit glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory as follows.

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"
set time on

Related Post: For Pre 10g Releases

Example of DBMS_XMLGEN.getxml to generate XML Tag using oracle query

Below is an example of DBMS_XMLGEN.getxml to generate XML tags directly out of the query

SELECT DBMS_XMLGEN.getxml(
'SELECT CURSOR(SELECT oha.order_number,
                     ola.ordered_item,
                     ola.ordered_quantity
                FROM ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id 
                 and oha.order_number in (&order_number) order by ola.line_id) as order_detail,
       CURSOR(SELECT ohd.name, ohs.hold_comment
                FROM ont.oe_hold_sources_all ohs,
                     ont.oe_order_holds_all ohld,
                     ont.oe_hold_definitions ohd,
                     ont.oe_order_headers_all oha,
                     ont.oe_order_lines_all ola
               WHERE oha.header_id = ola.header_id
                 AND ola.line_id = ohld.line_id 
                 and ohld.hold_release_id is null
                 AND ohld.hold_source_id = ohs.hold_source_id
                 AND ohs.hold_id =  ohd.hold_id
                 AND oha.order_number = &&order_number) as holds_detail
FROM DUAL')
FROM DUAL

Output

Insert BLOB image file in oracle database table

Here we will discuss how to insert BLOB file in the database. For this we will create a table and then a procedure that will be used to insert records in the table.

Use following script to create an employee table

CREATE TABLE SV_EMP_PHOTO
(
  ID          NUMBER(3) NOT NULL,
  PHOTO_NAME  VARCHAR2(40),
  PHOTO_RAW   BLOB,
  EMP_NAME    VARCHAR2(80)
)

Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.

Create directory SV_PHOTO_DIR as '/u002/app/applmgr/empphoto'

Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.

CREATE OR REPLACE PROCEDURE sv_load_image (
   p_id                NUMBER
 , p_emp_name     IN   VARCHAR2
 , p_photo_name   IN   VARCHAR2
)
IS
   l_source   BFILE;
   l_dest     BLOB;
   l_length   BINARY_INTEGER;
BEGIN
   l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

   INSERT INTO sv_emp_photo
               (ID
              , photo_name
              , emp_name
              , photo_raw
               )
   VALUES      (p_id
              , p_photo_name
              , p_emp_name
              , EMPTY_BLOB ()
               )
   RETURNING   photo_raw
   INTO        l_dest;

   -- lock record
   SELECT     photo_raw
   INTO       l_dest
   FROM       sv_emp_photo
   WHERE      ID = p_id AND photo_name = p_photo_name
   FOR UPDATE;

   -- open the file
   DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
   -- get length
   l_length := DBMS_LOB.getlength (l_source);
   -- read the file and store in the destination
   DBMS_LOB.loadfromfile (l_dest, l_source, l_length);

   -- update the blob field with destination
   UPDATE sv_emp_photo
   SET photo_raw = l_dest
   WHERE  ID = p_id AND photo_name = p_photo_name;

   -- close file
   DBMS_LOB.fileclose (l_source);
END --sv_load_image;
/

I have copied few .jpg images in /u002/app/applmgr/empphoto in UNIX.
Execute the procedure as follows to create record in database

exec sv_load_image(1,'Pavki','one.jpg')
exec sv_load_image(2,'Suresh','two.jpg')
exec sv_load_image(3,'Rachna','three.jpg')

Following is how data is stored in the database

NVL2 Function in Oracle

In Oracle, the NVL2 function extends the functionality found in the NVL function. It can be used to substitute a value when a null value is encountered as well as when a non-null value is encountered.

The syntax for the NVL2 function is:

    NVL2( string1, value_if_NOT_null, value_if_null )
string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.

Retrieve IP Address and Host Name usingPL/SQL

On request from one of our reader.
Using the UTL_INADDR package, a PL/SQL subprogram can determine the host name of the local system or the IP address of a given host name.

E.g. Retrieve the local host name and IP address.

SET serveroutput on
BEGIN
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);  -- get local host name
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);  -- get local IP addr
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS('www.oracle.com'));
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME('141.146.9.91')); 
END;

Reference: www.oracle.com

Printing Barcode on a zebra Printer using ZPL

Here we will discuss on how to automatically print Barcodes on a zebra printer from Oracle concurrent program.
Below are the 3 approaches that we tried and was successful with the third approach.
1) Print Labels on a Zebra printer using BI Publisher
The first approach we tried was to print barcodes using BI(formerly XML) Publisher. The report generates a PDF output and should print directly on a Zebra printer using PASTA driver. For some reason the report was not printing directly on a printer. The PDF opened in window and printed seperately/manually using Windows driver was working fine but not meeting our requirement of automatic printing. Hence we had to try second approach.
2) Print Labels using Zebra Enterprise Connector Solution
The Zebra Enterprise Connector Solution is designed to streamline the printing
process for companies using ERP systems such as Oracle BI Publisher. The Zebra
Enterprise Connector Solution helps to lower middleware costs, overhead, and
pre-printer licensing fees. It can be used with an unlimited number of
Zebra ZPL-II printers without additional per-printer licensing fees. We didnt do much research on this as were looking for an approach with minimal cost. More details on this is available at the website of Zebra
3) Print Lables on Zebra printers using Zebra Programming Language(ZPL)
The second and successful approach was to print labels using ZPL. A text output with all the zebra codes was created and which would ultimately print Barcode images on a printer. A normal regular lp command for printing text is used to print over Zebra Printers.
Below is an example of how to print Barcodes

^XA
^FO40,35^AR,10,10^FDItem :^FS
^FO40,115^AR,10,10^FDItem Description:^FS
^FO40,165^AR,10,10^FDQuantity :^FS
^FO40,245^AR,10,10^FDDiscrete Job# :^FS
^FO40,295^AR,10,10^FDSupply Sub :^FS
^FO40,375^AR,10,10^FDLocator :^FS
^FO40,455^AR,10,10^FDDestination Sub :^FS
^FO40,505^AR,10,10^FDLocator :^FS
^FO40,555^AR,10,10^FDOrder # :^FS
^FO40,635^AR,10,10^FDLine # :^FS
^FO40,715^AR,10,10^FDOrder Type :^FS
^FO40,765^AR,10,10^FDDepartment :^FS^FO305,765^AR,10,10^FDResource Group :^FS
^FO40,845^AR,10,10^FDWork Center :^FS^FO305,845^AR,10,10^FDItem Sequence :^FS
^FO40,925^AR,10,10^FDAsm Item # :^FS^FO305,925^AR,10,10^FDAsm Item Desc :^FS

^FO265,35^BY3^B3N,N,50,Y,Y^FD199395B^FS
^FO265,115^AR,10,10^FDFILTER, LUBE OIL.^FS
^FO265,165^BY3^B3N,N,55,Y,N^FD2^FS
^FO265,245^AR,10,10^FD613467^FS
^FO265,295^BY3^B3N,N,50,Y,N^FDASM^FS
^FO265,375^BY3^B3N,N,50,Y,N^FDASM.REW..^FS
^FO265,555^BY3^B3N,N,50,Y,N^FD1264076^FS
^FO265,635^BY3^B3N,N,55,Y,N^FD1^FS
^FO265,715^AR,10,10^FDMove Order^FS
^FO50,795^AR,10,10^FD6400^FS
^FO375,795^AR,10,10^FD0047^FS
^FO70,875^AR,10,10^FDASMVH12.4.5.A^FS
^FO335,875^AR,10,10^FD3^FS
^FO70,955^AR,10,10^FDL7044GSI-1021598^FS
^FO335,955^AR,10,10^FDP2212J13 L7044 GSI W/ESM & AFR^FS
^XZ

More details on how to write codes using ZPL can be found in the website of zebra
The output of the above code is shown below.

Set the Low and High Date range parameter in the concurrent program

On request to one of the reader...
Below is the scenario,
The concurrent program has 2 date parameter e.g. Start Date and End date. The requirement is that start date should always be less than end date.

Solution:
1) In the concurrent program definition parameters, select Range as Low for Start Date. See Screenshot below
2) In the concurrent program definition parameters, select Range as high for End Date. See Screenshot below. Note that the value set being used for Start date and End date should be same.

3) Test program entering incorrect value for the parameter.

4) Test again by entering correct value.

Display Negative Number in angle brackets

We often wonder and write all the codes to display negative numbers in brackets. But this can be easily achieved using a TO_CHAR function with the correct format.

TO_CHAR(number, '9999PR') -
Returns negative value in angle brackets.
Returns positive value with a leading and trailing blank.
Restriction: The PR format element can appear only in the last position of a number format model.

Below is an example to achieve this

select to_char(-133133,'99999999999PR') from dual

In above example the number is displayed in brackets without comma seperator.

Following query can be used to display negative numbers in angular bracket with comma seperator.

select to_char(-133133,'999G999G990D99PR') from dual

Keywords: angle, angular, negative, brackets

ORA-24323: Value not Allowed error in Oracle Reports

Sometimes while working in Oracle Reports whenever we are trying to modify any query in the data model
ORA-24323: Value not allowed error is raised.

This happens when we are connected to database and for some reason the connection is dropped/disconnected. When we click on file menu, we feel like the connection still exists, but in reality this is not true. To overcome this error, completely close report builder and reopen it.

Enhancing 11i/12 Homepage Menu via Firefox and Greasemonkey

Source: Gareth Robert Blog
Here is something I found very interesting and useful and thought of sharing it with others.

In oracle E-Business suite the Oracle homepage menus is not in an organized manner and hence many times we have to make use of favourite option and for easy access save frequently used ones there.

By Default the home page is displayed as


After the enhancement the menu will be displayed as


Now lets discuss how this can be achieved.
Following things should be installed
1) Firefox
2) GreaseMonkey (Firefox Addon)
3) Install the Script

Once the Add-On is installed a Monkey icon will be displayed in status bar on the right side of firefox. Right click on that and click on Manage User Scripts and add the applications URL.

Many thanks to the Author for sharing this with us.

Wish you all a Merry Christmas and Happy new year

The PL/SQL Wrap Utility - Hide source code in Oracle

A company has a code(Package, Procedure, Function etc) with all the proprietary information and logic in it. If this information is leaked out in the market then the competitors can take advantage of it and this can affect the business. One of the way to deal with this is to hide the code from others.
This can be achieved using oracle's WRAP utility. The advantage of WRAP utility is that this converts the source code into some language that is not understood but the code can still be compiled like any other source code.
Using Wrap is very simple. In the bin directory of Oracle Home, the wrap utility is installed. The file name could be WRAP.exe or WRAP80.exe depending on the oracle version installed.
Syntax

 C:\orant\BIN>wrap.exe iname=[inputfilename] oname=[outputfilename] 

e.g.

 C:\orant\BIN>wrap.exe iname=wrap_test.sql oname=wrap_test.plb 

An example of using WRAP

Create a sample procedure wrap_test using following code

CREATE OR REPLACE PROCEDURE wrap_test
IS
BEGIN
 dbms_output.put_line('Wrap test complete');
END;
/
then call the wrap utility using following
wrap.exe iname=wrap_test.sql oname=wrap_test.plb

Content of new file wrap_test.plb
CREATE OR REPLACE PROCEDURE wrap_test wrapped 
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4f 8d
LPjE3qKQyH/yQRCK4+efvSyST50wg5nnm7+fMr2ywFznKMB04yhSssvum3SLwMAy/tKGBvVS
m7JK/iiyveeysx0GMCyuJOqygaVR2+EC8XcG0wJd5GisbnfxwzIu9tHqJB/2OabWTW+0

/

It is very clear from this that the new code is not readable and so is completely hidden from others.
Drop your procedure(if already created) and recreate using the the new file wrap_test.plb which can be compiled as any other package. Important point here is that the source code will be hidden and cannot be read.
Another important point to remember is that once wrapped, a code cannot be unwrapped.

Thanks please let me know your feedback.

Splitting String using Oracle SQL 9i

Run following query to split values seperated by comma(,)

 SELECT TRIM( SUBSTR ( txt
                     , INSTR (txt, ',', 1, level ) + 1
                     , INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1 
                     ) 
            )
         AS token
   FROM ( SELECT ','||:in_string||',' AS txt  FROM dual )
 CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1

Example
-------
If value of in_string is entered as 1234,2,3,45,6,7,7,88,9,346

Output is
TOKEN
-----
1234
2
3
45
6
7
7
88
9
346

Reference:
Tom Kyte's Blog

SQL Trace and TKPROF

Understanding SQL Trace
Times when program is performing poorly, creating and examining a trace file is one of the best way to find what is causing the problem for poor performance.
Following is the list of some of the SQL Trace statictics that are generated in the trace file.

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Commits and Rollback

There are several ways by which you can turn on/off SQL Trace

Turn on
DBMS_SESSION.SET_SQL_TRACE(TRUE);
ALTER SESSION SET SQL_TRACE = TRUE;

Turn off
DBMS_SESSION.SET_SQL_TRACE(FALSE);
ALTER SESSION SET SQL_TRACE = FALSE;

Click here to set SQL Trace on a concurrent program.
The trace file is created in the udump directory.

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.

Oracle Table Rename Syntax

There is a direct command to rename table.
The syntax is as follows

alter table
   table_name
rename to
   new_table_name;

Note: When the table is renamed the referenced object like PL/SQL, applications etc are not updated. Hence this may make those invalid. However the indexes, constraints etc are updated.

Send Email through PL/SQL

The short procedure can be used to send a mail through PL/SQL.
Although there is a limitation to this as the code below does not allow you to send attachments. This can only be used to send text messages.

CREATE OR REPLACE PROCEDURE email_message (
   from_name   VARCHAR2
 , to_name     VARCHAR2
 , subject     VARCHAR2
 , MESSAGE     VARCHAR2
)
IS
   l_mailhost    VARCHAR2 (64)       := 'server.com';
   l_mail_conn   UTL_SMTP.connection;
BEGIN
   l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
   UTL_SMTP.helo (l_mail_conn, l_mailhost);
   UTL_SMTP.mail (l_mail_conn, from_name);
   UTL_SMTP.rcpt (l_mail_conn, to_name);
   UTL_SMTP.open_data (l_mail_conn);
   UTL_SMTP.write_data (l_mail_conn
                      ,    'Date: '
                        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                        || CHR (13)
                       );
   UTL_SMTP.write_data (l_mail_conn, 'From: ' || from_name || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || subject || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, 'To: ' || to_name || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, Message );

   UTL_SMTP.close_data (l_mail_conn);
   UTL_SMTP.quit (l_mail_conn);
END;
/

Emails with attachment can be done using Java as a wrapper. The metalink Note:120994.1 has a very good explanation on how to create a program that facilates sending emails with attachment.