NLS_LENGTH_SEMANTICS -BYTE/CHAR

Have you ever run into a problem when you are trying to insert data with special characters blowing up with ORA-12899: value too large for column.

The most surprising issue about this error is that encounter it when you might be trying to insert a string of length 25 into a table column of VARCHAR2(25) which should work like magic.

I will explain with an example:

Create a database Table abc_t.

CREATE TABLE abc_t (name_my VARCHAR2(25 BYTE));

Try inserting the 25 character value to abc_t.

INSERT INTO abc_t VALUES ('Höhenkirchen-Siegertsbrun');

This will blow up as though the length for the above string is 25 and column size is 25. Its because the byte size of the string above is 26 characters. The character causing the problem is ö .

Now one way to solve the problem would be to increase the size of the column to VARCHAR2(26). This is not a good fix.

The best solution to the problem is to define the table column

Create a database Table abc_t1.

CREATE TABLE abc_t (name_my VARCHAR2(25 CHAR));

Try inserting the 25 character value to abc_t.

INSERT INTO abc_t VALUES ('Höhenkirchen-Siegertsbrun');

This will work.

Now whats the difference. The difference is the way we defined the table VARCHAR2(25 BYTE/CHAR).

Now rather than taking care of this everytime when you define a database table you can set a Database Parameter called NLS_LENGTH_SEMANTICS.

You can query the value of this DB parameter using this query.

SELECT * FROM v$parameter WHERE name = Lower('NLS_LENGTH_SEMANTICS')

This value by default is set to BYTE so when you define a table column with VARCHAR2(25) it translates into VARCHAR2(25 BYTE).

But if you set this option to CHAR when you define a table column with VARCHAR2(25) it translates into VARCHAR2(25 CHAR).

Recommendation/Conclusion

For a System with Oracle Database where user will be keying in multilingual characters like French/Greek/German Characters one should set the NLS_LENGTH_SEMANTICS parameter to CHAR.

Free Code Migration Tool for Oracle Applications Release 12

Yes you read it correct. A free code migration tool for Oracle Applications Entity. Coming soon.

http://www.applikast.net/technical/tools/oracle-e-business-customization-installation-tool

Oracle OPM: Updating Batch Material Line based on the source Formula Line

I have seen quite a few times that we have a requirement of copying the DFF attributes on the Formula Line to the Material Line when the Batch containing this Material Line gets created.

One way is to create a trigger on the base table of Material Line and implement the necessary logic on ON-INSERT trigger. I personally hate triggers.

One neater way of doing it is to subscribe to Batch Created (oracle.apps.gme.batch.created).

Steps to create the Subscription for the event and execute the

DFF

copying logic thru a plsql function

Ø Go to the Workflow Administrator Webapplications -> Administrator Workflow.

Ø Open the Business Events page.

Ø Search for business event “oracle.apps.gme.batch.created”.

Ø Click on the subscription button. It displays the subscriptions page.

Ø Click on the Create Subscription button.

Ø Specify the following values for the fields in the page.

Subscriber Section

System:

Select

Triggering Event Section

Leave the Section as it is.

Execution Condition Section

Phase: 80

Status: Enabled.

Rule Data: Key

Action type Section

Action type: Custom

On Error: Skip to Next

Ø Click on Next Button:

Action Section

PL/SQL Rule Function: XX_UPDATE_MTL_LINE_DFF_PKG.update_mtl_line_dff_prc Priority: Normal

Documentation Section

Owner Name: Custom Application.

Documentation: XX.

Click on Apply Button.

The custom plsql procedure to copy the DFF will leverage the std Oracle Public API

GME_API_PUB.update_material_line

The sample plsql code below explains how the plsql procedure is hooked on with the plsql procedure via the subscription to the Business Event.

