File Handling using PL/SQL - UTL_FILE

Intention of this post is to list out different exceptions being raised while using UTL_FILE.

First create DBA Directory

File Handling Program -

DECLARE

   p_file_path     VARCHAR2 (200);

   p_file_handle   UTL_FILE.FILE_TYPE;

   p_data_file     VARCHAR2 (200);

BEGIN

   p_data_file := 'SANDEEP_UTLFILE_TEST.txt'; 

   p_file_path := 'SANDEEP_DIR'; 

  
  DBMS_OUTPUT.PUT_LINE('File Path     >>' ||p_file_path );
  DBMS_OUTPUT.PUT_LINE('File Name   >>' ||p_data_file ); 

   p_file_handle := UTL_FILE.FOPEN (p_file_path,p_data_file,'W',32767);// OPEN the FILE

 /********************************************************************************** 

   p_file_path                    >> Location where data file being placed

   p_data_file                    >> Data file name

  ‘W’                               >> Operation Mode  
                                             'W' indicates write mode , 

                                              'R' indicates read mode , 
                                              'A' indicates Append mode

   32767                           >> Length of the string. 32767 is the max length.

 **********************************************************************************/

   UTL_FILE.PUT_LINE (p_file_handle, 'Sandeep is working on UTL File Demo'); 
   // WRITE into FILE
-- UTL_FILE.FFLUSH(p_file_handle); 
-- FFLUSH will write all buffer contents, useful if contents are larger in size.

  UTL_FILE.FCLOSE(p_file_handle);// CLOSE the FILE

EXCEPTION

          WHEN  UTL_FILE.invalid_mode  THEN

            DBMS_OUTPUT.PUT_LINE('20051, Invalid Option in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_path THEN

            DBMS_OUTPUT.PUT_LINE('20052, Invalid Path in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_filehandle THEN

            DBMS_OUTPUT.PUT_LINE('20053, Invalid Filehandle in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN UTL_FILE.invalid_operation THEN

            DBMS_OUTPUT.PUT_LINE('20054, Invalid Operation in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.read_error THEN

            DBMS_OUTPUT.PUT_LINE('20055, Read Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.write_error THEN

            DBMS_OUTPUT.PUT_LINE('20056, Write Error in UTL File >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

          WHEN UTL_FILE.internal_error THEN

            DBMS_OUTPUT.PUT_LINE('20057, Internal Error in UTL File le >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

         WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('Unexpected Error occured during report generation >>' ||SQLERRM );

            UTL_FILE.FCLOSE(p_file_handle);

END;

OUTPUT – File Generated 

               

Oracle PIM - Oracle Product Information Management

Introduction
Product Information Management is an oracle product family.Oracle had launched PLM (Product Life Management) in 11, which has been upgraded as PIM with few enhanced features like document management etc in release R12. 

1- Product Information Management is another product family of Oracle Application to store iproduct (items) details
2- Its similar to Item Master of SCM family with some additional enhancements.
3- It’s self service application
5- This is EGO_TOP

Basic terminology for product information management -

Item catalog Category - ICC
- To segregate items having similar characteristic,we classify those into categories. ICC is a name to different categories created under Item catalog to grouped similar items.
- Item catalog categories provide a logical way to classify items that have similar characteristics in Item Catalog
- It is suggested that every Item should be created under an ICC

Attribute Group - AG
- Oracle provides operational attributes to define items.
- Customer may need some more information about items and that can be achieved using Attribute group.
- Create custom attribute groups and attributes under this group to support custom need.
- Assign this AG to your ICC
- Associate this AG to any custom Page 

Custom Page
- PIM Supports user defined AG and to display these UDAs on UI, It provides functionality to create custom Pages.
- Pages can be created only under any ICC
- Associate your UDAs to custom Pages

Functions
- To avoid manual entry and use data with specific pattern, functions are available in PIM
- Customer can create own functions and return value based on his logic.
- PIM supports below fields to be used as function generated: 
       1) Item Number
       2) Item Description
       3) Actions
 

Do we kNOw ????

Through this post, we highlight few points being used in oracle & application very often but still unknown....

1) ORA-0000 : normal,successful completion - For every successful transaction oracle returns ORA-0000.

2)Concurrent Programs use auto commit - If invoking same procedure from database do commit at end, to avoid different behavior as concurrent program.  

3)Change profile using FA resp - sometime few profiles need cache clear to reflect the change value. Changing  profile value using Functional Administrator responsibility does not require any cache clear.

4)Dummy Oracle Apps Link - mentioned url will launch the oracle application, create your login and do  practice oracle apps at home
  http://vis1200.solutionbeacon.net/OA_HTML/AppsLogin

