SQL : Global Temporary Tables

he data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
Miscellaneous Features
If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
There are a number of restrictions related to temporary tables but these are version specific.

Oracle Apps : BI/XML Publisher Download and Upload commands

Term XML Publisher and BI Publisher are used interchangeably. In this article i will be using the term BI Publisher.
In this article we will discuss in details the steps involved in the moving the XML Publisher / BI Publisher report components across instances.
The mechanism by which BI Publisher reports can be integrated in Oracle Applications is Listed below
Each of the above mechanism requires the creation and registration of
Data DefinitionData templateData Template file in RTF, PDF, XSL-HTML, XSL-XML, XSL-FO, XSL-TEXT, eTEXT, Microsoft Excel.Optional creation of XSD schema and Sample Data XML
Data Template requires
Creation of XML DATA TEMPLATE file.
XML Bursting requires
Bursting Control File
BI Publisher provides the FNDLOAD and XDOLoader utilities to move component across instances. The FNDLOAD utility is used to upload and download the data definition and Data template created via XML Publisher Administrator responsibility.
The XDOLoader utility is used to upload and download the physical files (Template files, XSD Schema, Sample XML Data , Bursting Control File, Data Template ........)
Execute the FNDLOAD and XDOLoader utility from Application Server Linux terminal with the appsenv initialized. This will provide you access to the FND and appl_top files on the application server classpath.
FNDLOAD : Download and Upload Data Definition and Data TemplateUse the following commands to 1) Download all the data definitions and corresponding templates in an instance.FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt XDO_DS_DEFINITIONS
2) Download all the data definitions and the corresponding Templates in a specific product.FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX
3) Download a particular data definitions and its corresponding Templates.FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX DATA_SOURCE_CODE=EMP_DD
4) Upload the data definitions and its corresponding Templates to an instance.FNDLOAD apps/apps_pwd@db 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lcttargetldtfile.ldt
Sample LDT File:# $Header$
# dbdrv: exec fnd bin FNDLOAD bin &phase=dat checkfile:~PROD:~PATH:~FILE &ui_apps 0 Y UPLOAD @XDO:patch/115/import/xdotmpl.lct @~PROD:~PATH/~FILELANGUAGE = "US"LDRCONFIG = "xdotmpl.lct 120.5"
#Source Database R124
#RELEASE_NAME 12.0.4
# -- Begin Entity Definitions --
DEFINE XDO_DS_DEFINITIONSKEY APPLICATION_SHORT_NAME VARCHAR2(50)KEY DATA_SOURCE_CODE VARCHAR2(80)CTX OWNER VARCHAR2(4000)BASE DATA_SOURCE_STATUS VARCHAR2(1)BASE START_DATE VARCHAR2(11)BASE END_DATE VARCHAR2(11)BASE OBJECT_VERSION_NUMBER NUMBER(22)BASE LAST_UPDATE_DATE VARCHAR2(11)BASE ATTRIBUTE_CATEGORY VARCHAR2(90)BASE ATTRIBUTE1 VARCHAR2(450)BASE ATTRIBUTE2 VARCHAR2(450)BASE ATTRIBUTE3 VARCHAR2(450)BASE ATTRIBUTE4 VARCHAR2(450)BASE ATTRIBUTE5 VARCHAR2(450)BASE ATTRIBUTE6 VARCHAR2(450)BASE ATTRIBUTE7 VARCHAR2(450)BASE ATTRIBUTE8 VARCHAR2(450)BASE ATTRIBUTE9 VARCHAR2(450)BASE ATTRIBUTE10 VARCHAR2(450)BASE ATTRIBUTE11 VARCHAR2(450)BASE ATTRIBUTE12 VARCHAR2(450)BASE ATTRIBUTE13 VARCHAR2(450)BASE ATTRIBUTE14 VARCHAR2(450)BASE ATTRIBUTE15 VARCHAR2(450)TRANS DATA_SOURCE_NAME VARCHAR2(120)TRANS DESCRIPTION VARCHAR2(2000)
DEFINE X_TEMPLATESKEY TMPL_APP_SHORT_NAME VARCHAR2(50)KEY TEMPLATE_CODE VARCHAR2(80)CTX OWNER VARCHAR2(4000)BASE TEMPLATE_TYPE_CODE VARCHAR2(30)BASE DEFAULT_LANGUAGE VARCHAR2(6)BASE DEFAULT_TERRITORY VARCHAR2(6)BASE MLS_LANGUAGE VARCHAR2(6)BASE MLS_TERRITORY VARCHAR2(6)BASE TEMPLATE_STATUS VARCHAR2(6)BASE USE_ALIAS_TABLE VARCHAR2(6)BASE DEPENDENCY_FLAG VARCHAR2(1)BASE START_DATE VARCHAR2(11)BASE END_DATE VARCHAR2(11)BASE OBJECT_VERSION_NUMBER NUMBER(22)BASE LAST_UPDATE_DATE VARCHAR2(11)BASE ATTRIBUTE_CATEGORY VARCHAR2(100)BASE ATTRIBUTE1 VARCHAR2(450)BASE ATTRIBUTE2 VARCHAR2(450)BASE ATTRIBUTE3 VARCHAR2(450)BASE ATTRIBUTE4 VARCHAR2(450)BASE ATTRIBUTE5 VARCHAR2(450)BASE ATTRIBUTE6 VARCHAR2(450)BASE ATTRIBUTE7 VARCHAR2(450)BASE ATTRIBUTE8 VARCHAR2(450)BASE ATTRIBUTE9 VARCHAR2(450)BASE ATTRIBUTE10 VARCHAR2(450)BASE ATTRIBUTE11 VARCHAR2(450)BASE ATTRIBUTE12 VARCHAR2(450)BASE ATTRIBUTE13 VARCHAR2(450)BASE ATTRIBUTE14 VARCHAR2(450)BASE ATTRIBUTE15 VARCHAR2(450)TRANS TEMPLATE_NAME VARCHAR2(120)TRANS DESCRIPTION VARCHAR2(2000)
DEFINE X_TEMPLATE_FIELDSKEY FIELD_NAME VARCHAR2(255)CTX OWNER VARCHAR2(4000)BASE ALIAS_NAME VARCHAR2(255)BASE LAST_UPDATE_DATE VARCHAR2(11)END X_TEMPLATE_FIELDSEND X_TEMPLATESEND XDO_DS_DEFINITIONS
# -- End Entity Definitions --
BEGIN XDO_DS_DEFINITIONS "XXX" "EMP_DD"OWNER = "PRABHAKAR"DATA_SOURCE_STATUS = "E"START_DATE = "2008/05/11"OBJECT_VERSION_NUMBER = "1"LAST_UPDATE_DATE = "2008/05/11"DATA_SOURCE_NAME = "EMP DD"DESCRIPTION = "Data Template Demo"
BEGIN X_TEMPLATES "XXX" "Emp_Template"OWNER = "PRABHAKAR"TEMPLATE_TYPE_CODE = "RTF"DEFAULT_LANGUAGE = "en"DEFAULT_TERRITORY = "US"TEMPLATE_STATUS = "E"USE_ALIAS_TABLE = "N"DEPENDENCY_FLAG = "P"START_DATE = "2008/05/12"OBJECT_VERSION_NUMBER = "1"LAST_UPDATE_DATE = "2008/05/12"TEMPLATE_NAME = "EmpTemplate" END X_TEMPLATES
END XDO_DS_DEFINITIONS
XDOLOADER : Download and Upload physical files
The XDO Loader comes in two mode .
File download only modeFile download and LDT/DRVX generation mode
In theroy, the File download Only Mode allows you to download files of the specified type (BURSTING_FILE, DATA_TEMPLATE,TEMPLATE,XML_SAMPLE,XML_SCHEMA) from a specified application short name. If you specify the data definition or template code, then it download the specified file type of that data definition or Template. But when i tested the command, it generated files of all type attached to a data definition in a specified application short name including the drv file.
The File Download and LDT/DRVX Generation Mode allows you to download all the files (BURSTING_FILE,DATA_TEMPLATE,TEMPLATE,XML_SAMPLE,XML_SCHEMA) for a specified application short name. If you specify the data definition, then it will download all the files for the specified data definition. IN addition these files, it also generates a DRVX files, which has the upload script for each of the file downloaded.
As a technical consultant, i would suggest every one to use the File Download and LDT/DRVX Generation Mode as it is defined to downloads all the files types in one go including the drvx file that contains the upload scripts. This will reduce our effort considerably.If you want to download a specify file type, then you can use the File Download Mode.
File Download Only ModeSyntax :java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME \-DB_PASSWORD \-JDBC_CONNECTION \-LOB_TYPE \-APPS_SHORT_NAME \-LOB_CODE \-LANGUAGE \-TERRITORY \-LOG_FILE

