Search a value within all tables

DECLARE
match_count INTEGER;
BEGIN
FOR t IN (SELECT table_name, column_name FROM user_tab_cols) LOOP

EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE instr('||t.column_name||' , :1)>0'
INTO match_count
USING '01919323232';

IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;

END LOOP;

END;

Oracle sysdate fixed and back to Os date

ALTER SYSTEM SET fixed_date = '2011-01-01-10:00:00';

ALTER SYSTEM RESET fixed_date SCOPE=SPFILE SID='*';

Oracle sql (Cumulative SUM / Running Total)

1. At first create a table: cumulative_sum 

create table cumulative_sum 
(
instr_date date,
instr_id  number,
amount number
);

2. Insert some Data:

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate-1, 1, 100);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate-1, 2, 100);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate-1, 5, 300);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate-1, 3, 200);



insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate+1, 100, 50);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate+1, 200, 75);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate+1, 400, 60);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate+1, 300,10);




insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate, 1, 100);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate, 2, 100);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate, 3, 600);

insert into cumulative_sum(instr_date, instr_id, amount)
values(sysdate, 5, 700);


commit;

3. Select data from table :

SELECT * FROM cumulative_sum;

4. Select data with running total from the table :

SELECT a.*, sum(amount) over (order by rowid range between unbounded preceding and current row) 
running_total
 FROM cumulative_sum a;

5. Select data from the table order by instr_date, instr_id:

SELECT * FROM cumulative_sum order by instr_date, instr_id;

6. Select data with running total from the table order by instr_date, instr_id:

SELECT a.*, sum(amount) over (order by instr_date, instr_id range between unbounded preceding and current row) 
running_total
 FROM cumulative_sum a order by instr_date, instr_id;

Find data in a table list

declare

v_sql varchar2(500);

v_cnt number;

begin

for i in (select distinct table_name from
dba_tab_cols where owner='UBSGOLD' and column_name like 'PRODUCT_CODE')
loop


v_cnt := 0;
v_sql := 'select count(*) into :cnt from ' || i.table_name ||
' where product_code in (''ACSW'', ''ATMS'')' ;

execute immediate v_sql into v_cnt;

if v_cnt > 0 then

dbms_output.put_line(i.table_name);
end if;

end loop;
end;

as_xlsx package for create excel file (xlsx)

create or replace package as_xlsxis