DBA Activities

below are some basic and usefull dba activities for oracle application environment -

1) To bounce the OA Core service
Solution -   cd   $ADMIN_SCRIPTS_HOME  or
                   cd   $INST_TOP/admin/scripts
stop and start script adoacorectl.sh to bounce and restore oa core. run status to check the oa core status-

2) Audit Sign On
Solution - There is a system level profile "Sign-On:Audit Level". Set this profile with following values -

                 1) None

                 2) User

                 3) Responsibility

                 4) Forms

After enabling this profile table "FND_LOGIN_RESP_FORMS" will capture the log in details

3) Hide password in unix

    echo "Write down Your Name"

    stty -echo

    read v_user_name

   stty echo

   echo " "use of stty will hide the user inputon screen.

Defects Manager - XML Publisher

Using troubleshoot page, I shall summarize the list of problems faced during xml report creation and it's resolution.

1) Leading zero issue - Invoice number can have leading zeros ("000INV1").Reporting any invoice number field in excel format can not preserve the leading zeros and display only "INV1".

Solutions -  use inbuilt function fo-bidi
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?INVOICE_NO_TAG?></fo:bidi-override>
or
Open the template and put some space before invoice number tag field.
==========================================================================

2) RTF formatting issue – Any changes to border and shading is reverting back if we close the rtf and re open it.

Solution – While changing border and shading apply it to TABLE level not COLUMN level. Changes at TABLE level will be preserved. refer below screenshots - 

                         







==========================================================================
3) OPP service failure - This issue occurs during concurrent program execution and most frequently while CP is generating any xml report and program will end in warning.
Solution -  Check with DBA and ask to up the OPP service if down or check your template. there will be few garbage character available in template, which is causing the issue.(For more details check - OPP Service post)




                                             

To be continued.......

Functional Administrator Responsibility - Advantages

Oracle provides so many features in core ( oracle forms) to ease the application use in real time. Fox example lookups, profiles, functions and menus.

Below lines explain how to do all these without opening oracle forms. Oracle provides Functional Administrator responsibility (self service application) to achieve most of the core features available in oracle forms. Add this responsibility to your user.

Click on Functional Administrator Responsibility –

Lookup – Core services >> Lookups : search for a lookup. Also create a new lookup type here

FND messages – Core Services>>Messages : Search existing messages or create a new message here

Profile Categories – Core Services>>Profile Categories : Search existing profile category or create a new profile category here

Profile  – Core Services>>Profiles : Search existing profile and change values or create a new profiles here

Function – Core Services>>Function : Search existing functions or create a new function here

Menus – Core Services>>Menus : Search existing menus or create a new menu here

Cache Clear – Core Services>>Caching Framework>>Global Configuration>>

Clear All Statistics or Clear Cache

By adding Functional administrator responsibility, user does not require to open old forms application any more :) while working on self service pages.

Execute sql script in OAF

// Required import packages
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;   
import oracle.jbo.domain.Number;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//


