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
Saturday, March 28, 2009
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)