/**********************************************
**
** Author: Anton Scheffer
** Date: 19-02-2011
** Website: http://technology.amis.nl/blog
** See also: http://technology.amis.nl/blog/?p=10995
**
** Changelog:
** Date: 21-02-2011
** Added Aligment, horizontal, vertical, wrapText
** Date: 06-03-2011
** Added Comments, MergeCells, fixed bug for dependency on NLS-settings
** Date: 16-03-2011
** Added bold and italic fonts
** Date: 22-03-2011
** Fixed issue with timezone's set to a region(name) instead of a offset
** Date: 08-04-2011
** Fixed issue with XML-escaping from text
**
******************************************** */
--
type tp_alignment is record
( vertical varchar2(11)
, horizontal varchar2(16)
, wrapText boolean
);
--
procedure clear_workbook;
--
procedure new_sheet( p_sheetname varchar2 := null );
--
function OraFmt2Excel( p_format varchar2 := null )
return varchar2;
--
function get_numFmt( p_format varchar2 := null )
return pls_integer;
--
function get_font
( p_name varchar2
, p_family pls_integer := 2
, p_fontsize number := 11
, p_theme pls_integer := 1
, p_underline boolean := false
, p_italic boolean := false
, p_bold boolean := false
)
return pls_integer;
--
function get_fill
( p_patternType varchar2
, p_fgRGB varchar2 := null -- this is a hex ALPHA Red Green Blue value
)
return pls_integer;
--
function get_border
( p_top varchar2 := 'thin'
, p_bottom varchar2 := 'thin'
, p_left varchar2 := 'thin'
, p_right varchar2 := 'thin'
)
/*
none
thin
medium
dashed
dotted
thick
double
hair
mediumDashed
dashDot
mediumDashDot
dashDotDot
mediumDashDotDot
slantDashDot
*/
return pls_integer;
--
function get_alignment
( p_vertical varchar2 := null
, p_horizontal varchar2 := null
, p_wrapText boolean := null
)
/* horizontal
center
centerContinuous
distributed
fill
general
justify
left
right
*/
/* vertical
bottom
center
distributed
justify
top
*/
return tp_alignment;
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value number
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value varchar2
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value date
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure hyperlink
( p_col pls_integer
, p_row pls_integer
, p_url varchar2
, p_value varchar2 := null
, p_sheet pls_integer := null
);
--
procedure comment
( p_col pls_integer
, p_row pls_integer
, p_text varchar2
, p_author varchar2 := null
, p_width pls_integer := 150 -- pixels
, p_height pls_integer := 100 -- pixels
, p_sheet pls_integer := null
);
--
procedure mergecells
( p_tl_col pls_integer -- top left
, p_tl_row pls_integer
, p_br_col pls_integer -- bottom right
, p_br_row pls_integer
, p_sheet pls_integer := null
);
--
procedure set_column_width
( p_col pls_integer
, p_width number
, p_sheet pls_integer := null
);
--
procedure set_column
( p_col pls_integer
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure set_row
( p_row pls_integer
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
);
--
procedure freeze_rows
( p_nr_rows pls_integer := 1
, p_sheet pls_integer := null
);
--
procedure freeze_cols
( p_nr_cols pls_integer := 1
, p_sheet pls_integer := null
);
--
procedure set_autofilter
( p_column_start pls_integer := null
, p_column_end pls_integer := null
, p_row_start pls_integer := null
, p_row_end pls_integer := null
, p_sheet pls_integer := null
);
--
function finish
return blob;
--
procedure save
( p_directory varchar2
, p_filename varchar2
);
--
procedure query2sheet
( p_sql varchar2
, p_column_headers boolean := true
, p_directory varchar2 := null
, p_filename varchar2 := null
, p_sheet pls_integer := null
);
--
/* Example
begin
as_xlsx.new_sheet;
as_xlsx.cell( 5, 1, 5 );
as_xlsx.cell( 3, 1, 3 );
as_xlsx.cell( 2, 2, 45 );
as_xlsx.cell( 3, 2, 'Anton Scheffer', p_alignment => as_xlsx.get_alignment( p_wraptext => true ) );
as_xlsx.cell( 1, 4, sysdate );
as_xlsx.cell( 2, 4, sysdate, p_numFmtId => as_xlsx.get_numFmt( 'dd/mm/yyyy h:mm' ) );
as_xlsx.cell( 3, 4, sysdate, p_numFmtId => as_xlsx.get_numFmt( as_xlsx.orafmt2excel( 'dd/mon/yyyy' ) ) );
as_xlsx.cell( 5, 5, 75, p_borderId => as_xlsx.get_border( 'double', 'double', 'double', 'double' ) );
as_xlsx.cell( 2, 3, 33 );
as_xlsx.hyperlink( 1, 6, 'http://www.amis.nl', 'Amis site' );
as_xlsx.cell( 1, 7, 'Some merged cells', p_alignment => as_xlsx.get_alignment( p_horizontal => 'center' ) );
as_xlsx.mergecells( 1, 7, 3, 7 );
for i in 1 .. 5
loop
as_xlsx.comment( 3, i + 3, 'Row ' || (i+3), 'Anton' );
end loop;
as_xlsx.new_sheet;
as_xlsx.set_row( 1, p_fillId => as_xlsx.get_fill( 'solid', 'FFFF0000' ) ) ;
for i in 1 .. 5
loop
as_xlsx.cell( 1, i, i );
as_xlsx.cell( 2, i, i * 3 );
as_xlsx.cell( 3, i, 'x ' || i * 3 );
end loop;
as_xlsx.query2sheet( 'select rownum, x.*
, case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
, case when mod( rownum, 2 ) = 1 then ''demo '' || rownum end demo2 from dual x connect by rownum <= 5' );
as_xlsx.save( 'MY_DIR', 'my.xlsx' );
end;
*/

end;
/

------------------------------------------------------------------------

CREATE OR REPLACE package body as_xlsx
is

type tp_XF_fmt is record
( numFmtId pls_integer
, fontId pls_integer
, fillId pls_integer
, borderId pls_integer
, alignment tp_alignment
);
type tp_col_fmts is table of tp_XF_fmt index by pls_integer;
type tp_row_fmts is table of tp_XF_fmt index by pls_integer;
type tp_widths is table of number index by pls_integer;
type tp_strings is table of varchar2(32767) index by pls_integer;
type tp_cell is record
( value number
, style varchar2(50)
);
type tp_cells is table of tp_cell index by pls_integer;
type tp_rows is table of tp_cells index by pls_integer;
type tp_autofilter is record
( column_start pls_integer
, column_end pls_integer
, row_start pls_integer
, row_end pls_integer
);
type tp_autofilters is table of tp_autofilter index by pls_integer;
type tp_hyperlink is record
( cell varchar2(10)
, url varchar2(1000)
);
type tp_hyperlinks is table of tp_hyperlink index by pls_integer;
subtype tp_author is varchar2(32767);
type tp_authors is table of pls_integer index by tp_author;
authors tp_authors;
type tp_comment is record
( text varchar2(32767)
, author tp_author
, row pls_integer
, column pls_integer
, width pls_integer
, height pls_integer
);
type tp_comments is table of tp_comment index by pls_integer;
type tp_mergecells is table of varchar2(21) index by pls_integer;
type tp_sheet is record
( rows tp_rows
, widths tp_widths
, name varchar2(100)
, freeze_rows pls_integer
, freeze_cols pls_integer
, autofilters tp_autofilters
, hyperlinks tp_hyperlinks
, col_fmts tp_col_fmts
, row_fmts tp_row_fmts
, comments tp_comments
, mergecells tp_mergecells
);
type tp_sheets is table of tp_sheet index by pls_integer;
type tp_numFmt is record
( numFmtId pls_integer
, formatCode varchar2(100)
);
type tp_numFmts is table of tp_numFmt index by pls_integer;
type tp_fill is record
( patternType varchar2(30)
, fgRGB varchar2(8)
);
type tp_fills is table of tp_fill index by pls_integer;
type tp_cellXfs is table of tp_xf_fmt index by pls_integer;
type tp_font is record
( name varchar2(100)
, family pls_integer
, fontsize number
, theme pls_integer
, underline boolean
, italic boolean
, bold boolean
);
type tp_fonts is table of tp_font index by pls_integer;
type tp_border is record
( top varchar2(17)
, bottom varchar2(17)
, left varchar2(17)
, right varchar2(17)
);
type tp_borders is table of tp_border index by pls_integer;
type tp_numFmtIndexes is table of pls_integer index by pls_integer;
type tp_book is record
( sheets tp_sheets
, strings tp_strings
, fonts tp_fonts
, fills tp_fills
, borders tp_borders
, numFmts tp_numFmts
, cellXfs tp_cellXfs
, numFmtIndexes tp_numFmtIndexes
);
workbook tp_book;
--
procedure blob2file
( p_blob blob
, p_directory varchar2 := 'MY_DIR'
, p_filename varchar2 := 'my.xlsx'
)
is
t_fh utl_file.file_type;
t_len pls_integer := 32767;
begin
t_fh := utl_file.fopen( p_directory
, p_filename
, 'wb'
);
for i in 0 .. trunc( ( dbms_lob.getlength( p_blob ) - 1 ) / t_len )
loop
utl_file.put_raw( t_fh
, dbms_lob.substr( p_blob
, t_len
, i * t_len + 1
)
);
end loop;
utl_file.fclose( t_fh );
end;
--
function little_endian( p_big number, p_bytes pls_integer := 4 )
return raw
is
begin
return utl_raw.substr( utl_raw.cast_from_binary_integer( p_big, utl_raw.little_endian ), 1, p_bytes );
end;
--
procedure add1file
( p_zipped_blob in out nocopy blob
, p_name varchar2
, p_content blob
)
is
t_now date;
t_blob blob;
t_clen integer;
begin
t_now := sysdate;
t_blob := utl_compress.lz_compress( p_content );
t_clen := dbms_lob.getlength( t_blob );
if p_zipped_blob is null
then
dbms_lob.createtemporary( p_zipped_blob, true );
end if;
dbms_lob.append( p_zipped_blob
, utl_raw.concat( hextoraw( '504B0304' ) -- Local file header signature
, hextoraw( '1400' ) -- version 2.0
, hextoraw( '0000' ) -- no General purpose bits
, hextoraw( '0800' ) -- deflate
, little_endian( to_number( to_char( t_now, 'ss' ) ) / 2
+ to_number( to_char( t_now, 'mi' ) ) * 32
+ to_number( to_char( t_now, 'hh24' ) ) * 2048
, 2
) -- File last modification time
, little_endian( to_number( to_char( t_now, 'dd' ) )
+ to_number( to_char( t_now, 'mm' ) ) * 32
+ ( to_number( to_char( t_now, 'yyyy' ) ) - 1980 ) * 512
, 2
) -- File last modification date
, dbms_lob.substr( t_blob, 4, t_clen - 7 ) -- CRC-32
, little_endian( t_clen - 18 ) -- compressed size
, little_endian( dbms_lob.getlength( p_content ) ) -- uncompressed size
, little_endian( length( p_name ), 2 ) -- File name length
, hextoraw( '0000' ) -- Extra field length
, utl_raw.cast_to_raw( p_name ) -- File name
)
);
dbms_lob.copy( p_zipped_blob, t_blob, t_clen - 18, dbms_lob.getlength( p_zipped_blob ) + 1, 11 ); -- compressed content
dbms_lob.freetemporary( t_blob );
end;
--
procedure finish_zip( p_zipped_blob in out nocopy blob )
is
t_cnt pls_integer := 0;
t_offs integer;
t_offs_dir_header integer;
t_offs_end_header integer;
t_comment raw(32767) := utl_raw.cast_to_raw( 'Zip-implementation by Anton Scheffer' );
begin
t_offs_dir_header := dbms_lob.getlength( p_zipped_blob );
t_offs := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), 1 );
while t_offs > 0
loop
t_cnt := t_cnt + 1;
dbms_lob.append( p_zipped_blob
, utl_raw.concat( hextoraw( '504B0102' ) -- Central directory file header signature
, hextoraw( '1400' ) -- version 2.0
, dbms_lob.substr( p_zipped_blob, 26, t_offs + 4 )
, hextoraw( '0000' ) -- File comment length
, hextoraw( '0000' ) -- Disk number where file starts
, hextoraw( '0100' ) -- Internal file attributes
, hextoraw( '2000B681' ) -- External file attributes
, little_endian( t_offs - 1 ) -- Relative offset of local file header
, dbms_lob.substr( p_zipped_blob
, utl_raw.cast_to_binary_integer( dbms_lob.substr( p_zipped_blob, 2, t_offs + 26 ), utl_raw.little_endian )
, t_offs + 30
) -- File name
)
);
t_offs := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), t_offs + 32 );
end loop;
t_offs_end_header := dbms_lob.getlength( p_zipped_blob );
dbms_lob.append( p_zipped_blob
, utl_raw.concat( hextoraw( '504B0506' ) -- End of central directory signature
, hextoraw( '0000' ) -- Number of this disk
, hextoraw( '0000' ) -- Disk where central directory starts
, little_endian( t_cnt, 2 ) -- Number of central directory records on this disk
, little_endian( t_cnt, 2 ) -- Total number of central directory records
, little_endian( t_offs_end_header - t_offs_dir_header ) -- Size of central directory
, little_endian( t_offs_dir_header ) -- Relative offset of local file header
, little_endian( nvl( utl_raw.length( t_comment ), 0 ), 2 ) -- ZIP file comment length
, t_comment
)
);
end;
--
function alfan_col( p_col pls_integer )
return varchar2
is
begin
return case
when p_col > 702 then chr( 64 + trunc( ( p_col - 27 ) / 676 ) ) || chr( 65 + mod( trunc( ( p_col - 1 ) / 26 ) - 1, 26 ) ) || chr( 65 + mod( p_col - 1, 26 ) )
when p_col > 26 then chr( 64 + trunc( ( p_col - 1 ) / 26 ) ) || chr( 65 + mod( p_col - 1, 26 ) )
else chr( 64 + p_col )
end;
end;
--
function col_alfan( p_col varchar2 )
return pls_integer
is
begin
return ascii( substr( p_col, -1 ) ) - 64
+ nvl( ( ascii( substr( p_col, -2, 1 ) ) - 64 ) * 26, 0 )
+ nvl( ( ascii( substr( p_col, -3, 1 ) ) - 64 ) * 676, 0 );
end;
--
procedure clear_workbook
is
t_row_ind pls_integer;
begin
for s in 1 .. workbook.sheets.count()
loop
t_row_ind := workbook.sheets( s ).rows.first();
while t_row_ind is not null
loop
workbook.sheets( s ).rows( t_row_ind ).delete();
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
workbook.sheets( s ).rows.delete();
workbook.sheets( s ).widths.delete();
workbook.sheets( s ).autofilters.delete();
workbook.sheets( s ).hyperlinks.delete();
workbook.sheets( s ).col_fmts.delete();
workbook.sheets( s ).row_fmts.delete();
workbook.sheets( s ).comments.delete();
workbook.sheets( s ).mergecells.delete();
end loop;
workbook.strings.delete();
workbook.fonts.delete();
workbook.fills.delete();
workbook.borders.delete();
workbook.numFmts.delete();
workbook.cellXfs.delete();
workbook := null;
end;
--
procedure new_sheet( p_sheetname varchar2 := null )
is
t_nr pls_integer := workbook.sheets.count() + 1;
t_ind pls_integer;
begin
workbook.sheets( t_nr ).name := nvl( dbms_xmlgen.convert( translate( p_sheetname, 'a/\[]*:?', 'a' ) ), 'Sheet' || t_nr );
if workbook.fonts.count() = 0
then
t_ind := get_font( 'Calibri' );
end if;
if workbook.fills.count() = 0
then
t_ind := get_fill( 'none' );
t_ind := get_fill( 'gray125' );
end if;
if workbook.borders.count() = 0
then
t_ind := get_border( '', '', '', '' );
end if;
end;
--
procedure set_col_width
( p_sheet pls_integer
, p_col pls_integer
, p_format varchar2
)
is
t_width number;
t_nr_chr pls_integer;
begin
if p_format is null
then
return;
end if;
if instr( p_format, ';' ) > 0
then
t_nr_chr := length( translate( substr( p_format, 1, instr( p_format, ';' ) - 1 ), 'a\"', 'a' ) );
else
t_nr_chr := length( translate( p_format, 'a\"', 'a' ) );
end if;
t_width := trunc( ( t_nr_chr * 7 + 5 ) / 7 * 256 ) / 256; -- assume default 11 point Calibri
if workbook.sheets( p_sheet ).widths.exists( p_col )
then
workbook.sheets( p_sheet ).widths( p_col ) :=
greatest( workbook.sheets( p_sheet ).widths( p_col )
, t_width
);
else
workbook.sheets( p_sheet ).widths( p_col ) := greatest( t_width, 8.43 );
end if;
end;
--
function OraFmt2Excel( p_format varchar2 := null )
return varchar2
is
t_format varchar2(1000) := substr( p_format, 1, 1000 );
begin
t_format := replace( replace( t_format, 'hh24', 'hh' ), 'hh12', 'hh' );
t_format := replace( t_format, 'mi', 'mm' );
t_format := replace( replace( replace( t_format, 'AM', '~~' ), 'PM', '~~' ), '~~', 'AM/PM' );
t_format := replace( replace( replace( t_format, 'am', '~~' ), 'pm', '~~' ), '~~', 'AM/PM' );
t_format := replace( replace( t_format, 'day', 'DAY' ), 'DAY', 'dddd' );
t_format := replace( replace( t_format, 'dy', 'DY' ), 'DAY', 'ddd' );
t_format := replace( replace( t_format, 'RR', 'RR' ), 'RR', 'YY' );
t_format := replace( replace( t_format, 'month', 'MONTH' ), 'MONTH', 'mmmm' );
t_format := replace( replace( t_format, 'mon', 'MON' ), 'MON', 'mmm' );
return t_format;
end;
--
function get_numFmt( p_format varchar2 := null )
return pls_integer
is
t_cnt pls_integer;
t_numFmtId pls_integer;
begin
if p_format is null
then
return 0;
end if;
t_cnt := workbook.numFmts.count();
for i in 1 .. t_cnt
loop
if workbook.numFmts( i ).formatCode = p_format
then
t_numFmtId := workbook.numFmts( i ).numFmtId;
exit;
end if;
end loop;
if t_numFmtId is null
then
t_numFmtId := case when t_cnt = 0 then 164 else workbook.numFmts( t_cnt ).numFmtId + 1 end;
t_cnt := t_cnt + 1;
workbook.numFmts( t_cnt ).numFmtId := t_numFmtId;
workbook.numFmts( t_cnt ).formatCode := p_format;
workbook.numFmtIndexes( t_numFmtId ) := t_cnt;
end if;
return t_numFmtId;
end;
--
function get_font
( p_name varchar2
, p_family pls_integer := 2
, p_fontsize number := 11
, p_theme pls_integer := 1
, p_underline boolean := false
, p_italic boolean := false
, p_bold boolean := false
)
return pls_integer
is
t_ind pls_integer;
begin
if workbook.fonts.count() > 0
then
for f in 0 .. workbook.fonts.count() - 1
loop
if ( workbook.fonts( f ).name = p_name
and workbook.fonts( f ).family = p_family
and workbook.fonts( f ).fontsize = p_fontsize
and workbook.fonts( f ).theme = p_theme
and workbook.fonts( f ).underline = p_underline
and workbook.fonts( f ).italic = p_italic
and workbook.fonts( f ).bold = p_bold
)
then
return f;
end if;
end loop;
end if;
t_ind := workbook.fonts.count();
workbook.fonts( t_ind ).name := p_name;
workbook.fonts( t_ind ).family := p_family;
workbook.fonts( t_ind ).fontsize := p_fontsize;
workbook.fonts( t_ind ).theme := p_theme;
workbook.fonts( t_ind ).underline := p_underline;
workbook.fonts( t_ind ).italic := p_italic;
workbook.fonts( t_ind ).bold := p_bold;
return t_ind;
end;
--
function get_fill
( p_patternType varchar2
, p_fgRGB varchar2 := null
)
return pls_integer
is
t_ind pls_integer;
begin
if workbook.fills.count() > 0
then
for f in 0 .. workbook.fills.count() - 1
loop
if ( workbook.fills( f ).patternType = p_patternType
and nvl( workbook.fills( f ).fgRGB, 'x' ) = nvl( upper( p_fgRGB ), 'x' )
)
then
return f;
end if;
end loop;
end if;
t_ind := workbook.fills.count();
workbook.fills( t_ind ).patternType := p_patternType;
workbook.fills( t_ind ).fgRGB := upper( p_fgRGB );
return t_ind;
end;
--
function get_border
( p_top varchar2 := 'thin'
, p_bottom varchar2 := 'thin'
, p_left varchar2 := 'thin'
, p_right varchar2 := 'thin'
)
return pls_integer
is
t_ind pls_integer;
begin
if workbook.borders.count() > 0
then
for b in 0 .. workbook.borders.count() - 1
loop
if ( nvl( workbook.borders( b ).top, 'x' ) = nvl( p_top, 'x' )
and nvl( workbook.borders( b ).bottom, 'x' ) = nvl( p_bottom, 'x' )
and nvl( workbook.borders( b ).left, 'x' ) = nvl( p_left, 'x' )
and nvl( workbook.borders( b ).right, 'x' ) = nvl( p_right, 'x' )
)
then
return b;
end if;
end loop;
end if;
t_ind := workbook.borders.count();
workbook.borders( t_ind ).top := p_top;
workbook.borders( t_ind ).bottom := p_bottom;
workbook.borders( t_ind ).left := p_left;
workbook.borders( t_ind ).right := p_right;
return t_ind;
end;
--
function get_alignment
( p_vertical varchar2 := null
, p_horizontal varchar2 := null
, p_wrapText boolean := null
)
return tp_alignment
is
t_rv tp_alignment;
begin
t_rv.vertical := p_vertical;
t_rv.horizontal := p_horizontal;
t_rv.wrapText := p_wrapText;
return t_rv;
end;
--
function get_XfId
( p_sheet pls_integer
, p_col pls_integer
, p_row pls_integer
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
)
return varchar2
is
t_cnt pls_integer;
t_XfId pls_integer;
t_XF tp_XF_fmt;
t_col_XF tp_XF_fmt;
t_row_XF tp_XF_fmt;
begin
if workbook.sheets( p_sheet ).col_fmts.exists( p_col )
then
t_col_XF := workbook.sheets( p_sheet ).col_fmts( p_col );
end if;
if workbook.sheets( p_sheet ).row_fmts.exists( p_row )
then
t_row_XF := workbook.sheets( p_sheet ).row_fmts( p_row );
end if;
t_XF.numFmtId := coalesce( p_numFmtId, t_col_XF.numFmtId, t_row_XF.numFmtId, 0 );
t_XF.fontId := coalesce( p_fontId, t_col_XF.fontId, t_row_XF.fontId, 0 );
t_XF.fillId := coalesce( p_fillId, t_col_XF.fillId, t_row_XF.fillId, 0 );
t_XF.borderId := coalesce( p_borderId, t_col_XF.borderId, t_row_XF.borderId, 0 );
t_XF.alignment := coalesce( p_alignment, t_col_XF.alignment, t_row_XF.alignment );
if ( t_XF.numFmtId + t_XF.fontId + t_XF.fillId + t_XF.borderId = 0
and t_XF.alignment.vertical is null
and t_XF.alignment.horizontal is null
and not nvl( t_XF.alignment.wrapText, false )
)
then
return '';
end if;
if t_XF.numFmtId > 0
then
set_col_width( p_sheet, p_col, workbook.numFmts( workbook.numFmtIndexes( t_XF.numFmtId ) ).formatCode );
end if;
t_cnt := workbook.cellXfs.count();
for i in 1 .. t_cnt
loop
if ( workbook.cellXfs( i ).numFmtId = t_XF.numFmtId
and workbook.cellXfs( i ).fontId = t_XF.fontId
and workbook.cellXfs( i ).fillId = t_XF.fillId
and workbook.cellXfs( i ).borderId = t_XF.borderId
and nvl( workbook.cellXfs( i ).alignment.vertical, 'x' ) = nvl( t_XF.alignment.vertical, 'x' )
and nvl( workbook.cellXfs( i ).alignment.horizontal, 'x' ) = nvl( t_XF.alignment.horizontal, 'x' )
and nvl( workbook.cellXfs( i ).alignment.wrapText, false ) = nvl( t_XF.alignment.wrapText, false )
)
then
t_XfId := i;
exit;
end if;
end loop;
if t_XfId is null
then
t_cnt := t_cnt + 1;
t_XfId := t_cnt;
workbook.cellXfs( t_cnt ) := t_XF;
end if;
return 's="' || t_XfId || '"';
end;
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value number
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
)
is
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := null;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := get_XfId( t_sheet, p_col, p_row, p_numFmtId, p_fontId, p_fillId, p_borderId, p_alignment );
end;
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value varchar2
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
)
is
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
t_alignment tp_alignment := p_alignment;
begin
workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := workbook.strings.count();
workbook.strings( workbook.strings.count() ) := p_value;
if t_alignment.wrapText is null and instr( p_value, chr(13) ) > 0
then
t_alignment.wrapText := true;
end if;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := 't="s" ' || get_XfId( t_sheet, p_col, p_row, p_numFmtId, p_fontId, p_fillId, p_borderId, t_alignment );
end;
--
procedure cell
( p_col pls_integer
, p_row pls_integer
, p_value date
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
)
is
t_numFmtId pls_integer := p_numFmtId;
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value - to_date('01-01-1904','DD-MM-YYYY');
if t_numFmtId is null
and not ( workbook.sheets( t_sheet ).col_fmts.exists( p_col )
and workbook.sheets( t_sheet ).col_fmts( p_col ).numFmtId is not null
)
and not ( workbook.sheets( t_sheet ).row_fmts.exists( p_row )
and workbook.sheets( t_sheet ).row_fmts( p_row ).numFmtId is not null
)
then
t_numFmtId := get_numFmt( 'dd/mm/yyyy' );
end if;
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := get_XfId( t_sheet, p_col, p_row, t_numFmtId, p_fontId, p_fillId, p_borderId, p_alignment );
end;
--
procedure hyperlink
( p_col pls_integer
, p_row pls_integer
, p_url varchar2
, p_value varchar2 := null
, p_sheet pls_integer := null
)
is
t_ind pls_integer;
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := workbook.strings.count();
workbook.strings( workbook.strings.count() ) := nvl( p_value, p_url );
workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := 't="s" ' || get_XfId( t_sheet, p_col, p_row, '', get_font( 'Calibri', p_theme => 10, p_underline => true ) );
t_ind := workbook.sheets( t_sheet ).hyperlinks.count() + 1;
workbook.sheets( t_sheet ).hyperlinks( t_ind ).cell := alfan_col( p_col ) || p_row;
workbook.sheets( t_sheet ).hyperlinks( t_ind ).url := p_url;
end;
--
procedure comment
( p_col pls_integer
, p_row pls_integer
, p_text varchar2
, p_author varchar2 := null
, p_width pls_integer := 150
, p_height pls_integer := 100
, p_sheet pls_integer := null
)
is
t_ind pls_integer;
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
t_ind := workbook.sheets( t_sheet ).comments.count() + 1;
workbook.sheets( t_sheet ).comments( t_ind ).row := p_row;
workbook.sheets( t_sheet ).comments( t_ind ).column := p_col;
workbook.sheets( t_sheet ).comments( t_ind ).text := dbms_xmlgen.convert( p_text );
workbook.sheets( t_sheet ).comments( t_ind ).author := dbms_xmlgen.convert( p_author );
workbook.sheets( t_sheet ).comments( t_ind ).width := p_width;
workbook.sheets( t_sheet ).comments( t_ind ).height := p_height;
end;
--
procedure mergecells
( p_tl_col pls_integer -- top left
, p_tl_row pls_integer
, p_br_col pls_integer -- bottom right
, p_br_row pls_integer
, p_sheet pls_integer := null
)
is
t_ind pls_integer;
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
t_ind := workbook.sheets( t_sheet ).mergecells.count() + 1;
workbook.sheets( t_sheet ).mergecells( t_ind ) := alfan_col( p_tl_col ) || p_tl_row || ':' || alfan_col( p_br_col ) || p_br_row;
end;
--
procedure set_column_width
( p_col pls_integer
, p_width number
, p_sheet pls_integer := null
)
is
begin
workbook.sheets( nvl( p_sheet, workbook.sheets.count() ) ).widths( p_col ) := p_width;
end;
--
procedure set_column
( p_col pls_integer
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
)
is
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
workbook.sheets( t_sheet ).col_fmts( p_col ).numFmtId := p_numFmtId;
workbook.sheets( t_sheet ).col_fmts( p_col ).fontId := p_fontId;
workbook.sheets( t_sheet ).col_fmts( p_col ).fillId := p_fillId;
workbook.sheets( t_sheet ).col_fmts( p_col ).borderId := p_borderId;
workbook.sheets( t_sheet ).col_fmts( p_col ).alignment := p_alignment;
end;
--
procedure set_row
( p_row pls_integer
, p_numFmtId pls_integer := null
, p_fontId pls_integer := null
, p_fillId pls_integer := null
, p_borderId pls_integer := null
, p_alignment tp_alignment := null
, p_sheet pls_integer := null
)
is
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
workbook.sheets( t_sheet ).row_fmts( p_row ).numFmtId := p_numFmtId;
workbook.sheets( t_sheet ).row_fmts( p_row ).fontId := p_fontId;
workbook.sheets( t_sheet ).row_fmts( p_row ).fillId := p_fillId;
workbook.sheets( t_sheet ).row_fmts( p_row ).borderId := p_borderId;
workbook.sheets( t_sheet ).row_fmts( p_row ).alignment := p_alignment;
end;
--
procedure freeze_rows
( p_nr_rows pls_integer := 1
, p_sheet pls_integer := null
)
is
begin
workbook.sheets( nvl( p_sheet, workbook.sheets.count() ) ).freeze_rows := p_nr_rows;
end;
--
procedure freeze_cols
( p_nr_cols pls_integer := 1
, p_sheet pls_integer := null
)
is
begin
workbook.sheets( nvl( p_sheet, workbook.sheets.count() ) ).freeze_cols := p_nr_cols;
end;
--
procedure set_autofilter
( p_column_start pls_integer := null
, p_column_end pls_integer := null
, p_row_start pls_integer := null
, p_row_end pls_integer := null
, p_sheet pls_integer := null
)
is
t_ind pls_integer;
t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
begin
t_ind := workbook.sheets( t_sheet ).autofilters.count() + 1;
workbook.sheets( t_sheet ).autofilters( t_ind ).column_start := p_column_start;
workbook.sheets( t_sheet ).autofilters( t_ind ).column_end := p_column_end;
workbook.sheets( t_sheet ).autofilters( t_ind ).row_start := p_row_start;
workbook.sheets( t_sheet ).autofilters( t_ind ).row_end := p_row_end;
end;
--
procedure add1xml
( p_excel in out nocopy blob
, p_filename varchar2
, p_xml clob
)
is
t_tmp blob;
begin
dbms_lob.createtemporary( t_tmp, true );
for i in 0 .. trunc( length( p_xml ) / 4000 )
loop
dbms_lob.append( t_tmp, utl_i18n.string_to_raw( substr( p_xml, i * 4000 + 1, 4000 ), 'AL32UTF8' ) );
end loop;
add1file( p_excel, p_filename, t_tmp );
dbms_lob.freetemporary( t_tmp );
end;
--
function finish
return blob
is
t_excel blob;
t_xxx clob;
t_tmp clob;
t_c number;
t_h number;
t_w number;
t_cw number;
t_cell varchar2(1000);
t_row_ind pls_integer;
t_col_min pls_integer;
t_col_max pls_integer;
t_col_ind pls_integer;
begin
dbms_lob.createtemporary( t_excel, true );
t_xxx := '




