24 February 2009

Oracle : storing strings in multibyte character format

Creating tables using varchar2 datatype by default allocates specified value in bytes. That means if one is storing multi-byte character then you may not be able to store all the characters as some character might be multi-byte i.e taking 2 bytes per character.

We can store multibyte charater strings with specified number of characters using CHAR along with the varchar2 datatype. e.g
Create table lingual ( text varchar2(2o) CHAR);

this way you can store up to 40 bytes or 20 multibyte characters in column text.

To find out how many characters a column of a row has, use length() function. This will return the number of character, multibyte or single byte.

Use lengthb() to determine the number of bytes the column value contains.

Validation Oracle Apps User Name and Password in ULR

Want to login automatically in Oracle Apps by bypassing the Login Screeen then use the below URL.



http://<application ULR>/OA_HTML/fndvald.jsp?username=<username>&password=<password>&requesturl=<url>

Example.
http://usnbkw030.us.ul.com:9002/OA_HTML/fndvald.jsp?username=XXULTEST&password=abc123


To Open some funciton directorly from URL

http://<application ULR>/OA_HTML/fndvald.jsp?username=<user Name>&password=<password>&requesturl=<url>&requestUrl=

20 February 2009

FNDLOAD Message Lang

How to download FNDLOAD messages for other languages

Find the language from FND_LANGUAGES

select NlS_LANGUAGE||'.'||NLS_CODESET from FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B')

Before upload
NLS_LANG="TRADITIONAL CHINESE.ZHT16BIG5"
export NLS_LANG

Then run the FNDLOAD.

FNDLOAD apps/O Y DOWNLOAD ulafmdmsg.lct .ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME= MESSAGE_NAME=

Upload
FNDLOAD apps/O Y UPLOAD ulafmdmsg.lct .ldt UPLOAD_MODE =NLS

Lang setting in Oracle Apps

fnd_global.set_nls_context(l_new_lang,null,null,null,null,null);

UserEnv Function

Oracle/PLSQL: UserEnv Function

In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for backwards compatibility, it is recommended that you use the sys_context function instead.

The syntax for the userenv function is:

userenv( parameter )

parameter is the value to return from the current Oracle session. The possible values are:

Parameter Explanation
CLIENT_INFO Returns user session information stored using the DBMS_APPLICATION_INFO package
ENTRYID Available auditing entry identifier
INSTANCE The identifier number of the current instance
ISDBA Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.
LANG The ISO abbreviation for the language
LANGUAGE The language, territory, and character of the session. In the following format:
language_territory.characterset
SESSIONID The identifier of the auditing session
TERMINAL The OS identifier of the current session

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

userenv('ENTRYID') would return FALSE
userenv('LANGUAGE') would return 'AMERICAN_AMERICA.WE8DEC'

10 February 2009

How To Use XDOLoader? Metalink : 469585.1

In this Document

  Goal

  Solution

  References




Applies to:


Oracle XML Publisher - Version: 11.5.10
Information in this document applies to any platform.


Goal


How to run the XDOLoader?



Solution


Using XDOLoader to Manage Files
The XDOLoader utility is a Java-based command line program to load template
(RTF, PDF, and XSL-FO), XML, and XSD files to the XML Publisher database tables. Use this utility to download files from one instance
and load to another.

The XDOLoader has two modes:
  • File download only mode
    Use this mode to download files from
    the XDO_LOBS table. Specify the target LOB_CODE, APPS_SHORT_NAME, and LOB_TYPE, LANGUAGE, and TERRITORY to download all files that
    match the criteria.
  • File download and LDT/DRVX generation mode
    Use this mode to download files from the XDO_LOBS tables
    and create and LDT file for the downloaded file.
           Note: A DRVX file is also created. This file is used by Oracle Development to
           load templates during patch application. It is not required for use at your site
           and can be ignored.
    Specify the APPS_SHORT_NAME to download all files (including template files, data definition files and sample xml files) that have the same application short
    name. You can also specify the DS_CODE to select files that are related to the specific data source definition.
Downloading
Files

To download the files, first set up your environment for your session by setting the
APPL_TOP and CLASSPATH.
Execute the XDOLoader utility as follows:
% java oracle.apps.xdo.oa.util.XDOLoader
DOWNLOAD \

-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION <jdbc_con_string> \
-LOB_TYPE <lob_type> \
-APPS_SHORT_NAME <application_short_name> \
-LOB_CODE <lob_code> \
-LANGUAGE <language> \
-TERRITORY <territory> \
-LOG_FILE <log file>
The parameters are described in the following table:
class="km">Parameter Name
Description