Example :1) Download all the physical files in the instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD apps \-JDBC_CONNECTION apsrtc:1521:SID \-LANGUAGE en \-TERRITORY US
2) Download all the physical files for a specified application short name
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD apps \-JDBC_CONNECTION apsrtc:1521:SID \-APPS_SHORT_NAME AR \-LANGUAGE en \-TERRITORY US
3) Download all the physical files for a specified application short name and LOB Type
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD apps \-JDBC_CONNECTION apsrtc:1521:SID \-LOB_TYPE BURSTING_FILE \-LANGUAGE en \-TERRITORY US
As per syntax, it should download only Bursting File. But it will downloads all the file types.
The File Download and LDT/DRVX Generation Mode Syntax : java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME \-DB_PASSWORD \-JDBC_CONNECTION \-APPS_SHORT_NAME \-DS_CODE (data source code> \-LCT_FILE \-LDT_FILE \-DRVX_FILE \-LOG_FILE

Example :1) Download all the physical files of a specified application short name in an instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD welcome \-JDBC_CONNECTION r12.com:1533:R124 \-APPS_SHORT_NAME AR \-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct
2) Download all the physical files of a specified application short name and in and data definition in an instance.java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \-DB_USERNAME apps \-DB_PASSWORD welcome \-JDBC_CONNECTION r12.com:1533:R124 \-APPS_SHORT_NAME AMW \-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \-DS_CODE EMP_DD
Uploading Physical Files
Syntax :java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME \-DB_PASSWORD \-JDBC_CONNECTION \-LOB_TYPE \-APPS_SHORT_NAME \-LOB_CODE \-LANGUAGE \-TERRITORY \-XDO_FILE_TYPE \-NLS_LANG \-FILE_CONTENT_TYPE \-FILE_NAME \-OWNER \-CUSTOM_MODE [FORCENOFORCE] \-LOG_FILE


