ORACLE MASTERMINDS

Wednesday, 4 July 2018

Using Oracle Forms to load local files

using the below code In load file “button” using “when-button-pressed” trigger.

DECLARE

l_userhome VARCHAR2 (200)

:= webutil_clientinfo.get_system_property ('user.home')

|| '\Desktop';

l_filename VARCHAR2 (200) := NULL;

l_bare_filename VARCHAR2 (200) := NULL;

l_fileprefix VARCHAR2 (200) := NULL;

l_serverfilename VARCHAR2 (200) := NULL;

l_upload_path VARCHAR2 (200) := NULL;

l_version_path VARCHAR2 (200) := NULL;

l_option_grade_path VARCHAR2 (200) := NULL;

l_size_curve_path VARCHAR2 (200) := NULL;

l_filesize NUMBER;

al_con alert;

l_status BOOLEAN;

os_name VARCHAR2 (100)

:= UPPER (webutil_clientinfo.get_operating_system);

l_mainbrand VARCHAR2 (100);

l_country VARCHAR2 (100);

l_jobid NUMBER;

CURSOR c_upload_dir

SELECT t.VALUE AS directory_path

FROM sys_directories t

WHERE t.NAME = 'OE_TRACKING_FILE';

BEGIN

OPEN c_upload_dir;

FETCH c_upload_dir

INTO l_upload_path;

CLOSE c_upload_dir;

l_filename :=

webutil_file.file_open_dialog

(directory_name => l_userhome,

file_name => NULL,

file_filter => 'CSV files (*.csv) |*.csv',

title => 'Dir Upload'

);

IF l_filename IS NOT NULL

THEN

--- to get the file size ---

l_filesize := webutil_file.file_size (file_name => l_filename);

--- to get the filename without the path ---

IF os_name LIKE 'WINDOWS%'

THEN

l_bare_filename :=

SUBSTR (l_filename, INSTR (l_filename, '\', -1)

+ 1);

ELSE

l_bare_filename :=

SUBSTR (l_filename, INSTR (l_filename, '/', -1)

+ 1);

END IF;

IF webutil_file.file_exists (file_name => l_filename)

THEN

IF webutil_file.file_is_readable (file_name => l_filename)

THEN

IF l_filesize > 0

THEN

l_serverfilename :=

l_bare_filename

|| '_'

|| TO_CHAR (SYSDATE, 'DDMMYYYYHH24MISS');

IF l_upload_path IS NOT NULL

THEN

l_status :=

webutil_file_transfer.client_to_as_with_progress

(clientfile => l_filename,

serverfile => l_upload_path

|| '/'

|| REPLACE

(l_serverfilename,

'.csv',

)

|| '.csv',

progresstitle => 'File Upload in progress',

progresssubtitle => 'Please wait',

asynchronous => FALSE,

callbacktrigger => ''

);

SYNCHRONIZE;

IF l_status = TRUE

THEN

MESSAGE ('done transfer');

l_status := FALSE;

ELSE

MESSAGE ('Not transfer');

END IF;

ELSE

MESSAGE ('File ' || l_bare_filename || ' is blank');

END IF;

ELSE

MESSAGE ( 'Unable to read the file'

|| CHR (10)

|| 'File Name='

|| l_bare_filename

|| ', '

|| CHR (10)

|| 'File Size='

|| l_filesize

|| ' Byte'

);

END IF;

ELSE

MESSAGE ('File ' || l_bare_filename || ' is not found');

END IF;

EXCEPTION

WHEN OTHERS

THEN

MESSAGE (SQLERRM);

END;

Load JSON File to Oracle table

For Example :

1) Sample Json:-

{

"department": {

"department_number": 10,

"department_name": "ACCOUNTING",

"employees": [

{

"employee_number": 7782,

"employee_name": "CLARK"

{

"employee_number": 7839,

"employee_name": "KING"

{

"employee_number": 7934,

"employee_name": "MILLER"

}

]

}

2) Above File Stored in the below table and this column “order_document”

CREATE TABLE json_order (ID NUMBER NOT NULL,

order_document CLOB

)

3) Above File data load to below table.

create table Json_emp (E_no number,

E_name varchar2(100),

D_no number,

d_name varchar2(100)

)

4) Below coding for above file to data insertion on Json_emp Table.

DECLARE

l_json_text CLOB;

l_count PLS_INTEGER;

l_members wwv_flow_t_varchar2;

l_paths apex_t_varchar2;

l_exists BOOLEAN;

BEGIN

SELECT order_document

INTO l_json_text

FROM json_order;

apex_json.parse (l_json_text);

l_count := apex_json.get_count (p_path => 'department.employees');

FOR i IN 1 .. l_count

LOOP

INSERT INTO json_emp

(e_no,

e_name,

d_no,

d_name

)

VALUES (apex_json.get_number

(p_path => 'department.employees[%d].employee_number',

p0 => i

apex_json.get_varchar2

(p_path => 'department.employees[%d].employee_name',

p0 => i

apex_json.get_number

(p_path => 'department.department_number'),

apex_json.get_varchar2

(p_path => 'department.department_name')

);

END LOOP;

COMMIT;

END;