DOWNLOAD
class="km">The first parameter: DOWNLOAD
will be implemented in the feature.
Mandatory
class="km">DB_USERNAME
Database user name (example: apps).
Mandatory
class="km">DB_PASSWORD
Database user password (example: manager).
Mandatory
class="km">JDBC_CONNECTION
JDBC database connection string
(example: ap000sun:1521:db222)
class="km">Mandatory
LOB_TYPE
XDO LOB type. Valid values are:
TEMPLATE
XML_SCHEMA
XML_SAMPLE
class="km">Mandatory
APPS_SHORT_NAME
Application short name (example: AR).
class="km">Mandatory
LOB_CODE
XDO LOB code. Enter either the Template Code
or the Data Definition Code (see below).
Optional
LCT_FILE
This
is the control file for XML Publisher metadata (see below).
Optional
LANGUAGE
class="km">ISO two-letter language code (example: en)
Mandatory for template files only
rowspan="1" class="km">TERRITORY
ISO two-letter territory code (example: US)
class="km">Mandatory for template files only
LOG_FILE
Enter
a file name for the uutput log
file (default: xdotmpl.log).
Optional
rowspan="1" class="km">DEBUG
Turns debug on or off. Valid values are:
true / false (default)
rowspan="1" class="km">Optional


The parameters LOB_CODE and LCT_FILE are optional, but one
must be defined as follows:
  • LOB_CODE - use this parameter to download an individual template.
  • LCT_FILE - if you
    do not define an LOB_CODE then this parameter is required. If  you want to download multiple templates, then you must provide the LCT file. The loaded needs this file to retrieve the templates. The LCT file can be found under $XDO_TOP/patch/115/import/xdotmpl.lct.
    When you use this option you will not only get the templates, but the ldt file for the templates will be generated for you as well.

                   Note: A DRVX file is also created. This file is used by Oracle Development to
                   load templates during patch application. It is not required for use at your site
                   and can be ignored.
Example
Sample usage is as follows:
java
oracle.apps.xdo.oa.util.XDOLoader \

DOWNLOAD \
-DB_USERNAME
apps \

-DB_PASSWORD apps \
-JDBC_CONNECTION ap000sun:1521:apps115
\

-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME XDO \
class="kmfixedwidthfont">-LOB_CODE XDOTMPL1 \
-LANGUAGE ja \
-TERRITORY
JP

Download usage in LDT/DRVX mode:
java oracle.apps.xdo.oa.util.XDOLoader
DOWNLOAD \

