some times we want to know what are queries that are getting executed when any process is run..one way of finding it is using the V$Sql..the SID and Serial# can be used to kill the session in case of long running queries
set pagesize 24
set newpage 1
set linesize 125
column sql_text format a100
column user_name format a12
select b.sid, b.serial# ,
substr(b.username,1,12) user_name,
a.sql_text
from v$sql a, v$session b
where a.hash_value = b.sql_hash_value
and sql_text not like '%from v$sql s, v$session u%'
order by b.sid;
SQL> @cur_sql
sid serial user_name sql_text
5 390 apps select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an d t.bitmapped=0
To kill a session use the below command
ALTER SYSTEM KILL SESSION 'SID, SER#';
alter system kill session '5,390';
Wednesday, November 5, 2008
Monday, September 29, 2008
Thursday, August 28, 2008
Adding Concurrent Program to a request group from backend
This one of the common requirements of adding adding the new concurrent program to the request group in production environment...
The critical comes when we dont have access to the sysadmin responsibility and guide the DBA to do it...
Actually there is API's for adding the concurrent program from backend.. it is better to create that script and askt he DBA to run the script which will add the concurrent program to the request group.
The API is given in the Application Developer the only thing is knowing that it exists and remembering it at the right movement...
FND_PROGRAM.ADD_TO_GROUP
FND_PROGRAM.ADD_TO_GROUP
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
Description Use this procedure to add a concurrent program to a
request group. This procedure corresponds to the "Requests" region in the "Request Groups" window in System Administration.
Arguments (input):
program_short_ name The short name used as the developer name of the
concurrent program.
program_ application The application that owns the concurrent program.
request_group The request group to which to add the concurrent program.
group_ application The application that owns the request group.
Sample:
begin
FND_PROGRAM.ADD_TO_GROUP(program_short_name=>'XX_TEST',
program_=>'Application Object Library',
request_group=>'Application Developer Reports',
group_application=>'Application Object Library');
Commit;
Exception
when others then
Dbms_output.put_line('Exception while adding'SQLERRM);
End;
You need to commit the trasaction to get things effected
The critical comes when we dont have access to the sysadmin responsibility and guide the DBA to do it...
Actually there is API's for adding the concurrent program from backend.. it is better to create that script and askt he DBA to run the script which will add the concurrent program to the request group.
The API is given in the Application Developer the only thing is knowing that it exists and remembering it at the right movement...
FND_PROGRAM.ADD_TO_GROUP
FND_PROGRAM.ADD_TO_GROUP
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
Description Use this procedure to add a concurrent program to a
request group. This procedure corresponds to the "Requests" region in the "Request Groups" window in System Administration.
Arguments (input):
program_short_ name The short name used as the developer name of the
concurrent program.
program_ application The application that owns the concurrent program.
request_group The request group to which to add the concurrent program.
group_ application The application that owns the request group.
Sample:
begin
FND_PROGRAM.ADD_TO_GROUP(program_short_name=>'XX_TEST',
program_=>'Application Object Library',
request_group=>'Application Developer Reports',
group_application=>'Application Object Library');
Commit;
Exception
when others then
Dbms_output.put_line('Exception while adding'SQLERRM);
End;
You need to commit the trasaction to get things effected
Tuesday, August 12, 2008
TKPROF-Peformance Tunning(Example2,3)-4
STEP 2 - Examine statements using high resourcee
===============================================
update ...
where ...
call------ count ---- cpu --- elapsed --- disk ----query --- current ----rows --
Parse-----1--------- 7 -----122 -------- -0----------0----------0-------------0---
Execute---1------- 75------ 461-------- 5----- [H] 297 -----[I] 3-------- [J]1---
Fetch ---- 0-------- 0------0-------------0--------0-------- 0------------ 0 ---
[H] shows that this query is visiting 297 blocks to find the rows
to update[I] shows that only 3 blocks are visited performing
the update[J] shows that only 1 row is updated.
297 block to update 1 rows is a lot. Possibly there is an index missing?
STEP 3 - Look for over parsing
select ...
call--------count------cpu-------elapsed------disk----query----current---- rows--
--------------------------------------------------------------------------------------
Parse------[M] 2 ----- [N] 221--- 329---------0-------45---------0--------- 0--
Execute---- [O] 3----- [P]9-------17----------0--------0--------- 0----------0--
Fetch------- 3----------6----------8-----------0-------[L] 4-------0-------- [K] 1
Misses in library cache during parse: 2 [Q]
[K] is shows that the query has returned 1 row.
[L] shows that we had to read 4 blocks to get this row back.This is fine.
[M] show that we are parsing the statement twice - this is not desirable especially as the cpu usage is high [N] in comparison to the execute
figures : [O] & [P]. [Q] shows that these parses are hard parses.
If [Q] was 1 then the statemnent would have had 1 hard parse followed by a soft parse (which just looks up the already parsed detail in the library cache).
This is not a particularly bad example since the query has only been executed a few times.
However excessive parsing should be avoided as far as possible by:
o Ensuring that code is shared:
- use bind variables - make shared pool large enough to hold query definitions in memory long enough to be reused.
===============================================
update ...
where ...
call------ count ---- cpu --- elapsed --- disk ----query --- current ----rows --
Parse-----1--------- 7 -----122 -------- -0----------0----------0-------------0---
Execute---1------- 75------ 461-------- 5----- [H] 297 -----[I] 3-------- [J]1---
Fetch ---- 0-------- 0------0-------------0--------0-------- 0------------ 0 ---
[H] shows that this query is visiting 297 blocks to find the rows
to update[I] shows that only 3 blocks are visited performing
the update[J] shows that only 1 row is updated.
297 block to update 1 rows is a lot. Possibly there is an index missing?
STEP 3 - Look for over parsing
select ...
call--------count------cpu-------elapsed------disk----query----current---- rows--
--------------------------------------------------------------------------------------
Parse------[M] 2 ----- [N] 221--- 329---------0-------45---------0--------- 0--
Execute---- [O] 3----- [P]9-------17----------0--------0--------- 0----------0--
Fetch------- 3----------6----------8-----------0-------[L] 4-------0-------- [K] 1
Misses in library cache during parse: 2 [Q]
[K] is shows that the query has returned 1 row.
[L] shows that we had to read 4 blocks to get this row back.This is fine.
[M] show that we are parsing the statement twice - this is not desirable especially as the cpu usage is high [N] in comparison to the execute
figures : [O] & [P]. [Q] shows that these parses are hard parses.
If [Q] was 1 then the statemnent would have had 1 hard parse followed by a soft parse (which just looks up the already parsed detail in the library cache).
This is not a particularly bad example since the query has only been executed a few times.
However excessive parsing should be avoided as far as possible by:
o Ensuring that code is shared:
- use bind variables - make shared pool large enough to hold query definitions in memory long enough to be reused.
TKPROF-Peformance tunning(Example1)-3
Examples:
Step 1 - Look at the totals at the end of the tkprof output===========================================================
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
-----------------------------------------------------------------
Parse [A] 7 1.87 4.53 385 [G] 553 22 0
Execute [E] 7 0.03 0.11 [P] 0 [C] 0 [D] 0 [F] 0
Fetch [E] 6 1.39 4.21 [P] 128 [C] 820 [D] 3 [F] 20
--------------------------------------------------------------------------
Misses in library cache during parse: 5
Misses in library cache during execute: 1
8 user SQL statements in session.
12 internal SQL statements in session
.[B] 54 SQL statements in session.
3 statements EXPLAINed in this session.
1. Compare [A] & [B] to spot over parsing.
In this case we have 7 parses for 54 statements which is ok.
2. You can use [P], [C] & [D] to determine the hit ratio.
Hit Ratio is logical reads/physical reads:
Logical Reads = Consistent Gets + DB Block Gets
Logical Reads = query + current
Logical Reads = Sum[C] + Sum[D]
Logical Reads = 0+820 + 0+3
Logical Reads = 820 + 3
Logical Reads = 823
Hit Ratio = 1 - (Physical Reads / Logical Reads)
Hit Ratio = 1 - (Sum[P] / Logical Reads)
Hit Ratio = 1 - (128 / 823)
Hit Ratio = 1 - (0.16)
Hit Ratio = 0.84 or 84%
3. We want fetches to be less than the number of rows as this will mean we have done less work (array fetching).
To see this we can compare [E] and [F].
[E] = 6 = Number of Fetches[F] = 20 = Number of Rows
So we are doing 6 fetches to retrieve 20 rows - not too bad.
If arrayfetching was configured then rows could be retrieved with less fetches.
Remember that an extra fetch will be done at the end to check thatthe end of fetch has been reached.
4. [G] Shows reads on the Dictionary cache for the statements.
- this should not be a problem on Oracle7.
In this case we have done 553 reads from the Library cache.
TKPROF--Performance Tunning -2
Hi All,
Now we will see how to read the trace files..It is very difficult to understand from the trace files so we use the TKPROF utility to make the trace files in a better readable format..
This is one of the documents i had which i collected from metalink..a good one with clear examples...
If you have a system that is performing badly, a good way to identify problem SQL statements is to trace a typical user session and then use TkProfto format the output using the sort functions on the tkprof command line.
There are a huge number of sort options that can be accessed by simply typing 'TkProf' at the command prompt.
A useful starting point is the 'fchela' sort option which orders the output by elapsed time fetching (rememberthat timing information is only available with timed_statistics set to true in the "init.ora" file).
The resultant .prf file will contain the most time consuming SQL statement at the start of the file.
Another useful parameter is sys.
This can be used to prevent SQL statements run as user SYS from being displayed. This can make the output file much shorter an easier to manage.
Remember to always set the TIMED_STATISTICS parameter to TRUE when tracingsessions as otherwise no time based comparisons can be made.
A typical TKPROF out look like for select,insert,update,delete..
call count cpu elapsed disk query current rows
--------- ------------------------------------------------------------------------------------
Parse 2 221 329 0 45 0 0
Execute 3 9 17 0 0 0 0
Fetch 3 6 8 0 4 0 1
------------------------------------------------------------------------------- --------------
Let see the definition for each and every column
Interpreting TkProf Output Guidelines
call : Statisics for each cursor's activity are divided in to 3 areas:
Parse: statisitics from parsing the cursor.
This includes information for plan generation etc.
Execute: statisitics for the exection phase of a cursor
Fetch : statistics for actually fetching the rows
count : number of times we have performed a particular activity on this particular cursor
cpu: cpu time used by this cursor
elapsed: elapsed time for this cursor
disk: This indicates the number of blocks read from disk.
Generally you want to see blocks being read from the buffer cache rather than disk.
query : This column is incremented if a buffer is read in Consistent mode.
A Consistent mode buffer is one that has been generated to give a consistent read snapshot for a long running transaction.
The buffer actually contains this status in its header.
current: This column is incremented if a buffer found in the buffer cache that is new enough for the current transaction and is in current mode (and it is not a CR buffer).
This applies to buffers that have been read in to the cache as well as buffers that already exist in the cache in current mode.
rows: Rows retrieved by this step
What is my first step while tunning the performance of a query ?
It is better to use autotrace feature of SQL*Plus be used on statements rather than using TkProf mainly because the TkProf output can be confusing with regard to whether the Rule or Cost Based optimizer has been used. Because TkProf explain plan does not show any costs or statistics,
it is sometimes not possible to tell definitively which optimizer has been used.
That said, the following output from Tkprof explain plan is useful.
The Rows column next to the explain plan output shows the number of rows processed by that particular step.
The information is gathered from the STAT lines for each cursor in the raw trace output.
Remember that if the cursor is not closed then you will not see any output.
Setting SQL_TRACE to false DOES NOT close PL/SQL child cursors.
Cursors are closed in SQL*Plus immediately after execution.
Let's see some tkprof examples ..in the next post..
Now we will see how to read the trace files..It is very difficult to understand from the trace files so we use the TKPROF utility to make the trace files in a better readable format..
This is one of the documents i had which i collected from metalink..a good one with clear examples...
If you have a system that is performing badly, a good way to identify problem SQL statements is to trace a typical user session and then use TkProfto format the output using the sort functions on the tkprof command line.
There are a huge number of sort options that can be accessed by simply typing 'TkProf' at the command prompt.
A useful starting point is the 'fchela' sort option which orders the output by elapsed time fetching (rememberthat timing information is only available with timed_statistics set to true in the "init.ora" file).
The resultant .prf file will contain the most time consuming SQL statement at the start of the file.
Another useful parameter is sys.
This can be used to prevent SQL statements run as user SYS from being displayed. This can make the output file much shorter an easier to manage.
Remember to always set the TIMED_STATISTICS parameter to TRUE when tracingsessions as otherwise no time based comparisons can be made.
A typical TKPROF out look like for select,insert,update,delete..
call count cpu elapsed disk query current rows
--------- ------------------------------------------------------------------------------------
Parse 2 221 329 0 45 0 0
Execute 3 9 17 0 0 0 0
Fetch 3 6 8 0 4 0 1
------------------------------------------------------------------------------- --------------
Let see the definition for each and every column
Interpreting TkProf Output Guidelines
call : Statisics for each cursor's activity are divided in to 3 areas:
Parse: statisitics from parsing the cursor.
This includes information for plan generation etc.
Execute: statisitics for the exection phase of a cursor
Fetch : statistics for actually fetching the rows
count : number of times we have performed a particular activity on this particular cursor
cpu: cpu time used by this cursor
elapsed: elapsed time for this cursor
disk: This indicates the number of blocks read from disk.
Generally you want to see blocks being read from the buffer cache rather than disk.
query : This column is incremented if a buffer is read in Consistent mode.
A Consistent mode buffer is one that has been generated to give a consistent read snapshot for a long running transaction.
The buffer actually contains this status in its header.
current: This column is incremented if a buffer found in the buffer cache that is new enough for the current transaction and is in current mode (and it is not a CR buffer).
This applies to buffers that have been read in to the cache as well as buffers that already exist in the cache in current mode.
rows: Rows retrieved by this step
What is my first step while tunning the performance of a query ?
It is better to use autotrace feature of SQL*Plus be used on statements rather than using TkProf mainly because the TkProf output can be confusing with regard to whether the Rule or Cost Based optimizer has been used. Because TkProf explain plan does not show any costs or statistics,
it is sometimes not possible to tell definitively which optimizer has been used.
That said, the following output from Tkprof explain plan is useful.
The Rows column next to the explain plan output shows the number of rows processed by that particular step.
The information is gathered from the STAT lines for each cursor in the raw trace output.
Remember that if the cursor is not closed then you will not see any output.
Setting SQL_TRACE to false DOES NOT close PL/SQL child cursors.
Cursors are closed in SQL*Plus immediately after execution.
Let's see some tkprof examples ..in the next post..
Monday, August 11, 2008
SQL Trace File--Performance Tunning -1
Hi All,Quite often we come across tunning the queires...This is the area where most people find it difficult to understand ..The blind rule every one says check the explain plan and try to remove the full table scans...most of times it works to a extent..
For really tunning to the best u need to understand what oracle is doing to the data you require or perform the transactions you have issued..
For that purpose oracle has give sqltrace utility which will help you to figure out what all operations are getting performed and the way and time taken by each of them
First and foremost we will see what all setups required(mostly done by DBA..but nice to know things)
lets try to answer some simple questions
1.At what level we can enable the trace??
Both at the system level and session level
2.How to know the sessions and enable the trace?
select username, sid, serial#, paddr from v$session where username='APPS'
USERNAME SID SERIAL# PADDR------------------------------ --------- --------- --------APPS 372 313 4308F6F8APPS 373 27 43092A70APPS 375 36 430924B8APPS 376 184 4308DA60APPS 377 334 4308FCB0APPS 378 102 4308A6E8APPS 380 24 43091948APPS 381 12823 4308F140APPS 382 5297 4308EB88
To enable the trace for any one of the sessions
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(
3.Where are the trace files getting stored??
you find the trace files into
Normally trace file are difficult to handle..so we use the tkprof utility
tkprof tracefile.trc t1 RECORD=sqlfile.sql
in other case they are stored in the directory specified in user_dump_dest parameter in the init.ora file
4.What does SQLTrace utility provides??
The diagnostic tool 'sql trace' provides performance information aboutindividual SQL statements and generates the following statistics foreach statement:
* parse, execute, and fetch counts
* CPU and elapsed times
* physical reads and logical reads
* number of rows processed
* misses on the library cache
This information is input to a trace (.trc) file and sql trace can beenabled/disabled for a session or an instance
5.What all setups need to be done for enabling trace in a instance??
The following parameters need to be set up in the "init
* SQL_TRACE + Enable/Disable SQL Trace for the instance.
Values -- TRUE Enable statistics to be collected for all sessions.
FALSE Disable statistics to be collected for all sessions.
* TIMED_STATISTICS + Enable/Disable the collection of timed statistics, such as CPU and elapsed times.
Values ------ TRUE Enable timing (we usually recommend this)
FALSE Default value.
* MAX_DUMP_FILE_SIZE : + Specifies the maximum size of trace files operating system blocks.
Values ------ The default value for this is 500 but if your trace file is truncated then increase this value.
* USER_DUMP_DEST: Specifies the destination for the trace file.
Values : The default value for this parameter is the default destination for system dumps on your operating system.
6.How to enable the trace for a session?
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET SQL_TRACE = FALSE;
For timed statistics
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
ALTER SYSTEM SET TIMED_STATISTICS = FALSE;
7.how to set SQL Trace Facility for an Instance?
If the initialization parameter SQL_TRACE=TRUE, then statisticswill be gathered for ALL sessions. If the facility has been enabled forthe instance, it may be disabled for an individual session by issuing bythe above SQL statement
Wednesday, August 6, 2008
Descriptive Flex Fields in Custom Forms
Hi All,
Here i would like to discuss the basic steps for implementing descriptive flex in our custom forms..These steps you will find in the application develeoper as well as in may sites on the net..
i am putting the content from one of the documents i have for ready reference and single source place..
If you are creating a DFF on the custom form for already defined DFF jump to step 4..
Step1: Registering your table(Required in case of the DFF on a custom table)
Use the add_dd package to register the table and the corresponding columns
For sample....
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'CHN_REP_CODE', 1, 'VARCHAR2', 10, 'N', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'NAME', 2, 'VARCHAR2', 30, 'N', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'LOCATION', 3, 'VARCHAR2', 10, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'REGION', 4, 'VARCHAR2', 5, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'PRIORITY', 5, 'VARCHAR2', 1, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'STATUS', 6, 'VARCHAR2', 1, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'EFFECTIVE_DATE', 7, 'DATE', 10, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'PARENT_CODE', 8, 'VARCHAR2', 10, 'Y', 'N');EXECUTE ad_dd.register_column('CUSTOM', 'C01_CHN_REP','last_update_date', 9, 'DATE', 10, 'N'
Step2:Check whether all the columns are registered or not...
use the sysadmin responsibility and see the table details..
Step3:Register the Descriptive Flex field..
Step4:Create a new item and attach the TEXT_ITEM_DESC_FLEX subclass information and set the following properties
Subclass ='Text_Item_Desc_Flex'
Database Items = 'No'
Query Allowed 'Yes'
Insert Allowed 'YES'
Update Allowed 'YES'
List of Values = 'Enable_List_Lamp'
Validate from list 'No'
Form Builder Flexfield a control 1 character size is created on the canvas. Block is correct C01_CHN_REP_BLK
Step5:use a event hanlder to make the code as per oracle standards..
2. Create a Procedure as event handler for the field. This will be used in WHEN-NEW-FORM-INSTANCE
to initialize the flexfield.
PROCEDURE c01_desc_flex( EVENT varchar2) IS
BEGIN
if ( event = 'WHEN-NEW-FORM-INSTANCE') then
FND_DESCR_FLEX.DEFINE( BLOCK=>'C01_CHN_REP_BLK',
FIELD=>'DESC_FLEX',
APPL_SHORT_NAME=>'CUSTOM',
DESC_FLEX_NAME=>'C01_CHN_Descr_flex') ;
else
null ;
end if ;
END;
3. Call the procedure c01_desc_flex , in WHEN-NEW-FORM-INSTANCE
C01_DESC_FLEX(‘WHEN-NEW-FORM-INSTANCE’);
4. Oracle apps provide api FND_FLEX for Descr flex field events. You call it from all events that can edit the DESC_FLEX field.
It is better to write all these block level triggers to have consistent normal behaviour of the descriptive flex field.
FND_FLEX.event('WHEN-NEW-ITEM-INSTANCE') ;
from the WHEN-VALIDATE-ITEM trigger of the DESC_FLEX item as
FND_FLEX.EVENT('WHEN-VALIDATE-ITEM ');
FND_FLEX.EVENT(’PRE-QUERY’);
FND_FLEX.EVENT(’POST-QUERY’);
FND_FLEX.EVENT('WHEN-VALIDATE-RECORD');
FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE');
FND_FLEX.EVENT('PRE-INSERT');
FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');
FND_FLEX.EVENT('PRE-UPDATE);
Here i would like to discuss the basic steps for implementing descriptive flex in our custom forms..These steps you will find in the application develeoper as well as in may sites on the net..
i am putting the content from one of the documents i have for ready reference and single source place..
If you are creating a DFF on the custom form for already defined DFF jump to step 4..
Step1: Registering your table(Required in case of the DFF on a custom table)
Use the add_dd package to register the table and the corresponding columns
For sample....
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'CHN_REP_CODE', 1, 'VARCHAR2', 10, 'N', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'NAME', 2, 'VARCHAR2', 30, 'N', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'LOCATION', 3, 'VARCHAR2', 10, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'REGION', 4, 'VARCHAR2', 5, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'PRIORITY', 5, 'VARCHAR2', 1, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'STATUS', 6, 'VARCHAR2', 1, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'EFFECTIVE_DATE', 7, 'DATE', 10, 'Y', 'N');
EXEC ad_dd.register_column('CUSTOM', 'C01_CHN_REP', 'PARENT_CODE', 8, 'VARCHAR2', 10, 'Y', 'N');EXECUTE ad_dd.register_column('CUSTOM', 'C01_CHN_REP','last_update_date', 9, 'DATE', 10, 'N'
Step2:Check whether all the columns are registered or not...
use the sysadmin responsibility and see the table details..
Step3:Register the Descriptive Flex field..
Step4:Create a new item and attach the TEXT_ITEM_DESC_FLEX subclass information and set the following properties
Subclass ='Text_Item_Desc_Flex'
Database Items = 'No'
Query Allowed 'Yes'
Insert Allowed 'YES'
Update Allowed 'YES'
List of Values = 'Enable_List_Lamp'
Validate from list 'No'
Form Builder Flexfield a control 1 character size is created on the canvas. Block is correct C01_CHN_REP_BLK
Step5:use a event hanlder to make the code as per oracle standards..
2. Create a Procedure as event handler for the field. This will be used in WHEN-NEW-FORM-INSTANCE
to initialize the flexfield.
PROCEDURE c01_desc_flex( EVENT varchar2) IS
BEGIN
if ( event = 'WHEN-NEW-FORM-INSTANCE') then
FND_DESCR_FLEX.DEFINE( BLOCK=>'C01_CHN_REP_BLK',
FIELD=>'DESC_FLEX',
APPL_SHORT_NAME=>'CUSTOM',
DESC_FLEX_NAME=>'C01_CHN_Descr_flex') ;
else
null ;
end if ;
END;
3. Call the procedure c01_desc_flex , in WHEN-NEW-FORM-INSTANCE
C01_DESC_FLEX(‘WHEN-NEW-FORM-INSTANCE’);
4. Oracle apps provide api FND_FLEX for Descr flex field events. You call it from all events that can edit the DESC_FLEX field.
It is better to write all these block level triggers to have consistent normal behaviour of the descriptive flex field.
FND_FLEX.event('WHEN-NEW-ITEM-INSTANCE') ;
from the WHEN-VALIDATE-ITEM trigger of the DESC_FLEX item as
FND_FLEX.EVENT('WHEN-VALIDATE-ITEM ');
FND_FLEX.EVENT(’PRE-QUERY’);
FND_FLEX.EVENT(’POST-QUERY’);
FND_FLEX.EVENT('WHEN-VALIDATE-RECORD');
FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE');
FND_FLEX.EVENT('PRE-INSERT');
FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');
FND_FLEX.EVENT('PRE-UPDATE);
Friday, July 25, 2008
Customizing Forms in HRMS-Part1(Custom Form)
Hi All,
Quite often we get the requirements for restricting access and fuctionalites in form which is normally done either by formspersonalization or custom.pll or standard form modification
In HRMS quite often i have seen the restrictions like create applicant ,create placment ,create employement functionalities to certain responsibilities...For these forms there is no need to do forms personalization ...because oracle have developed these forms in a architecture to allow common customisations by doing setup..
Customized version
Quite often we get the requirements for restricting access and fuctionalites in form which is normally done either by formspersonalization or custom.pll or standard form modification
In HRMS quite often i have seen the restrictions like create applicant ,create placment ,create employement functionalities to certain responsibilities...For these forms there is no need to do forms personalization ...because oracle have developed these forms in a architecture to allow common customisations by doing setup..
1.Custom form functionality
2.Task Flows..
First let us look at the custom Form Functionality..
For example
For some responsibility I want to allow the user to create Employees, Contingent workers and Other but not Applicants..Lets see how implement this requirement
1: Define custom form
Responsibility: Global HR Manager (or alike)
Navigation : Security > CustomFormSelect 'Combined Person & Assignment Form'
as Form to be customized
Customized version
Give your custom form a unique name and titles.
SaveRestrictions
Type: Restrict by person action
Value: Create employmentAdd any actions you want to have enabled on the form.
2. Define form function that uses this custom function
Responsibility: System AdministratorNavigation: Application > functionCreate a new form functionType: FormForm: Combined Person & Assignment FormParameters: HR_CUSTOMIZATION="xx_your_custom_form" in our case it will beHR_CUSTOMIZATION="CREATE ACTION"
3. Add this new function in your menuResponsibility: System Administrator
Navigation: Application > menuQuery the menu your responsibility is using and replace the existing function with the newly created custom function.
Please note that HR_CUSTOMIZATION parameter does not work in conjunction with WORKFLOW_NAME (Task Flow)parameter
Tuesday, July 1, 2008
Sending mails from PLSQL..UTL_SMTP
Hi All,
Recently we got a requirement for sending mail..on a business logic..we tried to set the mail system on alerts..but still not succesful due to some other reasons..which we are still figuring out..
In the mean time we tried to send mail functionality from PLSQL..This is one package we found out in asktom.oracle.com...use this one directly to compile ..and send mail...it works...
create or replace package XX_DOF_mail_pkg as
type array1 is table of varchar2(255);
procedure send( p_sender_email in varchar2,
p_from in varchar2,
p_to in array default array(),
p_cc in array default array(),
p_bcc in array default array(),
p_subject in varchar2,
p_body in long );
end;
create or replace package body mail_pkg as
g_crlf char(2) default chr(13)chr(10);
g_mail_conn utl_smtp.connection;
g_mailhost varchar2(255) := 'govmail1.gov.com;--Give your smtp mail address..check whether it exists in teh host file or not...
function address_email( p_string in varchar2,
p_recipients in array ) return varchar2 is
l_recipients long;
begin for i in 1 .. p_recipients.count loop
utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
if ( l_recipients is null ) then
l_recipients := p_string p_recipients(i) ;
else
l_recipients := l_recipients ', ' p_recipients(i);
end if;
end loop;
return l_recipients;
end;
procedure send( p_sender_email in varchar2,
p_from in varchar2 ,
p_to in array default array(),
p_cc in array default array(),
p_bcc in array default array(),
p_subject in varchar2 ,
p_body in long ) is
l_to_list long;
l_cc_list long;
l_bcc_list long;
l_date varchar2(255) default to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );
procedure writeData( p_text in varchar2 ) as
begin
if ( p_text is not null ) then
utl_smtp.write_data( g_mail_conn, p_text g_crlf );
end if;
end;
begin
g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
utl_smtp.helo(g_mail_conn, g_mailhost);
utl_smtp.mail(g_mail_conn, p_sender_email);
l_to_list := address_email( 'To: ', p_to );
l_cc_list := address_email( 'Cc: ', p_cc );
l_bcc_list := address_email( 'Bcc: ', p_bcc );
utl_smtp.open_data(g_mail_conn );
writeData( 'Date: ' l_date );
writeData( 'From: ' nvl( p_from, p_sender_email ) );
writeData( 'Subject: ' nvl( p_subject, '(no subject)' ) );
writeData( l_to_list ); writeData( l_cc_list );
utl_smtp.write_data( g_mail_conn, '' g_crlf );
utl_smtp.write_data(g_mail_conn, p_body );
utl_smtp.close_data(g_mail_conn );
utl_smtp.quit(g_mail_conn); end;
end;
/*******************************************************/
use this script to test the error....
begin mail_pkg.send
( p_sender_email => 'murthyganjam@gmail.com',
p_from => 'Oracle Database Account <mailto:murthyganjam@gmail.com,
p_to => mail_pkg.array( 'murthyganjam@gmail.com', 'murthyganjam@gmail.com' ),
p_cc => mail_pkg.array( 'murthyganjam@gmail.com' ),
p_bcc => mail_pkg.array( 'murthyganjam@gmail.com' ),
p_subject => 'Testing mailing package',
p_body => 'Hello murthy, this is the mail you need' );
end;
in case you want to handle the exception block for this program use the following exception block in the package...
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
Recently we got a requirement for sending mail..on a business logic..we tried to set the mail system on alerts..but still not succesful due to some other reasons..which we are still figuring out..
In the mean time we tried to send mail functionality from PLSQL..This is one package we found out in asktom.oracle.com...use this one directly to compile ..and send mail...it works...
create or replace package XX_DOF_mail_pkg as
type array1 is table of varchar2(255);
procedure send( p_sender_email in varchar2,
p_from in varchar2,
p_to in array default array(),
p_cc in array default array(),
p_bcc in array default array(),
p_subject in varchar2,
p_body in long );
end;
create or replace package body mail_pkg as
g_crlf char(2) default chr(13)chr(10);
g_mail_conn utl_smtp.connection;
g_mailhost varchar2(255) := 'govmail1.gov.com;--Give your smtp mail address..check whether it exists in teh host file or not...
function address_email( p_string in varchar2,
p_recipients in array ) return varchar2 is
l_recipients long;
begin for i in 1 .. p_recipients.count loop
utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
if ( l_recipients is null ) then
l_recipients := p_string p_recipients(i) ;
else
l_recipients := l_recipients ', ' p_recipients(i);
end if;
end loop;
return l_recipients;
end;
procedure send( p_sender_email in varchar2,
p_from in varchar2 ,
p_to in array default array(),
p_cc in array default array(),
p_bcc in array default array(),
p_subject in varchar2 ,
p_body in long ) is
l_to_list long;
l_cc_list long;
l_bcc_list long;
l_date varchar2(255) default to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );
procedure writeData( p_text in varchar2 ) as
begin
if ( p_text is not null ) then
utl_smtp.write_data( g_mail_conn, p_text g_crlf );
end if;
end;
begin
g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
utl_smtp.helo(g_mail_conn, g_mailhost);
utl_smtp.mail(g_mail_conn, p_sender_email);
l_to_list := address_email( 'To: ', p_to );
l_cc_list := address_email( 'Cc: ', p_cc );
l_bcc_list := address_email( 'Bcc: ', p_bcc );
utl_smtp.open_data(g_mail_conn );
writeData( 'Date: ' l_date );
writeData( 'From: ' nvl( p_from, p_sender_email ) );
writeData( 'Subject: ' nvl( p_subject, '(no subject)' ) );
writeData( l_to_list ); writeData( l_cc_list );
utl_smtp.write_data( g_mail_conn, '' g_crlf );
utl_smtp.write_data(g_mail_conn, p_body );
utl_smtp.close_data(g_mail_conn );
utl_smtp.quit(g_mail_conn); end;
end;
/*******************************************************/
use this script to test the error....
begin mail_pkg.send
( p_sender_email => 'murthyganjam@gmail.com',
p_from => 'Oracle Database Account <mailto:murthyganjam@gmail.com,
p_to => mail_pkg.array( 'murthyganjam@gmail.com', 'murthyganjam@gmail.com' ),
p_cc => mail_pkg.array( 'murthyganjam@gmail.com' ),
p_bcc => mail_pkg.array( 'murthyganjam@gmail.com' ),
p_subject => 'Testing mailing package',
p_body => 'Hello murthy, this is the mail you need' );
end;
in case you want to handle the exception block for this program use the following exception block in the package...
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
Saturday, June 28, 2008
All About SQL Loader-1
Hi all,
I am trying for some information on sqlloader on net..there is tonnes of information available..
out of all at one place i found this artice which covers most of the sqlloader queries...
"http://www.orafaq.com/wiki/SQL%2ALoader_FAQ"
I am putting this here for ready reference...next atricel i will try to cover doing it from oracel applications...
What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
How does one use the SQL*Loader utility?
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example: sqlldr username@server/password control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data: load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
The mydata.csv file may look like this: 10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file: load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
How does one load MS-Excel data into Oracle?
Open the MS-Excel spreadsheet and save it as a CSV (Comma Separated Values) file. This file can now be copied to the Oracle machine and loaded using the SQL*Loader utility.
Possible problems and workarounds:
The spreadsheet may contain cells with newline characters (ALT+ENTER). SQL*Loader expects the entire record to be on a single line. Run the following macro to remove newline characters (Tools -> Macro -> Visual Basic Editor): ' Removing tabs and carriage returns from worksheet cells
Sub CleanUp()
Dim TheCell As Range
On Error Resume Next
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub
Tools:
If you need a utility to load Excel data into Oracle, download quickload from sourceforge at http://sourceforge.net/projects/quickload
Is there a SQL*Unloader to download data to a flat file?
Oracle does not supply any data unload utilities. Here are some workarounds:
Using SQL*Plus
You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (common separated values) file that can be imported into MS-Excel: set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 ',' col2 ',' col3
from tab1
where col2 = 'XYZ';
spool off
You can also use the "set colsep ," command if you don't want to put the commas in by hand. This saves a lot of typing: set colsep ,
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
from tab1
where col2 = 'XYZ';
spool off
Using PL/SQL
PL/SQL's UTL_FILE package can also be used to unload data. Example: declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
Third-party programs
You might also want to investigate third party tools to help you unload data from Oracle. Here are some examples:
WisdomForce FastReader - http://www.wisdomforce.com
IxUnload from ixionsoftware.com - http://www.ixionsoftware.com/products/
FAst extraCT (FACT) for Oracle from CoSort - http://www.cosort.com/products/FACT
Unicenter (also ManageIT or Platinum) Fast Unload for Oracle from CA
Keeptool's Hora unload/load facility (part v5 to v6 upgrade) can export to formats cuch as as Microsoft Excel, DBF, XML, and text - http://www.keeptool.com/en/keeptool_hora.php
TOAD from Quest
SQLWays from Ispirer Systems
PL/SQL Developer from allroundautomation
Can one load variable and fix length data records?
Loading delimited (variable length) data
In the first example we will show how delimited (variable length) data can be loaded into Oracle: LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example: ...
resume char(4000),
...
Loading positional (fixed length) data
If you need to load positional data (fixed length), look at the following control file example: LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
For example, position(01:05) will give the 1st to the 5th character (11111 and 22222).
Can one skip header records while loading?
One can skip unwanted header records or continue an interrupted load (for example if you run out of space) by specifying the "SKIP=n" keyword. "n" specifies the number of logical rows to skip. Look at these examples: OPTIONS (SKIP=5)
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
...
sqlldr userid=ora_id/ora_passwd control=control_file_name.ctl skip=4
If you are continuing a multiple table direct path load, you may need to use the CONTINUE_LOAD clause instead of the SKIP parameter. CONTINUE_LOAD allows you to specify a different number of rows to skip for each of the tables you are loading.
Can one modify data as the database gets loaded?
Data can be modified as it loads into the Oracle Database. One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads). Here are some examples: LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
Can one load data from multiple files/ into multiple tables at once?
Loading from multiple input files
One can load from multiple input files provided they use the same record format by repeating the INFILE clause. Here is an example: LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
Loading into multiple tables
One can also specify multiple "INTO TABLE" clauses in the SQL*Loader control file to load into multiple tables. Look at the following example: LOAD DATA
INFILE *
INTO TABLE tab1 WHEN tab = 'tab1'
( tab FILLER CHAR(4),
col1 INTEGER
)
INTO TABLE tab2 WHEN tab = 'tab2'
( tab FILLER POSITION(1:4),
col1 INTEGER
)
BEGINDATA
tab11
tab12
tab22
tab33
The "tab" field is marked as a FILLER as we don't want to load it.
Note the use of "POSITION" on the second routing value (tab = 'tab2'). By default field scanning doesn't start over from the beginning of the record for new INTO TABLE clauses. Instead, scanning continues where it left off. POSITION is needed to reset the pointer to the beginning of the record again.
Another example: LOAD DATA
INFILE 'mydata.dat'
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37: LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
NOTE: SQL*Loader does not allow the use of OR in the WHEN clause. You can only use AND as in the example above! To workaround this problem, code multiple "INTO TABLE ... WHEN" clauses. Here is an example: LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
INTO TABLE my_selective_table
WHEN (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
Can one skip certain columns while loading data?
One cannot use POSITION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example: LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
CONCATENATE - use when SQL*Loader should combine the same number of physical recordstogether to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
How can one get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. For details, refer to the FAQ about the differences between the conventional and direct path loader below.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Oracle Server Utilities Guide (see chapter 8).
Some of the restrictions with direct path loads are:
Loaded data will not be replicated
Cannot always use SQL strings for column processing in the control file (something like this will probably fail: col1 date "ddmonyyyy" "substr(:period,1,9)"). Details are in Metalink Note:230120.1.
How does one use SQL*Loader to load images, sound clips and documents?
Any one has more information on this section please put forward ..
SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOBFILE. The LOBFILE method provides an easy way to load documents, photos, images and audio clips into BLOB and CLOB columns. Look at this example:
Given the following table: CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
Control File: LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg
How does one load EBCDIC data?
Specify the character set WE8EBCDIC500 for the EBCDIC data. The following example shows the SQL*Loader controlfile to load a fixed length EBCDIC record into the Oracle Database: LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE data.ebc "fix 86 buffers 1024"
BADFILE data.bad'
DISCARDFILE data.dsc'
REPLACE
INTO TABLE temp_data
(
field1 POSITION (1:4) INTEGER EXTERNAL,
field2 POSITION (5:6) INTEGER EXTERNAL,
field3 POSITION (7:12) INTEGER EXTERNAL,
field4 POSITION (13:42) CHAR,
field5 POSITION (43:72) CHAR,
field6 POSITION (73:73) INTEGER EXTERNAL,
field7 POSITION (74:74) INTEGER EXTERNAL,
field8 POSITION (75:75) INTEGER EXTERNAL,
field9 POSITION (76:86) INTEGER EXTERNAL
)
I am trying for some information on sqlloader on net..there is tonnes of information available..
out of all at one place i found this artice which covers most of the sqlloader queries...
"http://www.orafaq.com/wiki/SQL%2ALoader_FAQ"
I am putting this here for ready reference...next atricel i will try to cover doing it from oracel applications...
What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
How does one use the SQL*Loader utility?
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example: sqlldr username@server/password control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data: load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
The mydata.csv file may look like this: 10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file: load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
How does one load MS-Excel data into Oracle?
Open the MS-Excel spreadsheet and save it as a CSV (Comma Separated Values) file. This file can now be copied to the Oracle machine and loaded using the SQL*Loader utility.
Possible problems and workarounds:
The spreadsheet may contain cells with newline characters (ALT+ENTER). SQL*Loader expects the entire record to be on a single line. Run the following macro to remove newline characters (Tools -> Macro -> Visual Basic Editor): ' Removing tabs and carriage returns from worksheet cells
Sub CleanUp()
Dim TheCell As Range
On Error Resume Next
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub
Tools:
If you need a utility to load Excel data into Oracle, download quickload from sourceforge at http://sourceforge.net/projects/quickload
Is there a SQL*Unloader to download data to a flat file?
Oracle does not supply any data unload utilities. Here are some workarounds:
Using SQL*Plus
You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (common separated values) file that can be imported into MS-Excel: set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 ',' col2 ',' col3
from tab1
where col2 = 'XYZ';
spool off
You can also use the "set colsep ," command if you don't want to put the commas in by hand. This saves a lot of typing: set colsep ,
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
from tab1
where col2 = 'XYZ';
spool off
Using PL/SQL
PL/SQL's UTL_FILE package can also be used to unload data. Example: declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
Third-party programs
You might also want to investigate third party tools to help you unload data from Oracle. Here are some examples:
WisdomForce FastReader - http://www.wisdomforce.com
IxUnload from ixionsoftware.com - http://www.ixionsoftware.com/products/
FAst extraCT (FACT) for Oracle from CoSort - http://www.cosort.com/products/FACT
Unicenter (also ManageIT or Platinum) Fast Unload for Oracle from CA
Keeptool's Hora unload/load facility (part v5 to v6 upgrade) can export to formats cuch as as Microsoft Excel, DBF, XML, and text - http://www.keeptool.com/en/keeptool_hora.php
TOAD from Quest
SQLWays from Ispirer Systems
PL/SQL Developer from allroundautomation
Can one load variable and fix length data records?
Loading delimited (variable length) data
In the first example we will show how delimited (variable length) data can be loaded into Oracle: LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example: ...
resume char(4000),
...
Loading positional (fixed length) data
If you need to load positional data (fixed length), look at the following control file example: LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
For example, position(01:05) will give the 1st to the 5th character (11111 and 22222).
Can one skip header records while loading?
One can skip unwanted header records or continue an interrupted load (for example if you run out of space) by specifying the "SKIP=n" keyword. "n" specifies the number of logical rows to skip. Look at these examples: OPTIONS (SKIP=5)
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
...
sqlldr userid=ora_id/ora_passwd control=control_file_name.ctl skip=4
If you are continuing a multiple table direct path load, you may need to use the CONTINUE_LOAD clause instead of the SKIP parameter. CONTINUE_LOAD allows you to specify a different number of rows to skip for each of the tables you are loading.
Can one modify data as the database gets loaded?
Data can be modified as it loads into the Oracle Database. One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads). Here are some examples: LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
Can one load data from multiple files/ into multiple tables at once?
Loading from multiple input files
One can load from multiple input files provided they use the same record format by repeating the INFILE clause. Here is an example: LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
Loading into multiple tables
One can also specify multiple "INTO TABLE" clauses in the SQL*Loader control file to load into multiple tables. Look at the following example: LOAD DATA
INFILE *
INTO TABLE tab1 WHEN tab = 'tab1'
( tab FILLER CHAR(4),
col1 INTEGER
)
INTO TABLE tab2 WHEN tab = 'tab2'
( tab FILLER POSITION(1:4),
col1 INTEGER
)
BEGINDATA
tab11
tab12
tab22
tab33
The "tab" field is marked as a FILLER as we don't want to load it.
Note the use of "POSITION" on the second routing value (tab = 'tab2'). By default field scanning doesn't start over from the beginning of the record for new INTO TABLE clauses. Instead, scanning continues where it left off. POSITION is needed to reset the pointer to the beginning of the record again.
Another example: LOAD DATA
INFILE 'mydata.dat'
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37: LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
NOTE: SQL*Loader does not allow the use of OR in the WHEN clause. You can only use AND as in the example above! To workaround this problem, code multiple "INTO TABLE ... WHEN" clauses. Here is an example: LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
INTO TABLE my_selective_table
WHEN (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
Can one skip certain columns while loading data?
One cannot use POSITION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example: LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
CONCATENATE - use when SQL*Loader should combine the same number of physical recordstogether to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
How can one get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. For details, refer to the FAQ about the differences between the conventional and direct path loader below.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Oracle Server Utilities Guide (see chapter 8).
Some of the restrictions with direct path loads are:
Loaded data will not be replicated
Cannot always use SQL strings for column processing in the control file (something like this will probably fail: col1 date "ddmonyyyy" "substr(:period,1,9)"). Details are in Metalink Note:230120.1.
How does one use SQL*Loader to load images, sound clips and documents?
Any one has more information on this section please put forward ..
SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOBFILE. The LOBFILE method provides an easy way to load documents, photos, images and audio clips into BLOB and CLOB columns. Look at this example:
Given the following table: CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
Control File: LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg
How does one load EBCDIC data?
Specify the character set WE8EBCDIC500 for the EBCDIC data. The following example shows the SQL*Loader controlfile to load a fixed length EBCDIC record into the Oracle Database: LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE data.ebc "fix 86 buffers 1024"
BADFILE data.bad'
DISCARDFILE data.dsc'
REPLACE
INTO TABLE temp_data
(
field1 POSITION (1:4) INTEGER EXTERNAL,
field2 POSITION (5:6) INTEGER EXTERNAL,
field3 POSITION (7:12) INTEGER EXTERNAL,
field4 POSITION (13:42) CHAR,
field5 POSITION (43:72) CHAR,
field6 POSITION (73:73) INTEGER EXTERNAL,
field7 POSITION (74:74) INTEGER EXTERNAL,
field8 POSITION (75:75) INTEGER EXTERNAL,
field9 POSITION (76:86) INTEGER EXTERNAL
)
Monday, June 9, 2008
Query Find Form/ Find Block Form
1. Open APPSTAND form in your current form first
2. drag and drop QUERY_FIND object from APPSTAND as
COPY onto c01_CHN_REP form.
After It copies in three places.
1. then Delete QUERY_FIND object group from your C01_CHN_REP form.
Query Find
There are two implementations for Query Find. One implementation shows a Row–LOV that shows the available rows and allows you to choose one. The other implementation opens a Find window, which shows you the fields the user is likely to want to use for selecting data.
Use only one implementation for a given block. All queryable blocks within your form should support Query Find. The Oracle Applications User Interface Standards for Forms–Based Products describe what situations are better served by the two implementations.
Query Find Window
To implement a Find window, create an additional window that contains the fields a user is most likely to search by when they initiate the search and copy all the item values from that block into the results block just before executing a query.
Step 1: Copy the Query_find object group from APPSTAND, by opening APPSTAND form. Delete the object group from your form. ( after they are copied you do not need the Object group). Rename the appropriate objects
Step 2:
Rename the New Block, Canvas and Window just now created please !!
Rename the Find Block, Canvas, and Window.
Set the Queryable property of the block to No. ( query Allowed = N )
For this example, rename the block, canvas and window to
QF_BLK, QF_CANVAS and QF_W
Step3:
Edit the NEW Button’s Triggerin the QF_BLK provided button.
Edit the WHEN–BUTTON–PRESSED trigger for the NEW button in the Find window block so that it passes the Results block name as the argument. This information allows Oracle Applications to navigate to your block and place you on a new record. This button is included because when you first enter a form, the Find window may automatically come up; users who want to immediately start entering a new record can press this button. The code you will add is :
app_find.new('C01_CHN_REP_BLK');
Step 4:
Edit the FIND Button’s Trigger
Edit the WHEN–BUTTON–PRESSED trigger for the FIND button so that it passes the Results block name. This information allows Oracle Applications to navigate to your block and execute a query.
app_find.find(’’); becomes
:parameter.G_query_find := 'TRUE';
/* can place additional Validation code here */
fnd_message.set_string('Inside when-button-pressed:
Find');
fnd_message.show ;
app_find.find('C01_CHN_REP_BLK');
:parameter.G_query_find := 'FALSE';
If you need to do further validation of items in the Find window, place your code before the call to APP_FIND.FIND. Specifically, you should validate that any low/high range fields are correct.
Step 5:
Set Navigation Data Block Properties
Set the Previous Navigation Data Block property of the
QF_BLK (Find ) block to be
the results block ( C01_CHN_REP_BLK). This allows the user to leave the Find window
without executing a query.
From the results block, next and previous data block only move up and down the hierarchy of objects; they never take you to the Find window.
Step 6:
Edit the KEY–NXTBLK Trigger
Edit the KEY–NXTBLK trigger on the QF_BLK Find block so that it has the exact
same functionality as the FIND button.
If the user selects ”Go–>NextBlock,” the behavior should mimic pressing the FIND button.
:parameter.G_query_find := 'TRUE';
app_find.find('C01_CHN_REP_BLK');
:parameter.G_query_find := 'FALSE';
Step 7:
Change the Find Window Title
Change the title of the QF_W Find window.
The Channel example uses ”Find Channel Reps”.
Step 8:
Create Necessary Items
Create the items that the user can query on in the Find window block.
You may find it convenient to copy items from the Results block to the Find window block.
Follow these guidelines for items in the Find window:
??Set the Required property to No
??Set the default value to NULL
??If you copied the items from the Results block, ensure that your new items all have Database Item set to No, and remove all triggers associated with them (especially validation triggers). If for some reason you decide you need to keep a particular trigger,
remember to change the fields it references to point to the Find block.
??Typically, an item in the Find window block has an LOV associated with it, because users should usually be able to select exactly one valid value for the item. The LOV should show all values that have ever been valid, not just those values that are currently valid. Date fields may use the Calendar and the related KEY–LISTVAL trigger.
??If you have an item that has a displayed value and an associated ID field, the Find window block should have both as well. The ID field should be used to drive the query to improve performance.
Step 9
Fit the Find Window to Your Form
Adjust your Find window for your specific case: resize the window,
position, fields, and so on.( very dangerous fear WATSON !! )
Create a PRE–QUERY Trigger
Create a block–level Pre–Query trigger in the Results block CHN_REP_BLK (Execution Hierarchy: Before)
that copies query criteria from the Find window block to the Results block (where the query actually occurs).
You can use the Oracle Forms COPY built–in to copy character data.
For other data types, you can assign the values directly using :=, but this method does not allow the user to use wildcards. However, most of your Find window items use LOVs to provide a unique value, so wildcards would not be necessary.
IF :parameter.G_query_find = ’TRUE’ THEN
COPY (,’’);
:parameter.G_query_find := ’FALSE’;
END IF;
Example for our form in PRE-QUERY trigger on CHN_REP_BLK
if :parameter.G_query_find = 'TRUE'
then
copy (:qf_blk.qf_chn_rep_code,
'C01_CHN_REP_BLK.CHN_REP_CODE') ;
copy (:qf_blk.qf_NAME, 'C01_CHN_REP_BLK.NAME') ;
:parameter.G_query_find := 'FALSE';
end if ;
Step 11
Create a QUERY_FIND Trigger
Create a block–level user–named trigger ”QUERY_FIND”
(Execution Hierarchy: Override) on the CHN_REP_BLK Results block
that contains:
APP_FIND.QUERY_FIND(’’,
’’,
’’);
/* Result Window, Query window, Query Blk */
app_find.query_find('C01_CHN_REPS_W',
'QF_W',
'QF_BLK' ) ;
Step 12:
Raising Query Find on Form Startup
Note: If you want a Row–LOV or
Find window to raise immediately upon entering the form, at the end of your WHEN–NEW–FORM–INSTANCE trigger, call:
EXECUTE_TRIGGER(’QUERY_FIND’);
This will simulate the user invoking the function while in the first block of the form.
Also Save version c01_chn_rep_V8.fmb
Implementing Row–LOV
To implement a Row–LOV, create an LOV that selects the primary key of the row the user wants into a form parameter, and then copy that value into the primary key field in the results block right before executing a query.
This example uses the DEPT block, which is based on the DEPT table, and consists of the three columns DEPTNO, DNAME and LOC. This table contains a row for each department in a company.
Step 1: Create a Parameter for Your Primary Key
Create a form parameter(s) to hold the primary key(s) for the LOV. If the Row–LOV is for a detail block, you do not need a parameter for the foreign key to the master block (the join column(s)), as you should include that column in the WHERE clause of your record group in a later step. Set the data type and length appropriately.
For example, for the DEPT block, create a parameter called DEPTNO_QF.
Step 2: Create an LOV
Create an LOV that includes the columns your user needs to identify the desired row. If the Row–LOV is for a detail block, you should include the foreign key to the master block (the join column(s)) in the WHERE clause of your record group. Return the primary key for the row into the parameter.
For our example, create an LOV, DEPT_QF that contains the columns DEPTNO and DNAME. Set the return item for DEPTNO into parameter DEPTNO_QF. Although the user sees DNAME, it is not returned into any field.
Step 3: Create a PRE–QUERY Trigger
Create a block–level PRE–QUERY trigger (Execution Hierarchy: Before) that contains:
IF: PARAMETER.G_QUERY_FIND = ’TRUE’ THEN
:= :parameter.;
: PARAMETER.G_QUERY_FIND := ’FALSE’;
END IF;
For the Dept example, your PRE–QUERY trigger contains:
IF :parameter.G_query_find = ’TRUE’ THEN
: DEPT.DEPTNO := :parameter.DEPTNO_QF
:parameter.G_query_find := ’FALSE’;
END IF;
Step 4: Create a QUERY_FIND Trigger
Finally, create a block–level user–named trigger QUERY_FIND on the results block (Execution Hierarchy: Override) that contains:
APP_FIND.QUERY_FIND (’’);
For DEPT:
APP_FIND.QUERY_FIND (’DEPT_QF’);
2. drag and drop QUERY_FIND object from APPSTAND as
COPY onto c01_CHN_REP form.
After It copies in three places.
1. then Delete QUERY_FIND object group from your C01_CHN_REP form.
Query Find
There are two implementations for Query Find. One implementation shows a Row–LOV that shows the available rows and allows you to choose one. The other implementation opens a Find window, which shows you the fields the user is likely to want to use for selecting data.
Use only one implementation for a given block. All queryable blocks within your form should support Query Find. The Oracle Applications User Interface Standards for Forms–Based Products describe what situations are better served by the two implementations.
Query Find Window
To implement a Find window, create an additional window that contains the fields a user is most likely to search by when they initiate the search and copy all the item values from that block into the results block just before executing a query.
Step 1: Copy the Query_find object group from APPSTAND, by opening APPSTAND form. Delete the object group from your form. ( after they are copied you do not need the Object group). Rename the appropriate objects
Step 2:
Rename the New Block, Canvas and Window just now created please !!
Rename the Find Block, Canvas, and Window.
Set the Queryable property of the block to No. ( query Allowed = N )
For this example, rename the block, canvas and window to
QF_BLK, QF_CANVAS and QF_W
Step3:
Edit the NEW Button’s Triggerin the QF_BLK provided button.
Edit the WHEN–BUTTON–PRESSED trigger for the NEW button in the Find window block so that it passes the Results block name as the argument. This information allows Oracle Applications to navigate to your block and place you on a new record. This button is included because when you first enter a form, the Find window may automatically come up; users who want to immediately start entering a new record can press this button. The code you will add is :
app_find.new('C01_CHN_REP_BLK');
Step 4:
Edit the FIND Button’s Trigger
Edit the WHEN–BUTTON–PRESSED trigger for the FIND button so that it passes the Results block name. This information allows Oracle Applications to navigate to your block and execute a query.
app_find.find(’
:parameter.G_query_find := 'TRUE';
/* can place additional Validation code here */
fnd_message.set_string('Inside when-button-pressed:
Find');
fnd_message.show ;
app_find.find('C01_CHN_REP_BLK');
:parameter.G_query_find := 'FALSE';
If you need to do further validation of items in the Find window, place your code before the call to APP_FIND.FIND. Specifically, you should validate that any low/high range fields are correct.
Step 5:
Set Navigation Data Block Properties
Set the Previous Navigation Data Block property of the
QF_BLK (Find ) block to be
the results block ( C01_CHN_REP_BLK). This allows the user to leave the Find window
without executing a query.
From the results block, next and previous data block only move up and down the hierarchy of objects; they never take you to the Find window.
Step 6:
Edit the KEY–NXTBLK Trigger
Edit the KEY–NXTBLK trigger on the QF_BLK Find block so that it has the exact
same functionality as the FIND button.
If the user selects ”Go–>NextBlock,” the behavior should mimic pressing the FIND button.
:parameter.G_query_find := 'TRUE';
app_find.find('C01_CHN_REP_BLK');
:parameter.G_query_find := 'FALSE';
Step 7:
Change the Find Window Title
Change the title of the QF_W Find window.
The Channel example uses ”Find Channel Reps”.
Step 8:
Create Necessary Items
Create the items that the user can query on in the Find window block.
You may find it convenient to copy items from the Results block to the Find window block.
Follow these guidelines for items in the Find window:
??Set the Required property to No
??Set the default value to NULL
??If you copied the items from the Results block, ensure that your new items all have Database Item set to No, and remove all triggers associated with them (especially validation triggers). If for some reason you decide you need to keep a particular trigger,
remember to change the fields it references to point to the Find block.
??Typically, an item in the Find window block has an LOV associated with it, because users should usually be able to select exactly one valid value for the item. The LOV should show all values that have ever been valid, not just those values that are currently valid. Date fields may use the Calendar and the related KEY–LISTVAL trigger.
??If you have an item that has a displayed value and an associated ID field, the Find window block should have both as well. The ID field should be used to drive the query to improve performance.
Step 9
Fit the Find Window to Your Form
Adjust your Find window for your specific case: resize the window,
position, fields, and so on.( very dangerous fear WATSON !! )
Create a PRE–QUERY Trigger
Create a block–level Pre–Query trigger in the Results block CHN_REP_BLK (Execution Hierarchy: Before)
that copies query criteria from the Find window block to the Results block (where the query actually occurs).
You can use the Oracle Forms COPY built–in to copy character data.
For other data types, you can assign the values directly using :=, but this method does not allow the user to use wildcards. However, most of your Find window items use LOVs to provide a unique value, so wildcards would not be necessary.
IF :parameter.G_query_find = ’TRUE’ THEN
COPY (
:parameter.G_query_find := ’FALSE’;
END IF;
Example for our form in PRE-QUERY trigger on CHN_REP_BLK
if :parameter.G_query_find = 'TRUE'
then
copy (:qf_blk.qf_chn_rep_code,
'C01_CHN_REP_BLK.CHN_REP_CODE') ;
copy (:qf_blk.qf_NAME, 'C01_CHN_REP_BLK.NAME') ;
:parameter.G_query_find := 'FALSE';
end if ;
Step 11
Create a QUERY_FIND Trigger
Create a block–level user–named trigger ”QUERY_FIND”
(Execution Hierarchy: Override) on the CHN_REP_BLK Results block
that contains:
APP_FIND.QUERY_FIND(’
’
’
/* Result Window, Query window, Query Blk */
app_find.query_find('C01_CHN_REPS_W',
'QF_W',
'QF_BLK' ) ;
Step 12:
Raising Query Find on Form Startup
Note: If you want a Row–LOV or
Find window to raise immediately upon entering the form, at the end of your WHEN–NEW–FORM–INSTANCE trigger, call:
EXECUTE_TRIGGER(’QUERY_FIND’);
This will simulate the user invoking the function while in the first block of the form.
Also Save version c01_chn_rep_V8.fmb
Implementing Row–LOV
To implement a Row–LOV, create an LOV that selects the primary key of the row the user wants into a form parameter, and then copy that value into the primary key field in the results block right before executing a query.
This example uses the DEPT block, which is based on the DEPT table, and consists of the three columns DEPTNO, DNAME and LOC. This table contains a row for each department in a company.
Step 1: Create a Parameter for Your Primary Key
Create a form parameter(s) to hold the primary key(s) for the LOV. If the Row–LOV is for a detail block, you do not need a parameter for the foreign key to the master block (the join column(s)), as you should include that column in the WHERE clause of your record group in a later step. Set the data type and length appropriately.
For example, for the DEPT block, create a parameter called DEPTNO_QF.
Step 2: Create an LOV
Create an LOV that includes the columns your user needs to identify the desired row. If the Row–LOV is for a detail block, you should include the foreign key to the master block (the join column(s)) in the WHERE clause of your record group. Return the primary key for the row into the parameter.
For our example, create an LOV, DEPT_QF that contains the columns DEPTNO and DNAME. Set the return item for DEPTNO into parameter DEPTNO_QF. Although the user sees DNAME, it is not returned into any field.
Step 3: Create a PRE–QUERY Trigger
Create a block–level PRE–QUERY trigger (Execution Hierarchy: Before) that contains:
IF: PARAMETER.G_QUERY_FIND = ’TRUE’ THEN
: PARAMETER.G_QUERY_FIND := ’FALSE’;
END IF;
For the Dept example, your PRE–QUERY trigger contains:
IF :parameter.G_query_find = ’TRUE’ THEN
: DEPT.DEPTNO := :parameter.DEPTNO_QF
:parameter.G_query_find := ’FALSE’;
END IF;
Step 4: Create a QUERY_FIND Trigger
Finally, create a block–level user–named trigger QUERY_FIND on the results block (Execution Hierarchy: Override) that contains:
APP_FIND.QUERY_FIND (’
For DEPT:
APP_FIND.QUERY_FIND (’DEPT_QF’);
Monday, April 28, 2008
Attachment Functionality in Oracle Applications
why do i need a attachment functionality?
The attachments feature enables users to link unstructured data, such as images, wordprocessing documents, spreadsheets, or text to their application data. For example,users can link images to items or video to operations as operation instructions.
Can i enable it to any form i want?
For most of the standar you can do that..i hope custom form done in standard way can also have this feauture enabled
where should i start???
Lets Define the terms first
1.Entity:An entity is an object within Oracle Applications data, such as an item, an order, or anorder line. The attachments feature must be enabled for an entity before users can link attachments to the entity.
2.Category:A document category is a label that users apply to individual attachments anddocuments. Document categories provide security by restricting the documents that canbe viewed or added via a specific form or form function.
In the below example i am tryign to enable the attachment funcionality for the USer form..I want to upload the approval copy for creating a user or attaching new responsibility
what are the steps to do that??
Step1:First Define a Entity for which you are creating the attachments.In my case it is users.
define the base table and entityid and others things
Step2:Define a category(optional).if the attachments belongs logically to different group.
In this example i am addding to the miscellenous category.
Make the assignments of the Function once you define it.
step3:Find the Form Name,Function Name and block name to which the attachment feauture need to be enabled.
Step4:Define the Attachment Function .Seelct the type either you want the attachmetn feature at form level or Function level
Press the Blocks Button. and Enter the Block name you want to enable the attachment feauture.
Press the Entities Button
Enter the entity and other settings and see what fuctionalties are required for this form.like query,insert,update and delete allowed can be done or not from this form
Enter the primary Key so that it show the document based on the primary key.
If it a common document like across users then primary key should not be given.
Press the categories button to select the caegories
Press the Assignments Button in document categories and attach the Function.
Now the attache button is enabled..press the button and upload the document..
uploading document is self explainable
for developing some complex one..read tthe application developer for more info..
more or less it depends on the entity definition and the primary key for the document..
you can have more than one primary key...just play aroud with the primary key..you will be able to get any requirement...
The attachments feature enables users to link unstructured data, such as images, wordprocessing documents, spreadsheets, or text to their application data. For example,users can link images to items or video to operations as operation instructions.
Can i enable it to any form i want?
For most of the standar you can do that..i hope custom form done in standard way can also have this feauture enabled
where should i start???
Lets Define the terms first
1.Entity:An entity is an object within Oracle Applications data, such as an item, an order, or anorder line. The attachments feature must be enabled for an entity before users can link attachments to the entity.
2.Category:A document category is a label that users apply to individual attachments anddocuments. Document categories provide security by restricting the documents that canbe viewed or added via a specific form or form function.
In the below example i am tryign to enable the attachment funcionality for the USer form..I want to upload the approval copy for creating a user or attaching new responsibility
what are the steps to do that??
Step1:First Define a Entity for which you are creating the attachments.In my case it is users.
define the base table and entityid and others things
Step2:Define a category(optional).if the attachments belongs logically to different group.
In this example i am addding to the miscellenous category.
Make the assignments of the Function once you define it.
step3:Find the Form Name,Function Name and block name to which the attachment feauture need to be enabled.
Step4:Define the Attachment Function .Seelct the type either you want the attachmetn feature at form level or Function level
Press the Blocks Button. and Enter the Block name you want to enable the attachment feauture.
Press the Entities Button
Enter the entity and other settings and see what fuctionalties are required for this form.like query,insert,update and delete allowed can be done or not from this form
Enter the primary Key so that it show the document based on the primary key.
If it a common document like across users then primary key should not be given.
Press the categories button to select the caegories
Press the Assignments Button in document categories and attach the Function.
Now the attache button is enabled..press the button and upload the document..
uploading document is self explainable
for developing some complex one..read tthe application developer for more info..
more or less it depends on the entity definition and the primary key for the document..
you can have more than one primary key...just play aroud with the primary key..you will be able to get any requirement...
Sunday, April 27, 2008
How to Make a Form Query only?
Hi all,
This is one of the common requirement.They are many ways of doing this like formpersonalization ,Cutom.plland others ..the easiest way to do it is by passing the parameter QUERY_ONLY=YES..
But it doesn't gurantee all the forms will behave as expected because the form should be coded to make it work for this..not all standard forms have this..
In HRMS Forms case there is another concept called task flow by which we can make the form read only..(i will provide a article on this at some other time)..
Steps:
1.Check the form which need ot be made as query only.
2.Go to the menu to which it is attached and the get the user function name of the form
3.query the function from application developer(resp)-->application-->function
4.query the function you have to modify
5.Create a new function logically same as the old one (attach the same form)
6.Go to Form tab in the functions window.
7.Enter QUERY_ONLY="YES" in the parameters window
8.Attach the new view only function to the required menu..
Using Form Personalization:
My requrirement is to not all the users to add new responsibilities or new user with responsibilities..it is only allowed for certain people..lets see this sceniro implementation throught form personalization
1.Go to the form which needs to be personalized
2.Open the personalization screen
3.create a rule at when new form instance level
make update,delete,insert allowed property to false to the block you want to remove the funcionaltiy
4.save the record..
if you want to give access to only one user sysadmin
create one rule to remove access by making delete ,insert and update allowed false..
Create another rule at new form instance level , context-> user and sysadmin and then make insert ,delete,update allowed true..
These two rules will make that only sysadmin to attach new responsibilities..
This is one of the common requirement.They are many ways of doing this like formpersonalization ,Cutom.plland others ..the easiest way to do it is by passing the parameter QUERY_ONLY=YES..
But it doesn't gurantee all the forms will behave as expected because the form should be coded to make it work for this..not all standard forms have this..
In HRMS Forms case there is another concept called task flow by which we can make the form read only..(i will provide a article on this at some other time)..
Steps:
1.Check the form which need ot be made as query only.
2.Go to the menu to which it is attached and the get the user function name of the form
3.query the function from application developer(resp)-->application-->function
4.query the function you have to modify
5.Create a new function logically same as the old one (attach the same form)
6.Go to Form tab in the functions window.
7.Enter QUERY_ONLY="YES" in the parameters window
8.Attach the new view only function to the required menu..
Using Form Personalization:
My requrirement is to not all the users to add new responsibilities or new user with responsibilities..it is only allowed for certain people..lets see this sceniro implementation throught form personalization
1.Go to the form which needs to be personalized
2.Open the personalization screen
3.create a rule at when new form instance level
make update,delete,insert allowed property to false to the block you want to remove the funcionaltiy
4.save the record..
if you want to give access to only one user sysadmin
create one rule to remove access by making delete ,insert and update allowed false..
Create another rule at new form instance level , context-> user and sysadmin and then make insert ,delete,update allowed true..
These two rules will make that only sysadmin to attach new responsibilities..
Thursday, April 24, 2008
Special Validation type Valuesets
How do you validate the parameter value of a concurrent program submitted through SRS window?
This is one of the recent requirement i come across..after some research i came to know it can be done using special valueset.
1.create a valueset of specail validation type
2.create a validate Event.you can write the PLSQL COde to validate
FND PLSQL "declare
l_email VARCHAR2(2000) := :!VALUE;
BEGIN
IF NVL(UPPER(substr(l_email,-9,9)),'INVALID')<>'GMAIL.COM' THEN
fnd_message.set_name('FND','INVALID_EMAIL');
fnd_message.raise_error;
end if;
end;"
3.attach it to the parameter in ..you will get a warning while saving the program ..ignore it..
As far i checked it is validation only when you enter...
THis is another sample available in one of the forums..but didn't work for me..
FND PLSQL "declare
l_payroll VARCHAR2(20) := ':$FLEX$.VALSET_TST5';
l_input DATE := :!VALUE;
BEGIN
if l_payroll is null and l_input is not null then
fnd_message.set_name('XXPAY','ONLY_ENTER_DATE_WITH_PAYROLL');
fnd_message.raise_error;
end if;
end;"
Jus try to experiment around..this might meet your requirement....
This is one of the recent requirement i come across..after some research i came to know it can be done using special valueset.
1.create a valueset of specail validation type
2.create a validate Event.you can write the PLSQL COde to validate
FND PLSQL "declare
l_email VARCHAR2(2000) := :!VALUE;
BEGIN
IF NVL(UPPER(substr(l_email,-9,9)),'INVALID')<>'GMAIL.COM' THEN
fnd_message.set_name('FND','INVALID_EMAIL');
fnd_message.raise_error;
end if;
end;"
3.attach it to the parameter in ..you will get a warning while saving the program ..ignore it..
As far i checked it is validation only when you enter...
THis is another sample available in one of the forums..but didn't work for me..
FND PLSQL "declare
l_payroll VARCHAR2(20) := ':$FLEX$.VALSET_TST5';
l_input DATE := :!VALUE;
BEGIN
if l_payroll is null and l_input is not null then
fnd_message.set_name('XXPAY','ONLY_ENTER_DATE_WITH_PAYROLL');
fnd_message.raise_error;
end if;
end;"
Jus try to experiment around..this might meet your requirement....
Changing Oracle Applications LOGO
We got this requirement to change the logo after some little search a good article i found in metalink 468971.1 .
1. Attributes of the login page
It is possible to control the display of some attributes of the login page, for instance user name or password hints, language switchers, forgot password link, corporate policy message, etc.
For this, you need to set the profile option 'Local Login Mask' (FND_SSO_LOCAL_LOGIN_MASK) with a number being the sum of the mask values described in the table below:
Description ask value
Hint for Username (USERNAME_HINT) 01
Hint for Password (PASSWORD_HINT) 02
Cancel button (CANCEL_BUTTON) 04
Forgot Password link (FORGOT_PASSWORD_URL) 08
Registration link (REGISTER_URL) 16
Language Images (LANGUAGE_IMAGES) 32
Corporate Policy Message (SARBANES_OXLEY_TEXT) 64
For instance the value 32 (default) displays only the language icons and value 127 will show all the attributes on the page. Please see the attached screen shots as an example.
The change takes effect immediately after re-login to E-Business Suite.
2. Message texts
It is possible to modify or add text on the login page by changing the value of some messages.
The following table shows the related messages and their default value:
Description Default value
FND_SSO_WELCOME Login
FND_SSO_EBIZ_SUITE E-Business Suite
FND_SSO_COPYRIGHT_TEXT Copyright (c) 2007, Oracle. All rights reserved.
FND_SSO_SARBANES_OXLEY_TEXT Corporate Policy Message
Note that some messages can be used elsewhere that in the login page.
The 'FND_SSO_SARBANES_OXLEY_TEXT' message is only displayed when the mask
value 64 is added to the profile option 'Local Login Mask'. The text will appear at the bottom of the page. Please review Note 391826.1 if you want to add a long text.
To change the value of a message:
1. Go to "Application Developer" responsibility
2. Select "Messages" from the menu
3. Query the message name and then enter your message text in the "Current Message Text" field
4. Save changes and exit
5. Clear cache and bounce Apache to see the change
Note that these message values can be updated by a patch.
3. Corporate branding logo
The Oracle logo is displayed on various E-Business Suite pages and can be changed by setting the
'Corporate Branding Image for Oracle Applications' (FND_CORPORATE_BRANDING_IMAGE) profile option to the full path name of an image file (.gif) that contains your corporate image.
However it is not possible to use this method for AppsLocalLogin.jsp since it is hard coded with the Oracle logo image file 'FNDSSCORP.gif'.
The non supported solution consists in:
1. Go to the $OA_HTML directory
2. Backup the AppsLocalLogin.jsp file
3. Copy your own corporate branding image under $OA_MEDIA directory
4. Edit the AppsLocalLogin.jsp file :
from :
ImageBean imgBean1 = new ImageBean("/OA_MEDIA/FNDSSCORP.gif", FND_ORACLE_LOGO);
to :ImageBean imgBean1 = new ImageBean("/OA_MEDIA/", FND_ORACLE_LOGO);
5. Clear caches and bounce Apache to see the change
4. Other modifications
AppsLocalLogin.jsp being a Java Server Page you can change the HTML or Java code (for instance with JDeveloper), create you own messages in the Messages Dictionnary thru AOL responsibility, etc., if you want to add other customizations. This is considered a customization and thus not supported by Oracle. If you apply patches replacing AppsLocalLogin.jsp the file will be overwritten.
1. Attributes of the login page
It is possible to control the display of some attributes of the login page, for instance user name or password hints, language switchers, forgot password link, corporate policy message, etc.
For this, you need to set the profile option 'Local Login Mask' (FND_SSO_LOCAL_LOGIN_MASK) with a number being the sum of the mask values described in the table below:
Description ask value
Hint for Username (USERNAME_HINT) 01
Hint for Password (PASSWORD_HINT) 02
Cancel button (CANCEL_BUTTON) 04
Forgot Password link (FORGOT_PASSWORD_URL) 08
Registration link (REGISTER_URL) 16
Language Images (LANGUAGE_IMAGES) 32
Corporate Policy Message (SARBANES_OXLEY_TEXT) 64
For instance the value 32 (default) displays only the language icons and value 127 will show all the attributes on the page. Please see the attached screen shots as an example.
The change takes effect immediately after re-login to E-Business Suite.
2. Message texts
It is possible to modify or add text on the login page by changing the value of some messages.
The following table shows the related messages and their default value:
Description Default value
FND_SSO_WELCOME Login
FND_SSO_EBIZ_SUITE E-Business Suite
FND_SSO_COPYRIGHT_TEXT Copyright (c) 2007, Oracle. All rights reserved.
FND_SSO_SARBANES_OXLEY_TEXT Corporate Policy Message
Note that some messages can be used elsewhere that in the login page.
The 'FND_SSO_SARBANES_OXLEY_TEXT' message is only displayed when the mask
value 64 is added to the profile option 'Local Login Mask'. The text will appear at the bottom of the page. Please review Note 391826.1 if you want to add a long text.
To change the value of a message:
1. Go to "Application Developer" responsibility
2. Select "Messages" from the menu
3. Query the message name and then enter your message text in the "Current Message Text" field
4. Save changes and exit
5. Clear cache and bounce Apache to see the change
Note that these message values can be updated by a patch.
3. Corporate branding logo
The Oracle logo is displayed on various E-Business Suite pages and can be changed by setting the
'Corporate Branding Image for Oracle Applications' (FND_CORPORATE_BRANDING_IMAGE) profile option to the full path name of an image file (.gif) that contains your corporate image.
However it is not possible to use this method for AppsLocalLogin.jsp since it is hard coded with the Oracle logo image file 'FNDSSCORP.gif'.
The non supported solution consists in:
1. Go to the $OA_HTML directory
2. Backup the AppsLocalLogin.jsp file
3. Copy your own corporate branding image under $OA_MEDIA directory
4. Edit the AppsLocalLogin.jsp file :
from :
ImageBean imgBean1 = new ImageBean("/OA_MEDIA/FNDSSCORP.gif", FND_ORACLE_LOGO);
to :ImageBean imgBean1 = new ImageBean("/OA_MEDIA/
5. Clear caches and bounce Apache to see the change
4. Other modifications
AppsLocalLogin.jsp being a Java Server Page you can change the HTML or Java code (for instance with JDeveloper), create you own messages in the Messages Dictionnary thru AOL responsibility, etc., if you want to add other customizations. This is considered a customization and thus not supported by Oracle. If you apply patches replacing AppsLocalLogin.jsp the file will be overwritten.
Getting Profile values at different levels
This query will give the values of the profile option set at different level..
just use the profile option name or user profile option name which you see in the front end.
to query
SELECT user_profile_option_name,SUBSTR(e.profile_option_name,1,25) PROFILE
,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') "Level"
,DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue
,NVL(a.profile_option_value,'Is Null') Value
,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
, fnd_responsibility_tl b
, fnd_application c
,fnd_user d
, fnd_profile_options e
, fnd_profile_options_tl f
WHERE 1=1
--and e.profile_option_name IN ('FND_BRANDING_SIZE','ASO_COMP_LOGO')
and f.user_profile_option_name in ('FND: Branding Size')
AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
and f.PROFILE_OPTION_NAME(+)=e.profile_option_name
ORDER BY e.profile_option_name;
just use the profile option name or user profile option name which you see in the front end.
to query
SELECT user_profile_option_name,SUBSTR(e.profile_option_name,1,25) PROFILE
,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') "Level"
,DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue
,NVL(a.profile_option_value,'Is Null') Value
,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
, fnd_responsibility_tl b
, fnd_application c
,fnd_user d
, fnd_profile_options e
, fnd_profile_options_tl f
WHERE 1=1
--and e.profile_option_name IN ('FND_BRANDING_SIZE','ASO_COMP_LOGO')
and f.user_profile_option_name in ('FND: Branding Size')
AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
and f.PROFILE_OPTION_NAME(+)=e.profile_option_name
ORDER BY e.profile_option_name;
Friday, April 18, 2008
Forms Personalization -1
What is forms Personalization?
Hide Diagnostics = Yes/No
It is new features where some of the customization can be handled easy by simply setting the rules from the front end.It is available from 11.5.10.2
Why do we need forms Personalization?
For example in scenarios like
1.Change the prompts
2.Display some custom message when certain conditions are met
3.Enable and disabling fields/buttons based on conditions
4.For Enabling special menu.(like opening another reference form from the current form)
IF both Form personalization and custom pll are done for a form which one will take effect first?
How to start doing it?
Open the form where you want to personalize .
Menu Navigation: Help ->Diagnostics->Custom Code -> Personalize
This should open the personalization Form.
There are two profile options which will decide whether you able to see or not
Profiles:
Utilities: Diagnostics = Yes/No
Hide Diagnostics = Yes/No
There is new option available for level to define whether the rule need to be applied at form level or function level.Because a form can be attached to multiple functions.
Insert Get Expression & Insert item value button help you in building the conditional statement , very help for non technical people.
The major thing to observe are
1.SEQ: The sequence in which the rules are executed
2.Description:Description of the Rule
3.Level -- whether it is at form level or Function level..
4.Condition –what is the event and the object where the below condition need to
be checked
5.Action-- what actions it should perform when the condition is met
6.Context -- same a profile option levels at what level the rule need to be applied
Under actions
SEQ: it gives the list of actions that need to be performed when the condition is met
Type:1.Property – used for changing the properties of field
Message –used to display message when the condition is met
Built in --used to calling some standard form and AOL functionalities
Menu -- used in case of enabling special menu’s
General & Important Observations:
All actions and rules can be enabled and disabled(using checkbox) or deleted using delete option on the tool bar
Always validate the rule after you create using validate button
Save the record before you close this personalization form
To move form personalization from one instance to other there is FNDLOAD utility available .
Check the site for FNDLOAD examples…
UNIX Commands -Basics For Me-1
i am not a expert in unix and i always maintain a list of simple commands to work in the black screens..here i am posting it for people like for easy reference...
How to convert a file from dos mode to unix??
some time shell script developed in the window environment will create some problems because of carriage returns and other thing..those can be removed using the follwoing command
dos2unix
dos2unix movepdf
How to search files in unix??
find . -name "rc.conf" -print
This command will search in the current directory and all sub directories for a file named rc.conf.
Note: The -print option will print out the path of any file that is found with that name. In general -print wil print out the path of any file that meets the find criteria
How to search for a string in a selection of files (-exec grep ...).??
find . -exec grep "murthy ganjam" '{}' \; -print
If you want to just find each file then pass it on for processing use the -q grep option.
This finds the first occurrance of the search string. It then signals success to find and find continues searching for more files.
find . -exec grep -q "murthy ganjam" '{}' \; -print
This command is very important for process a series of files that contain a specific string.
You can then process each file appropriately.
How to view Files in UNIX??
use the cat command
cat filename
The more command will pause after displaying a page of text, and you can go on to the next page of text by hitting the space bar.
You can also do a keyword search of the text by typing
/keyword
For example, if you were looking through a file using the more command, and wanted to skip to the word "drosophila" you would type
/drosophila
and the more command would search through the file until it found that word.
LISTING FILES:
ls -a list all files, including the hidden ones
ls -g list which group owns the files
ls -lag list everything
ls *.txt list only files with a .txt on the end
ls data* list only files that start with the word "data"
ls -lrt list of all the files sorted
ls -la |grep '^d' Look only for files that are directories
ls -la |grep -v '^d' Let's only look for files that are not directories
COPY COMMAND:
cp -r * /tmp you would copy everything in the directory and RECURSIVELY (-r)
everything in the subdirectories underneath that directory to the /tmp directory.
cp file1 file2 copy file1 to a file called file2
cp file1 /tmp copy file1 to the /tmp directory
cp file1 ~smith copy file1 to the home directory of "smith"
cp * /tmp copy everything in the directory to the /tmp directory
MOVE COMMAND:
mv file1 file2 rename file1 to the name file2
mv file1 /tmp move file1 to the /tmp directory
mv file1 ~smith move file1 to the home directory of "smith"
mv * /tmp move everything in the directory to the /tmp directory
mv dir2 /tmp move the directory called dir2,and everything in it, to the /tmp directory
There is no rename command in unix use this command to rename...
Remove Command:
rm * delete everything in a subdirectory
rm *.txt remove only files with a .txt on the end
rm data* remove only files that start with the word "data"
rm -r dir2 removes everything in the subdirectory "dir2"
CD Command:
cd change to your home directory
cd .. move up one level
cd ~applmgr change to the home directory of user "smith"
cd /tmp change to the /tmp subdirectory
How to convert a file from dos mode to unix??
some time shell script developed in the window environment will create some problems because of carriage returns and other thing..those can be removed using the follwoing command
dos2unix
dos2unix movepdf
How to search files in unix??
find . -name "rc.conf" -print
This command will search in the current directory and all sub directories for a file named rc.conf.
Note: The -print option will print out the path of any file that is found with that name. In general -print wil print out the path of any file that meets the find criteria
How to search for a string in a selection of files (-exec grep ...).??
find . -exec grep "murthy ganjam" '{}' \; -print
If you want to just find each file then pass it on for processing use the -q grep option.
This finds the first occurrance of the search string. It then signals success to find and find continues searching for more files.
find . -exec grep -q "murthy ganjam" '{}' \; -print
This command is very important for process a series of files that contain a specific string.
You can then process each file appropriately.
How to view Files in UNIX??
use the cat command
cat filename
The more command will pause after displaying a page of text, and you can go on to the next page of text by hitting the space bar.
You can also do a keyword search of the text by typing
/keyword
For example, if you were looking through a file using the more command, and wanted to skip to the word "drosophila" you would type
/drosophila
and the more command would search through the file until it found that word.
LISTING FILES:
ls -a list all files, including the hidden ones
ls -g list which group owns the files
ls -lag list everything
ls *.txt list only files with a .txt on the end
ls data* list only files that start with the word "data"
ls -lrt list of all the files sorted
ls -la |grep '^d' Look only for files that are directories
ls -la |grep -v '^d' Let's only look for files that are not directories
COPY COMMAND:
cp -r * /tmp you would copy everything in the directory and RECURSIVELY (-r)
everything in the subdirectories underneath that directory to the /tmp directory.
cp file1 file2 copy file1 to a file called file2
cp file1 /tmp copy file1 to the /tmp directory
cp file1 ~smith copy file1 to the home directory of "smith"
cp * /tmp copy everything in the directory to the /tmp directory
MOVE COMMAND:
mv file1 file2 rename file1 to the name file2
mv file1 /tmp move file1 to the /tmp directory
mv file1 ~smith move file1 to the home directory of "smith"
mv * /tmp move everything in the directory to the /tmp directory
mv dir2 /tmp move the directory called dir2,and everything in it, to the /tmp directory
There is no rename command in unix use this command to rename...
Remove Command:
rm * delete everything in a subdirectory
rm *.txt remove only files with a .txt on the end
rm data* remove only files that start with the word "data"
rm -r dir2 removes everything in the subdirectory "dir2"
CD Command:
cd change to your home directory
cd .. move up one level
cd ~applmgr change to the home directory of user "smith"
cd /tmp change to the /tmp subdirectory
Saturday, April 12, 2008
INTRO TO INTERFACES(For Freshers to oracle Apps)-1
Hi All,
Here i a trying to get the basics of Interfaces.I will try to post as many interfaces code available.
Even though they might not very much helpful ..but gives the direction to look into and some head start...
First before i post the interfaces codes i would like to introduce some general concepts of interfaces for freshers..
What is a interface?
In terms of oracle applications interface is a communication channel that allows the data to move in and out of the system.
How many types of interfaces are there?
1.INBOUND INTERFACES
2.OUTBOUND INTEFACES
Inbound InterFace:The one which allows data to get into oracle application from outside is called inbound interface.
OutBound Interface:The one which allows data to get data from oracle applications to other systems is called outbound interface.
What are different types of inbound interfaces available?
1.Open interfaces
2.API's(Application Program Interface)
3.EDI(Electronic Data interchange)--Mainly used for automation transactions with third party systems
4.XML GATEWAY --Mainly used for automation transactions with third party systems
5.WEBADI--Used for uploading data from excel with the scope to have some validations--mainly used for one time loading..Just like sqlloader+validation program..
5.PLSQL Packages for Reading XML Data--Use this in case of importing non stadard transactions
What are different types of outbound interfaces available?
1.Custom programs where we pull data to a csv file using UTL_FILE in the required format
2.EDI
3.XMLGATEWAY
4.PLSQL Packages for generating XML
what is the difference between OPEN INTERFACE & API's?
OPEN INTERFACE:
I dont see much difference between the open other than the way we load data.
In case of open interface the data is loaded in to open interfce table like GL_INTERFACE or Sales Order Interface(OE_ORDER_HEADERS_IFACE_ALL).
Run the interface import program.
This will validate the data and put it into oracle applications.All the invalid records are marked as Error.
One thing is there are GUI Screens available for most of these interface where you check the errror message correct it there only and resubmit the interface.
From the technical perspective there are Error tables available for each interface
API:
API's are the oracle built packages where the validation logic is wrapped inside a package and the data is passed as parameters to the API.
Most of these api's use the PLSQL tables as paremeters to take the advantage of bulk binding concepts for faster loading data.
THey will have the two OUT parameterst to throw back the error code and message in case of data validation failure
Apis' are compartively faster than open interfaces.
If a API's and open interface are available it is better to load through API's.If the records are more...
This is what i understand till date...Plz put your comment in case of i am wrong any thing more that would be helpful to make this better...
Here i a trying to get the basics of Interfaces.I will try to post as many interfaces code available.
Even though they might not very much helpful ..but gives the direction to look into and some head start...
First before i post the interfaces codes i would like to introduce some general concepts of interfaces for freshers..
What is a interface?
In terms of oracle applications interface is a communication channel that allows the data to move in and out of the system.
How many types of interfaces are there?
1.INBOUND INTERFACES
2.OUTBOUND INTEFACES
Inbound InterFace:The one which allows data to get into oracle application from outside is called inbound interface.
OutBound Interface:The one which allows data to get data from oracle applications to other systems is called outbound interface.
What are different types of inbound interfaces available?
1.Open interfaces
2.API's(Application Program Interface)
3.EDI(Electronic Data interchange)--Mainly used for automation transactions with third party systems
4.XML GATEWAY --Mainly used for automation transactions with third party systems
5.WEBADI--Used for uploading data from excel with the scope to have some validations--mainly used for one time loading..Just like sqlloader+validation program..
5.PLSQL Packages for Reading XML Data--Use this in case of importing non stadard transactions
What are different types of outbound interfaces available?
1.Custom programs where we pull data to a csv file using UTL_FILE in the required format
2.EDI
3.XMLGATEWAY
4.PLSQL Packages for generating XML
what is the difference between OPEN INTERFACE & API's?
OPEN INTERFACE:
I dont see much difference between the open other than the way we load data.
In case of open interface the data is loaded in to open interfce table like GL_INTERFACE or Sales Order Interface(OE_ORDER_HEADERS_IFACE_ALL).
Run the interface import program.
This will validate the data and put it into oracle applications.All the invalid records are marked as Error.
One thing is there are GUI Screens available for most of these interface where you check the errror message correct it there only and resubmit the interface.
From the technical perspective there are Error tables available for each interface
API:
API's are the oracle built packages where the validation logic is wrapped inside a package and the data is passed as parameters to the API.
Most of these api's use the PLSQL tables as paremeters to take the advantage of bulk binding concepts for faster loading data.
THey will have the two OUT parameterst to throw back the error code and message in case of data validation failure
Apis' are compartively faster than open interfaces.
If a API's and open interface are available it is better to load through API's.If the records are more...
This is what i understand till date...Plz put your comment in case of i am wrong any thing more that would be helpful to make this better...
Monday, April 7, 2008
Standard Forms Customization(Part1--Custom PLL )Draft
There are different ways to handle Forms Level Customizations.
1.Using Custom.PLL
2.Using Forms Personalizations
3.Copy the Standard form Object and Change the Code
First lets see Custom.pll how to use it?
What is Custom.PLL??
The CUSTOM.pll library is a standard Oracle Forms PL/SQL library that is supplied by Oracle with the Oracle
Applications. This is Oracle’s built-in feature that allows the customer to enhance the standard functionality of the
Applications by implementing site-specific business rules. Every Oracle Forms -based eBusiness screen, and any
custom form developed using the Oracle Application development standards, will access the CUSTOM library.
This makes an ideal point of creating business rules that effect the entire organization.
Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.
How to add code to this ?
open this pll using the Form builder.make changes to the program units
How to compile this PLL ?
Once you make changes you need to compile the pll.use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special
What are Different Triggers that is supported?
WHEN-NEW-FORM-INSTANCE – initially entering a form
WHEN-NEW-BLOCK-INSTANCE – entering a zone (or block) within a form
WHEN-NEW-ITEM-INSTANCE – moving into a new field within the form
WHEN-NEW-RECORD-INSTANCE - creating a new record
WHEN-FORM-NAVIGATE – navigating thru a form using the mouse
WHEN-VALIDATE-RECORD – saving (committing) the information to the database
EXPORT – triggered by using the Export feature Some events are field specific
ZOOM – Pre -11 feature for moving to another form and querying up specific records
Some events are form specific
SPECIALn - (where n is a number between 1 and 45) used to generate entries in the ‘Special’ menu of the
tool bar and the code is triggered by selecting a menu choices from the ‘Special’ option on the toolbar
KEY-Fn – (where n is a number between 1 and 8) triggered by pressing the corresponding function key
Some events are application specific:
Application Object Library
WHEN-LOGIN-CHANGED – when a user logs on as a different user
WHEN-RESPONSIBILITY-CHANGED – when a user changes responsibilities
WHEN-PASSWORD-CHANGED – when a user changes their password
How to make changes get affected?
Once you make the changes compile the pll and generate the PLX
Since the CUSTOM library is loaded once for a given session, a user must log out of the
application and sign-on again before any changes will become apparent.
Examples--Metalink:
1. Sample code to make all the responsibilities read only for a specific user.
BEGIN
IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='' THEN
BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);
WHILE (blockname is not null) LOOP
IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
END IF;
blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
END LOOP;
END query_only_mode;
END;
2.How does one restrict or reduce the LOV?
"The customer LOV can be overriden using the when-new-item-instance or when-new-form-instance event at the form level through CUSTOM.pll."
You will need to write custom code using that specific event in the custom.pll
Some sample code
if (event_name = 'WHEN-NEW-FORM-INSTANCE' and form_name = 'form name')then
r:=find_group('group name');
if not id_null(r) then
delete_group('group name');
end if;
v:='select colum1,column2
from table';
r:=create_group_from_query('group name',v);
set_lov_property('lov NAME',group_name,r);--lov
See that the column names should be same as the old query so that the mappings still holds good
3.How to make the attachment function in specific responsibilities to act as read-only mode so that users who log into these specific responsibilities can only view attachments, while for the rest of the responsibilities allow users to add, update and delete attachments?
// Source File Name: custom.pll
// Source File path: $AU_TOP/resource
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'FNDATTCH') then
if (FND_GLOBAL.RESP_NAME Like '') then --
Set_item_Property( SEQ_NUM, ENABLED,PROPERTY_FALSE);
Set_item_Property( CATEGORY_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DOCUMENT_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DATATYPE_NAME, ENABLED,PROPERTY_FALSE);
Set_item_Property( FILE_NAME_DISPLAY, ENABLED,PROPERTY_FALSE);
end if;
end if;
end if;
4. How to make the customisation CustomPO Number not less than PO 4 digits in sales order form?
procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
item_name varchar2(30) := name_in('system.cursor_item');
Begin
if (form_name = 'OEXOEORD'and block_name = 'ORDER') then
if LENGTH(name_in('ORDER.CUST_PO_NUMBER')) > 3 then
fnd_message.set_name('FND','Cust PO Number should be less than 4 digits');
fnd_message.Error;
RAISE FORM_TRIGGER_FAILURE;
End if;
End if;
End Event;
1.Using Custom.PLL
2.Using Forms Personalizations
3.Copy the Standard form Object and Change the Code
First lets see Custom.pll how to use it?
What is Custom.PLL??
The CUSTOM.pll library is a standard Oracle Forms PL/SQL library that is supplied by Oracle with the Oracle
Applications. This is Oracle’s built-in feature that allows the customer to enhance the standard functionality of the
Applications by implementing site-specific business rules. Every Oracle Forms -based eBusiness screen, and any
custom form developed using the Oracle Application development standards, will access the CUSTOM library.
This makes an ideal point of creating business rules that effect the entire organization.
Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.
How to add code to this ?
open this pll using the Form builder.make changes to the program units
How to compile this PLL ?
Once you make changes you need to compile the pll.use the F60gen to compile it
f60gen module=custom.pll userid=APPS/
What are Different Triggers that is supported?
WHEN-NEW-FORM-INSTANCE – initially entering a form
WHEN-NEW-BLOCK-INSTANCE – entering a zone (or block) within a form
WHEN-NEW-ITEM-INSTANCE – moving into a new field within the form
WHEN-NEW-RECORD-INSTANCE - creating a new record
WHEN-FORM-NAVIGATE – navigating thru a form using the mouse
WHEN-VALIDATE-RECORD – saving (committing) the information to the database
EXPORT – triggered by using the Export feature Some events are field specific
ZOOM – Pre -11 feature for moving to another form and querying up specific records
Some events are form specific
SPECIALn - (where n is a number between 1 and 45) used to generate entries in the ‘Special’ menu of the
tool bar and the code is triggered by selecting a menu choices from the ‘Special’ option on the toolbar
KEY-Fn – (where n is a number between 1 and 8) triggered by pressing the corresponding function key
Some events are application specific:
Application Object Library
WHEN-LOGIN-CHANGED – when a user logs on as a different user
WHEN-RESPONSIBILITY-CHANGED – when a user changes responsibilities
WHEN-PASSWORD-CHANGED – when a user changes their password
How to make changes get affected?
Once you make the changes compile the pll and generate the PLX
Since the CUSTOM library is loaded once for a given session, a user must log out of the
application and sign-on again before any changes will become apparent.
Examples--Metalink:
1. Sample code to make all the responsibilities read only for a specific user.
BEGIN
IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='
BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);
WHILE (blockname is not null) LOOP
IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
END IF;
blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
END LOOP;
END query_only_mode;
END;
2.How does one restrict or reduce the LOV?
"The customer LOV can be overriden using the when-new-item-instance or when-new-form-instance event at the form level through CUSTOM.pll."
You will need to write custom code using that specific event in the custom.pll
Some sample code
r:=find_group('group name');
if not id_null(r) then
delete_group('group name');
end if;
v:='select colum1,column2
from table';
r:=create_group_from_query('group name',v);
set_lov_property('lov NAME',group_name,r);--lov
See that the column names should be same as the old query so that the mappings still holds good
3.How to make the attachment function in specific responsibilities to act as read-only mode so that users who log into these specific responsibilities can only view attachments, while for the rest of the responsibilities allow users to add, update and delete attachments?
// Source File Name: custom.pll
// Source File path: $AU_TOP/resource
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'FNDATTCH') then
if (FND_GLOBAL.RESP_NAME Like '
Set_item_Property( SEQ_NUM, ENABLED,PROPERTY_FALSE);
Set_item_Property( CATEGORY_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DOCUMENT_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DATATYPE_NAME, ENABLED,PROPERTY_FALSE);
Set_item_Property( FILE_NAME_DISPLAY, ENABLED,PROPERTY_FALSE);
end if;
end if;
end if;
4. How to make the customisation CustomPO Number not less than PO 4 digits in sales order form?
procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
item_name varchar2(30) := name_in('system.cursor_item');
Begin
if (form_name = 'OEXOEORD'and block_name = 'ORDER') then
if LENGTH(name_in('ORDER.CUST_PO_NUMBER')) > 3 then
fnd_message.set_name('FND','Cust PO Number should be less than 4 digits');
fnd_message.Error;
RAISE FORM_TRIGGER_FAILURE;
End if;
End if;
End Event;
Subscribe to:
Posts (Atom)