Example :
1) Upload the RTF file.
java oracle.apps.xdo.oa.util.XDOLoader \UPLOAD \-DB_USERNAME apps \-DB_PASSWORD welcome \-JDBC_CONNECTION r12.com:1533:R124 \-LOB_TYPE TEMPLATE \-APPS_SHORT_NAME AMW \-LOB_CODE EMP_DD \-LANGUAGE en \-TERRITORY US \-NLS_LANG American_America.WE8ISO8859P1 \-XDO_FILE_TYPE RTF \-FILE_CONTENT_TYPE ’text/html’ \-FILE_NAME /tmp/psomanat/TEMPLATE_SOURCE_AMW_Emp_Template_en_US.rtfSet as favorite Bookmark Email This Hits: 1255Comments (2) Subscribe to this comment's feedLinks dont take to the pagewritten by kicha , June 26, 2008 None of the link take it to the corresponding pages report abusevote downvote upVotes: +0 Good Informationwritten by Mayur , July 29, 2008 Thanks for the information on XDO Loader Utility.. It was helpful and saved lot of time report abusevote downvote upVotes: +0

OAF: Scripts for OAFramework Customization.

Scripts for OAFramework Customization.2.1 OAFramework page related scripts.2.2 Upload PG.xml files from the jdeveloper directory.2.3 Change the package structure, < > according to your project
1. Upload a Single PG.xml file
\jdevbin\jdev\bin\import \jdevbin\jdev\myclasses\xxt\oracle\apps\pon\registration\webui\XXTSupplierRegistrationPG.xml -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))" -rootDir \jdevbin\jdev\myclasses\ -rootPackage /
2. Upload multiple PG.xml files
/jdevbin/jdev/bin/import /jdevbin/jdev/myclasses /oracle/apps/xxpo/pdt/ordering/webui -rootDir /jdevbin/jdev/myclasses / -username apps -password -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))" -includeSubpackages -jdk13 -mmddir /jdevbin/jdev/lib/ext/jrad/config/mmd -rootPackage /
3. View the PG.xml from the database in TOAD or SQLDeveloper.
Begin
Jdr_utils.printDocument(‘/oracle/apps/pon/award/completion/webui/ponCompleteAward2PG’);
End;
4. To find the personalized file path for a particular PG.xml
begin
jdr_utils.listCustomizations('/oracle/apps/fnd/framework/navigate/webui/AppsNavigateMobilePG');
end;
Will give /oracle/apps/fnd/framework/navigate/webui/customizations/site/0/AppsNavigateMobilePG
When the page is personalized at site level.
5. To download the personalized file, first run the above script in SQL, that will display the full path for the personalized file, then run the below command
Eg.
Begin
Jdr_utils.printDocument(‘/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/AppsNavigateMobilePG’);
End;
Save the file as AppsNavigateMobilePG.xml in the mentioned path and import into database using the above script.
6. To create translations for the personalized page or for a custom page, download the corresponding xlf file for the personalized page or the custom page. Then change the prompts in the destination language and import the page again.
Extract XLF file for a particular directory (For arabic language, for a different language, change accordingly).
\jdevbin\jdev\bin\xliffextract /xxt/oracle/apps/pon/registration/webui -includeSubpackages -mmd_dir D:\Jdev1150CU2\jdevbin\jdev\lib\ext\jrad\config\mmd -root D:\Temp\XLIF\registration -xliff_dir D:\Temp\XLIF\registration -source db -username apps -password -dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))" -languages ar-AE
Extract XLF file for a particular file(For arabic language, for a different language, change accordingly).
\jdevbin\jdev\bin\xliffextract /oracle/apps/pon/outcome/creation/webui/customizations/site/0/ponCreatePOPG -root D:\temp -xliff_dir D:\temp -mmd_dir \jdevbin\jdev\lib\ext\jrad\config\mmd\ -source db -username apps -password -dbconnection " (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL= TCP)(HOST = )(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = ) ) )" -languages ar-AE

