I have created a form using HTML. Now i have to browse excel sheet and on click of submit button i want excel data to be submitted in oracle table through procedure.
Its very urgent.
Please help me out.
I have created a form using HTML. Now i have to browse excel sheet and on click of submit button i want excel data to be submitted in oracle table through procedure.
Its very urgent.
Please help me out.
you can call this ddl in dynamic sql to create a external table.
Code:CREATE TABLE EMP_ext ( EMPNO NUMBER (4), ENAME VARCHAR2 (10 BYTE), JOB VARCHAR2 (9 BYTE), MGR NUMBER (4), HIREDATE DATE, SAL NUMBER (7,2), COMM NUMBER (7,2), DEPTNO NUMBER (2), MERGE VARCHAR2 (50 BYTE) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY XML_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION ( 'emp.txt') ) PARALLEL 5 REJECT LIMIT UNLIMITED;
Or You can use the following code to do that data loading. Of course with some modifications.
Code:/ * Formatted on 2008/05/14 13:43 (Formatter Plus v4.8.7) * / DECLARE f UTL_FILE.file_type; s VARCHAR2 (200); l LONG: =''; i NUMBER (3): = 0; BEGIN f: = UTL_FILE.fopen ( 'XML_DIR', 'emp.txt', 'R'); WHILE (TRUE) LOOP UTL_FILE.get_line (f, s); DBMS_OUTPUT.put_line (s); i: = i + 1; l: = l | | s; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('================ END OF FILE ================'); DBMS_OUTPUT.put_line ( '** NO OF LINES:' | | i); DBMS_OUTPUT.put_line (l); UTL_FILE.fclose (f); INSERT INTO chk_long VALUES (l); WHEN OTHERS THEN DBMS_OUTPUT.put_line ('================ SOME ERROR OCCURED ==============='); UTL_FILE.fclose (f); END;
"form using HTML" implies a client front end.
"excel data" implies excel file which is Microsoft proprietary data format (binary data).
"submitted in oracle table" implies excel data on client has to get from client to database server.
Hint: you will need some java that is written to access files on the client through the web browser (which then reads the binary file and uploads it to a BLOB in a table on the database).
If this is not your requirement you need to be more clear.
Bookmarks