';
for s in 1 .. workbook.sheets.count()
loop
t_xxx := t_xxx || '
';
end loop;
t_xxx := t_xxx || '




';
for s in 1 .. workbook.sheets.count()
loop
if workbook.sheets( s ).comments.count() > 0
then
t_xxx := t_xxx || '
';
end if;
end loop;
t_xxx := t_xxx || '';
add1xml( t_excel, '[Content_Types].xml', t_xxx );
t_xxx := '

' || sys_context( 'userenv', 'os_user' ) || '
' || sys_context( 'userenv', 'os_user' ) || '
' || to_char( current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM' ) || '
' || to_char( current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM' ) || '';
add1xml( t_excel, 'docProps/core.xml', t_xxx );
t_xxx := '

Microsoft Excel
0
false



Worksheets

' || workbook.sheets.count() || '

';
for s in 1 .. workbook.sheets.count()
loop
t_xxx := t_xxx || '
' || workbook.sheets( s ).name || '';
end loop;
t_xxx := t_xxx || '
false
false
false
14.0300';
add1xml( t_excel, 'docProps/app.xml', t_xxx );
t_xxx := '




';
add1xml( t_excel, '_rels/.rels', t_xxx );
t_xxx := '
';
if workbook.numFmts.count() > 0
then
t_xxx := t_xxx || '';
for n in 1 .. workbook.numFmts.count()
loop
t_xxx := t_xxx || '';
end loop;
t_xxx := t_xxx || '';
end if;
t_xxx := t_xxx || '';
for f in 0 .. workbook.fonts.count() - 1
loop
t_xxx := t_xxx || '' ||
case when workbook.fonts( f ).bold then '' end ||
case when workbook.fonts( f ).italic then '' end ||
case when workbook.fonts( f ).underline then '' end ||
'