7. When you have the Substitutions upload the JPX using the following script from the UNIX prompt,
java oracle.jrad.tools.xml.importer.JPXImporter $CUST_TOP/TestProject.jpx -username $APPS_NAME -password $APPS_PASSWORD -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=)(Port=)) (CONNECT_DATA=(SID=)))"
8. To find the personalizations for a particular object or for the whole system or for a particular package.
Login with the user who has Functional Administrator responsibility, you search the Object you have substituted (Functional Administrator=>Personalization=>Import/Export).
Search with a particular path, Eg. /oracle/apps/icx will give all the personalization under the path.
If the the profile option “FND: Personalization Document Root Path” set properly, the personalization can be exported to the system directly.

OAF: Find the version of the Jdeveloper for Apps 11i or 12

To determine which patch to use, you can check the framework version in your instance by using http://host:port/OA_HTML/OAInfo.jsp,

Choose the matched JDeveloper patch.


OA Framework 5.10 patch
JDeveloper 9i Patch
ATG.PF.H (patch 3438354 or Oracle Applications 11.5.10)
Patch 4045639 9IJDEVELOPER WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (patch 4017300)
Patch 4141787 9IJDEVELOPER WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (patch 4125550)
Patch 4573517 Oracle9i JDeveloper with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (patch 4334965)
Patch 4725670 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (patch 4676589)
Patch 5455514 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (patch 5473858)
Patch 6012619 9IJDeveloper With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (patch 5903765)
Patch 6469392 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11I10 RUP6

ATG Release 12 Version
JDeveloper 10g Patch
12.0.0
Patch 5856648 10g Jdev with OA Extension
12.0.1 (patch 5907545)
Patch 5856648 10g Jdev with OA Extension
12.0.2 (patch 5484000 or 5917344)
Patch 6491398 10g Jdev with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3 (patch 6141000 or 6077669)
Patch 6509325 10g Jdev with OA Extension ARU for R12 RUP3
12.0.4 (patch 6435000 or 6272680)
Patch 6908968 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12 RUP4

OAF:Important profile options used in OAFramework

1. Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION
This is intended for system administrators who wish to personalize regions at the localization, site, verticalization, org and responsibility levels. On enabling this profile option for the administrator, every OA Framework page will contain a global Personalize button. By clicking on this global button, the administrator can personalize the regions available on that page.
2. Disable Self-service Personal / FND_DISABLE_OA_CUSTOMIZATIONS
This is a system profile option specifically created for use by Oracle Support. You can set this profile option to "Yes" or "No" at the site or application level. If this system profile option is set to Yes, any personalizations made by the customer, regardless of the level at which the personalizations were made, will not be applied. All pages using OA Framework will now display the regions based on their original definitions.
Note: When this profile is set to "Yes", a warning message that all personalizations are disabled is displayed on every page to which a user navigates.
3. FND: Personalization Region Link Enabled / FND_PERSONALIZATION_REGION_LINK_ENABLED
Enables the "Personalize Region" links on a page if the Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION profile is set to Yes.
4. Fnd Xliff Export Root Path / FND_XLIFF_EXPORT_ROOT_PATH
Use this profile option to set the root path used to generate the full path where the Xliff files are exported to when users extract their translated personalizations using the Extract Translation Files page in OA Personalization Framework. The permissions for the root path directory that you specify must be set to read, write, create for all users, using chmod 777 [dir_path].
5. Xliff Import Root Path / FND_XLIFF_IMPORT_ROOT_PATH
Use this profile option to set the root path used to derive the full path from where the Xliff files are uploaded when users use the Upload Translations page in OA Personalization Framework to upload translated personalizations.
6. FND: Personalization Document Root Path / FND_PERZ_DOC_ROOT_PATH
Use this profile option to define the root path where personalizations documents are exported to or imported from when users use the Database page or the File System page of the Functional Administrator responsibility's Document Manager, respectively.