try
   {
    Connection conn = paramOAPageContext.getApplicationModule(paramOAWebBean).getOADBTransaction().getJdbcConnection();

    String Query ="SELECT empId resultId,empName resultName,empPosition resultPos from XXXXX_TABLE WHERE DeptId=:1";
                                   
    PreparedStatement stmt = conn.prepareStatement(Query);               

    stmt.setInt(1, departmentId);                                       

                                   
    for (ResultSet resultset = stmt.executeQuery(); resultset.next(); )
    {   
        int employeeId = resultset.getInt("resultId");
        String employeeName  = resultset.getstring("resultName");
        String employeePosition = resultset.getString("resultPos");
    }

    stmt.close();
                                              
   }catch(SQLException sqle)
   {
       throw new OAException("Exception Block"+sqle);
   }

PL/SQL Program unit call from OAF

Writing bigger program logic in controller will be bit complex, To resolve such cases write logic in PL/SQL program units and invoke this from OAF pages.In next lines I am writing the code to call pl/sql in oaf pages.


// Required import packages
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;   
import oracle.jdbc.OracleCallableStatement;
import oracle.jbo.domain.Number;
//

OAApplicationModule localOAApplicationModule = paramOAPageContext.getApplicationModule(paramOAWebBean);
OADBTransaction oadbtransaction =
(OADBTransaction)((OAApplicationModuleImpl)localOAApplicationModule).getDBTransaction();
String callSQLPackage = "begin XXXXX_CUSTOM_PACKAGE.XXXXX_CUSTOM_PROCEDURE(:1,:2,:3,:4,:5);" + " end;";
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(callSQLPackage,1);
try
{
      // Set IN Parameters               
      oraclecallablestatement.setString(1,in1Parameter);                               
      oraclecallablestatement.setString(2,in2Parameter);
      
      // Set OUT Parameters
      oraclecallablestatement.registerOutParameter(3,Types.INTEGER);             
      oraclecallablestatement.registerOutParameter(4,Types.INTEGER);             
      oraclecallablestatement.registerOutParameter(5,Types.VARCHAR,0,2000);
                 
      oraclecallablestatement.execute();
 
      // Extract the OUT parameter value
    int out1Param = oraclecallablestatement.getInt(3); 
    int out2Param = oraclecallablestatement.getInt(4);
    String out3Param = oraclecallablestatement.getString(5);
          
 }catch(SQLException sqle)
 {
    throw new OAException("Exception Block"+sqle);
 }

Import OAF Personalization

OAF provides 2 java program to import any personalization. This is equivalent to FNDLOAD command in oracle apps to handle any LDT Files.These java programs used to import XML and JPX files.

XML Files >> OAF personalization can be retrieve as XML files 

JPX Files >> Substitution definition stored in JPX files. 

Use below command to import OAF personalization -

 

>java oracle.jrad.tools.xml.importer.JPXImporter JpxFileName  DBConnection

>java oracle.jrad.tools.xml.importer.XMLImporter  XmlFileName DBConnection -rootdir CustomPersonalizationFolder -rootPackage StandardPersonalizationFolder

DBConnection 

  -username $APPS_USER -password  $APPS_USER_PASSWORD -dbconnection "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=$DB_HOST_NAME)(Port=$PORT_NUMBER))(CONNECT_DATA=(SID=$SID)))"

StandardPersonalizationFolder

/oracle/apps/icx/por/approval/webui/customizations/site/0
 

CustomPersonalizationFolde

$JAVA_TOP/xxxxx/oracle/apps/icx/por/approval/webui/customizations/site/0

VO Extention

View Object Exception -

VO extension is very useful, when need to add a new item to a region. Extend the VO and include the respective column in sql statement. Below are the steps need to follow in order to extend any VO.

1) Go to About This page and check the VO name being used for that region

2) FTP to JAVA_TOP to download the VO and dependent files.

3) De compile the class files and generate the source code

4) Create the same file structure in my projects under Jdeveloper folder

5) Create a new View Object in same structure under your custom path

