Query to Check the PL/SQL and Oracle Database Version

It becomes a necessity to sometimes check the version of Oracle database being used or to check the version of PL/SQL being used.To check which Oracle database you are working on and also to check which PL/SQL release , the query used :

          

select  *  from  v$version ;

This query can be tested on sql*Plus , SqlDeveloper or any other query executable tools.Along the above version details you could see the CORE version , TNS version and NLSRTL version

The Complete query result would look like this :

                                                     ( click on the image to expand )

Lsnrctl : error loading shared libraries libnnzll.so

The main problem causing this error is that the SElinux on Rhel 5 is blocking the listener to start the Listener control services ( lsnrctl ) . I had encountered this problem when i was starting the database for Oracle E-Business suite applications.After doin a small research i could get  the solution to this particular problem.

Error : lsnrctl : error loading shared libraries : /directories/libnnzll.so : cannot restore segment prot after reloc : permission denied

Cause : The SElinux of redhat preventing the lsnrctl service to start

Solution : set the SElinux to Permissive and then start the lsnrctl service , this will solve the issue.

Steps : at Root , use the following commands

• getenforce 
• setenforce 0 
• getenforce 

Follow the above commands in sequence at once ,these commands change the SElinux mode to permissive . Hope this will solve the issue .

XML Importer for OA Framework ( A Simple - Easy to Edit Batch File)

As i had mentioned in the previous post about the XML importer . The easy to use and Ready batch file is given below which can be downloaded and a small modification to that file is needed for it to be used. Once this batch file is edited to the page whichever you want to import and just a Double-Click will do the trick to import the (.xml) file to MDS of database. The placing of this batch file will be discussed below .

Usage and Placing of Batch File :

Steps ,

1) Download the import.bat Batch file from the link below -

            import.bat

2) Go to jdevbin of jdeveloper extension and look for oaext directory and place the downloaded batch file in bin directory of oaext

            ie -  jdevbin/oaext/bin/BATCH_FILE_DOWNLOADED

3) Edit the following and leave the rest as it is

           right click on import.bat file and click edit , change :

•    JDEV_BIN_HOME  to your jdev_bin_home
•    JDEV_USER_HOME  to your jdev_user_home
•    In DB_CONNECTION change host , port ( if applicable ) and SID
•    PAGE2UPLOAD path of the (.xml ) file to import 
•    APPS_PASSWD your password     

4) Save the import.bat file

5) Double_Click on batch file

and thats it , simple and cool batch script which can be reused just by changing the XML file.

Your XML file will be imported to the MDS of database and follow my post : (click)  to check whether the XML file is imported or not .

A note on all the $TOP directory of Oracle apps R12

The  $TOP is the top most directory or can be said as an important directory for all oracle apps R12 .In order to use these directories through "cd" command of linux,  we must the source the Environment variables .The two important Environment variables for a developer would be :

• [sid]_[HostName].env
• APPS[SID]_(HostName).env

Where are these two files located ?
- Its located at $APPL_TOP ie at  " d01/oracle/[SID]/apps/apps_st/appl  " .

How do you source the Environment variables ?
- sourcing is done by using a dot (.) followed by and then environment variable name

example : To source VIS_localhost.env :

           

.  VIS_localhost.env

Note : don't forget to leave a space between dot (.) and env file .

A list of $TOP directories and their paths :

1) $APPL_TOP  -  d01/oracle/[SID]/apps/apps_st/appl/

2) $JAVA_TOP -  d01/oracle/[SID]/apps/apps_st/comn/java/classes/

3) $(application_short_name)_TOP  -    d01/oracle/[SID]/apps/apps_st/appl/(application_short_name)/12.0.0

example : for application_short_name PER - ( human resource)

     $PER_TOP  -  d01/oracle/[SID]/apps/apps_st/appl/per/12.0.0

Last thing to mention is about , $ORACLE_HOME ,

4) $ORACLE_HOME  -  d01/oracle/[SID]/apps/tech_st/10.1.2

Note : SID and HostName varies from system to system 

A note on jdr_utils API s of OA Framework

Jdr_utils package is most important APIs commonly used in OA Framework customisation. The most import scripts used are as follows :

• JDR_UTILS.LISTDOCUMENTS
• JDR_UTILS.PRINTDOCUMENT
• JDR_UTILS.DELETEDOCUMENT

A note on each of this  is as follows ,

1) The script which lists all the MDS documents of specified directory currently imported into database :

DECLARE 
BEGIN 
    jdr_utils.listdocuments ( '/oracle/apps/per/selfservice.webui' , TRUE ) ;
END ;

2) The script to list the Personalizations or Extensions for a specific object :

DECLARE 
BEGIN
    jdr_utils.printdocument ( ' p_document => /oracle/apps/per/selfservice.webui/ somepagePG ') ;
END ;

3) The script to delete document or personalizations/extensions :

DECLARE 
BEGIN 
    jdr_utils.deletedocument ( ' p_document => /oracle/apps/per/selfservice.webui/ somepagePG ') ;
END ;

Checking the presence of ( . xml) file in MDS of Oracle (OA Framework)

Whenever there is a new page added or any customization done to Oracle apps applications we use XML importer to import the new custom XML page to MDS of database.The XML importer Batch script to import XML files into MDS is ( here )

Assuming that you have imported the XML file ,and when you run the application the most common error would be (see the screen Shot)

                                                        ( click the image to expand )

The first thing to check here is whether you have successfully imported the XML page or not.To check this :

Run : On Sql * Plus or SqlDeveloper ,

DECLARE
BEGIN
   jdr_utils.printdocument ( p_document => ' /oracle/apps/per/selfservice/appraisals/somePG' )
END ;

The parameter to p_document is your XML path you wish to check . Few of the jdr_utils package will be discussed in coming posts.

Bounce Apache for Oracle R12 applications (Linux RHEL 5 )

Any changes made to R12 applications through Jdeveloper or professional interface such as forms interface has to be bounced . One easiest way to bounce apache is to use adapcctl script located at scripts directory of INSTANCE_TOP.

Root user and Applmgr user can run this script , for R12 the instance top directory  is located at /d01/oracle//inst/apps/_/ i.e  $INST_TOP

Bounce the Apache server :

Locate the scripts directory at $INST_TOP/admin/scripts

then ,
           

./adapcctl.sh  stopall

then,
           

./adapcctl.sh  startall

Note : Root user and Applmgr user can run this script