Edi Yanto (何 萬 新)


You can raise exception with this below scripts:

DECLARE
ey_custom EXCEPTION;
PRAGMA EXCEPTION_INIT (ey_custom, -20288);
BEGIN
raise_application_error (-20288, ‘EY – This is a custom error’);
EXCEPTION
WHEN ey_custom
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;

–OR

DECLARE
ey_custom EXCEPTION;
BEGIN
RAISE ey_custom;
EXCEPTION
WHEN ey_custom
THEN
RAISE_APPLICATION_ERROR (-20288, ‘EY – This is a custom error’);
END;

Advertisements

The below code is used to compress the file using standard package (UTL_COMPRESS) in Oracle.

DECLARE
v_filename VARCHAR2 (100);
src_file BFILE;
v_content BLOB;
v_blob_len INTEGER;
v_file UTL_FILE.file_type;
v_buffer RAW (32767);
v_amount BINARY_INTEGER := 32767;
v_pos INTEGER := 1;
BEGIN
v_filename := ‘EY_TOP.dat’;
src_file := BFILENAME (‘EY_DIR’, v_filename);
DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
v_content := UTL_COMPRESS.lz_compress (src_file, 6);
v_blob_len := DBMS_LOB.getlength (v_content);
v_file := UTL_FILE.fopen (‘EY_DIR’, v_filename || ‘.zip’, ‘wb’);

WHILE v_pos < v_blob_len
LOOP
DBMS_LOB.READ (v_content,
v_amount,
v_pos,
v_buffer);
UTL_FILE.put_raw (v_file, v_buffer, TRUE);
v_pos := v_pos + v_amount;
END LOOP;

UTL_FILE.fclose (v_file);
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (v_file)
THEN
UTL_FILE.fclose (v_file);
END IF;

RAISE;
END;

If you want the extension of the source file included in the compress file you must include the source file extension also in the filename of compress file. 


We can enable attachment link in Oracle Forms with below steps:

  • Navigate to “Application Developer” Responsibility and choose the menu Attachments > Document Entities. Identify Document Entity exists or not. If exists then do nothing otherwise create new.
  • You can create a new Document Categories or use the existing categories assign to the Oracle Forms (Attachments > Document Categories)
  • Setup attachment at Form or Function Level and make sure Enabled checkbox is checked (Attachments > Attachment Functions
  • Assign categories (ex: Miscellaneous), click button Categories on Attachment Functions Form

Read the rest of this entry »


Scenario: We want to disable “Sample No” parameter if “Type” parameter value is “Receipt“, otherwise disable “Receipt No” parameter if “Type” parameter value is “Sample” and the parameter must be mandatory if enable.

We have to define concurrent program with the following parameters:

  1. Type (value are Sample or Receipt, using value set independent (ex: “EY_TYPE”))
  2. Run_Mode_S (use SQL Statement with value “select ‘S’ from dual where :$FLEX$.EY_TYPE:null in (‘Sample’)“, and the parameter is  not display)
  3. Run_Mode_R (use SQL Statement with value “select ‘R’ from dual where :$FLEX$.EY_TYPE:null in (‘Receipt’)“, and the parameter is  not display)
  4. Sampe_No (value set “EY_SAMPLE” and set parameter required)
  5. Receipt_No (value set “EY_RECEIPT” and set parameter required)

Read the rest of this entry »


SELECT p.profile_option_name short_name, n.user_profile_option_name NAME,
DECODE (v.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10006, ‘Org’,
10007, DECODE (TO_CHAR (v.level_value2), ‘-1’, ‘Responsibility’, DECODE (TO_CHAR (v.level_value), ‘-1’, ‘Server’, ‘Server+Resp’)),
‘UnDef’
) level_set,
DECODE (TO_CHAR (v.level_id),
‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10004’, usr.user_name,
‘10005’, svr.node_name,
‘10006’, org.NAME,
‘10007’, DECODE (TO_CHAR (v.level_value2),
‘-1’, rsp.responsibility_key,
DECODE (TO_CHAR (v.level_value),
‘-1’, (SELECT node_name
FROM fnd_nodes
WHERE node_id = v.level_value2),
(SELECT node_name
FROM fnd_nodes
WHERE node_id = v.level_value2) || ‘-‘ || rsp.responsibility_key
)
),
‘UnDef’
) “CONTEXT”,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND UPPER (p.profile_option_name) IN (SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE UPPER (user_profile_option_name) LIKE UPPER (‘%&user_profile_name%’))
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY short_name, user_profile_option_name, level_id, level_set;


Oracle APEX 4 Tutorial

 

Oracle APEX 5 Tutorial


Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,457 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications







Follow me on Twitter

Archives

May 2018
M T W T F S S
« Mar    
 123456
78910111213
14151617181920
21222324252627
28293031  

Visitors

Categories

Blog Stats

  • 196,188 hits

Users Online

web counter

Pages

Advertisements