Recommended this profile set to the $APPL_TOP staging area: $APP_TOP// / /mds/webui
of the current deployed environment, where personalization documents are to be imported from or
exported to. This profile option should be set at the Site level.

7.FND: Diagnostics / FND_DIAGNOSTICS
Setting this to “Yes” causes a Diagnostics global button to render on every page. Select this button to view the log messages for the page. Enabling this profile also automatically renders the "About this page" link at the bottom of every OA Framework page.

Oracle: Business Events:Raise the event

Raising a Business Event :

DECLARE
x_event_parameter_list wf_parameter_list_t;
x_user_id INTEGER := ;
x_user_name VARCHAR2(100) := ;
x_param wf_parameter_t;
x_event_name VARCHAR2(100) := ;
x_event_key VARCHAR2(100) := 'Event Key';
x_parameter_index NUMBER := 0;
BEGIN
x_event_parameter_list := wf_parameter_list_t();
--Lets add the first value to the Event Parameter i.e. user_id
x_param := wf_parameter_t(NULL
,NULL);
x_event_parameter_list.EXTEND;
x_param.setname('XX_TEST_USER_ID');
x_param.setvalue(x_user_id);
x_parameter_index := x_parameter_index + 1;
x_event_parameter_list(x_parameter_index) := x_param;
--Lets add the second value to the Event Parameter i.e. User Name
x_param := wf_parameter_t(NULL
,NULL);
x_event_parameter_list.EXTEND;
x_param.setname('XX_TEST_USER_NAME');
x_param.setvalue(x_user_name);
x_parameter_index := x_parameter_index + 1;
x_event_parameter_list(x_parameter_index) := x_param;
wf_event.RAISE(p_event_name => x_event_name
,p_event_key => x_event_key
,p_parameters => x_event_parameter_list
/*,p_event_data => p_data*/
);
END;

To get the latest list of events, run

SELECT owner_name, NAME, statusFROM wf_eventsWHERE upper(NAME) LIKE '%' upper('&event_name') '%'AND status = 'ENABLED'


Create a pl/sql function xx_test_event_01 that will be executed when the event is raised.
All that this pl/sql will do is to create a record in table xx_event_result.
When invoking the event, two parameters namely user_id and user_name will be passed. It is the value of these parameters that will be inserted into the tables.
CREATE OR REPLACE FUNCTION xx_test_event_01(
p_subscription_guid IN RAW
,p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2 IS
l_user_name VARCHAR2(100);
l_user_id INTEGER;
BEGIN
--read the parameters values passed to this event
l_user_id := p_event.getvalueforparameter('XX_TEST_USER_ID');
l_user_name := p_event.getvalueforparameter('XX_TEST_USER_NAME');
INSERT INTO xx_event_result
(x_user_id
,x_user_name)
VALUES
(l_user_id
,l_user_name);
COMMIT;
RETURN 'SUCCESS';
END xx_test_event_01;
/

More information visit :

http://oracle.anilpassi.com/workflows-business-events-training-lesson-4-2.html

XML/HTML SPECIAL/MULTI BYTE CHARACTERS PARSING

From Oracle 10g onwards you can use utl_i18n.escape_reference api to handle any special characters in XML/HTML parsing

Example
SELECT utl_i18n.escape_reference('Àà¡ÁáÂâÃãÄä¥ÅåÆæÇçÈèÉéÊêËëÌì­ÍíÎîÏïÐðÑñÒòÓóÔôµÕõÖöØøÙùÚúÛûÜüÝýÞþ¿ßÿ°ªº','US7ASCII') utl_fld
from dual;

Output:
Aa!AaAaÃãAaYÅåÆæCcEeEeEeEeIi-IiIiIiÐðÑñOoOoOoµÕõOoØøUuUuUuUuYyÞþ¿ßy°ªº

Till Oracle 9 use Convert and replace fuctions to change the double byte characters to single byte characters.


SELECT replace(convert('Àà¡ÁáÂâÃãÄä¥ÅåÆæÇçÈèÉéÊêËëÌì­ÍíÎîÏïÐðÑñÒòÓóÔôµÕõÖöØøÙùÚúÛûÜüÝýÞþ¿ßÿ°ªº','US7ASCII') ,'?') utl_fld from dual;

Output

Aa!AaAaAaYCcEeEeEeEeIi-IiIiIiOoOoOoOoUuUuUuUuYyy