';
end loop;
t_xxx := t_xxx || '
';
for f in 0 .. workbook.fills.count() - 1
loop
t_xxx := t_xxx || '' ||
case when workbook.fills( f ).fgRGB is not null then '' end ||
'';
end loop;
t_xxx := t_xxx || '
';
for b in 0 .. workbook.borders.count() - 1
loop
t_xxx := t_xxx || '' ||
case when workbook.borders( b ).left is null then '' else '' end ||
case when workbook.borders( b ).right is null then '' else '' end ||
case when workbook.borders( b ).top is null then '' else '' end ||
case when workbook.borders( b ).bottom is null then '' else '' end ||
'';
end loop;
t_xxx := t_xxx || '




';
for x in 1 .. workbook.cellXfs.count()
loop
t_xxx := t_xxx || '';
if ( workbook.cellXfs( x ).alignment.horizontal is not null
or workbook.cellXfs( x ).alignment.vertical is not null
or workbook.cellXfs( x ).alignment.wrapText
)
then
t_xxx := t_xxx || ' case when workbook.cellXfs( x ).alignment.horizontal is not null then ' horizontal="' || workbook.cellXfs( x ).alignment.horizontal || '"' end ||
case when workbook.cellXfs( x ).alignment.vertical is not null then ' vertical="' || workbook.cellXfs( x ).alignment.vertical || '"' end ||
case when workbook.cellXfs( x ).alignment.wrapText then ' wrapText="true"' end || '/>';
end if;
t_xxx := t_xxx || '';
end loop;
t_xxx := t_xxx || '








