Selecting sample data from table

Sometimes you may need to select a percentage of data from the table to create test data. The following statement will select approximately 0.2 percent of the data in the table;

select * from RA_CUSTOMER_TRX_ALL sample(0.2);

Commit, commit_form and forms_ddl

commit
this is db command (only), not FORMS and if:

• form has upPOSTed statements they may be ignored
• form not fire PRE, POST, ON - commit triggers

commit execute as client side statement


forms_ddl(commit)

this is dynamic sql - commit and commit execute as server side. And form doesn't know about commit. it commits the all the DML transactions if any executed and pendig for commit .

commit_form

• It fires commit for all the database blocks on the form
• it commits the all the DML transactions if any executed and pendig for commit .

STRING_TO_DATE

For the concurrent request parameters, if you are sending a VARCHAR2 type of parameter as a date variable, I would suggest to use STRING_TO_DATE function for conversion; See below;

g_DateUB := NVL(Fnd_Conc_Date.STRING_TO_DATE (p_dateUB), SYSDATE);

Call a pl/sql API from forms personalization

To call a pl/sql API from forms personalization, do this

• Personalize the form
• Create an action of type "BuiltIn"
• BuiltIn Type for Action should be "Execute a Procedure"
• Argument should be as below

='declare
v_field_value VARCHAR2(200) ;
begin
xx_proc ('''||${item.BLOCKNAME.FIELDNAME.value}||''');
end'

Note the syntax, after =, entire declare begin end is within single quote. Also, there is no semi colon after "end"

You can pass field values as

'''||${item.BLOCKNAME.FIELDNAME.value}||'''

Utilities:Diagnostics

Making "Profile – Utilities:Diagnostics" YES will give enduser the ability to use Diagnostic without entering the APPS Password.

FORMAT_ERROR_STACK

Replace SQLERRM with the much better DBMS_UTILITY.format_error_stack. Now you will see the full error stack information -- but if the string exceeds 255 characters, you get an ERROR!

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.format_error_stack);

FORMAT_ERROR_STACK

Replace SQLERRM with the much better DBMS_UTILITY.format_error_stack. Now you will see the full error stack information -- but if the string exceeds 255 characters, you get an ERROR!

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.format_error_stack);

MSCA Turkish

After successful installation of Mobile Supply Chain Applications (MSCA) of Oracle Applications Release 11.5.10, we discovered Telnet client can not display characters for the Turkish language.

Telnet Protocol; Mobile Applications Framework's Presentation Manager Layer can send data to telnet clients in multi-byte. However, for telnet clients (whether it be a telnet client software on Microsoft Windows, Unix, etc. or a telnet client on a mobile device) to display characters for the specified language, system administrator's have to set the CHARACTER_SET parameter in their default_key.ini file (or whatever ini file they use for this in $MWA_TOP/secure directory) to the proper character encoding value.

For example, for West European character sets (like French ), CHARACTER_SET has to be set to WE8ISO8859P1. When this fails for you, try using the encoding scheme format for your characterset as referenced from java character codes:

For example, we tried using 'Cp1254' (case sensitive) for Turkish charactersets as the value in CHARACTER_SET.

Also check CLASSPATH. Some charactersets are available by default in the Basic Encoding set (rt.jar) file, while others are available in the Extended Encoding set (i18n.jar or characters.jar in the upper versions of Java). Therefore, CLASSPATH needs to include the correct file.

Tip : Oracle Home

When you have several oracle_home, here is a way to find which is the oracle home for a particular instance:

1) get the pid of a background process:
ps -edf | grep [o]ra_smon_ORAPRD01
oracle 307694 1 0 Jan 24 - 1:43 ora_smon_ORAPRD01

2) get its current working directory from /proc:
ls -l /proc/307694/cwd
lr-x------ 2 oracle dba 0 Feb 28 16:00 /proc/307694/cwd -> /app/oracle/product/9.2.0/dbs/

Here oracle_home is /app/oracle/product/9.2.0

Tip : dbms_java

By executing the following 2 statements in your session :

SET SERVEROUTPUT ON SIZE 1000000;
CALL DBMS_JAVA.SET_OUTPUT (1000000);

All the output of the System.out.println command in your stored procedures will be shown in your serveroutput. This gives you simple and fast way to debug your procedure like any other pl/sql procedure. It's jus like you would use the dbms_output.put_line proc.