Follow me on Twitter
iTerra Consulting Inc. released Migration LogiK™. (Check the video tour)It is a highly efficient and interactve GUI for FNDLOAD. It is located at Migration LogiK™ site. Download is free. 30 days trial period. This tool allows you to download/upload AOL objects definitions in seconds. No need to browse System Admin guide for FNDLOAD syntax or going through Oracle blogs. Just choose your objects and click download. Recent addition to the app functionality is the ability to identify all the FND objects, created by a certain user and then download it if needed. It is simple for understanding and learning curve is minimal. Good luck trying.
Friday, November 20, 2009
Thursday, October 1, 2009
Java stored procedures in PL/SQL. Reading files.
This post is to demonstrate how to create Java stored procedure in oracle and how to read files from certain directory on DB server. PL/SQL is restricted to read files only from the directories specified in DBA_DIRECTORIES table or in utl_file_dir database parameter. Java store proc on the contrary has the ability to read file from any readable location.
Step 1. Create Java source and compile.
create or replace and compile java source named ReadFileJava as
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
public class readFileJava
{
public static String returnFile(String fileName) throws IOException{
File file = new File(fileName);
FileReader fis = null;
BufferedReader bis = null;
String fileString="";
String out="";
try {
fis = new FileReader(file);
// Here BufferedInputStream is added for fast reading.
bis = new BufferedReader(fis);
// dis.available() returns 0 if the file does not have more lines.
while ((out = bis.readLine())!=null) {
fileString+=out+"\n";
}
}
catch (IOException ioe) { System.out.println("No file");}
bis.close();
fis.close();
return fileString;
}
}
Step 2.Associate Java source with PL/SQL function.
CREATE OR REPLACE FUNCTION readfileJava(fileName VARCHAR2) RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'readFileJava.returnFile(java.lang.String) return java.lang.String';
Step 3. Use it.
----------------------<
declare
b CLOB;
begin
select ReadFileJava('/usr/tmp/l0347291.tmp') into b from dual;
dbms_output.put_line(length(b));
dbms_output.put_line(substr(b,1,200));
End;
----------------------->
Step 1. Create Java source and compile.
create or replace and compile java source named ReadFileJava as
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
public class readFileJava
{
public static String returnFile(String fileName) throws IOException{
File file = new File(fileName);
FileReader fis = null;
BufferedReader bis = null;
String fileString="";
String out="";
try {
fis = new FileReader(file);
// Here BufferedInputStream is added for fast reading.
bis = new BufferedReader(fis);
// dis.available() returns 0 if the file does not have more lines.
while ((out = bis.readLine())!=null) {
fileString+=out+"\n";
}
}
catch (IOException ioe) { System.out.println("No file");}
bis.close();
fis.close();
return fileString;
}
}
Step 2.Associate Java source with PL/SQL function.
CREATE OR REPLACE FUNCTION readfileJava(fileName VARCHAR2) RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'readFileJava.returnFile(java.lang.String) return java.lang.String';
Step 3. Use it.
----------------------<
declare
b CLOB;
begin
select ReadFileJava('/usr/tmp/l0347291.tmp') into b from dual;
dbms_output.put_line(length(b));
dbms_output.put_line(substr(b,1,200));
End;
----------------------->
Friday, August 14, 2009
FNDLOAD REQUEST GROUP
Alternative GUI for FNDLOAD - watch video
FNDLOAD syntax for downloading REQUEST GROUP somehow didn't work for me with REQUEST_GROUP_NAME parameter. It was much more success with REQUEST_GROUP_CODE.
REQUEST_GROUP_CODE is all capital no spaces internal name.
Final syntax is:
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct output-file REQUEST_GROUP REQUEST_GROUP_CODE=YOURREQUESTGROUPCODE
Check the video tour for Migration LogiK(TM) FNDLOAD GUI
FNDLOAD syntax for downloading REQUEST GROUP somehow didn't work for me with REQUEST_GROUP_NAME parameter. It was much more success with REQUEST_GROUP_CODE.
REQUEST_GROUP_CODE is all capital no spaces internal name.
Final syntax is:
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct output-file REQUEST_GROUP REQUEST_GROUP_CODE=YOURREQUESTGROUPCODE
Check the video tour for Migration LogiK(TM) FNDLOAD GUI
Thursday, July 16, 2009
Select Multiple rows from DUAL
Sometimes Oracle developer needs to present multiple values by selecting multiple rows. Using one of the existing tables can do the trick, but it can be resource consuming. Example below shows how to do the same using DUAL with its single row:
select 'x',level from dual a
connect by level < 10
Just change 10 to whatever number of rows you need.
select 'x',level from dual a
connect by level < 10
Just change 10 to whatever number of rows you need.
Wednesday, June 3, 2009
FNDLOAD solution for CONCURRENT MANAGERS and QUEUES
Tired of using FNDLOAD in command mode? Time to switch to GUI.
Check the video tour for Migration LogiK(TM) FNDLOAD GUI
FNDLOAD also can be used to DOWNLOAD/UPLOAD Concurrent Managers definitions. If parameter value is "%", then all concurrent managers will be processed. Otherwise just specify the one you need to migrate.
Below is the syntax example:
to DOWNLOAD:
FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpque.lct your-output.ldt QUEUE APPLICATION_SHORT_NAME=[] CONCURRENT_MANAGER_QUEUE=[]
*) Replace [] with the actual value or % ( wild card )
to UPLOAD:
FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpque.lct output.ldt
Configuration file delivered by Oracle doesn't update INCLUDE_FLAG field.
You might need to create your own version of the configuration file by adding INCLUDE_FLAG in the "update" statement of "UPLOAD" section.
Check the video tour for Migration LogiK(TM) FNDLOAD GUI
Check the video tour for Migration LogiK(TM) FNDLOAD GUI
FNDLOAD also can be used to DOWNLOAD/UPLOAD Concurrent Managers definitions. If parameter value is "%", then all concurrent managers will be processed. Otherwise just specify the one you need to migrate.
Below is the syntax example:
to DOWNLOAD:
FNDLOAD apps/pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpque.lct your-output.ldt QUEUE APPLICATION_SHORT_NAME=[] CONCURRENT_MANAGER_QUEUE=[]
*) Replace [] with the actual value or % ( wild card )
to UPLOAD:
FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpque.lct output.ldt
Configuration file delivered by Oracle doesn't update INCLUDE_FLAG field.
You might need to create your own version of the configuration file by adding INCLUDE_FLAG in the "update" statement of "UPLOAD" section.
Check the video tour for Migration LogiK(TM) FNDLOAD GUI
Sunday, April 26, 2009
Javascript - find object position
Determining DOM object position:
It is one of the AJAX actions when you are trying to attach drop down selection to the existing form field.
Script below helps to identify these coordinates.
obj - is DOM object ( for example some <input type=text name=selectme id=selectme>...</input>
We assume that field height is 20 pixels. Function returns array of X,Y representing left lower corner position of the input field.
//---------------------------------------------------------------------------
function findPos(obj) {
var curleft = curtop = 0;
if (obj.offsetParent) {
curleft = obj.offsetLeft
curtop = obj.offsetTop+20;
while (obj = obj.offsetParent) {
curleft += obj.offsetLeft;
// curleft += 1;
curtop += obj.offsetTop;
}
}
return [curleft,curtop];
}
//-----------------------------------------------------
Example: Dynamically build list of US States.
//-------------------------------------------
// This function is called when user keys in state name or abbreviation
// External php script named getStates.php contains query and
// dynamic generation of the drop down selection. See below
function ajaxStates(obj) {
var state;
var s=obj.value;
var url="getStates.php?keys="+encodeURI(s)+"&addr="+encodeURI(obj.name);
executeXMLHttpRequestStates(url,obj);
}
//-------------------------END ---------------------------------
//-----------getStates.php--------------------------------------
session_start();
$sess=session_name().'='.session_id();
require('config.php'); // contains dbConnection
require(SOME_PATH.'/vendors.php');
$aKeys=$_GET['keys'];
$Elem=$_GET['addr'];
$QBEtrans = new vendors();
$records=$QBEtrans->getStates($aKeys)->getRecords();
$cr=count($records)+1;
echo '';
?>
//-- Query from vendors.php --
#----------------------------------------------------------
protected function pGetStates($key) {
// Get DB Connection from the factory
$dbh = $this->getDBConnection();
$key=$key.'%';
$sql ="select nick,name from states where name like upper(:key) order by name";
$psBindVars = array();
$psBindVars[':key'] =strtoupper($key);
// Prepare starement
$stmt = $dbh->prepare($sql);
$stmt->execute($psBindVars);
// fetch the record
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = null;
$dbh = null;
include_once CLASSPATH.'/ReadOnlyResultSet.php';
return (new ReadOnlyResultSet($records));
}
}
//----------------------------
//----- ReadOnlyResultSet.php -----
class ReadOnlyResultSet {
// This member variable will hold the array of the returned records
private $records;
// transform the PDO::PDOStatement to the array, and assign it
// to the local variable
function __construct($records)
{
if (count($records)>0) {
$this->records = $records;
reset($this->records);
}
}
function getRecords(){
return $this->records;
}
// Receives an instance of the DataObject we're working on
// returns the next element from array
function getNext($dataobject) {
$row = current($this->records);
// Use reflection to fetch the DO's field names
$class = new ReflectionObject($dataobject);
$properties = $class->getProperties();
// Loop through the properties to set them from the current row
for ($i = 0; $i < count($properties); $i++) {
$prop_name = $properties[$i]->getName();
$dataobject->$prop_name = $row[$prop_name];
}
next($this->records);
return $dataobject;
}
// Move the pointer back to the beginning of the result set
function reset() {
$this->records->reset();
}
// Return the number of rows
function rowCount() {
return count($this->records);
}
public function sort($key)
{
for ($i = 0; $i < sizeof($this->records); $i++) {
$sort_values[$i] = $this->records[$i][$key];
}
asort ($sort_values);
reset ($sort_values);
while (list ($arr_key, $arr_val) = each ($sort_values)) {
$sorted_arr[] = $this->records[$arr_key];
}
$this->records = $sorted_arr;
reset($this->records);
}
}
?>
//---------------------------------
I hope this is enough for you to get work started.
It is one of the AJAX actions when you are trying to attach drop down selection to the existing form field.
Script below helps to identify these coordinates.
obj - is DOM object ( for example some <input type=text name=selectme id=selectme>...</input>
We assume that field height is 20 pixels. Function returns array of X,Y representing left lower corner position of the input field.
//---------------------------------------------------------------------------
function findPos(obj) {
var curleft = curtop = 0;
if (obj.offsetParent) {
curleft = obj.offsetLeft
curtop = obj.offsetTop+20;
while (obj = obj.offsetParent) {
curleft += obj.offsetLeft;
// curleft += 1;
curtop += obj.offsetTop;
}
}
return [curleft,curtop];
}
//-----------------------------------------------------
Example: Dynamically build list of US States.
//-------------------------------------------
// This function is called when user keys in state name or abbreviation
// External php script named getStates.php contains query and
// dynamic generation of the drop down selection. See below
function ajaxStates(obj) {
var state;
var s=obj.value;
var url="getStates.php?keys="+encodeURI(s)+"&addr="+encodeURI(obj.name);
executeXMLHttpRequestStates(url,obj);
}
//-------------------------END ---------------------------------
//-----------getStates.php--------------------------------------
session_start();
$sess=session_name().'='.session_id();
require('config.php'); // contains dbConnection
require(SOME_PATH.'/vendors.php');
$aKeys=$_GET['keys'];
$Elem=$_GET['addr'];
$QBEtrans = new vendors();
$records=$QBEtrans->getStates($aKeys)->getRecords();
$cr=count($records)+1;
echo '';
?>
//-- Query from vendors.php --
#----------------------------------------------------------
protected function pGetStates($key) {
// Get DB Connection from the factory
$dbh = $this->getDBConnection();
$key=$key.'%';
$sql ="select nick,name from states where name like upper(:key) order by name";
$psBindVars = array();
$psBindVars[':key'] =strtoupper($key);
// Prepare starement
$stmt = $dbh->prepare($sql);
$stmt->execute($psBindVars);
// fetch the record
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = null;
$dbh = null;
include_once CLASSPATH.'/ReadOnlyResultSet.php';
return (new ReadOnlyResultSet($records));
}
}
//----------------------------
//----- ReadOnlyResultSet.php -----
class ReadOnlyResultSet {
// This member variable will hold the array of the returned records
private $records;
// transform the PDO::PDOStatement to the array, and assign it
// to the local variable
function __construct($records)
{
if (count($records)>0) {
$this->records = $records;
reset($this->records);
}
}
function getRecords(){
return $this->records;
}
// Receives an instance of the DataObject we're working on
// returns the next element from array
function getNext($dataobject) {
$row = current($this->records);
// Use reflection to fetch the DO's field names
$class = new ReflectionObject($dataobject);
$properties = $class->getProperties();
// Loop through the properties to set them from the current row
for ($i = 0; $i < count($properties); $i++) {
$prop_name = $properties[$i]->getName();
$dataobject->$prop_name = $row[$prop_name];
}
next($this->records);
return $dataobject;
}
// Move the pointer back to the beginning of the result set
function reset() {
$this->records->reset();
}
// Return the number of rows
function rowCount() {
return count($this->records);
}
public function sort($key)
{
for ($i = 0; $i < sizeof($this->records); $i++) {
$sort_values[$i] = $this->records[$i][$key];
}
asort ($sort_values);
reset ($sort_values);
while (list ($arr_key, $arr_val) = each ($sort_values)) {
$sorted_arr[] = $this->records[$arr_key];
}
$this->records = $sorted_arr;
reset($this->records);
}
}
?>
//---------------------------------
I hope this is enough for you to get work started.
Tuesday, April 14, 2009
UNIX Shell substrings. Cut last substring.
To extract file name from the absolute file path:
Assuming that DIR is something like /u01/oracle/apps/appl/bin/myFile.sh
Then
basename $DIR - gives you the file name. It returns myFile.sh
If you need to return path without the file name :echo $DIR sed 's/`basename $DIR`//'
If you need to return the directory name prior to the file name Then:
echo $DIR | awk -F/ '{print $(NF-1)}'
NF - is built-in AWK variable indicating the total number of fields.
NF-1 - is the field preceeding the last field.
-F - separator indicator - in this case it is /
-f source-file - awk option indicating that source file is found in source-file.
Another built-in variables"
FILENAME - file name which awk is currently reading
FNR - is current record number awk is processing in the current file
NR - is the number of records awk processed so far
Assuming that DIR is something like /u01/oracle/apps/appl/bin/myFile.sh
Then
basename $DIR - gives you the file name. It returns myFile.sh
If you need to return path without the file name :echo $DIR sed 's/`basename $DIR`//'
If you need to return the directory name prior to the file name Then:
echo $DIR | awk -F/ '{print $(NF-1)}'
NF - is built-in AWK variable indicating the total number of fields.
NF-1 - is the field preceeding the last field.
-F - separator indicator - in this case it is /
-f source-file - awk option indicating that source file is found in source-file.
Another built-in variables"
FILENAME - file name which awk is currently reading
FNR - is current record number awk is processing in the current file
NR - is the number of records awk processed so far
Sunday, April 12, 2009
Oracle APPS FNDLOAD batch solution for UPLOAD
Check video of alternative GUI ( finally arrived) for FNDLOAD.
With all the examples I've seen on Oracle FNDLOAD, I never ran across some sort of batch solution. I built one and tested on the few objects and it worked just fine. I wasn't able to test all of the objects UPLOAD.
Below is the source and sequence of the actions.
Step 1. Build your list of the objects needed to be migrated.
Follow the file layout below. Keep in mind that there are always 3 fields. (Put 'X' if there is no application associated with the AOL object):
# OBJECT_TYPE_IDENTIFICATOR,OBJECT_SHORT_NAME,APPLICATION_SHORT_NAME
# Below the example of the objects load list:
#
# CP - concurrent program, PO - profile option, USER - user etc...
# Enclose the object name in double quotes if its name contains spaces or commas..
# Example of the file layout
CP,CONC_PROG_SHORT_NAME,APP_SHORT_NAME
USER,USERNAME,FND
PO,PROFILE_OPTION_NAME,FND
Step 2. Save this file - for example as loadList on the server.
Step 3. Copy shell script from the source below and save it on the server as batchFNDLOAD_U.sh - this one for UPLOAD
#!/bin/ksh
# . batchFNDLOAD_U loadList appsPassword
output_dir='apps_objects_download_dir'
login=apps/$2
action='UPLOAD'
while read line
do
type=`echo $line cut -f1 -d,`
type=`echo $type tr [a-z] [A-Z]`
object=`echo $line cut -f2 -d,`
app=`echo $line cut -f3 -d,`
case $1 in
HELP)
echo ' Use the following format: MyFNDLOAD [helpRSCP] [D,U] output_file application_short entity_short'
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookups';
echo ' Example - MyFNDLOAD_U MN D save_as_file_name short_application_name apps_object_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO D save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO U save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U MN U MYMENU FND MENU_SHORT - to upload menu';
;;
RSET)
# FNDLOAD Upload Request Set Syntax
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object.ldt
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt
;;
CP)
# FNDLOAD UploadConcurrent Program Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt
;;
PO)
# FNDLOAD Upload Profile Option Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt
;;
VS)
# FNDLOAD Upload Value Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
VSV)
# FNDLOAD Upload Value Set With Values Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
RSP)
# FNDLOAD Upload Responsibility Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt
;;
MN)
# FNDLOAD Upload Menu Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt
;;
RG)
# FNDLOAD Upload Request Group Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt
;;
LKP)
# FNDLOAD Upload Lookup Type Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt
;;
DFF)
# FNDLOAD Upload Descriptive Flex Field Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
MSG)
# FNDLOAD Upload FND Message Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt
;;
USER)
# FNDLOAD Upload APPS User Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt
;;
*) ;;
esac
# keep the line below if you wish to overwrite the owner of the object to ANONYMOUS.
# This is not really what Oracle recommends, but just in case you need to know if #this has been done via batched FNDLOAD. Choose appropriate existing USER name.
cat $output_dir/$object.ldt sed 's/OWNER.*=.*\".*\"/OWNER = \"ANONYMOUS\"/' > xx
mv xx $output_dir/$object.ldt
done < $1 # the end Step 4. Execute the script : batchFNDLOAD_U loadList appsPassword.
All objects will be processed and migrated in a single run.
It is small, but improvement.
Don't forget to verify that all just created .ldt files correctly downloaded the designated objects.
Every file, which ends with # -- End Entity Definitions --
failed downloading the object definition. Most likely due to incorrect object name provided in the loadList file.
With all the examples I've seen on Oracle FNDLOAD, I never ran across some sort of batch solution. I built one and tested on the few objects and it worked just fine. I wasn't able to test all of the objects UPLOAD.
Below is the source and sequence of the actions.
Step 1. Build your list of the objects needed to be migrated.
Follow the file layout below. Keep in mind that there are always 3 fields. (Put 'X' if there is no application associated with the AOL object):
# OBJECT_TYPE_IDENTIFICATOR,OBJECT_SHORT_NAME,APPLICATION_SHORT_NAME
# Below the example of the objects load list:
#
# CP - concurrent program, PO - profile option, USER - user etc...
# Enclose the object name in double quotes if its name contains spaces or commas..
# Example of the file layout
CP,CONC_PROG_SHORT_NAME,APP_SHORT_NAME
USER,USERNAME,FND
PO,PROFILE_OPTION_NAME,FND
Step 2. Save this file - for example as loadList on the server.
Step 3. Copy shell script from the source below and save it on the server as batchFNDLOAD_U.sh - this one for UPLOAD
#!/bin/ksh
# . batchFNDLOAD_U loadList appsPassword
output_dir='apps_objects_download_dir'
login=apps/$2
action='UPLOAD'
while read line
do
type=`echo $line cut -f1 -d,`
type=`echo $type tr [a-z] [A-Z]`
object=`echo $line cut -f2 -d,`
app=`echo $line cut -f3 -d,`
case $1 in
HELP)
echo ' Use the following format: MyFNDLOAD [helpRSCP] [D,U] output_file application_short entity_short'
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookups';
echo ' Example - MyFNDLOAD_U MN D save_as_file_name short_application_name apps_object_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO D save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO U save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U MN U MYMENU FND MENU_SHORT - to upload menu';
;;
RSET)
# FNDLOAD Upload Request Set Syntax
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object.ldt
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt
;;
CP)
# FNDLOAD UploadConcurrent Program Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt
;;
PO)
# FNDLOAD Upload Profile Option Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt
;;
VS)
# FNDLOAD Upload Value Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
VSV)
# FNDLOAD Upload Value Set With Values Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
RSP)
# FNDLOAD Upload Responsibility Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt
;;
MN)
# FNDLOAD Upload Menu Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt
;;
RG)
# FNDLOAD Upload Request Group Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt
;;
LKP)
# FNDLOAD Upload Lookup Type Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt
;;
DFF)
# FNDLOAD Upload Descriptive Flex Field Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt
;;
MSG)
# FNDLOAD Upload FND Message Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt
;;
USER)
# FNDLOAD Upload APPS User Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt
;;
*) ;;
esac
# keep the line below if you wish to overwrite the owner of the object to ANONYMOUS.
# This is not really what Oracle recommends, but just in case you need to know if #this has been done via batched FNDLOAD. Choose appropriate existing USER name.
cat $output_dir/$object.ldt sed 's/OWNER.*=.*\".*\"/OWNER = \"ANONYMOUS\"/' > xx
mv xx $output_dir/$object.ldt
done < $1 # the end Step 4. Execute the script : batchFNDLOAD_U loadList appsPassword.
All objects will be processed and migrated in a single run.
It is small, but improvement.
Don't forget to verify that all just created .ldt files correctly downloaded the designated objects.
Every file, which ends with # -- End Entity Definitions --
failed downloading the object definition. Most likely due to incorrect object name provided in the loadList file.
Friday, April 10, 2009
Find Request Group, Responsibility based on concurrent program name,executable name.
Sometimes I ran into task to identify Oracle APPS responsibility or request group to which certain concurrent program is attached. Going through Oracle APPS forms is not really a straight path.
Below are few SQL scripts helping to identify that.
Find concurrent programs attached to certain responsibility.
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'YOUR RESPONSIBILITY KEY')
))
Find all responsibilities and concurrent program names based on the executable Name
select --r.rowid rrowid,p.rowid prowid,e.rowid erowid,fcp.rowid frowid,
e.executable_name,responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u,
fnd_executables e,
fnd_concurrent_programs fcp
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and executable_name like '%SEARCH EXECUTABLE PATTERN%'
and fcp.executable_id = e.executable_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
and fcp.concurrent_program_id = p.concurrent_program_id
Find all responsibilities having the "search concurrent program" attached to the request group
------->
select responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
---------------<
Find concurrent Program Names based on Responsibility Key Value
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'Your Responsibility Key Search Pattern')
))
Below are few SQL scripts helping to identify that.
Find concurrent programs attached to certain responsibility.
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'YOUR RESPONSIBILITY KEY')
))
Find all responsibilities and concurrent program names based on the executable Name
select --r.rowid rrowid,p.rowid prowid,e.rowid erowid,fcp.rowid frowid,
e.executable_name,responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u,
fnd_executables e,
fnd_concurrent_programs fcp
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and executable_name like '%SEARCH EXECUTABLE PATTERN%'
and fcp.executable_id = e.executable_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
and fcp.concurrent_program_id = p.concurrent_program_id
Find all responsibilities having the "search concurrent program" attached to the request group
------->
select responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
---------------<
Find concurrent Program Names based on Responsibility Key Value
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'Your Responsibility Key Search Pattern')
))
Wednesday, April 8, 2009
The Idiot test.
I grabbed this "The Idiot Test" from Bex Huff personal page.
Considering that I'm color blind, it was some sort of challenge. On top of everything it helps to understand the beauty of Oracle FNDLOAD automation and packaging and keep your mind fresh.
Don't get addicted.
Considering that I'm color blind, it was some sort of challenge. On top of everything it helps to understand the beauty of Oracle FNDLOAD automation and packaging and keep your mind fresh.
Don't get addicted.
Batch Oracle FNDLOAD solution for DOWNLOAD
With all the examples I've seen on Oracle FNDLOAD, I never ran across some sort of packaged solution. I built one and tested on the few objects and it worked just fine.
As an alternative Check the video tour for Migration LogiK(TM) FNDLOAD GUI
Below is the source and sequence of the actions.
Step 1. Build your list of the objects to needed to be migrated.
Follow the layout. Keep in mind that there are always 3 fields. (Put 'X' if there no application associated with the object):
# OBJECT_TYPE_IDENTIFICATOR,OBJECT_SHORT_NAME,APPLICATION_SHORT_NAME
# Below the example of the objects load list:
#
# CP - concurrent program, PO - profile option, USER - user etc...
# Enclose the object name in double quotes if its name contains spaces, commas #etc...
CP,CONC_PROG_SHORT_NAME,APP_SHORT_NAME
USER,USERNAME,FND
PO,PROFILE_OPTION_NAME,FND
Step 2. Save this file - for example as loadList on the server.
Step 3. Copy shell script from the source below and save it on the server as batchFNDLOAD_D.sh - this one for DOWNLOAD
#!/bin/ksh
# . batchFNDLOAD_D loadList appsPassword
output_dir='apps_objects_download_dir'
login=apps/$2
action='DOWNLOAD'
while read line
do
type=`echo $line | cut -f1 -d,`
type=`echo $type | tr [a-z] [A-Z]`
object=`echo $line | cut -f2 -d,`
app=`echo $line | cut -f3 -d,`
case $type in
HELP)
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookup TYPEs';
echo ' RSP - responsibility';
echo ' DFF - descriptive flexfield';
;;
RSET)
# FNDLOAD Download Request Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $output_dir/$object.ldt REQ_SET REQUEST_SET_NAME=$object
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt REQ_SET_LINKS REQUEST_SET_NAME=$object
;;
CP)
# FNDLOAD Download Concurrent Program Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt PROGRAM APPLICATION_SHORT_NAME=$app CONCURRENT_PROGRAM_NAME=$object
;;
PO)
# FNDLOAD Download Profile Option Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt PROFILE PROFILE_NAME=$object APPLICATION_SHORT_NAME=$app
;;
VS)
# FNDLOAD Download Value Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET FLEX_VALUE_SET_NAME=$object
;;
VSV)
# FNDLOAD Download Value With Values Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=$object
;;
RSP)
# FNDLOAD Download Responsibility Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_RESPONSIBILITY RESP_KEY=$object
;;
MN)
# FNDLOAD Download Menu Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt MENU MENU_NAME=$object
;;
RG)
# FNDLOAD Download Request Group Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt REQUEST_GROUP REQUEST_GROUP_NAME=$object APPLICATION_SHORT_NAME=$app
;;
LKP)
# FNDLOAD Download Lookup Type Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=$app LOOKUP_TYPE=$object
;;
DFF)
# FNDLOAD Download Descriptive Flex Fields Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME=$object
;;
MSG)
# FNDLOAD Download FND Message Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=$app MESSAGE_NAME=$object
;;
USER)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_USER USER_NAME=$object
;;
*) ;;
esac
done < $1
# the end
Step 4. Execute the script : batchFNDLOAD_D loadList appsPassword.
All objects will be processed and migrated in a single run.
It is small, but improvement.
Don't forget to verify that created .ldt files correctly downloaded the designated objects.
Each file, which ends with
# -- End Entity Definitions --
didn't download the object definition. Most likely due to incorrect object name provided in the FNDLOAD command.
As an alternative Check the video tour for Migration LogiK(TM) FNDLOAD GUI
Below is the source and sequence of the actions.
Step 1. Build your list of the objects to needed to be migrated.
Follow the layout. Keep in mind that there are always 3 fields. (Put 'X' if there no application associated with the object):
# OBJECT_TYPE_IDENTIFICATOR,OBJECT_SHORT_NAME,APPLICATION_SHORT_NAME
# Below the example of the objects load list:
#
# CP - concurrent program, PO - profile option, USER - user etc...
# Enclose the object name in double quotes if its name contains spaces, commas #etc...
CP,CONC_PROG_SHORT_NAME,APP_SHORT_NAME
USER,USERNAME,FND
PO,PROFILE_OPTION_NAME,FND
Step 2. Save this file - for example as loadList on the server.
Step 3. Copy shell script from the source below and save it on the server as batchFNDLOAD_D.sh - this one for DOWNLOAD
#!/bin/ksh
# . batchFNDLOAD_D loadList appsPassword
output_dir='apps_objects_download_dir'
login=apps/$2
action='DOWNLOAD'
while read line
do
type=`echo $line | cut -f1 -d,`
type=`echo $type | tr [a-z] [A-Z]`
object=`echo $line | cut -f2 -d,`
app=`echo $line | cut -f3 -d,`
case $type in
HELP)
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookup TYPEs';
echo ' RSP - responsibility';
echo ' DFF - descriptive flexfield';
;;
RSET)
# FNDLOAD Download Request Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $output_dir/$object.ldt REQ_SET REQUEST_SET_NAME=$object
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt REQ_SET_LINKS REQUEST_SET_NAME=$object
;;
CP)
# FNDLOAD Download Concurrent Program Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt PROGRAM APPLICATION_SHORT_NAME=$app CONCURRENT_PROGRAM_NAME=$object
;;
PO)
# FNDLOAD Download Profile Option Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt PROFILE PROFILE_NAME=$object APPLICATION_SHORT_NAME=$app
;;
VS)
# FNDLOAD Download Value Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET FLEX_VALUE_SET_NAME=$object
;;
VSV)
# FNDLOAD Download Value With Values Set Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=$object
;;
RSP)
# FNDLOAD Download Responsibility Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_RESPONSIBILITY RESP_KEY=$object
;;
MN)
# FNDLOAD Download Menu Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt MENU MENU_NAME=$object
;;
RG)
# FNDLOAD Download Request Group Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt REQUEST_GROUP REQUEST_GROUP_NAME=$object APPLICATION_SHORT_NAME=$app
;;
LKP)
# FNDLOAD Download Lookup Type Example
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=$app LOOKUP_TYPE=$object
;;
DFF)
# FNDLOAD Download Descriptive Flex Fields Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME=$object
;;
MSG)
# FNDLOAD Download FND Message Example
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=$app MESSAGE_NAME=$object
;;
USER)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_USER USER_NAME=$object
;;
*) ;;
esac
done < $1
# the end
Step 4. Execute the script : batchFNDLOAD_D loadList appsPassword.
All objects will be processed and migrated in a single run.
It is small, but improvement.
Don't forget to verify that created .ldt files correctly downloaded the designated objects.
Each file, which ends with
# -- End Entity Definitions --
didn't download the object definition. Most likely due to incorrect object name provided in the FNDLOAD command.
Tuesday, April 7, 2009
Oracle API to populate Lookup Table from CSV fle.
Oracle API to populate FND Lookup Table from CSV file.
This task can be useful if your business users have a habit to keep their business rules data in MS Excel.
Here all will come together - reading file, split string and calling Oracle APIs.
To be Continued..
This task can be useful if your business users have a habit to keep their business rules data in MS Excel.
Here all will come together - reading file, split string and calling Oracle APIs.
To be Continued..
Thursday, April 2, 2009
Additional function to process MS Excel CSV record.
MS Excel record could contain additional delimiters such as " and have commas inside
the individual cells. MergeStrings function offers additional functionality allowing to process these or any other additional delimiters.
This can be useful when you need more control over the imported data or invoke Oracle APIs to upload these records into Oracle APPS tables.
Below is the example.
/*
* GetStrings(myString Varchar2) Return Table of Varchar2(300) Index by Binary_Integer
*/
declare
myString Varchar2(20000):='AX,AXX,AXXX,Anne,3333,Merchant,xxxx@yahoo.com,Manager I,18335,DOOM 300,300,"First, Middle Last (Nick)",2434,46678 BEAUMONT FOUNTAIN PLAZA,3050 DOWJON RD,,,Beaumont,TX,77706-7214,TX,"Goor, Stephen J","Duke (Lion)",Full Time >=35 hrs,Exempt,185431,,No,,,'; -- just an example
Type v_type is Table of Varchar2(3000) index by Binary_Integer;
v v_type;
---------------------------------------------------------
Function mergeStrings(v v_type,p_delimiter Varchar2,p_globalDelimiter Varchar2) Return v_type
is
i number:=1;
i_Temp Number :=1;
k Number:=0;
v_Temp V_type;
Begin
while(i_Temp <= v.LAST) Loop
if (v(i_temp) is null) Then v_temp(i):=v(i_temp); i:=i+1;i_temp:=i_temp+1;
Else
if regexp_instr(v(i_temp),'^'||p_delimiter)>0 and regexp_instr(v(i_temp),p_delimiter||'$') >0 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
Elsif regexp_instr (v(i_temp),'^'||p_delimiter)<1 and regexp_instr(v(i_temp),p_delimiter||'$')<1 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
ElsIf (regexp_instr (v(i_temp),'^'||p_delimiter)>0 and instr(v(i_temp),p_delimiter||'$')<1) Then
v_Temp(i):=v(i_temp);
i_temp:=i_temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
if k=0 Then
while ( k=0) Loop
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
i_Temp:=i_Temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
End Loop;
End If;
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
v_Temp(i):=ltrim(v_Temp(i),p_delimiter);
v_Temp(i):=rtrim(v_Temp(i),p_delimiter);
i:=i+1;
i_temp:=i_temp+1;
End If;
End If;
v_Temp(i):=ltrim(v_Temp(i),'"');
v_Temp(i):=rtrim(v_Temp(i),'"');
End Loop;
return v_Temp;
Exception
When NO_DATA_FOUND Then
dbms_output.put_line('String '||v(1) ||' cannot be pro');
return v_temp;
End;
------
Function GetStrings
(p_items Varchar2 Default ' ',
p_delimiter Varchar2 Default ',',
p_enclosed Varchar2 DEfault '"')
Return v_type
is
v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
t_items Varchar2(16000);
i_ds Number:=length(p_delimiter);
i_Length Number;
i_countEnclosed Number:=1;
v_debug Number:=0;
i_Exit Number:=0;
Begin
t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
v_debug :=1;
i_s :=1;
tempString:=substr(t_items,1);
i:=1;
while i_ep > 0
Loop
i_ep:=instr(tempString,p_delimiter,1);
v_debug :=2;
if i_ep >0 then
if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
else
v_varTab_i(i):=substr(tempString,1,i_ep-i_ds);
end if;
i:=i+1;
i_s:=1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
else
v(1):=p_items; -- in case no delimiter present
end if;
v_debug :=3;
End Loop;
v:=v_varTab_i;
v_varTab_i:=mergeStrings(v,'"',p_delimiter);
Return v_varTab_i;
Exception
when others then
dbms_output.put_line('GetString '||substr(SQLERRM,1,200));
return v_varTab_i;
End GetStrings;
-------------------------------------------------------------------------------------
Begin
v:=GetStrings(myString,',');
For j in 1..v.LAST Loop
dbms_output.put_line('Element='||v(j));
End Loop;
End;
the individual cells. MergeStrings function offers additional functionality allowing to process these or any other additional delimiters.
This can be useful when you need more control over the imported data or invoke Oracle APIs to upload these records into Oracle APPS tables.
Below is the example.
/*
* GetStrings(myString Varchar2) Return Table of Varchar2(300) Index by Binary_Integer
*/
declare
myString Varchar2(20000):='AX,AXX,AXXX,Anne,3333,Merchant,xxxx@yahoo.com,Manager I,18335,DOOM 300,300,"First, Middle Last (Nick)",2434,46678 BEAUMONT FOUNTAIN PLAZA,3050 DOWJON RD,,,Beaumont,TX,77706-7214,TX,"Goor, Stephen J","Duke (Lion)",Full Time >=35 hrs,Exempt,185431,,No,,,'; -- just an example
Type v_type is Table of Varchar2(3000) index by Binary_Integer;
v v_type;
---------------------------------------------------------
Function mergeStrings(v v_type,p_delimiter Varchar2,p_globalDelimiter Varchar2) Return v_type
is
i number:=1;
i_Temp Number :=1;
k Number:=0;
v_Temp V_type;
Begin
while(i_Temp <= v.LAST) Loop
if (v(i_temp) is null) Then v_temp(i):=v(i_temp); i:=i+1;i_temp:=i_temp+1;
Else
if regexp_instr(v(i_temp),'^'||p_delimiter)>0 and regexp_instr(v(i_temp),p_delimiter||'$') >0 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
Elsif regexp_instr (v(i_temp),'^'||p_delimiter)<1 and regexp_instr(v(i_temp),p_delimiter||'$')<1 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
ElsIf (regexp_instr (v(i_temp),'^'||p_delimiter)>0 and instr(v(i_temp),p_delimiter||'$')<1) Then
v_Temp(i):=v(i_temp);
i_temp:=i_temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
if k=0 Then
while ( k=0) Loop
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
i_Temp:=i_Temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
End Loop;
End If;
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
v_Temp(i):=ltrim(v_Temp(i),p_delimiter);
v_Temp(i):=rtrim(v_Temp(i),p_delimiter);
i:=i+1;
i_temp:=i_temp+1;
End If;
End If;
v_Temp(i):=ltrim(v_Temp(i),'"');
v_Temp(i):=rtrim(v_Temp(i),'"');
End Loop;
return v_Temp;
Exception
When NO_DATA_FOUND Then
dbms_output.put_line('String '||v(1) ||' cannot be pro');
return v_temp;
End;
------
Function GetStrings
(p_items Varchar2 Default ' ',
p_delimiter Varchar2 Default ',',
p_enclosed Varchar2 DEfault '"')
Return v_type
is
v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
t_items Varchar2(16000);
i_ds Number:=length(p_delimiter);
i_Length Number;
i_countEnclosed Number:=1;
v_debug Number:=0;
i_Exit Number:=0;
Begin
t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
v_debug :=1;
i_s :=1;
tempString:=substr(t_items,1);
i:=1;
while i_ep > 0
Loop
i_ep:=instr(tempString,p_delimiter,1);
v_debug :=2;
if i_ep >0 then
if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
else
v_varTab_i(i):=substr(tempString,1,i_ep-i_ds);
end if;
i:=i+1;
i_s:=1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
else
v(1):=p_items; -- in case no delimiter present
end if;
v_debug :=3;
End Loop;
v:=v_varTab_i;
v_varTab_i:=mergeStrings(v,'"',p_delimiter);
Return v_varTab_i;
Exception
when others then
dbms_output.put_line('GetString '||substr(SQLERRM,1,200));
return v_varTab_i;
End GetStrings;
-------------------------------------------------------------------------------------
Begin
v:=GetStrings(myString,',');
For j in 1..v.LAST Loop
dbms_output.put_line('Element='||v(j));
End Loop;
End;
Saturday, March 28, 2009
FNDLOAD Upload Oracle Applications (APPS) Objects
Check the video tour for Migration LogiK(TM) FNDLOAD GUI - alternative tool
FNDLOAD Upload Mode rules
If a row exists, but has different attributes, the row is updated. If a row
does not exist, a new row is inserted.
Depending on the configuration file, a row that exists in the database
but not in the text file may or may not be deleted when the text file is uploaded.
Refer to the configuration file to determine how such rows are handled.
Preservation of data
FNDLOAD uses the OWNER and LAST_UPDATE_DATE attributes to determine
whether to overwrite pre-existing data. The rules applied are:
1. If the entity being uploaded is not present in the database, a new entity is always inserted.
2. Entities uploaded from a file with OWNER=SEED never overwrite entities with
OWNER=CUSTOM in the database.
3. Entities with OWNER=CUSTOM uploaded from a file always update entities with
OWNER=SEED in the database.
4. If the owner of the entity is the same in the file and database, the entity is updated only if the LAST_UPDATE_DATE in the file is later than the LAST_UPDATE_DATE in the database.
If you have read Oracle FNDLOAD Download section, it is all true about Oracle FNDLOAD Upload as well.
Below is the Oracle FNDLOAD source example, which can be saved into UNIX shell script and executed in the same way as Oracle FNDLOAD Download.
If you saved it as MyFNDLOAD_U, then
. MyFNDLOAD_U CP CONCURRENT_PROG_SHORT_NAME APP_SHORT_NAME.
Of course your .ldt file from the previous download, should be stored in 'apps_objects_download_directory' or you have to make an appropriate change in the code.
Note: There is an error cloned from Metalink by multiple bloggers on Value Set with Values and Value Set migration parameter.
For Value Set it is really VALUE_SET.
For Value Set with Independent Values it is VALUE_SET_VALUE.
#!/bin/ksh
login=apps/password
action='UPLOAD'
output_dir='apps_objects_download_directory'
type=`echo $1 | tr [a-z] [A-Z]`
object=$2
app=$3
case $1 in
help)
echo ' Use the following format: MyFNDLOAD [help|RS|CP] [D,U] output_file application_short entity_short'
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookups';
echo ' Example - MyFNDLOAD_U MN D save_as_file_name short_application_name apps_object_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO D save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO U save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U MN U MYMENU FND MENU_SHORT - to upload menu';
;;
RSET)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afcprset.lct $object.ldt
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt
;;
CP)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afcpprog.lct $object.ldt
;;
PO)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscprof.lct $object.ldt
;;
VS)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $object.ldt
;;
VSV)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $object.ldt
;;
RSP)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $object.ldt
;;
MN)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afsload.lct $object.ldt
;;
RG)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afcpreqg.lct $object.ldt
;;
LKP)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/aflvmlu.lct $object.ldt
;;
DFF)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $object.ldt
;;
MSG)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $object.ldt
;;
USER)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $object.ldt
;;
*) ;;
esac
cat $output_dir/$object.ldt | sed 's/OWNER.*=.*\".*\"/OWNER = \"ANONYMOUS\"/' > xx
mv xx $output_dir/$object.ldt
FNDLOAD Upload Mode rules
If a row exists, but has different attributes, the row is updated. If a row
does not exist, a new row is inserted.
Depending on the configuration file, a row that exists in the database
but not in the text file may or may not be deleted when the text file is uploaded.
Refer to the configuration file to determine how such rows are handled.
Preservation of data
FNDLOAD uses the OWNER and LAST_UPDATE_DATE attributes to determine
whether to overwrite pre-existing data. The rules applied are:
1. If the entity being uploaded is not present in the database, a new entity is always inserted.
2. Entities uploaded from a file with OWNER=SEED never overwrite entities with
OWNER=CUSTOM in the database.
3. Entities with OWNER=CUSTOM uploaded from a file always update entities with
OWNER=SEED in the database.
4. If the owner of the entity is the same in the file and database, the entity is updated only if the LAST_UPDATE_DATE in the file is later than the LAST_UPDATE_DATE in the database.
If you have read Oracle FNDLOAD Download section, it is all true about Oracle FNDLOAD Upload as well.
Below is the Oracle FNDLOAD source example, which can be saved into UNIX shell script and executed in the same way as Oracle FNDLOAD Download.
If you saved it as MyFNDLOAD_U, then
. MyFNDLOAD_U CP CONCURRENT_PROG_SHORT_NAME APP_SHORT_NAME.
Of course your .ldt file from the previous download, should be stored in 'apps_objects_download_directory' or you have to make an appropriate change in the code.
Note: There is an error cloned from Metalink by multiple bloggers on Value Set with Values and Value Set migration parameter.
For Value Set it is really VALUE_SET.
For Value Set with Independent Values it is VALUE_SET_VALUE.
#!/bin/ksh
login=apps/password
action='UPLOAD'
output_dir='apps_objects_download_directory'
type=`echo $1 | tr [a-z] [A-Z]`
object=$2
app=$3
case $1 in
help)
echo ' Use the following format: MyFNDLOAD [help|RS|CP] [D,U] output_file application_short entity_short'
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookups';
echo ' Example - MyFNDLOAD_U MN D save_as_file_name short_application_name apps_object_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO D save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U PO U save_as_file_name short_application_name profile_short_name - to download menu';
echo ' Example - MyFNDLOAD_U MN U MYMENU FND MENU_SHORT - to upload menu';
;;
RSET)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afcprset.lct $object.ldt
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt
;;
CP)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afcpprog.lct $object.ldt
;;
PO)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscprof.lct $object.ldt
;;
VS)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $object.ldt
;;
VSV)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $object.ldt
;;
RSP)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $object.ldt
;;
MN)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afsload.lct $object.ldt
;;
RG)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afcpreqg.lct $object.ldt
;;
LKP)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/aflvmlu.lct $object.ldt
;;
DFF)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $object.ldt
;;
MSG)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $object.ldt
;;
USER)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $object.ldt
;;
*) ;;
esac
cat $output_dir/$object.ldt | sed 's/OWNER.*=.*\".*\"/OWNER = \"ANONYMOUS\"/' > xx
mv xx $output_dir/$object.ldt
Thursday, March 26, 2009
Pipelined function in Oracle. Example of reading flat file from /usr/tmp into SQL*Plus, sql query.
Below is an example of Oracle pipelined function.
On the downside you have to create an expicit Type, which sometimes hard to make one generic enough.
On the upside memory footprint is certainly smaller than creating a collection in memory and then pass it for processing.
---> CREATE CUSTOM TYPES
create Type xx_utl_r_type as object (s Varchar2(1020) );
create type xx_utl_t_type as table of xx_utl_r_type;
----<
---------->---------- Custom Package ---<<<<<<<<<<<<<<<
CREATE OR REPLACE package XX_UTL_FILE_PLUGIN as
Type v_type is table of Varchar2(500) index by binary_Integer;
Function ReadFile2Array(Path Varchar2,FileName Varchar2) return xx_utl_t_type pipelined;
gDir Varchar2(100);
gFile Varchar2(30);
Function getStrings
(p_items Varchar2,
p_delimiter Varchar2)
Return v_type;
End;
/
CREATE OR REPLACE package body XX_UTL_FILE_PLUGIN as
iFileCount Number:=0;
fileName UTL_FILE.FILE_TYPE;
--------------------------------
Function mergeStrings(v v_type,p_delimiter Varchar2,p_globalDelimiter Varchar2) Return v_type
is
i number:=1;
i_Temp Number :=1;
k Number:=0;
v_Temp V_type;
Begin
while(i_Temp <= v.LAST) Loop
if (v(i_temp) is null) Then v_temp(i):=v(i_temp); i:=i+1;i_temp:=i_temp+1;
Else
if regexp_instr(v(i_temp),'^'||p_delimiter)>0 and regexp_instr(v(i_temp),p_delimiter||'$') >0 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
Elsif regexp_instr (v(i_temp),'^'||p_delimiter)<1 and regexp_instr(v(i_temp),p_delimiter||'$')<1 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
ElsIf (regexp_instr (v(i_temp),'^'||p_delimiter)>0 and instr(v(i_temp),p_delimiter||'$')<1) Then
v_Temp(i):=v(i_temp);
i_temp:=i_temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
if k=0 Then
while ( k=0) Loop
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
i_Temp:=i_Temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
End Loop;
End If;
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
v_Temp(i):=ltrim(v_Temp(i),p_delimiter);
v_Temp(i):=rtrim(v_Temp(i),p_delimiter);
i:=i+1;
i_temp:=i_temp+1;
End If;
End If;
End Loop;
return v_Temp;
Exception
When NO_DATA_FOUND Then
dbms_output.put_line('String '||v(i_temp) ||' cannot be processed! Row Number='||i);
return v_temp;
End;
--------------------------------
Procedure myDebug(msg Varchar2) is
Begin
-- fnd_file.put_line(fnd_file.log,msg);
dbms_output.put_line(msg);
End;
-----------------------------------------------------------------------------
Function ReadFile2Array(Path Varchar2,FileName Varchar2) return xx_utl_t_type pipelined is
S Varchar2(1020);
FileHandle utl_file.file_type;
sr xx_utl_r_type;
iCount Number :=1;
Begin
dbms_output.put_line(path||'-'||filename);
fileHandle:=utl_file.fopen(path,FileName,'r');
while true Loop
Begin
iCount := iCount+1;
utl_file.get_line(fileHandle,s);
-- myDebug(iCount||'-'||substr(s,1,200));
iFileCount:=iFileCount+1;
mydebug('st1 '||length(s));
sr:=xx_utl_r_type(s);
mydebug('st2');
pipe row (sr);
Exception
When NO_DATA_FOUND Then
mydebug('No data found');
utl_file.fclose(fileHandle);
return;
End;
End Loop;
return;
Exception
When utl_file.INVALID_PATH Then
utl_file.fclose(fileHandle);
myDebug('Wrong file path! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('INVALID PATH');
pipe row (sr);
return;
When utl_file.INVALID_FILEHANDLE Then
utl_file.fclose(fileHandle);
myDebug('Wrong file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('INVALID FILEHANDLE');
pipe row (sr);
return;
When utl_file.INVALID_OPERATION Then
utl_file.fclose(fileHandle);
myDebug('Wrong file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('FILE NOT FOUND!');
pipe row (sr);
return;
When utl_file.READ_ERROR Then
utl_file.fclose(fileHandle);
myDebug('Can''t read file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('READ ERROR');
pipe row (sr);
return;
When Others Then
mydebug(substr(SQLERRM,1,200));
utl_file.fclose(fileHandle);
myDebug('Done file reading ');
myDebug('Read '||iFileCount||' records!');
End;
/*---------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------*/
Function getStrings
(p_items Varchar2,
p_delimiter Varchar2)
Return v_type
is
v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
--add Varchar2(100);
t_items Varchar2(16000);
Begin
t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
i_s :=1;
tempString:=substr(t_items,i_s);
i:=1;
while i_ep > 0
Loop
i_ep:=instr(tempString,p_delimiter,i_s);
if i_ep >0 then
if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
else
v_varTab_i(i):=substr(tempString,i_s,i_ep-i_s);
end if;
i:=i+1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
end if;
End Loop;
v_varTab_i:=mergeStrings(v_varTab_i,'"',p_delimiter);
Return v_varTab_i;
Exception
when others then
null;
end getStrings;
End xx_utl_file_plugin ;
/
---------->---------- Custom Package ---<<<<<<<<<<<<<<<
-- query below allows you to read flat file from OS straight into SQL query.
-- Directory. which in this case is '/usr/tmp' needs to be replaced with your
-- directory name. Security limitation on UTL_FILE is also applied here.
-- You can only use directories stored in DBA_DIRECTORIES table.
select * from table(XX_UTL_FILE_PLUGIN.ReadFile2Array('/usr/tmp','yourfilename.extension'));
On the downside you have to create an expicit Type, which sometimes hard to make one generic enough.
On the upside memory footprint is certainly smaller than creating a collection in memory and then pass it for processing.
---> CREATE CUSTOM TYPES
create Type xx_utl_r_type as object (s Varchar2(1020) );
create type xx_utl_t_type as table of xx_utl_r_type;
----<
---------->---------- Custom Package ---<<<<<<<<<<<<<<<
CREATE OR REPLACE package XX_UTL_FILE_PLUGIN as
Type v_type is table of Varchar2(500) index by binary_Integer;
Function ReadFile2Array(Path Varchar2,FileName Varchar2) return xx_utl_t_type pipelined;
gDir Varchar2(100);
gFile Varchar2(30);
Function getStrings
(p_items Varchar2,
p_delimiter Varchar2)
Return v_type;
End;
/
CREATE OR REPLACE package body XX_UTL_FILE_PLUGIN as
iFileCount Number:=0;
fileName UTL_FILE.FILE_TYPE;
--------------------------------
Function mergeStrings(v v_type,p_delimiter Varchar2,p_globalDelimiter Varchar2) Return v_type
is
i number:=1;
i_Temp Number :=1;
k Number:=0;
v_Temp V_type;
Begin
while(i_Temp <= v.LAST) Loop
if (v(i_temp) is null) Then v_temp(i):=v(i_temp); i:=i+1;i_temp:=i_temp+1;
Else
if regexp_instr(v(i_temp),'^'||p_delimiter)>0 and regexp_instr(v(i_temp),p_delimiter||'$') >0 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
Elsif regexp_instr (v(i_temp),'^'||p_delimiter)<1 and regexp_instr(v(i_temp),p_delimiter||'$')<1 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;
ElsIf (regexp_instr (v(i_temp),'^'||p_delimiter)>0 and instr(v(i_temp),p_delimiter||'$')<1) Then
v_Temp(i):=v(i_temp);
i_temp:=i_temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
if k=0 Then
while ( k=0) Loop
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
i_Temp:=i_Temp+1;
k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);
End Loop;
End If;
v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
v_Temp(i):=ltrim(v_Temp(i),p_delimiter);
v_Temp(i):=rtrim(v_Temp(i),p_delimiter);
i:=i+1;
i_temp:=i_temp+1;
End If;
End If;
End Loop;
return v_Temp;
Exception
When NO_DATA_FOUND Then
dbms_output.put_line('String '||v(i_temp) ||' cannot be processed! Row Number='||i);
return v_temp;
End;
--------------------------------
Procedure myDebug(msg Varchar2) is
Begin
-- fnd_file.put_line(fnd_file.log,msg);
dbms_output.put_line(msg);
End;
-----------------------------------------------------------------------------
Function ReadFile2Array(Path Varchar2,FileName Varchar2) return xx_utl_t_type pipelined is
S Varchar2(1020);
FileHandle utl_file.file_type;
sr xx_utl_r_type;
iCount Number :=1;
Begin
dbms_output.put_line(path||'-'||filename);
fileHandle:=utl_file.fopen(path,FileName,'r');
while true Loop
Begin
iCount := iCount+1;
utl_file.get_line(fileHandle,s);
-- myDebug(iCount||'-'||substr(s,1,200));
iFileCount:=iFileCount+1;
mydebug('st1 '||length(s));
sr:=xx_utl_r_type(s);
mydebug('st2');
pipe row (sr);
Exception
When NO_DATA_FOUND Then
mydebug('No data found');
utl_file.fclose(fileHandle);
return;
End;
End Loop;
return;
Exception
When utl_file.INVALID_PATH Then
utl_file.fclose(fileHandle);
myDebug('Wrong file path! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('INVALID PATH');
pipe row (sr);
return;
When utl_file.INVALID_FILEHANDLE Then
utl_file.fclose(fileHandle);
myDebug('Wrong file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('INVALID FILEHANDLE');
pipe row (sr);
return;
When utl_file.INVALID_OPERATION Then
utl_file.fclose(fileHandle);
myDebug('Wrong file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('FILE NOT FOUND!');
pipe row (sr);
return;
When utl_file.READ_ERROR Then
utl_file.fclose(fileHandle);
myDebug('Can''t read file ! '|| substr(SQLERRM,1,100));
sr:=xx_utl_r_type('READ ERROR');
pipe row (sr);
return;
When Others Then
mydebug(substr(SQLERRM,1,200));
utl_file.fclose(fileHandle);
myDebug('Done file reading ');
myDebug('Read '||iFileCount||' records!');
End;
/*---------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------*/
Function getStrings
(p_items Varchar2,
p_delimiter Varchar2)
Return v_type
is
v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
--add Varchar2(100);
t_items Varchar2(16000);
Begin
t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
i_s :=1;
tempString:=substr(t_items,i_s);
i:=1;
while i_ep > 0
Loop
i_ep:=instr(tempString,p_delimiter,i_s);
if i_ep >0 then
if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
else
v_varTab_i(i):=substr(tempString,i_s,i_ep-i_s);
end if;
i:=i+1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
end if;
End Loop;
v_varTab_i:=mergeStrings(v_varTab_i,'"',p_delimiter);
Return v_varTab_i;
Exception
when others then
null;
end getStrings;
End xx_utl_file_plugin ;
/
---------->---------- Custom Package ---<<<<<<<<<<<<<<<
-- query below allows you to read flat file from OS straight into SQL query.
-- Directory. which in this case is '/usr/tmp' needs to be replaced with your
-- directory name. Security limitation on UTL_FILE is also applied here.
-- You can only use directories stored in DBA_DIRECTORIES table.
select * from table(XX_UTL_FILE_PLUGIN.ReadFile2Array('/usr/tmp','yourfilename.extension'));
Wednesday, March 25, 2009
Oracle FNDLOAD Download. Organized
FNDLOAD is a powerful Oracle utility, providing significant time saving while migrating application objects from instance to instance. The downside is mandatory knowledge of all configuration files and parameters. In all the resourses dedicated to FNDLOAD which are available on the internet I haven't seen so far any suggestions to organize it in a simple and easy accesible way. Below is the code which can be turned into UNIX shell script and can help combine easy usage and power of FNDLOAD.
This is not something of industrial grade and I wasn't able to test it in full scale.
Any suggestion or errors reported will be greatly appreciated.
Lets say - if it is saved under name MyFNDLOAD_D, then to execute it , simply do
. MyFNDLOAD_D CP CONCURRENT_PROGRAM_SHORT_NAME APPLICATION_SHORT_NAME
As you can see there is no need to remember configuration file or the internal naming parameters.
Note: There is an error cloned from Metalink by multiple bloggers on Value Set with Values and Value Set migration parameter.
For Value Set it is really VALUE_SET.
For Value Set with Independent Values it is VALUE_SET_VALUE.
#!/bin/ksh
output_dir='apps_objects_download_dir'
login=apps/password
action='DOWNLOAD'
type=`echo $1 | tr [a-z] [A-Z]`
object=$2
app=$3
case $type in
help)
echo ' Use the following format: MyFNDLOAD_D [help|RS|CP] short_object_name application_short entity_short'
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookup TYPEs';
;;
RSET)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $output_dir/$object.ldt REQ_SET REQUEST_SET_NAME=$object
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt REQ_SET_LINKS REQUEST_SET_NAME=$object
;;
CP)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt PROGRAM APPLICATION_SHORT_NAME=$app CONCURRENT_PROGRAM_NAME=$object
;;
PO)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt PROFILE PROFILE_NAME=$object APPLICATION_SHORT_NAME=$app
;;
VS)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET FLEX_VALUE_SET_NAME=$object
;;
VSV)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=$object
;;
RSP)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_RESPONSIBILITY RESP_KEY=$object
;;
MN)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt MENU MENU_NAME=$object
;;
RG)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt REQUEST_GROUP REQUEST_GROUP_CODE=$object APPLICATION_SHORT_NAME=$app
;;
LKP)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=$app LOOKUP_TYPE=$object
;;
DFF)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME=$object
;;
MSG)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=$app MESSAGE_NAME=$object
;;
USER)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_USER USER_NAME=$object
;;
*) ;;
esac
# Now let change the OWNER name to ANONYMOUS if desired
cat $output_dir/$object.ldt | sed 's/OWNER.*=.*\".*\"/OWNER = \"ANONYMOUS\"/' > xx
# Move the files into output directory
mv xx $output_dir/$object.ldt
This is not something of industrial grade and I wasn't able to test it in full scale.
Any suggestion or errors reported will be greatly appreciated.
Lets say - if it is saved under name MyFNDLOAD_D, then to execute it , simply do
. MyFNDLOAD_D CP CONCURRENT_PROGRAM_SHORT_NAME APPLICATION_SHORT_NAME
As you can see there is no need to remember configuration file or the internal naming parameters.
Note: There is an error cloned from Metalink by multiple bloggers on Value Set with Values and Value Set migration parameter.
For Value Set it is really VALUE_SET.
For Value Set with Independent Values it is VALUE_SET_VALUE.
#!/bin/ksh
output_dir='apps_objects_download_dir'
login=apps/password
action='DOWNLOAD'
type=`echo $1 | tr [a-z] [A-Z]`
object=$2
app=$3
case $type in
help)
echo ' Use the following format: MyFNDLOAD_D [help|RS|CP] short_object_name application_short entity_short'
echo ' RSET - Request set ';
echo ' CP - Concurrent Program';
echo ' PO - Profile option';
echo ' VS - Value Set definition';
echo ' MN - Menu';
echo ' RG - Request Group';
echo ' LKP - FND Lookup TYPEs';
;;
RSET)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $output_dir/$object.ldt REQ_SET REQUEST_SET_NAME=$object
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcprset.lct $object_links.ldt REQ_SET_LINKS REQUEST_SET_NAME=$object
;;
CP)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpprog.lct $output_dir/$object.ldt PROGRAM APPLICATION_SHORT_NAME=$app CONCURRENT_PROGRAM_NAME=$object
;;
PO)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscprof.lct $output_dir/$object.ldt PROFILE PROFILE_NAME=$object APPLICATION_SHORT_NAME=$app
;;
VS)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET FLEX_VALUE_SET_NAME=$object
;;
VSV)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=$object
;;
RSP)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_RESPONSIBILITY RESP_KEY=$object
;;
MN)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afsload.lct $output_dir/$object.ldt MENU MENU_NAME=$object
;;
RG)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/afcpreqg.lct $output_dir/$object.ldt REQUEST_GROUP REQUEST_GROUP_CODE=$object APPLICATION_SHORT_NAME=$app
;;
LKP)
FNDLOAD $login O Y $action $FND_TOP/patch/115/import/aflvmlu.lct $output_dir/$object.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=$app LOOKUP_TYPE=$object
;;
DFF)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afffload.lct $output_dir/$object.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME=$object
;;
MSG)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afmdmsg.lct $output_dir/$object.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=$app MESSAGE_NAME=$object
;;
USER)
FNDLOAD $login 0 Y $action $FND_TOP/patch/115/import/afscursp.lct $output_dir/$object.ldt FND_USER USER_NAME=$object
;;
*) ;;
esac
# Now let change the OWNER name to ANONYMOUS if desired
cat $output_dir/$object.ldt | sed 's/OWNER.*=.*\".*\"/OWNER = \"ANONYMOUS\"/' > xx
# Move the files into output directory
mv xx $output_dir/$object.ldt
Regular expressions examples.
Below are some examples of Oracle Regular expressions. Very small in size, but practical in use.
--- Remove from the string all characters except digits
select REGEXP_REPLACE('ads687d6905%&^ sd word','[^][[:digit:]]','') from dual
--- Remove from the string all digits
select REGEXP_REPLACE('ads687d6905%&^ sd word','[[:digit:]]','') from dual
--- Identify number of digits in the string
select length(REGEXP_REPLACE('ads687d6905%&^ sd word','[^][[:digit:]]','')) from dual
-- Find the positiong of the substring, which starts with 2 capital letters and followed by 5 digits. For example like 'CO 80016' or 'CO,80016'
select REGEXP_INSTR('11203 St Birch str, CO, 80016-','[A-Z]{2,}[ |,]*[0-9]{5}') from dual
--- Remove from the string all characters except digits
select REGEXP_REPLACE('ads687d6905%&^ sd word','[^][[:digit:]]','') from dual
--- Remove from the string all digits
select REGEXP_REPLACE('ads687d6905%&^ sd word','[[:digit:]]','') from dual
--- Identify number of digits in the string
select length(REGEXP_REPLACE('ads687d6905%&^ sd word','[^][[:digit:]]','')) from dual
-- Find the positiong of the substring, which starts with 2 capital letters and followed by 5 digits. For example like 'CO 80016' or 'CO,80016'
select REGEXP_INSTR('11203 St Birch str, CO, 80016-','[A-Z]{2,}[ |,]*[0-9]{5}') from dual
Tuesday, March 24, 2009
String Split in Oracle
With all the wonders Oracle PL/SQL can produce at some moment in the past I could not find a suitable string split function. When majority of other programming languages have this functionality available, PL/SQL somehow was left behind. I spent a couple of hours mostly evaluating different data scenarios and wrote this function. Later it received some additions in the form of the flexible delimiter length, and not just a single token. This function proved to be very efficient and worked just well for me and my colleagues on various projects. I decided to offer it to whoever might need it.
If you are at the beginner level of Oracle PL/SQL, then below is a short example on how to call GetStrings function. If it is still not clear, shoot me an email. I will be happy to help.
/*
* GetStrings(myString Varchar2) Return Table of Varchar2(300) Index by Binary_Integer
*/
declare
myString Varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example
Type v_type is Table of Varchar2(300) index by Binary_Integer;
v v_type;
Function GetStrings
(p_items Varchar2 Default ' ',
p_delimiter Varchar2 Default ',')
Return v_type
is
v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
t_items Varchar2(16000);
Begin
t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
i_s :=1;
tempString:=substr(t_items,i_s);
i:=1;
while i_ep > 0
Loop
i_ep:=instr(tempString,p_delimiter,i_s);
if i_ep >0 then
if i_ep-i_s = 0 then
v_varTab_i(i):= NULL;
i_ep:=i_ep+length(p_delimiter);
else
v_varTab_i(i):=substr(tempString,i_s,i_ep-i_s);
dbms_output.put_line(v_varTab_i(i));
end if;
i:=i+1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
else
v(1):=p_items; -- in case no delimiter present
end if;
End Loop;
Return v_varTab_i;
Exception
when others then
NULL;
Return v_varTab_i;
End GetStrings;
------
Begin
v:=GetStrings(myString,':');
for j in 1..v.LAST Loop
dbms_output.put_line(v(j));
End Loop;
End;
/*-----------------------------------------------------------<---------*/
If you are at the beginner level of Oracle PL/SQL, then below is a short example on how to call GetStrings function. If it is still not clear, shoot me an email. I will be happy to help.
/*
* GetStrings(myString Varchar2) Return Table of Varchar2(300) Index by Binary_Integer
*/
declare
myString Varchar2(2000):='a:sd:dfg:31456:dasd: :sdfsdf'; -- just an example
Type v_type is Table of Varchar2(300) index by Binary_Integer;
v v_type;
Function GetStrings
(p_items Varchar2 Default ' ',
p_delimiter Varchar2 Default ',')
Return v_type
is
v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
t_items Varchar2(16000);
Begin
t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;
i_s :=1;
tempString:=substr(t_items,i_s);
i:=1;
while i_ep > 0
Loop
i_ep:=instr(tempString,p_delimiter,i_s);
if i_ep >0 then
if i_ep-i_s = 0 then
v_varTab_i(i):= NULL;
i_ep:=i_ep+length(p_delimiter);
else
v_varTab_i(i):=substr(tempString,i_s,i_ep-i_s);
dbms_output.put_line(v_varTab_i(i));
end if;
i:=i+1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
else
v(1):=p_items; -- in case no delimiter present
end if;
End Loop;
Return v_varTab_i;
Exception
when others then
NULL;
Return v_varTab_i;
End GetStrings;
------
Begin
v:=GetStrings(myString,':');
for j in 1..v.LAST Loop
dbms_output.put_line(v(j));
End Loop;
End;
/*-----------------------------------------------------------<---------*/
Subscribe to:
Posts (Atom)