';
add1xml( t_excel, 'xl/styles.xml', t_xxx );
t_xxx := '






';
for s in 1 .. workbook.sheets.count()
loop
t_xxx := t_xxx || '
';
end loop;
t_xxx := t_xxx || '

';
add1xml( t_excel, 'xl/workbook.xml', t_xxx );
t_xxx := '





















































































































































































































































';
add1xml( t_excel, 'xl/theme/theme1.xml', t_xxx );
for s in 1 .. workbook.sheets.count()
loop
t_col_min := 16384;
t_col_max := 1;
t_row_ind := workbook.sheets( s ).rows.first();
while t_row_ind is not null
loop
t_col_min := least( t_col_min, workbook.sheets( s ).rows( t_row_ind ).first() );
t_col_max := greatest( t_col_max, workbook.sheets( s ).rows( t_row_ind ).last() );
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
t_xxx := '



';
if workbook.sheets( s ).freeze_rows > 0
then
t_xxx := t_xxx || '';
end if;
if workbook.sheets( s ).freeze_cols > 0
then
t_xxx := t_xxx || '';
end if;
t_xxx := t_xxx || '
';
if workbook.sheets( s ).widths.count() > 0
then
t_xxx := t_xxx || '';
t_col_ind := workbook.sheets( s ).widths.first();
while t_col_ind is not null
loop
t_xxx := t_xxx ||
'';
t_col_ind := workbook.sheets( s ).widths.next( t_col_ind );
end loop;
t_xxx := t_xxx || '';
end if;
t_xxx := t_xxx || '';
t_row_ind := workbook.sheets( s ).rows.first();
t_tmp := null;
while t_row_ind is not null
loop
t_tmp := t_tmp || '';
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).first();
while t_col_ind is not null
loop
t_cell := ' t_cell := t_cell || ' ' || workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).style;
t_cell := t_cell || '>';
t_cell := t_cell || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' );
t_cell := t_cell || '';
t_tmp := t_tmp || t_cell;
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
end loop;
t_tmp := t_tmp || '';
if length( t_tmp ) > 8000
then
t_xxx := t_xxx || t_tmp;
t_tmp := null;
end if;
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
t_xxx := t_xxx || t_tmp || '';
for a in 1 .. workbook.sheets( s ).autofilters.count()
loop
t_xxx := t_xxx || '';
end loop;
if workbook.sheets( s ).mergecells.count() > 0
then
t_xxx := t_xxx || '';
for m in 1 .. workbook.sheets( s ).mergecells.count()
loop
t_xxx := t_xxx || '';
end loop;
t_xxx := t_xxx || '';
end if;
if workbook.sheets( s ).hyperlinks.count() > 0
then
t_xxx := t_xxx || '';
for h in 1 .. workbook.sheets( s ).hyperlinks.count()
loop
t_xxx := t_xxx || '';
end loop;
t_xxx := t_xxx || '';
end if;
t_xxx := t_xxx || '';
if workbook.sheets( s ).comments.count() > 0
then
t_xxx := t_xxx || '';
end if;
t_xxx := t_xxx || '';
add1xml( t_excel, 'xl/worksheets/sheet' || s || '.xml', t_xxx );
if workbook.sheets( s ).hyperlinks.count() > 0 or workbook.sheets( s ).comments.count() > 0
then
t_xxx := '
';
if workbook.sheets( s ).comments.count() > 0
then
t_xxx := t_xxx || '';
t_xxx := t_xxx || '';
end if;
for h in 1 .. workbook.sheets( s ).hyperlinks.count()
loop
t_xxx := t_xxx || '';
end loop;
t_xxx := t_xxx || '';
add1xml( t_excel, 'xl/worksheets/_rels/sheet' || s || '.xml.rels', t_xxx );
end if;
--
if workbook.sheets( s ).comments.count() > 0
then
declare
cnt pls_integer;
author_ind tp_author;
-- t_col_ind := workbook.sheets( s ).widths.next( t_col_ind );
begin
authors.delete();
for c in 1 .. workbook.sheets( s ).comments.count()
loop
authors( workbook.sheets( s ).comments( c ).author ) := 0;
end loop;
t_xxx := '

