Contents
1 Misc sample codes url
http://www.java2s.com/Tutorial/Oracle/0520__Collections/0040__Varray.htm
2 FTP from pl/sql
http://oracle-base.com/articles/misc/ftp-from-plsql.php#acl
CREATE
OR REPLACE DIRECTORY my_docs AS '/u01/app/oracle/';
SET SERVEROUTPUT
ON SIZE 1000000
@c:\ftp.pks
@c:\ftp.pkb
--
Retrieve an ASCII file from a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.ascii(p_conn => l_conn);
ftp.get(p_conn => l_conn,
p_from_file =>
'/u01/app/oracle/test.txt',
p_to_dir => 'MY_DOCS',
p_to_file => 'test_get.txt');
ftp.logout(l_conn);
END;
/
-- Send
an ASCII file to a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.ascii(p_conn => l_conn);
ftp.put(p_conn => l_conn,
p_from_dir => 'MY_DOCS',
p_from_file => 'test_get.txt',
p_to_file
=> '/u01/app/oracle/test_put.txt');
ftp.logout(l_conn);
END;
/
--
Retrieve a binary file from a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.binary(p_conn => l_conn);
ftp.get(p_conn => l_conn,
p_from_file =>
'/u01/app/oracle/product/9.2.0.1.0/sysman/reporting/gif/jobs.gif',
p_to_dir => 'MY_DOCS',
p_to_file => 'jobs_get.gif');
ftp.logout(l_conn);
END;
/
-- Send
a binary file to a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.binary(p_conn => l_conn);
ftp.put(p_conn => l_conn,
p_from_dir => 'MY_DOCS',
p_from_file => 'jobs_get.gif',
p_to_file => '/u01/app/oracle/jobs_put.gif');
ftp.logout(l_conn);
END;
/
-- Get a
directory listing from a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
l_list
ftp.t_string_table;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.list(p_conn => l_conn,
p_dir => '/u01/app/oracle',
p_list => l_list);
ftp.logout(l_conn);
IF l_list.COUNT > 0 THEN
FOR i IN l_list.first .. l_list.last LOOP
DBMS_OUTPUT.put_line(i || ': ' ||
l_list(i));
END LOOP;
END IF;
END;
/
-- Get a
directory listing (file names only) from a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
l_list
ftp.t_string_table;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.nlst(p_conn => l_conn,
p_dir => '/u01/app/oracle',
p_list => l_list);
ftp.logout(l_conn);
IF l_list.COUNT > 0 THEN
FOR i IN l_list.first .. l_list.last LOOP
DBMS_OUTPUT.put_line(i || ': ' ||
l_list(i));
END LOOP;
END IF;
END;
/
--
Rename a file on a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.rename(p_conn => l_conn,
p_from =>
'/u01/app/oracle/dba/shutdown',
p_to => '/u01/app/oracle/dba/shutdown.old');
ftp.logout(l_conn);
END;
/
--
Delete a file on a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.delete(p_conn => l_conn,
p_file =>
'/u01/app/oracle/dba/temp.txt');
ftp.logout(l_conn);
END;
/
--
Create a directory on a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.mkdir(p_conn => l_conn,
p_dir =>
'/u01/app/oracle/test');
ftp.logout(l_conn);
END;
/
--
Remove a directory from a remote FTP server.
DECLARE
l_conn
UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.company.com', '21',
'ftpuser', 'ftppassword');
ftp.rmdir(p_conn => l_conn,
p_dir => '/u01/app/oracle/test');
ftp.logout(l_conn);
END;
/
3 MS Exchange API for PL/SQL
Morten Braten’s blogs
4 calling OS command from PL/SQL
5 Extracting XML information using PL/SQL
DECLARE
xml_char VARCHAR2(1024);
xml xmltype;
v_type VARCHAR2(32);
v_server VARCHAR2(32);
v_port VARCHAR2(32);
BEGIN
-- create dummy XML document
xml_char := '<?xml
version="1.0"?>' ||
'<CONNECTION>' ||
' <MAPSERVER type="Arcims">'
||
' <HOST>uranus</HOST>' ||
' <PORT>5300</PORT>' ||
' </MAPSERVER>'
||
'</CONNECTION>';
-- convert to XML from char type
xml := xmltype.createxml(xml_char);
-- extract some elements
v_type
:= xml.EXTRACT('/CONNECTION/MAPSERVER/@type').getstringval();
v_server :=
xml.EXTRACT('/CONNECTION/MAPSERVER/HOST/text()').getstringval();
v_port :=
xml.EXTRACT('/CONNECTION/MAPSERVER/PORT/text()').getstringval();
-- display them
DBMS_OUTPUT.PUT_LINE('Type = ' ||
v_type);
DBMS_OUTPUT.PUT_LINE('Server = ' ||
v_server);
DBMS_OUTPUT.PUT_LINE('Port = ' || v_port);
END;
6 Oracle characterset. Ora-29275
https://community.qlik.com/ideas/3400
SELECT value$ FROM sys.props$ WHERE name='NLS_CHARACTERSET';
substr(convert(YourColumn,'US7ASCII'
),1,2000)
or
some trim function
along with the convert function.
LTRIM(RTRIM(substr(convert(YourColumn,'US7ASCII'),1,2000))) as
DesiredColumn_Name.
convert(COLUMN_NAME,'US7ASCII','WE8ISO8859P1')
as COLUMN_NAME
7 Read Excel file from pl/sql
http://www.dba-oracle.com/t_read_excel_file_from_pl_sql.htm -- using excel-db.
7.1 https://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/
ANTON Scheffer
select * from table( as_read_xlsx.read(
as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) );
SHEET_NR SHEET_NAME ROW_NR COL_NR CELL
CEL STRING_VAL NUMBER_VAL DATE_VAL
---------- ---------- ------ ------ ----- ---
---------- ---------- --------------------------
1 Mijn naam 1 1 A1
N 11
1 Mijn naam 1 2 B1
N 12
1 Mijn naam 1 3 C1
N 13
1 Mijn naam 2 1 A2
N 21
1 Mijn naam 2 2 B2
N 22
1 Mijn naam 2 3 C2
N 23
1 Mijn naam 3 1 A3
N 31
1 Mijn naam 3 2 B3
N 32
1 Mijn naam 3 3 C3
N 33
1 Mijn naam 4 4 D4
S D4
1 Mijn naam 6 2 B6
D
1 Mijn naam 7 2 B7
D
1 Mijn naam 8 2 B8
D
1 Mijn naam 9 2 B9
D
1 Mijn naam 10 2 B10
D
2 Sheet3 2 2 B2
S Test
2 Sheet3 3 3 C3
D 19-JAN-2013 20:17:00
2 Sheet3 4 1 A4
S Anton
7.2 http://www.apex-plugin.com/oracle-apex-plugins/process-type-plugin/excel2collections_271.html
Plugin.
7.3 http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/read-excel-file-using-utl_file-package-3588208
7.4 https://sites.google.com/site/craigsoraclestuff/oracle-forms-webutil/read-excel-into-forms
DECLARE
application Client_OLE2.Obj_Type;
workbooks Client_OLE2.Obj_Type;
workbook Client_OLE2.Obj_Type;
worksheets Client_OLE2.Obj_Type;
worksheet Client_OLE2.Obj_Type;
worksheet2 Client_OLE2.Obj_Type;
cell Client_OLE2.OBJ_TYPE;
args Client_OLE2.OBJ_TYPE;
cell_value varchar2(100);
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 1;
v_fName VARCHAR2(250);
BEGIN
-- Get the name
of the file to open
--v_fName
:=
'D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls';
--
My Way: Use a File Open Dialog to let the user select the file.
v_fName
:= WebUtil_File.File_Open_Dialog(
directory_name
=> 'C:\'
,File_Filter
=> null
,Title
=> 'Select Client filename to Open.'
);
-- Make sure the user selected a file
IF (
v_fName IS NOT NULL ) THEN
-- The following
sets up communication with the excel spreadsheet
--
--------------------------------------------------------------
--
Open the OLE application
application
:= Client_OLE2.create_obj('Excel.Application');
-- Keep the application
hidden
Client_OLE2.set_property(application,'Visible','false');
workbooks
:= Client_OLE2.Get_Obj_Property(application, 'Workbooks');
args
:= Client_OLE2.CREATE_ARGLIST;
-- Open the
selected File
--
----------------------
Client_OLE2.add_arg(args,v_fName);
workbook
:= Client_OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
Client_OLE2.destroy_arglist(args);
worksheets
:= Client_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
-- Get number of
worksheets
--
------------------------
num_wrkshts
:= Client_OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet
:= Client_OLE2.GET_OBJ_PROPERTY(
application,'activesheet');
--Go to the
first record
go_block('planets');
first_record;
--
Loop through the Block and create a new row if
needed.
loop
If :system.record_status
<> 'NEW' then
create_record;
end if;
--
Exit when the last row of the spreadsheet is
reached.
exit when eod;
--
Loop through the spreadsheet and get cell values
for k in 1..3 loop --3 fields per
record
-- You have to
know fields there are
args:=
Client_OLE2.create_arglist;
Client_OLE2.add_arg(args,
j);
Client_OLE2.add_arg(args,
k);
cell:=
Client_OLE2.get_obj_property(worksheet, 'Cells', args);
Client_OLE2.destroy_arglist(args);
cell_value
:=Client_OLE2.get_char_property(cell, 'Value');
--
Check for End of Data…
if upper(cell_value)
= 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;
--
Copy the value from Excel to the Forms block item
-- This is how
the Oracle example copied values
/*if
k =1 then
:dept.deptno:=cell_value;
end
if;
if
k =2 then
:dept.dname:=cell_value;
end
if;
if
k =3 then
:dept.loc:=cell_value;
end
if;
*/
--
This is my way; which is more efficient and less code
copy(cell_value,name_in('system.cursor_item'));
next_item;
end loop; --for
j:=j+1;
end loop; --main loop
-- Release the
Client_OLE2 object handles
IF (cell IS
NOT NULL) THEN
Client_OLE2.release_obj(cell);
END IF;
IF (worksheet
IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet);
END IF;
IF (worksheets
IS NOT NULL) THEN
Client_OLE2.release_obj(worksheets);
END IF;
IF (worksheet2
IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet2);
END IF;
IF (workbook
IS NOT NULL) THEN
Client_OLE2.release_obj(workbook);
END IF;
IF (workbooks
IS NOT NULL) THEN
Client_OLE2.release_obj(workbooks);
END IF;
Client_OLE2.invoke(application,'Quit');
Client_OLE2.release_obj(application);
ELSE
Message('No File
selected.');
message(' ');
RAISE Form_Trigger_Failure;
END
IF;
END;
7.5 http://www.dba-oracle.com/t_load_excel_spreadsheet_into_oracle_table.htm
DECLARE
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
ctr NUMBER(12);
cols NUMBER(2);
name_var1 VARCHAR2(2000);
name_var2 VARCHAR2(2000);
name_var3 VARCHAR2(2000);
name_var4 VARCHAR2(2000);
filename varchar2(100);
PROCEDURE OLEARG IS
args OLE2.OBJ_TYPE;
BEGIN
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,ctr); -- row value
ole2.add_arg(args,cols); -- column value
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell
ole2.destroy_arglist(args);
END;
BEGIN
filename :=GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','true');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args);
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'Sheet1');
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
ole2.destroy_arglist(args);
ctr := 2; --row number
cols := 1; -- column number
FIRST_RECORD;
LOOP
OLEARG;
name_var1 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var4 := ole2.get_num_property(cell,'Value'); --cell number value of the argument
IF ctr = 1 then
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
ELSE
NEXT_RECORD;
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
END IF;
EXIT WHEN length(name_var1) = 0 or length(name_var1) is null;
ctr := ctr + 1;
cols := 1;
END LOOP;
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
ctr NUMBER(12);
cols NUMBER(2);
name_var1 VARCHAR2(2000);
name_var2 VARCHAR2(2000);
name_var3 VARCHAR2(2000);
name_var4 VARCHAR2(2000);
filename varchar2(100);
PROCEDURE OLEARG IS
args OLE2.OBJ_TYPE;
BEGIN
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,ctr); -- row value
ole2.add_arg(args,cols); -- column value
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell
ole2.destroy_arglist(args);
END;
BEGIN
filename :=GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','true');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args);
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'Sheet1');
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
ole2.destroy_arglist(args);
ctr := 2; --row number
cols := 1; -- column number
FIRST_RECORD;
LOOP
OLEARG;
name_var1 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var4 := ole2.get_num_property(cell,'Value'); --cell number value of the argument
IF ctr = 1 then
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
ELSE
NEXT_RECORD;
:tf1 := name_var1;
:tf2 := name_var2;
:tf3 := name_var3;
:tf4 := name_var4;
END IF;
EXIT WHEN length(name_var1) = 0 or length(name_var1) is null;
ctr := ctr + 1;
cols := 1;
END LOOP;
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;
7.6 https://www.experts-exchange.com/questions/21917477/reading-Excel-file-using-PL-SQL.html
PACKAGE excel
IS
-- global variables (constants)
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
procedure open_excel;
procedure close_excel;
procedure read_num_cell( p_row number, p_col number, p_value in out number );
procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 );
procedure open_workbook;
procedure close_workbook;
procedure open_excel_doc( p_pateka varchar2 );
procedure close_excel_doc;
procedure activate_sheet( p_redosled integer );
end;
PACKAGE BODY excel IS
procedure open_excel is
begin
application := ole2.create_obj('Excel.Application');
end;
procedure close_excel is
begin
if worksheet is not null then
ole2.release_obj(worksheet);
end if;
if worksheets is not null then
ole2.release_obj(worksheets);
end if;
if workbook is not null then
ole2.release_obj(workbook);
end if;
if workbooks is not null then
ole2.release_obj(workbooks);
end if;
if application is not null then
ole2.release_obj(application);
end if;
end;
procedure read_num_cell( p_row number, p_col number, p_value in out number ) is
args ole2.list_type;
cell ole2.obj_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_row );
ole2.add_arg(args, p_cel );
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
p_value := ole2.get_num_property(cell,'Value');
ole2.release_obj(cell);
end;
procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 ) is
args ole2.list_type;
cell ole2.obj_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_row );
ole2.add_arg(args, p_col );
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
p_value := ole2.get_char_property(cell,'Value');
ole2.release_obj(cell);
end;
procedure open_workbook is
begin
workbooks := ole2.get_obj_property(application,'Workbooks');
end;
procedure close_workbook is
begin
ole2.invoke(workbooks,'Close');
end;
procedure open_excel_doc( p_pateka varchar2 ) is
args ole2.list_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_pateka );
workbook := ole2.invoke_obj(workbooks, 'Open', args);
ole2.destroy_arglist(args);
worksheets := ole2.get_obj_property (workbook,'Worksheets');
end;
procedure close_excel_doc is
begin
ole2.invoke(workbook, 'Close');
end;
procedure activate_sheet( p_redosled integer ) is
args ole2.list_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_redosled );
worksheet := ole2.get_obj_property(worksheets,'Item', args);
ole2.destroy_arglist(args);
ole2.invoke( worksheet, 'Activate');
end;
end;
THEN attach the PLL to the form and IN THE MAIN PROCEDURE in the form:
declare
p_value number := 4;
begin
EXCEL.open_excel;
EXCEL.open_workbook;
EXCEL.open_excel_doc('c:\a.xls');
EXCEL.activate_sheet(1); -- 1 is the first worksheet from left
EXCEL.READ_NUM_CELL(1, 1, p_value);
-- do what is needed with the p_value
EXCEL.close_excel_doc;
EXCEL.close_workbook;
EXCEL.close_excel;
end;
-- global variables (constants)
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
procedure open_excel;
procedure close_excel;
procedure read_num_cell( p_row number, p_col number, p_value in out number );
procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 );
procedure open_workbook;
procedure close_workbook;
procedure open_excel_doc( p_pateka varchar2 );
procedure close_excel_doc;
procedure activate_sheet( p_redosled integer );
end;
PACKAGE BODY excel IS
procedure open_excel is
begin
application := ole2.create_obj('Excel.Application');
end;
procedure close_excel is
begin
if worksheet is not null then
ole2.release_obj(worksheet);
end if;
if worksheets is not null then
ole2.release_obj(worksheets);
end if;
if workbook is not null then
ole2.release_obj(workbook);
end if;
if workbooks is not null then
ole2.release_obj(workbooks);
end if;
if application is not null then
ole2.release_obj(application);
end if;
end;
procedure read_num_cell( p_row number, p_col number, p_value in out number ) is
args ole2.list_type;
cell ole2.obj_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_row );
ole2.add_arg(args, p_cel );
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
p_value := ole2.get_num_property(cell,'Value');
ole2.release_obj(cell);
end;
procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 ) is
args ole2.list_type;
cell ole2.obj_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_row );
ole2.add_arg(args, p_col );
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
p_value := ole2.get_char_property(cell,'Value');
ole2.release_obj(cell);
end;
procedure open_workbook is
begin
workbooks := ole2.get_obj_property(application,'Workbooks');
end;
procedure close_workbook is
begin
ole2.invoke(workbooks,'Close');
end;
procedure open_excel_doc( p_pateka varchar2 ) is
args ole2.list_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_pateka );
workbook := ole2.invoke_obj(workbooks, 'Open', args);
ole2.destroy_arglist(args);
worksheets := ole2.get_obj_property (workbook,'Worksheets');
end;
procedure close_excel_doc is
begin
ole2.invoke(workbook, 'Close');
end;
procedure activate_sheet( p_redosled integer ) is
args ole2.list_type;
begin
args := ole2.create_arglist;
ole2.add_arg(args, p_redosled );
worksheet := ole2.get_obj_property(worksheets,'Item', args);
ole2.destroy_arglist(args);
ole2.invoke( worksheet, 'Activate');
end;
end;
THEN attach the PLL to the form and IN THE MAIN PROCEDURE in the form:
declare
p_value number := 4;
begin
EXCEL.open_excel;
EXCEL.open_workbook;
EXCEL.open_excel_doc('c:\a.xls');
EXCEL.activate_sheet(1); -- 1 is the first worksheet from left
EXCEL.READ_NUM_CELL(1, 1, p_value);
-- do what is needed with the p_value
EXCEL.close_excel_doc;
EXCEL.close_workbook;
EXCEL.close_excel;
end;
7.7 http://saubbane.blogspot.com/2011/05/reading-and-writing-excel-file-xls-from.html
set serveroutput on;
drop package ORDExcelSB;
CREATE PACKAGE ORDExcelSB AS
/* Declare externally callable subprograms.
*/
FUNCTION
CreateExcelApplication(servername VARCHAR2) RETURN binary_integer;
FUNCTION OpenExcelFile(filename
VARCHAR2, sheetname VARCHAR2) RETURN binary_integer;
FUNCTION
CreateExcelWorkSheet(servername varchar2) return binary_integer;
FUNCTION InsertData(range
varchar2, data binary_integer, type varchar2) return binary_integer;
FUNCTION InsertDataReal(range
varchar2, data double precision, type varchar2) return
binary_integer;
FUNCTION GetDataNum(range
varchar2) return binary_integer;
FUNCTION GetDataStr(range
varchar2) return varchar2;
FUNCTION GetDataReal(range
varchar2) return double precision;
FUNCTION GetDataDate(range
varchar2) return date;
FUNCTION InsertData(range
varchar2, data varchar2, type varchar2) return binary_integer;
FUNCTION InsertData(range
varchar2, data Date, type varchar2) return binary_integer;
FUNCTION InsertChart(xpos
binary_integer, ypos binary_integer, width binary_integer,
height binary_integer, range
varchar2, type varchar2) return binary_integer;
FUNCTION SaveExcelFile(filename
varchar2) return binary_integer;
FUNCTION ExitExcel return
binary_integer;
END ORDExcelSB;
/
CREATE PACKAGE BODY ORDExcelSB AS
DummyToken binary_integer;
applicationToken
binary_integer:=-1;
WorkBooksToken
binary_integer:=-1;
WorkBookToken
binary_integer:=-1;
WorkSheetToken
binary_integer:=-1;
WorkSheetToken1
binary_integer:=-1;
RangeToken binary_integer:=-1;
ChartObjectToken
binary_integer:=-1;
ChartObject1 binary_integer:=-1;
Chart1Token binary_integer:=-1;
i binary_integer;
retNum binary_integer;
retReal double precision;
retStr varchar2(255);
retDate DATE;
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN
binary_integer IS
BEGIN
dbms_output.put_line('Creating
Excel application...');
i := OrdCOM.CreateObject('Excel.Application',
0,
servername,
applicationToken);
IF (i != 0) THEN
ORDCOM.GetLastError(error_src,
error_description,
error_helpfile,
error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking
Workbooks...');
i :=
ORDCOM.GetProperty(applicationToken,
'WorkBooks',
0,
WorkBooksToken);
IF (i != 0) THEN
ORDCOM.GetLastError(error_src,
error_description,
error_helpfile,
error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
RETURN i;
END CreateExcelApplication;
FUNCTION OpenExcelFile(filename
VARCHAR2, sheetname VARCHAR2)
RETURN binary_integer IS
BEGIN
dbms_output.put_line('Opening
Excel file ' || filename || ' ...');
ORDCOM.InitArg();
ORDCOM.SetArg(filename, 'BSTR');
i :=
ORDCOM.Invoke(WorkBooksToken, 'Open', 1, DummyToken);
IF (i != 0) THEN
ORDCOM.GetLastError(error_src,
error_description,
error_helpfile,
error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Opening
WorkBook');
i :=
ORDCOM.GetProperty(applicationToken,
'ActiveWorkbook',
0,
WorkBookToken);
IF (i != 0) THEN
ORDCOM.GetLastError(error_src,
error_description,
error_helpfile,
error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking
WorkSheets..');
i :=
ORDCOM.GetProperty(applicationToken,
'WorkSheets',
0,
WorkSheetToken1);
IF (i != 0) THEN
ORDCOM.GetLastError(error_src,
error_description,
error_helpfile,
error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking
WorkSheet');
ORDCOM.InitArg();
ORDCOM.SetArg(sheetname, 'BSTR');
i :=
ORDCOM.GetProperty(WorkBookToken, 'Sheets', 1, WorkSheetToken);
IF (i != 0) THEN
ORDCOM.GetLastError(error_src,
error_description,
error_helpfile,
error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Opened
');
RETURN i;
END OpenExcelFile;
/***************************************************************************
* Invoke the Excel Automation
Server and create a Workbook object as
* well as a worksheet object
***************************************************************************/
FUNCTION CreateExcelWorkSheet(servername varchar2) return
binary_integer IS
BEGIN
dbms_output.put_line('Creating
Excel application...');
i:=ORDCOM.CreateObject('Excel.Application', 0,
servername,applicationToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking
Workbooks...');
/*i:=ORDCOM.Invoke(applicationToken,
'WorkBooks',0, WorkBooksToken);*/
i:=ORDCOM.GetProperty(applicationToken, 'WorkBooks', 0,
WorkBooksToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking
Add to WorkBooks...');
ORDCOM.InitArg();
ORDCOM.SetArg(-4167,'I4');
i:=ORDCOM.Invoke(WorkBooksToken, 'Add', 1,
WorkBookToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking
WorkSheets..');
ORDCOM.InitArg();
ORDCOM.SetArg('Sheet
1','BSTR');
/* i:=ORDCOM.Invoke(applicationToken, 'WorkSheets', 1,
WorkSheetToken);*/
i:=ORDCOM.GetProperty(applicationToken, 'WorkSheets', 0, WorkSheetToken1);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.Invoke(WorkSheetToken1, 'Add', 0, WorkSheetToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
return i;
END CreateExcelWorkSheet;
/***************************************************************************
* Invoke the Range method to
obtain a range token. Then set the property value
* at the specified range to the
data required
***************************************************************************/
FUNCTION InsertData( range varchar2,
data binary_integer,
type varchar2)
RETURN binary_integer IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken,
'Range', 1, RangeToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.SetProperty(RangeToken,
'Value', data, type);
IF (i=0) THEN
i:=ORDCOM.SetProperty(RangeToken, 'ColumnWidth', 15, 'I2');
END IF;
i:=ORDCOM.DestroyObject(RangeToken);
RETURN i;
END InsertData;
/***************************************************************************
* Invoke the Range method to
obtain a range token. Then set the property value
* at the specified range to the
data required
***************************************************************************/
FUNCTION GetDataNum( range varchar2)
RETURN binary_integer IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1,
RangeToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.GetProperty(RangeToken,
'Value', 0, retNum);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.DestroyObject(RangeToken);
RETURN retNum;
END GetDataNum;
FUNCTION GetDataReal( range varchar2)
RETURN double
precision IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.GetProperty(RangeToken,
'Value', 0, retReal);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.DestroyObject(RangeToken);
RETURN retReal;
END GetDataReal;
FUNCTION GetDataStr( range varchar2)
RETURN varchar2 IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.GetProperty(RangeToken,
'Value', 0, retStr);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.DestroyObject(RangeToken);
RETURN retStr;
END GetDataStr;
FUNCTION GetDataDate( range varchar2)
RETURN Date IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1,
RangeToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.GetProperty(RangeToken,
'Value', 0, retDate);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.DestroyObject(RangeToken);
RETURN retDate;
END GetDataDate;
FUNCTION InsertData( range varchar2,
data DATE,
type varchar2)
RETURN binary_integer IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken,
'Range', 1, RangeToken);
i:=ORDCOM.SetProperty(RangeToken,
'Value', data, type);
i:=ORDCOM.DestroyObject(RangeToken);
RETURN i;
END InsertData;
FUNCTION InsertDataReal( range varchar2,
data double
precision,
type varchar2)
RETURN binary_integer IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1,
RangeToken);
i:=ORDCOM.SetProperty(RangeToken,
'Value', data, type);
i:=ORDCOM.DestroyObject(RangeToken);
RETURN i;
END InsertDataReal;
FUNCTION InsertData( range varchar2,
data varchar2,
type varchar2)
RETURN binary_integer IS
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1,
RangeToken);
i:=ORDCOM.SetProperty(RangeToken,
'Value', data, type);
i:=ORDCOM.DestroyObject(RangeToken);
RETURN i;
END InsertData;
/******************************************************************************
* Insert a chart at the x and y
position of the spreadsheet with the desired
* height and width. Then also
uses the ChartWizard to draw the graph with data
* in a specified range area with
a specified charting type.
*******************************************************************************/
FUNCTION InsertChart(xpos binary_integer, ypos binary_integer,
width binary_integer, height
binary_integer,
range varchar2, type
varchar2) RETURN binary_integer IS
charttype binary_integer:= -4099;
BEGIN
ORDCOM.InitArg();
i:=ORDCOM.GetProperty(WorkSheetToken, 'ChartObjects', 0,
ChartObjectToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
ORDCOM.InitArg();
ORDCOM.SetArg(xpos,'I2');
ORDCOM.SetArg(ypos,'I2');
ORDCOM.SetArg(width,'I2');
ORDCOM.SetArg(height,'I2');
i:=ORDCOM.Invoke(ChartObjectToken, 'Add', 4,
ChartObject1);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.GetProperty(ChartObject1, 'Chart',
0,Chart1Token);
ORDCOM.InitArg();
ORDCOM.SetArg(range, 'BSTR');
i:=ORDCOM.GetProperty(WorkSheetToken,'Range', 1,
RangeToken);
ORDCOM.InitArg();
ORDCOM.SetArg(RangeToken, 'DISPATCH');
IF type='xlPie' THEN
charttype := -4102;
ELSIF type='xl3DBar' THEN
charttype := -4099;
ELSIF type='xlBar' THEN
charttype := 2;
ELSIF type='xl3dLine' THEN
charttype:= -4101;
END IF;
ORDCOM.SetArg(charttype,'I4');
i:=ORDCOM.Invoke(Chart1Token,'ChartWizard', 2,
DummyToken);
i:=ORDCOM.DestroyObject(RangeToken);
i:=ORDCOM.DestroyObject(ChartObjectToken);
i:=ORDCOM.DestroyObject(ChartObject1);
i:=ORDCOM.DestroyObject(Chart1Token);
RETURN i;
END InsertChart;
/******************************************************************************
* Save the Excel File. WARNING:
Do not specify a filename that already exist
* since there is no graphical
context, Oracle would not be able to pop
* out a warning message for
existing file. This causes Excel to hang
*******************************************************************************/
FUNCTION SaveExcelFile(filename varchar2) return
binary_integer IS
BEGIN
dbms_output.put_line('Saving
Excel file...');
ORDCOM.InitArg();
ORDCOM.SetArg(filename,'BSTR');
i:=ORDCOM.Invoke(WorkBookToken, 'SaveAs', 1,
DummyToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
RETURN i;
END SaveExcelFile;
/******************************************************************************
* Close the Excel spreadsheet and
exit from it
******************************************************************************/
FUNCTION ExitExcel return binary_integer is
BEGIN
dbms_output.put_line('Closing
workbook and quitting...');
ORDCOM.InitArg();
ORDCOM.InitArg();
ORDCOM.SetArg(FALSE,'BOOL');
dbms_output.put_line('Closing
workbook...');
i:=ORDCOM.Invoke(WorkBookToken, 'Close', 0, DummyToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.DestroyObject(WorkBookToken);
ORDCOM.InitArg();
dbms_output.put_line('Closing
workbooks...');
i:=ORDCOM.Invoke(WorkBooksToken, 'Close', 0,
DummyToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.DestroyObject(WorkBooksToken);
i:=ORDCOM.Invoke(applicationToken, 'Quit', 0,
DummyToken);
IF (i!=0) THEN
ORDCOM.GetLastError(error_src,
error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
i:=ORDCOM.DestroyObject(WorkSheetToken);
i:=ORDCOM.DestroyObject(WorkSheetToken1);
i:=ORDCOM.DestroyObject(applicationToken);
i:=ORDCOM.DestroyObject(ChartObjectToken);
i:=ORDCOM.DestroyObject(Chart1Token);
i:=ORDCOM.DestroyObject(ChartObject1);
i:=ORDCOM.DestroyObject(dummyToken);
RETURN i;
END ExitExcel;
END ORDExcelSB;
/
You can use
this code to read excel file (.xls)
SQL> SET SERVEROUT ON
SQL> DECLARE
2
3 v_Name varchar2(90);
4 v_SlNo varchar2(100);
5 v_Job varchar2(200);
6 v_Dept varchar2(100);
7 v_recon_remark varchar2(50);
8 v_sal_amt_usd number;
9 v_Bonus_amt_usd number;
10
11 result INTEGER;
12
13 i
binary_integer;
14 filename varchar2(255);
15
16 BEGIN
17
18 filename := 'C:\Example.xls';
19
20 result :=
ORDExcelSB.CreateExcelApplication('');
21 result :=
ORDExcelSB.OpenExcelFile(filename, 'Sheet1');
22
23 /* Excluding the header row and reading the
first 5 row */
24 FOR n in 2 .. 5 LOOP
25
26 v_Name := ORDExcelSB.GetDataStr('A' || n);
27 v_SlNo := ORDExcelSB.GetDataReal('B' || n);
28 v_Job := ORDExcelSB.GetDataStr('C' || n);
29 v_Dept := ORDExcelSB.GetDataStr('D' || n);
30 v_sal_amt_usd := ORDExcelSB.GetDataNum('E' || n);
31 v_Bonus_amt_usd := ORDExcelSB.GetDataNum('F' || n);
32
33 dbms_output.put_line(v_Name || ' ' || v_SlNo || ' ' || v_Job || ' ' ||
34 v_Dept || ' ' || v_sal_amt_usd || ' ' ||
35 v_Bonus_amt_usd);
36
37 END LOOP;
38
39 result := ORDExcelSB.ExitExcel();
40 EXCEPTION
41 WHEN OTHERS THEN
42 result := ORDExcelSB.ExitExcel();
43 RAISE;
44 END;
45 /
Creating Excel application...
Invoking Workbooks...
Opening Excel file C:\Example.xls ...
Opening WorkBook
Invoking WorkSheets..
Invoking WorkSheet
Opened
Saubhik Banerjee 706090 IT Specialist
GBS 100 10
Partha S Mohanty 706091 Pogrmmer
APPS 70 20
Partha Sarkar 889300 Condultant
FIN 200 30
Useless 98009 PM
PM 900 90
Closing workbook and quitting...
Closing workbook...
Closing workbooks...
PL/SQL procedure successfully completed.
SQL>
You can use this code to write to excel
file (.xls)
DECLARE
CURSOR c1 IS
SELECT empno, ename, dname, sal,
hiredate
FROM emp e, dept d
WHERE e.deptno = d.deptno;
error_message varchar2(1200);
n binary_integer:=2;
i binary_integer;
filename varchar2(255);
cellIndex varchar2(40);
cellValue varchar2(40);
cellColumn varchar2(10);
returnedTime varchar2(20);
currencyvalue double precision;
datevalue DATE;
empno binary_integer;
looptext varchar2(20);
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
begin
filename:='c:\example2.xls';
i:=ORDExcel.CreateExcelWorkSheet('');
i:=ORDExcel.InsertData('A1', 'EmpNo', 'BSTR');
i:=ORDExcel.InsertData('B1', 'Name', 'BSTR');
i:=ORDExcel.InsertData('C1', 'Dept', 'BSTR');
i:=ORDExcel.InsertData('D1', 'Salary', 'BSTR');
i:=ORDExcel.InsertData('E1', 'HireDate', 'BSTR');
For c1_rec IN c1 LOOP
cellColumn:=TO_CHAR(n);
cellIndex:=CONCAT('A',cellColumn);
cellValue:=TO_CHAR(c1_rec.empno);
empno:=cellValue;
i:=ORDExcel.InsertData(cellIndex, empno, 'I2');
cellIndex:=CONCAT('B',cellColumn);
cellValue:=c1_rec.ename;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
cellIndex:=CONCAT('C',cellColumn);
cellValue:=c1_rec.dname;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
cellIndex:=CONCAT('D',cellColumn);
cellValue:=c1_rec.sal;
currencyValue:=cellValue;
i:=ORDExcel.InsertData(cellIndex, currencyValue, 'CY');
cellIndex:=CONCAT('E',cellColumn);
dateValue:=c1_rec.hiredate;
i:=ORDExcel.InsertData(cellIndex, dateValue, 'DATE');
n:=n+1;
END LOOP;
i:=ORDExcel.SaveExcelFile(filename);
i:=ORDExcel.ExitExcel();
EXCEPTION
WHEN OTHERS THEN
i:=ORDExcel.ExitExcel();
RAISE;
END;
7.8 https://chrisonoracle.wordpress.com/2013/11/13/read-excel-file-in-plsql-using-java-in-the-db/
7.9
7.10
8 ODBC from Excel to oracle db
9 How to parse a CSV file using PL/SQL
select *
from
table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))
insert into my_table (first_column,
second_column)
select c001, c002
from
table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))
select *
from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))
where c002 = 'Chevy'
create table x_dump(clob_value
clob, dump_date
date default sysdate, dump_id
number);declare l_clob
clob; cursor l_cursor is select csv.* from x_dump d,
table(csv_util_pkg.clob_to_csv(d.clob_value)) csv where d.dump_id = 1;begin insert into x_dump (clob_value, dump_id) values (l_clob, 1); commit; dbms_lob.freetemporary
(l_clob); for l_rec in l_cursor loop dbms_output.put_line
('row ' ||
l_rec.line_number || ', col 1 = ' || l_rec.c001); end loop;end;
declare
l_array t_str_array;
l_val varchar2(4000);
begin
l_array := csv_util_pkg.csv_to_array
('10,SMITH,CLERK,"1200,50"');
for
i in l_array.first .. l_array.last loop
dbms_output.put_line('value ' || i || ' = ' || l_array(i));
end
loop;
--
should output SMITH
l_val
:= csv_util_pkg.get_array_value(l_array, 2);
dbms_output.put_line('value = ' || l_val);
--
should give an error message stating that there is no column called DEPTNO
because the array does not contain seven elements
--
leave the column name out to fail silently and return NULL instead of raising
exception
l_val := csv_util_pkg.get_array_value(l_array, 7, 'DEPTNO');
dbms_output.put_line('value = ' || l_val);
end;
create type
t_str_array as table of varchar2(4000);
/
create type t_csv_line
as object (
line_number number,
line_raw
varchar2(4000),
c001
varchar2(4000),
c002
varchar2(4000),
c003
varchar2(4000),
c004
varchar2(4000),
c005
varchar2(4000),
c006
varchar2(4000),
c007
varchar2(4000),
c008
varchar2(4000),
c009
varchar2(4000),
c010
varchar2(4000),
c011
varchar2(4000),
c012
varchar2(4000),
c013
varchar2(4000),
c014
varchar2(4000),
c015
varchar2(4000),
c016
varchar2(4000),
c017
varchar2(4000),
c018
varchar2(4000),
c019
varchar2(4000),
c020
varchar2(4000)
);
/
create type
t_csv_tab as table of t_csv_line;
/
Bonus: Exporting CSV data
select csv_util_pkg.array_to_csv
(t_str_array(company_id, company_name, company_type)) as the_csv_data
from company
order by company_name
read from url to table
declare
l_clob clob;
cursor l_cursor
is
select csv.*
from x_dump d, table(csv_util_pkg.clob_to_csv(d.clob_value)) csv
where d.id = 1;
l_array
t_str_array;
l_val varchar2(4000);
begin
l_clob := httpuritype('http://192.168.50.148:7777/i/tarmis/rpts/test.csv').getclob();
insert into x_dump (clob_value, id) values (l_clob, 1);
commit;
dbms_lob.freetemporary
(l_clob);
for l_rec in l_cursor loop
dbms_output.put_line
('row ' || l_rec.line_number || ', col 1 = ' || l_rec.c001);
end loop;
end;
10 How to export from PL/SQL – not tested
declare
BACK varchar2(556);
begin
BACK:=GET_FILE_NAME('E:\project\backup','Backup'|| To_Char(Sysdate,'ddmmyyyyhhmmss'),'NULL','Save As',Save_File);
--READ_IMAGE_FILE(BACK,'JPEG','CBLOCK.CIMAGE');
--:t:=BACK;
If bb Is Not Null Then
Host('Exp Work/info File='||bb||'.dmp');
Else
Null;
End If;
end;
BACK varchar2(556);
begin
BACK:=GET_FILE_NAME('E:\project\backup','Backup'|| To_Char(Sysdate,'ddmmyyyyhhmmss'),'NULL','Save As',Save_File);
--READ_IMAGE_FILE(BACK,'JPEG','CBLOCK.CIMAGE');
--:t:=BACK;
If bb Is Not Null Then
Host('Exp Work/info File='||bb||'.dmp');
Else
Null;
End If;
end;
11 SMS messaging from Oracle
Hello,
I was required to build a banking application. I wanted to add SMS functionality to it in case there is a missed call. An SMS message is sent to the phone number of the missed call with details regarding the credit. I solved the case with Oracle database and an SMS gateway, called Ozeki. In case you also would like to build a similar solution and add SMS messaging to Oracle then this is a good place to start: http://www.ozekisms.com/index.php?owpn=166 - How to send SMS messages from Oracle. I hope this solution will be useful for you.
I was required to build a banking application. I wanted to add SMS functionality to it in case there is a missed call. An SMS message is sent to the phone number of the missed call with details regarding the credit. I solved the case with Oracle database and an SMS gateway, called Ozeki. In case you also would like to build a similar solution and add SMS messaging to Oracle then this is a good place to start: http://www.ozekisms.com/index.php?owpn=166 - How to send SMS messages from Oracle. I hope this solution will be useful for you.
12 Oracle UTL_ENCODE
13
How
to encrypt my password field in oracle
http://thinktibits.blogspot.com/2014/07/create-sha-256-hash-oracle-pl-sql-example.html
For best
practice of password storage see https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet
CREATE OR REPLACE AND RESOLVE JAVA SOURCE
NAMED SDS.SHA2 as
import java.security.MessageDigest;
import oracle.sql.*;
public class sha2
{
public static oracle.sql.RAW get_digest( String p_string, int p_bits )
throws Exception
{
return RAW.newRAW(MessageDigest.getInstance( "SHA-" + p_bits
).digest((p_string == null ? "" : p_string).getBytes(
"UTF-8")));
}
}
/
CREATE OR REPLACE FUNCTION SDS.sha2(p_string
IN VARCHAR2, p_bits IN NUMBER)
RETURN RAW
AS
LANGUAGE JAVA
NAME 'sha2.get_digest( java.lang.String, int ) return oracle.sql.RAW';
/
SELECT
sha2('hello world',256) FROM DUAL
union all
SELECT sha2('hello world',384) FROM DUAL
union all
SELECT sha2('hello world',512) FROM DUAL
union all
SELECT sha2('hello world',384) FROM DUAL
union all
SELECT sha2('hello world',512) FROM DUAL
14
How
to wrap pl_sql code
wrap iname=[file] oname=[file]
No comments:
Post a Comment