-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION <jdbc_conn_string> \
-APPS_SHORT_NAME <application_short_name> \
-DS_CODE (data source code> \
-LCT_FILE <full path to lct file> \
-LDT_FILE <ldt file> \
-DRVX_FILE <drvx file> \
-LOG_FILE <log file>

DOWNLOAD (Mandatory) The first parameter: DOWNLOAD
will be implemented in the feature.
DB_USERNAME (Mandatory) Database user name (example:
apps).
DB_PASSWORD
(Mandatory) Database user password
(example: manager).
JDBC_CONNECTION (Mandatory) JDBC database connection string
(example:
ap000sun:1521:db222).
APPS_SHORT_NAME (Mandatory) Application short name
(example: AR).
LCT_FILE (Mandatory) Full path
to the xdotmpl.lct
DS_CODE (Optional) Data source code.
LDT_FILE (Optional) Output LDT file name (default:
xdotmpl.ldt)
DRVX_FILE
(Optional) Output DRVX file name (default:
xdotmpl.drvx)
LOG_FILE (Optional) Enter a file name for the output log
file
(default: xdotmpl.log).
DEBUG (Optional) Turns debug on or off. Valid values
are: true / false (default)

This mode
will create the template or data files, one LDT file, one DRVX file and one
log file.
Sample usage is as follows:
Sample
usage as follows:
% java oracle.apps.xdo.oa.util.XDOLoader \
DOWNLOAD
\

-DB_USERNAME apps \
-DB_PASSWORD apps \
class="kmfixedwidthfont">-JDBC_CONNECTION ap000sun:1521:apps115 \
-APPS_SHORT_NAME XDO
\

-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \
-DS_CODE
XDODS1

In this mode the LDT file can be used with the FNDLOAD utility to upload the metadata
for the downloaded
templates.

Uploading Files
To Upload the files, first set up your environment for your session by setting the
APPL_TOP
and CLASSPATH. Execute the XDOLoader utility as follows:
% java oracle.apps.xdo.oa.util.XDOLoader
UPLOAD \

-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION <jdbc_con_string> \
-LOB_TYPE <lob_type> \
-APPS_SHORT_NAME <application_short_name> \
-LOB_CODE <lob_code> \
-LANGUAGE <language> \
-TERRITORY <territory> \
-XDO_FILE_TYPE <xdo_file_type> \
-NLS_LANG <NLS_LANG> \
-FILE_CONTENT_TYPE <file_content_type> \
-FILE_NAME <file_name> \
-OWNER <owner> \
-CUSTOM_MODE [FORCE|NOFORCE] \
-LOG_FILE <log file>
The parameters are described in the following table:
 
Parameter Name
Description

UPLOAD
class="km">The first parameter: UPLOAD will be implemented in the feature.
Mandatory
class="km">DB_USERNAME
Database user name (example: apps).
Mandatory
class="km">DB_PASSWORD
 Database user password (example: manager).
Mandatory
JDBC_CONNECTION
JDBC database connection string
(example: ap000sun:1521:db222).
class="km">Mandatory
LOB_TYPE
XDO LOB type. Valid values are:
TEMPLATE
XML_SCHEMA
XML_SAMPLE
class="km">Mandatory
APPS_SHORT_NAME
Application short name (example: AR).
class="km">Mandatory
LOB_CODE
XDO LOB code. Enter either the Template Code
or the Data Definition Code.
Mandatory
NLS_LANG Enter
the NLS_LANG environment variable.
Mandatory
LANGUAGE
ISO
two-letter language code (example: en).
If NLS_LANGUAGE=’TRADITIONAL CHINESE’, then
cn_TW and if NLS_LANGUAGE=’SIMPLIFIED
CHINESE’
then cn_CN for combination of language and territory.
Optional
TERRITORY

ISO two-letter territory code (example: US),
default is ’00’.
Mandatory
class="km">XDO_FILE_TYPE
Enter the XDO file type, valid values are: PDF, RTF, XLS, XSL-FO, XSL-HTML, XSL-XML,
XSLTEXT, XSD, XML, RTF-ETEXT
Mandatory
FILE_CONTENT_TYPE
Content
type of the file (example: text/html,
application/pdf)
Optional
FILE_NAME
class="km">Name of the file you want to upload. (example: sample.pdf or test.xml)
This file name can be full path (example:
/u01/oracle/11iapp/xdo/115/patch/115/publisher/templates)
Mandatory
OWNER

Owner of the template. Default is "ORACLE".
Optional
CUSTOM_MODE
Whether to force update. Valid values are
FORCE and NOFORCE (default).
class="km">Optional
LOG_FILE
Enter a file name for the output log file (default:
xdotmpl.log).
Optional.
DEBUG
Turns
debug on or off. Valid values are: true 7 false (default)
Optional
class="km">USE_APPS_CONTEXT
Whether to use AppsContext or not. Valid values
are: true / false
(default)
If false, ’1’ is always used for Apps Login ID.
Optional


Example
Sample
usage is as follows:
% java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD
\

-DB_USERNAME apps \
-DB_PASSWORD apps \
class="kmfixedwidthfont">-JDBC_CONNECTION ap000sun:1521:apps115 \
-LOB_TYPE TEMPLATE \
class="kmfixedwidthfont">-APPS_SHORT_NAME XDO \
-LOB_CODE XDOTMPL1 \
-LANGUAGE
ja \

-TERRITORY JP \
-XDO_FILE_TYPE PDF \
class="kmfixedwidthfont">-FILE_CONTENT_TYPE ’aplication/pdf’ \
-FILE_NAME $XDO_TOP/patch/115/publisher/templates/XDOTMPL1_ja_JP.class="kmfixedwidthfont">pdf
-NLS_LANG JAPANESE_JAPAN.JA16EUC
The
XDOLoader program can be run either before or after the FNDLOAD command. The
files will be loaded with the appropriate LOB_CODE,
which will join to the metadata
loaded using the TEMPLATE_CODE or DATA_SOURCE_CODE mapping to the
LOB_CODE.


this can also be found at Appendix B starting on page 401 of

(<http://www.oracle.com/technology/products/xml-publisher/docs/xmlp5.6.2userguide.zip>)

 
but there are two missing pieces.



1. Data Templates - this is pretty straightforward. There is a new lob type called 'DATA_TEMPLATE'

so its treated just like any other XMLP file object.



2. XLIFF Files - these are the translation files, again an xml format but they have their own

loader called, surprisingly enough 'XLIFFLoader'. It's very similar to the XDOLoader, here's how

to use it



UPLOAD usage :
% java oracle.apps.xdo.oa.util.XLIFFLoader UPLOAD

-DB_USERNAME <db_username>


-DB_PASSWORD <db_password>


-JDBC_CONNECTION <jdbc_con_string>


-APPS_SHORT_NAME <application_short_name>


-TEMPLATE_CODE <template_code>


-OWNER <owner>


-CUSTOM_MODE [FORCE|NOFORCE]


-FILE_NAME <file_name>




Parameter
Name
Description

UPLOAD
The first parameter
Mandatory
style="width: 33%;" class="km">DB_USERNAME
Database user name (ex: apps)
style="width: 33%;" class="km">Mandatory
DB_PASSWORD
Database user password (ex: manager)
Mandatory
style="width: 33%;" class="km">JDBC_CONNETION
JDBC database connection string (e.g.:
ap000sun:1521:dev115)
Mandatory
APPS_SHORT_NAME

3 letter Application short name (ex: XDO)
Mandatory
style="width: 33%;" class="km">TEMPLATE_CODE
XDO Template code, if not given, it should
be indicated
in the header section of the xliff.
Optional
style="width: 33%;" class="km">OWNER
Owner of the template. Default is "ORACLE"
Optional
CUSTOM_MODE :
[FORCE|NOFORCE] Whether force update, default is NOFORCE
Optional
style="width: 33%;" class="km">FILE_NAME
Name of the file to be uploaded
Mandatory





Heres a sample usage:



% java oracle.apps.xdo.oa.util.XLIFFLoader


UPLOAD


-DB_USERNAME apps


-DB_PASSWORD apps


-JDBC_CONNECTION ap000sun:1521:apps115


-APPS_SHORT_NAME XDO


-FILE_NAME patch/115/publisher/templates/JA/XDOTMPL1_ja_JP.xlf










DOWNLOAD Usage :
% java oracle.apps.xdo.oa.util.XLIFFLoader DOWNLOAD

-DB_USERNAME <db_username>


-DB_PASSWORD <db_password>


-JDBC_CONNECTION <jdbc_con_string>


-APPS_SHORT_NAME <application_short_name>


-DS_CODE <ds_code>


-TEMPLATE_CODE <lob_code>


-FILES_DIR <directory>


-SUMMARY_FILE <summary_file>



 
Parameter Name
Description

DOWNLOAD
The
first parameter
Mandatory
DB_USERNAME
Database user
name (ex: apps)
Dandatory
DB_PASSWORD
Database user
password (ex: manager)
Mandatory
JDBC_CONNETION
JDBC
database connection string (e.g.: ap000sun:1521:dev115)
Mandatory
APPS_SHORT_NAME

3 letter Application short name (ex: XDO)
Optional
DS_CODE
class="km">XDO Data Definition code
Optional
TEMPLATE_CODE
XDO
Template code
Optional
FILES_DIR
Files will be downloaded
to the given directory. If not provided, files are placed in the current directory.
Optional
class="km">SUMMARY_FILE

If provided, will generate an xml file containing list of files downloaded. This
should be the full path name of the target file
(not affected by FILES_DIR parameter).

Optional




Heres a sample usage:



% java oracle.apps.xdo.oa.util.XLIFFLoader


DOWNLOAD


-DB_USERNAME apps


-DB_PASSWORD apps


-JDBC_CONNECTION ap000sun:1521:apps115


-APPS_SHORT_NAME XDO


-TEMPLATE_CODE XDOTMPL1




So now you should have everything you need to move those templates, data templates, translations,

etc from test to production. We will of course get all of the above into the doc as soon as we can.






For problems with JDBC_CONNECTION like:
     APP-FND-01564: ORACLE error 6401 in AFPCOA
please have a look at Metalink Note 374195.1
       - Running XDOLOAD to Download XML Data Definitions Fails with ORA-6401





References


Note 374195.1 - Running XDOLOAD to Download XML Data Definitions Fails with ORA-6401


Keywords


XML~PUBLISHER ; DATA~FILE ; TEMPLATE~FILE ; DATA~TEMPLATE ; LOADER ; XDO_LOBS ; FNDLOABLISHER ; 





Help us improve our service. Please email us your comments for this document.
.

  
 
















Copyright © 2006, Oracle. All rights reserved.
href="ml2.show_legal_text" onClick="return popup('ml2.show_legal_text')" style="color:#000000;font-weight:bold">Legal Notices  and Terms of Use | target=_top style="color:#000000;font-weight:bold">Privacy Statement







09 February 2009

How to sort VARCHAR2 field in numerically using ORDER BY

Courtesy : Kiran Bukka

Question:  I have a field defined in Oracle as a varchar2, but it contains both numbers and characters. When I use an "order by" clause, the records are sorted ascending by character. I want to sort it numerically without changing the datatype from varchar2 to numeric.

Solution : This can be done using LPAD function in the order by clause. LPAD with spaces for the maximum length of the field which
we want to sort by.

This SQL pads the front of the field with spaces up to maximum length of the field. Now, the results should be sorted numerically in ascending order

For Ex : If we want to sort by system_number field in csi_systems_b table. This field is a varchar(30) field.
so in order by clause we have to put it as order by LPAD(csb.system_number,30)

Query : See the difference in the below query result with and without LPAD function.