';
cnt := 0;
author_ind := authors.first();
while author_ind is not null or authors.next( author_ind ) is not null
loop
authors( author_ind ) := cnt;
t_xxx := t_xxx || '' || author_ind || '';
cnt := cnt + 1;
author_ind := authors.next( author_ind );
end loop;
end;
t_xxx := t_xxx || '';
for c in 1 .. workbook.sheets( s ).comments.count()
loop
t_xxx := t_xxx || '
';
if workbook.sheets( s ).comments( c ).author is not null
then
t_xxx := t_xxx || '' ||
workbook.sheets( s ).comments( c ).author || ':';
end if;
t_xxx := t_xxx || '' ||
case when workbook.sheets( s ).comments( c ).author is not null then '
' end || workbook.sheets( s ).comments( c ).text || '';
end loop;
t_xxx := t_xxx || '';
add1xml( t_excel, 'xl/comments' || s || '.xml', t_xxx );
t_xxx := '

';
for c in 1 .. workbook.sheets( s ).comments.count()
loop
t_xxx := t_xxx || 'style="position:absolute;margin-left:35.25pt;margin-top:3pt;z-index:' || to_char( c ) || ';visibility:hidden;" fillcolor="#ffffe1" o:insetmode="auto">

';
t_w := workbook.sheets( s ).comments( c ).width;
t_c := 1;
loop
if workbook.sheets( s ).widths.exists( workbook.sheets( s ).comments( c ).column + t_c )
then
t_cw := 256 * workbook.sheets( s ).widths( workbook.sheets( s ).comments( c ).column + t_c );
t_cw := trunc( ( t_cw + 18 ) / 256 * 7); -- assume default 11 point Calibri
else
t_cw := 64;
end if;
exit when t_w < t_cw;
t_c := t_c + 1;
t_w := t_w - t_cw;
end loop;
t_h := workbook.sheets( s ).comments( c ).height;
t_xxx := t_xxx || to_char( '' || workbook.sheets( s ).comments( c ).column || ',15,' ||
workbook.sheets( s ).comments( c ).row || ',30,' ||
( workbook.sheets( s ).comments( c ).column + t_c - 1 ) || ',' || round( t_w ) || ',' ||
( workbook.sheets( s ).comments( c ).row + 1 + trunc( t_h / 20 ) ) || ',' || mod( t_h, 20 ) || '' );
t_xxx := t_xxx || to_char( 'False' ||
( workbook.sheets( s ).comments( c ).row - 1 ) || '' ||
( workbook.sheets( s ).comments( c ).column - 1 ) || '' );
end loop;
t_xxx := t_xxx || '';
add1xml( t_excel, 'xl/drawings/vmlDrawing' || s || '.vml', t_xxx );
end if;
--
end loop;
t_xxx := '