6)  Include the new column in SQL query  and finish

7) Open the project properties and click on BC4J component

8) Select substitution and choose standard view as source and custom extended view as substitute

9) Project_Name.jpx file will be generated in myprojects

10) FTP back new extended view and subsequent class files to correct location on server

11) Also place the server.xml file

12) Run the JPXimporter

13) Go to Application and personalize the region

14) Create new item with viewSource = Standard VO Name and viewAttribute= newly added column name

Example -

Requirement – Introduce a new column in My Requisition Table Region to display the Item number in comma separated concatenated format  This can be achieved by extending underlying VO, Below are the steps.

Write a program unit to get required values. Input parameter must be in existing VO columns.

CREATE OR REPLACE FUNCTION APPS.Sandeep_Test_prog(param IN VARCHAR2)  
 RETURN VARCHAR2 AS

         CURSOR cur_item_desc(p_segment1 IN VARCHAR2) IS
         SELECT NVL(item_description,'Sandeep') item_desc
         FROM po_requisition_lines_all a,po_requisition_headers_all b
         WHERE a.requisition_header_id = b.requisition_header_id
         AND b.segment1=p_segment1;

         CURSOR cur_item_number(p_description IN VARCHAR2) IS
         SELECT DISTINCT NVL(segment1, 'Sandeep') itemNumber
         FROM mtl_system_items_b
         WHERE UPPER (description) = UPPER (p_description);

           p_item_number VARCHAR2 (100) := null;
 

BEGIN
        FOR c1 in cur_item_desc(param)
        LOOP
                   dbms_output.put_line('Outer Loop>>'||c1.item_desc);
                    FOR c2 in cur_item_number(c1.item_desc)
                    LOOP
                               dbms_output.put_line('Inner Loop >>'||c2.itemNumber);
                               IF c2.itemNumber<>'Sandeep' THEN
                                    IF p_item_number IS NULL THEN
                                              p_item_number := p_item_number ||c2.itemNumber ; 

                                    ELSE
                                              p_item_number := p_item_number||','|| c2.itemNumber ;
                                    END IF;
                               ELSE
                                    p_item_number := p_item_number ||',' ;
                               END IF;

                      END LOOP;
             END LOOP;

        RETURN p_item_number;
EXCEPTION
         WHEN OTHERS  THEN
             DBMS_OUTPUT.put_line ('Error');
             RETURN 'Sandeep NULL';
END;

Check the VO name required to be extended.Click on About This Page

VO being used for these beans values is MyReqsGlanceVO

Check the full path in Business Component References Details

oracle.apps.icx.por.reqmgmt.server.MyReqsGlanceVO

 

FTP VO and respective VOImpl and VORowImpl class files. Create a similar package structure in JDev myprojects forlder and place the all files and create a new view object.

Modify the SQL and include custom column

 Some time you may encounter below error message . To avoid it keep sql query as original and press next till last page. Then come back to SQL page and do the your changes and then press next. It won’t come again.

Press next till reach below page and click the checkbox Generate Java file for ViewRowClass and finish.

This will generate VOImpl and VORowImpl java files also. Now open the Project Properties.

Business Components  >> Substitutions >> Available ( Choose the standard VO ) >> Substitute (Pick the extended VO).

If this is new substitution, ADD button will be enabled else UPDATE.

This will generate a OAProject10.jpx and server.xml files. FTP all files at server and run JPX importer to upload the import.

Now Go to Application

Click on Personalize “This Table display a list…..” link

Click create Item icon for Advanced Table

Give the ID Name and Apply – It will create a new column in Advanced Table

Now click new Item icon for TestColumn – It will create a new item for that column. Also create a new item in columnHeader and give a prompt there, to display the column title.

Select the Item style and give the name of item.View Instance hold the name of standard VO and view attribute is custom column name included by us.

Now return to application

New column ColumnHeader holding Item number appears.