FUNCTION update_mtl_line_dff_prc( p_subscription_guid_in IN RAW

,p_event_inout IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2

IS

TYPE l_tbl_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

l_material_detail_id_tbl l_tbl_num_type;

l_batch_header_rec gme_batch_header%ROWTYPE;

l_material_detail_rec gme_material_details%ROWTYPE;

x_material_detail_rec gme_material_details%ROWTYPE;

temp_material_detail_rec gme_material_details%ROWTYPE;

l_formula_orig_line_no FM_MATL_DTL.ATTRIBUTE1%TYPE;

l_batch_id gme_batch_header.batch_id%TYPE;

l_event_name_txt VARCHAR2 (2000);

l_event_key_num VARCHAR2 (2000);

x_message_count NUMBER := 0;

x_message_list VARCHAR2(4000) := NULL;

x_return_status VARCHAR2(1) := 'U';

l_msg_index_out NUMBER := 0;

MTL_LINE_EXC EXCEPTION;

BEGIN

l_event_name_txt := p_event_inout.geteventname ();

l_event_key_num := p_event_inout.geteventkey ();

l_batch_id := to_number(l_event_key_num);

SELECT *

INTO l_batch_header_rec

FROM gme_batch_header

WHERE batch_id = l_batch_id;

SELECT material_detail_id

BULK COLLECT INTO l_material_detail_id_tbl

FROM gme_material_details

WHERE batch_id = l_batch_id

AND LINE_TYPE = -1;

FOR I IN 1..l_material_detail_id_tbl.COUNT LOOP

l_material_detail_rec := temp_material_detail_rec;

SELECT FMD.ATTRIBUTE1

INTO l_formula_orig_line_no

FROM FM_MATL_DTL FMD

, GME_MATERIAL_DETAILS GMD

WHERE FMD.FORMULALINE_ID = GMD.FORMULALINE_ID

AND GMD.MATERIAL_DETAIL_ID = l_material_detail_id_tbl(I);

l_material_detail_rec.material_detail_id := l_material_detail_id_tbl(I);

l_material_detail_rec.attribute1 := l_formula_orig_line_no;

GME_API_PUB.update_material_line( p_batch_header_rec => l_batch_header_rec

,p_material_detail_rec => l_material_detail_rec

,p_locator_code => NULL

,p_org_code => NULL

,x_material_detail_rec => x_material_detail_rec

,x_message_count => x_message_count

,x_message_list => x_message_list

,x_return_status => x_return_status

,p_init_msg_list => fnd_api.g_true

,p_commit => fnd_api.g_false

,p_validate_flexfields => fnd_api.g_false);

IF x_return_status = FND_API.g_ret_sts_success THEN

COMMIT;

ELSE

IF x_message_count = 1 THEN

RAISE MTL_LINE_EXC;

ELSE

FOR j IN 1..x_message_count LOOP

FND_MSG_PUB.get( p_msg_index => j

,p_encoded => 'F'

,p_data => x_message_list

,p_msg_index_out => l_msg_index_out);

RAISE MTL_LINE_EXC;

END LOOP;

END IF;

END IF;

END LOOP;

RETURN 'SUCCESS';

EXCEPTION

WHEN OTHERS THEN

RETURN 'FAILURE';

RAISE;

END update_mtl_line_dff_prc;

RTF Template Labels get displayed as Junk Characters when generated as PDF

An RTF Layout Template includes the Greek/Turkish Symbol and is displayed correctly using Microsoft Word. Generating a PDF output causes these symbols to be converted in to inverted question mark '?'.

Thanks to Metalink I was able to figure out the root cause and solution. I have summarized what should be the fix for such issues.

These characters requires the Albany font files to be available within the Java Runtime

Environment (JRE). The question mark is an indication the that the XML Publisher generator

was not able to find the proper font file.

In Release12, Copy the ALB*.ttf font files to the $OA_JRE_TOP/lib/fonts and $AF_JRE_TOP/lib/fonts directory.

You can find the ALB*.ttf font files in the $FND_TOP/resource folder.

For me this file did the magic ALBANYWT.ttf

Converting the PLD to PLL and PLX

Generally one would always work on a PLL when it comes to working on Custom PLL and compile it to PLX.

I personally feel that working on a text version allows one to work on the Custom PLL much faster and especially when converting the text format to executable is just two commands.

For e.g. if you have a .pld file you can convert it to .plx using the following two step process.

Converting PLD to PLL

frmcmp_batch Module=.pld Userid=apps/apps@MYDEVDB parse=yes Module_Type=LIBRARY

Converting PLL to PLX

frmcmp_batch Module=.pll Userid= apps/apps@MYDEVDB compile_all=yes Module_Type=LIBRARY

Converting PLL to PLD

If you already have something in PLL format and want to convert it to text

frmcmp_batch Module=.pll Userid= apps/apps@MYDEVDB Script=YES Module_Type=LIBRARY Output_File= .pld

Just thought this might come in handy for the XML Publisher experts out there.

I quite have run into situation when my DBA friend missed out on installing the RTF Template as part of object migration and as I don't have access to the XML Publisher Administrator responsibility I cannot verify the same.

The queries below lets one verify from the backend whether the template and the Data Definition were uploaded by your System Administrators or not.

/* Query for Data Definition */

SELECT xddt.data_source_name "Data Source Name",
xddb.data_source_code "Code",
fat.application_name "Application Name"
FROM xdo_ds_definitions_b xddb,
xdo_ds_definitions_tl xddt,
fnd_application fa,
fnd_application_tl fat
WHERE xddb.data_source_code LIKE 'XX%'
AND xddt.data_source_code = xddb.data_source_code
AND xddt.LANGUAGE = 'US'
AND fa.application_short_name = xddb.application_short_name
AND fat.application_id = fa.application_id
AND fat.LANGUAGE = 'US'

/* Query for Templates */

SELECT xtt.template_name "Template Name",
xtb.template_code "Template Code",
fat.application_name "Application Name",
xddt.data_source_name "Data Definition",
xtb.template_type_code "Template Type",
xtb.default_language "Language",
xtb.default_territory "Territory",
xl.file_name "File Name",
xl.file_data "File"
FROM xdo_templates_tl xtt,
xdo_templates_b xtb,
fnd_application_tl fat,
xdo_ds_definitions_tl xddt,
xdo_lobs xl
WHERE (xtt.template_name LIKE '%' OR xtt.template_name LIKE 'XX%')
AND xtt.LANGUAGE = 'US'
AND xtb.template_code = xtt.template_code
AND fat.application_id = xtb.application_id
AND fat.LANGUAGE = 'US'
AND xddt.data_source_code = xtb.data_source_code
AND xddt.LANGUAGE = 'US'
AND xl.lob_code = xtb.template_code
AND NVL (xl.program, 'X') <> 'RTF2XSLParser 5.6.3'
AND xl.LANGUAGE = xtb.default_language
AND xl.territory = xtb.default_territory