';
for s in 1 .. workbook.sheets.count()
loop
t_xxx := t_xxx || '
';
end loop;
t_xxx := t_xxx || '';
add1xml( t_excel, 'xl/_rels/workbook.xml.rels', t_xxx );
t_xxx := '
';
t_tmp := null;
for i in 0 .. workbook.strings.count() - 1
loop
t_tmp := t_tmp || '' || dbms_xmlgen.convert( substr( workbook.strings( i ), 1, 32000 ) ) || '';
if length( t_tmp ) > 8000
then
t_xxx := t_xxx || t_tmp;
t_tmp := null;
end if;
end loop;
t_xxx := t_xxx || t_tmp || '';
add1xml( t_excel, 'xl/sharedStrings.xml', t_xxx );
finish_zip( t_excel );
clear_workbook;
return t_excel;
end;
--
procedure save
( p_directory varchar2
, p_filename varchar2
)
is
begin
blob2file( finish, p_directory, p_filename );
end;
--
procedure query2sheet
( p_sql varchar2
, p_column_headers boolean := true
, p_directory varchar2 := null
, p_filename varchar2 := null
, p_sheet pls_integer := null
)
is
t_sheet pls_integer;
t_c integer;
t_col_cnt integer;
t_desc_tab dbms_sql.desc_tab2;
d_tab dbms_sql.date_table;
n_tab dbms_sql.number_table;
v_tab dbms_sql.varchar2_table;
t_bulk_size pls_integer := 200;
t_r integer;
t_cur_row pls_integer;
begin
if p_sheet is null
then
new_sheet;
end if;
t_c := dbms_sql.open_cursor;
dbms_sql.parse( t_c, p_sql, dbms_sql.native );
dbms_sql.describe_columns2( t_c, t_col_cnt, t_desc_tab );
for c in 1 .. t_col_cnt
loop
if p_column_headers
then
cell( c, 1, t_desc_tab( c ).col_name, p_sheet => t_sheet );
end if;
-- dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type );
case
when t_desc_tab( c ).col_type in ( 2, 100, 101 )
then
dbms_sql.define_array( t_c, c, n_tab, t_bulk_size, 1 );
when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 )
then
dbms_sql.define_array( t_c, c, d_tab, t_bulk_size, 1 );
when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
then
dbms_sql.define_array( t_c, c, v_tab, t_bulk_size, 1 );
else
null;
end case;
end loop;
--
t_cur_row := case when p_column_headers then 2 else 1 end;
t_sheet := nvl( p_sheet, workbook.sheets.count() );
--
t_r := dbms_sql.execute( t_c );
loop
t_r := dbms_sql.fetch_rows( t_c );
if t_r > 0
then
for c in 1 .. t_col_cnt
loop
case
when t_desc_tab( c ).col_type in ( 2, 100, 101 )
then
dbms_sql.column_value( t_c, c, n_tab );
for i in 0 .. t_r - 1
loop
cell( c, t_cur_row + i, n_tab( i + n_tab.first() ), p_sheet => t_sheet );
end loop;
n_tab.delete;
when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 )
then
dbms_sql.column_value( t_c, c, d_tab );
for i in 0 .. t_r - 1
loop
cell( c, t_cur_row + i, d_tab( i + d_tab.first() ), p_sheet => t_sheet );
end loop;
d_tab.delete;
when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
then
dbms_sql.column_value( t_c, c, v_tab );
for i in 0 .. t_r - 1
loop
cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet );
end loop;
v_tab.delete;
else
null;
end case;
end loop;
end if;
exit when t_r != t_bulk_size;
t_cur_row := t_cur_row + t_r;
end loop;
dbms_sql.close_cursor( t_c );
if ( p_directory is not null and p_filename is not null )
then
save( p_directory, p_filename );
end if;
exception
when others
then
if dbms_sql.is_open( t_c )
then
dbms_sql.close_cursor( t_c );
end if;
end;

end;
/

Show Plan for a query

explain plan set statement_id = '123456' for
delete from dup1 d1
where rowid >
( select min(rowid) from dup1 d2 where d1.num = d2.num );


SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '
||object_name||' '||
DECODE(id,0,'Cost = '|| position) AS "Query Plan"
FROM plan_table
START WITH id = 0
AND statement_id='123456'
CONNECT BY PRIOR ID = PARENT_ID
AND statement_id = '123456';

Delete Duplicate Rows from a table

delete from dup1 d1
where rowid >
( select min(rowid) from dup1 d2 where d1.num = d2.num )

OR

delete from dup1 d1
where rowid  <
( select max(rowid) from dup1 d2 where d1.num = d2.num )