The environment variables$ORACLE_PATH (or %SQLPATH% in Window cmd.exe) contains a list of directories (separated by colons (or semicolons on Windows) that can be executed by the start command of SQL*Plus.
determine my current schema and the logged in user.
db.sql
--
-- Some basic information about the database from v$database.
--
-- See -> os.sql for column PLATFORM_NAME
--
select
-- dbid,
name,
to_char(created, 'dd.mm.yyyy') created,
-- resetlogs_change#, -- System change number (SCN) at open resetlogs
-- resetlogs_time,
log_mode, -- NOARCHIVELOG, ARCHIVELOG, MANUAL
checkpoint_change#, -- Last SCN checkpointed
archive_change#, -- Database force archiving SCN. Any redo log with a start SCN below this will be forced to archive out.
controlfile_type, -- STANDBY: database is in standby mode
-- CLONE
-- BACKUP | CREATED : database is being recovered using a backup or created control file
-- CURRENT database available for general use
to_char(controlfile_created, 'dd.mm.yyyy') "Ctrl Cr.t", -- Creation date of the control file
controlfile_sequence# "Ctrl Seq",
controlfile_change# "Ctrl SCN",
controlfile_time "Ctrl Tim",
open_resetlogs,
open_mode,
database_role
-- switchover_status
-- version_time
from
v$database;
--
-- Find object dependencies, report them in a �hierarchical� layout.
--
-- See also -> find_unreferenced_objects.sql and -> ref.sql
--
with obj (name, owner, type, level_) as (
select
upper('&object_name'),
user,
upper('&object_type'),
0
from
dual
union all
select
dep.name,
dep.owner,
dep.type,
obj.level_ + 1
from
obj join dba_dependencies
dep on obj.name = dep.referenced_name and
obj.owner = dep.referenced_owner and
obj.type = dep.referenced_type
)
select
lpad(' ', level_ * 2) || name || ' [' || owner || ']' || ' {' || type || '}'
from
obj;
--
-- Compare content of two tables
--
set verify off
define table_1=&1
define table_2=&2
select * from &table_1 minus
select * from &table_2;
select * from &table_2 minus
select * from &table_1;
--
-- Show Objects thare are not enabled or valid
--
-- Not the same thing as DBA_INVALID_OBJECTS.
--
select object_type , owner, object_name , status from dba_objects where owner not in ('SYS', 'SYSTEM') and status != 'VALID' union all
select 'CONSTRAINT' , owner, constraint_name, status from dba_constraints where owner not in ('SYS', 'SYSTEM') and status != 'ENABLED' union all
select 'TRIGGER' , owner, trigger_name , status from dba_triggers where owner not in ('SYS', 'SYSTEM', 'WMSYS') and status != 'ENABLED' union all
select 'INDEX' , owner, index_name , status from dba_indexes where owner not in ('SYS', 'SYSTEM', 'XDB') and status != 'VALID' union all
select 'CONSTRAINT' , owner, constraint_name, status from dba_constraints where owner not in ('SYS', 'SYSTEM') and status != 'ENABLED';
-- Create a dotfile
--
-- The parameter given to this script is
-- the path to the dot file without (.dot)
-- suffix.
--
-- Creates a file whose type can be specified
-- with dot_output_format in the same directory
-- as the dot file.
--
-- See also spool.sql
--
define dot_output_format=pdf
$dot &1..dot -T&dot_output_format -o&1..&dot_output_format
$&1..&dot_output_format
set verify off
declare
--
-- Drop a schema (user) if it exists.
--
-- To drop an object, use
-- drop_if_exists.sql
--
schema_name varchar2(30) := '&1';
begin
execute immediate 'drop user ' || schema_name || ' cascade';
exception when others then
if sqlcode = -1918 then -- user '...' does not exist
null; -- Ignore, nothing to do.
else
dbms_output.put_line('drop_schema_if_exists');
dbms_output.put_line(' ' || sqlcode);
dbms_output.put_line(' ' || sqlerrm);
end if;
end;
/
--
-- Quickly evaluate an SQL expression
--
-- If the expression contains spaces, it must be embedded within quotes.
--
-- @expr "7 * 6"
-- @expr length('foo')
-- @expr "length('one two three')"
--
set verify off
select &1 expr from dual;
--
-- Show some basic information about datafiles
--
-- For tablespaces, see -> ts.sql
--
column "Cont" format a4
column "File name" format a70
select
case when ts.tablespace_name = lag(ts.tablespace_name) over (order by nvl(df.file_name, tf.file_name)) then '' else initcap(substr(ts.contents, 1, 4)) end "Cont",
case when ts.tablespace_name = lag(ts.tablespace_name) over (order by nvl(df.file_name, tf.file_name)) then '' else ts.tablespace_name end "Name",
round(ts.max_size / 1024 / 1024 / 1024 , 2) "TS Max GB",
substr(nvl(df.file_name, tf.file_name), 1, 70) "File name",
to_char(nvl(df.bytes, tf.bytes)/1024/1024/1024, '999990.99') "File GB",
nvl(df.autoextensible, tf.autoextensible) "Auto Ext?"
from
dba_tablespaces ts left join
dba_data_files df on ts.tablespace_name = df.tablespace_name left join
dba_temp_files tf on ts.tablespace_name = tf.tablespace_name
order by
ts.tablespace_name,
nvl(df.file_name, tf.file_name) ;
prompt
prompt "Trace files"
prompt
select
initcap(substr(name, 1, 4)) what,
substr(value, 1, 100) directory
from
v$parameter
where
name like '%dump_dest';
@rem
@rem Pass the name of a file to this script
@rem
@rem This script will then create the file 'c:\temp\file_to_table.out'.
@rem
@rem For each line found in the file whose name
@rem is passed to this script, the script will
@rem append a line into c:\temp\file_to_table.out
@rem with a
@rem insert into tmp_file_to_table values (<LINENUMBER>, <LINETEXT>);
@rem
@rem It is intended that this script is called by 'file_to_table.sql'.
@rem
@rem The following
@rem setlocal ENABLEDELAYEDEXPANSION
@rem ist very necessary as it allows for the local variable 'linenumber'
@rem to be incremented. Such variables to be locally expanded (or evaluated)
@rem are not identified by a %-sign, but rather with the !-sign.
@rem
@set /a linenumber=1
@ setlocal ENABLEDELAYEDEXPANSION
@FOR /F " usebackq delims==" %%i IN (`type %1`) DO @( echo insert into tmp_file_to_table values ^(!linenumber!, q'#%%i#'^)^;
@set /a linenumber+=1
) >> c:\temp\file_to_table.out
@endlocal
.
set feedback off
set termout off
save c:\temp\file_to_table.sqlplus_buffer replace
-- The two previous commands save the 'current' or
--'up to now' content of the SQL buffer because we will need
-- it later.
-- The comment is AFTER the commands so that it doesn't
-- get saved along with the current SQL buffer.
--
-- This script's purpose is to "convert" a file within
-- SQL*Plus into a table, so that it can be used like
-- for example so:
--
--
-- begin
--
-- for r in (
-- @@file_to_table.sql <filename>
-- ) loop
--
-- dbms_output.put_line(r.linetext);
--
-- end loop;
-- end;
--
-- --------------------------------------------------
-- ../file_to_table/tmp_file_to_table.sql
-- installs the necessary temp-table.
-- --------------------------------------------------
--
-- Unfortunately, this script only works when called from within
-- another script.
--
-- Delete the .out file:
$@del c:\temp\file_to_table.out > nul
$@echo set define off >> c:\temp\file_to_table.out
$@echo set feedback off >> c:\temp\file_to_table.out
$@echo truncate table tmp_file_to_table^; >> c:\temp\file_to_table.out
-- rem $@FOR /F " usebackq delims==" %i IN (`type &1`) DO @echo insert into tmp_file_to_table values (%zeilen_nummer, q'!%i!')^; >> c:\temp\file_to_table.out && set /a zeilen_nummer + 1
$%SQLPATH%\file_to_table.bat &1
--$file_to_table.bat &1
$@echo set define ^& >> c:\temp\file_to_table.out
@@c:\temp\file_to_table.out
set feedback on
-- Now that the tmp_file_to_table is filled, the previously
-- saved buffer can be retrieved again:
set termout on
get c:\temp\file_to_table.sqlplus_buffer nolist
-- -- and add a string for the select statement:
select linenumber, linetext from tmp_file_to_table
--
-- Find gaps.
--
-- This script finds "Value Gaps" in columns that contain
-- numeric, non fractional values (integers), possibly
-- created by sequences.
--
select a+1 "From/Start Value",
lead_ "To (Value)",
lead_ - a "Size"
from (
select &&column a,
lag (&&column) over (order by &&column) lag_,
lead(&&column) over (order by &&column) lead_,
&&column - lead(&&column) over (order by &&column) count_
from &&table
order by &&column - lead(&&column) over (order by &&column)
)
where rownum < 20;
--
-- Find Objects that are not referenced by other objects.
--
-- See also -> dep.sql and -> ref.sql
--
select owner, lower(object_name), object_name, object_type from (
select
owner,
object_name
from
dba_objects
where
owner = '&owner' and
object_type not in ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TABLE PARTITION', 'LOB', 'LOB PARTITION', 'TRIGGER', 'JOB', 'SYNONYM')
minus
select
referenced_owner, referenced_name
from
dba_dependencies
where type not in ('SYNONYM')
) join
user_objects using (object_name)
order by object_name
;
-- http://blog.tanelpoder.com/files/scripts/hint.sql
select
name,
version,
version_outline,
inverse
from
v$sql_hint
where lower(name) like lower('%&1%');
--
-- Select objects that are in
-- an invalid state.
--
select
object_name, owner, object_type, status
from
all_objects
where
status != 'VALID';
select
index_name, owner, 'INDEX', status
from
all_indexes
where
status != 'VALID';
-- Displays the name of the table and its columns belonging to an index.
-- The Name of the index is passed as the first and only argument
-- to this script.
select
'Table: ' || table_name table_name
from
dba_indexes
where
upper(index_name) = upper('&1');
select
substr(column_name, 1, 30) columns
from
dba_ind_columns
where
upper(index_name) = upper('&1');
select
substrb(object_name, 1, 30) object_name,
substrb(object_type, 1, 30) object_type,
substrb(owner , 1, 30) owner
from
dba_objects
where
lower(object_name) like lower('%&1%') and
object_type not in ('TABLE PARTITION')
order by
owner,
object_name;
-- http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
--
-- Compare with «roles_and_privileges_of_user.sql» and «roles_and_users_of_system_privilege.sql»
--
select
case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
end
from
(
/* THE OBJECTS */
select
null p1,
null p2,
object_name obj,
owner own,
object_type typ
from
dba_objects
where
owner not in
('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS',
'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
and object_type not in ('SYNONYM', 'INDEX')
/* THE OBJECT TO PRIVILEGE RELATIONS */
union
select
table_name p1,
owner p2,
grantee,
grantee,
privilege
from
dba_tab_privs
/* THE ROLES TO ROLES/USERS RELATIONS */
union
select
granted_role p1,
granted_role p2,
grantee,
grantee,
null
from
dba_role_privs
)
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;
--
-- Don't use v$open_cursor...
--
select
stat.value
from
v$mystat stat join
v$statname name on stat.statistic# = name.statistic#
where
name.name = 'opened cursors current';
--
-- Used by ps_upwards.sql and ps_downwards.sql
--
define complete_name=&1
define proc=&2
set verify off
@spool c:\temp\ps_.dot
declare
signature varchar2(32);
begin
if length('&complete_name') = 32 and instr('&complete_name', '.') = 0 then
-- complete_name seems to be a signature.
signature := '&complete_name';
else
-- complete name seems to be in the 'package.procedure' form:
select signature into signature
from plscope_callable
where lower(complete_name) = lower('&complete_name');
end if;
plscope.&proc(signature, 'dot');
end;
/
spool off
@dot c:\temp\ps_
--
-- Where is an object referenced?
--
-- See also -> find_unreferenced_objects.sql and -> dep.sql
--
select type, name from dba_dependencies where lower(referenced_name) = lower('&1');
--
-- http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
--
-- Compare with «object_privileges.sql» and «roles_and_users_of_system_privilege.sql»
--
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
--
-- http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
--
-- Compare with «roles_and_privileges_of_user.sql» and «object_privileges.sql»
--
select
lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
(
/* THE PRIVILEGES */
select
null p,
name c
from
system_privilege_map
where
name like upper('%&enter_privliege%')
/* THE ROLES TO ROLES RELATIONS */
union
select
granted_role p,
grantee c
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
privilege p,
grantee c
from
dba_sys_privs
)
start with p is null
connect by p = prior c;
select
nam.name,
sta.value
from
v$sesstat sta join
v$statname nam using (statistic#)
where
sta.sid = &1 and
sta.value != 0
order by
nam.class,
sta.value;
set verify off
declare
r v$session_wait%rowtype;
begin
select * into r from v$session_wait where sid = &1;
dbms_output.put_line('');
if r.wait_time = 0 then -- See Metalink 43718.1 / 1360119.1
dbms_output.put_line('Session is waiting (no CPU activity)');
else
dbms_output.put_line('Session is waiting with CPU activity');
if r.wait_time > 0 then
dbms_output.put_line('Duration of last wait in 100th of seconds');
else
dbms_output.put_line('TODO: Implement me, wait_time = ' || r.wait_time);
-- wait_time = -2 -> 'Duration of last wait unknown'
-- wait_time = -1 -> 'Last wait < 1 ms'
-- wait_time < -2 -> 'Time has probl. wrapped'
end if;
end if;
dbms_output.put_line('');
dbms_output.put_line(r.event);
if r.event = 'db file sequential read' then -- See Metalink 181306.1 -- {
declare
ts varchar2( 30);
fn varchar2(500);
is_tempfile boolean;
begin
begin
select tablespace_name, file_name
into ts , fn
from dba_data_files
where file_id = r.p1;
is_tempfile := false;
exception when no_data_found then
-- If select statement does not return anything AND
-- r.p1 > db_files parameter THEN the file is probably
-- a tempfile:
select tablespace_name, file_name
into ts , fn
from dba_temp_files t join
v$parameter p on p.value + t.file_id = r.p1
where p.name = 'db_files';
is_tempfile := true;
end;
dbms_output.put_line(' Tablespace: ' || ts );
dbms_output.put_line(' File: ' || fn );
dbms_output.put_line(' Blocks: ' || r.p3);
if not is_tempfile then -- { Show segment name
declare
own varchar2(30);
seg varchar2(30);
typ varchar2(30);
begin
--
-- Slow query ahead
--
select owner, segment_name, segment_type
into own , seg , typ
from dba_extents
where file_id = r.p1 and
r.p2 between block_id and block_id + blocks - 1;
dbms_output.put_line(' Segment: ' || initcap(typ) || ' ' || own || '.' || seg);
end;
end if; -- }
end;
end if; -- }
end;
/
--
-- ses_wide.sql is basically the same thing as ses.sql but
-- uses listagg() to concatenate the sql pieces so that
-- the statement returns one row per session.
--
select
ses.sid,
ses.serial#,
ses.username,
ses.osuser,
ses.logon_time,
listagg(sql.sql_text, '') within group (order by sql.piece) sql_text,
(sysdate - ses.sql_exec_start) * 60 * 60 * 24 sql_running_since
from
v$session ses left join
v$sqltext sql on ses.sql_address = sql.address and ses.sql_hash_value = sql.hash_value
where
ses.sid != sys_context('USERENV','SID') and
ses.osuser != 'oracle'
group by
ses.sid,
ses.serial#,
ses.username,
ses.osuser,
ses.logon_time,
(sysdate - ses.sql_exec_start) * 60 * 60 * 24
order by
(sysdate - ses.sql_exec_start) * 60 * 60 * 24 desc nulls last;
set long 32000
set pages 0
set termout off
set lines 9999
set trimspool on
spool c:\temp\tq84_vw_defs.sql
select
'spool ' || lower(owner) || '.' || lower(view_name) || '.sql' || chr(10) ||
'select text from all_views where owner =''' || owner || ''' and view_name = ''' || view_name || ''';' || chr(10) ||
'spool off' || chr(10)
from
all_views
where
owner in ('BI_AT', 'BI');
spool off
@c:\temp\tq84_vw_defs.sql
$del c:\temp\tq84_vw_defs.sql
--
-- Shows the text of an SQL statement if its sql_id is known.
--
set verify off
-- Old Version: Could not cope with statement longer the 4K...
--
-- select listagg(sql_text, '') within group (order by piece)
-- from v$sqltext_with_newlines
-- where sql_id = '&1';
declare
l varchar2(4000);
c char(1);
begin
for s in (select sql_text
from v$sqltext_with_newlines
where sql_id = '&1'
order by piece) loop
for i in 1 .. length(s.sql_text) loop
c := substr(s.sql_text, i, 1);
if c = chr(10) then
dbms_output.put_line(l);
l := '';
elsif length(l) > 3999 then
-- Adjust for width of terminal here.
dbms_output.put_line(l);
l := c;
else
l := l || c;
end if;
end loop;
end loop;
dbms_output.put_line(l);
end;
/
--
-- Creates a graphviz-dot file based on the latest
-- entries in the plan_table (filled by "explain plan
-- for ...") and then produces a graph with dot
--
define temp_dir=c:\temp\
@spool &temp_dir.generated.dot
declare
function grey_if_not_null(txt in varchar2) return varchar2 is/*{*/
begin
if txt is null then
return null;
end if;
return ' <font color="#aaaaaa">(' || txt || ')</font>';
end grey_if_not_null;/*}*/
procedure nodes_with_same_parent(parent_node in number) is/*{*/
last_node number;
begin
for nodes in (
select id, position from (
select id, position,
rank () over (order by timestamp desc) r
from plan_table
where (parent_node is null and parent_id is null) or
(parent_node is not null and parent_id = parent_node)
)
where r = 1
order by position
) loop
if parent_node is not null then
dbms_output.put_line (parent_node || ' -> ' || nodes.id || ' [arrowhead=none];');
end if;
if last_node is not null then
dbms_output.put_line(last_node || ' -> ' || nodes.id || ' [color="#aaaaaa" constraint=false];');
end if;
last_node := nodes.id;
nodes_with_same_parent(nodes.id);
end loop;
end nodes_with_same_parent;/*}*/
function html_encode(txt in varchar2) return varchar2 is
begin
return replace(
replace(txt
, '<', chr(38) || 'lt;')
, '>', chr(38) || 'gt;');
end html_encode;
begin
dbms_output.put_line('digraph G {');
dbms_output.put_line(' node [shape=plaintext fontname="Arial"];');
--First the edges:
-- http://stackoverflow.com/questions/9238672/how-does-a-script-optimally-layout-a-pure-hierarchical-graphviz-dot-graph and
-- https://github.com/ReneNyffenegger/development_misc/tree/master/graphviz/layout/edge_crossing
nodes_with_same_parent(null);
--Then the nodes:
for node in (
select * from (
select statement_id,/*{*/
plan_id,
timestamp,
remarks,
operation,
options,
object_node,
object_owner,
object_name,
object_alias,
object_instance,
object_type,
optimizer,
search_columns,
id,
parent_id,
depth,
position,
cost,
cardinality,
bytes,
other_tag,
partition_start,
partition_stop,
partition_id,
other,
other_xml,
distribution,
cpu_cost,
io_cost,
temp_space,
access_predicates,
filter_predicates,
projection,
time,
qblock_name,
--
rank() over (order by timestamp desc) r/*}*/
from plan_table
)
where r = 1
) loop/*{*/
dbms_output.put_line( node.id || ' [label=<');
dbms_output.put_line('<table border="1" cellborder="0" cellspacing="0">');
dbms_output.put_line('<tr><td align="left">' ||
'<font point-size="12">' || node.operation ||
grey_if_not_null(node.options) ||
'</font></td></tr>');
if node.object_name is not null then/*{*/
dbms_output.put_line('<tr><td>' ||
'<font point-size="12">' || node.object_name ||
grey_if_not_null(node.object_alias) ||
'</font>' ||
case when node.object_instance is not null then
' <font point-size="12" color="#ff8c00" face="Arial Bold">' || node.object_instance || '</font>'
end ||
'</td></tr>'
);
end if;/*}*/
dbms_output.put_line('<tr><td align="left" bgcolor="#aaaaff">' ||
'<font point-size="9">Cost: ' || node.cost || ', bytes: ' || node.bytes || ', card: ' || node.cardinality || ', io: ' || node.io_cost || ', cpu: ' || node.cpu_cost || '</font>' ||
'</td></tr>');
if node.access_predicates is not null then
dbms_output.put_line('<tr><td align="left"><font point-size="9">Acc: ' || html_encode(node.access_predicates) || '</font></td></tr>');
end if;
if node.filter_predicates is not null then
dbms_output.put_line('<tr><td align="left"><font point-size="9">Flt: ' || html_encode(substr(node.filter_predicates, 1, 50)) || '</font></td></tr>');
end if;
if node.projection is not null then
for proj in (
select column_value from table(string_op.strtok(node.projection, ', '))
) loop
dbms_output.put_line('<tr><td align="left"><font point-size="9">Proj: ' || proj.column_value ||'</font></td></tr>');
end loop;
end if;
dbms_output.put_line('</table>');
dbms_output.put_line('>];');
end loop;/*}*/
dbms_output.put_line('}');
end;
/
spool off
set termout on
@dot &temp_dir.generated
-- This script goes along with ./sqlsnape.sql and ../sql_snap
--
-- Ends an SQL snap that started with ./sqlsnaps.sql
--
-- The same functionality, but without ../sql_snap package
-- is offered by sql_snap_t_s.sql/sql_snap_t_e.sql.
--
set tab off
exec sql_snap.end___;
-- This script goes along with ./sqlsnape.sql and ../sql_snap
--
-- It starts an SQL snap, to be ended with sqlsnape.sql
--
-- The same functionality, but without ../sql_snap package
-- is offered by sql_snap_t_s.sql/sql_snap_t_e.sql.
--
exec sql_snap.start_;
--
-- This script �starts� (hence the 's') an
-- SQL snap. The snap is ended with
-- sql_snap_t_e.sql.
--
-- These two script offer the same functionality
-- as sqlsnaps.sql/sqlsnape.sql, but without the
-- ../sql_snap package.
--
drop table tq84_sql_snap;
create table tq84_sql_snap as
select
-- sql_text,
executions,
elapsed_time,
cpu_time,
disk_reads,
buffer_gets,
address,
hash_value
from sys.v_$sqlarea;
--
-- SQL> @to_csv file_with_sqlstatement.sql c:\temp\result.csv
--
-- Executue an SQL-query (select statement) that is stored in
-- the file with the name passed as the first argument (file_with_sqlstatement.sql)
-- and store the result as csv (with ; as seperators)
--
-- Additionally, create a header line with the column names of the
-- result set.
--
-- Needs ./spool.sql and ./spool_off.sql
--
-- TODO: Currently, the SQL statement must not have an ending /
--
-- See also
-- o https://github.com/ReneNyffenegger/development_misc/blob/master/vba/excel/CSV_import.bas and
-- o https://github.com/ReneNyffenegger/development_misc/blob/master/vba/excel/some_data_import.bas
-- for a solution on how to import csv Data into excel.
--
define sqlFile=&1
define csvFile=&2
@spool &csvFile
declare
sql_stmt varchar2(32000) :=
q'{
@&sqlFile
}';
cur number;
cntCols number;
cols dbms_sql.desc_tab;
header varchar2(32000);
selected_columns varchar2(32000);
result sys_refcursor;
v varchar2(5000);
begin
--Remove possible trailing semicolon «;» in sqlFile.
sql_stmt := regexp_replace(sql_stmt, ';\s*$', '');
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, sql_stmt, dbms_sql.native);
dbms_sql.describe_columns(cur, cntCols, cols);
for col in 1 .. cntCols loop
if selected_columns is not null then
selected_columns := selected_columns || '||'',''||';
header := header || ',';
end if;
header := header || cols(col).col_name;
selected_columns := selected_columns || '"' || cols(col).col_name || '"';
end loop;
dbms_sql.close_cursor(cur);
dbms_output.put_line(header);
open result for 'select ' || selected_columns || ' v from (' || sql_stmt || ')';
loop
fetch result into v;
exit when result%notfound;
dbms_output.put_line(v);
end loop;
close result;
end;
/
@spool_off
--
-- Wrap the call of utl_file.fremove into an
-- anonymous block so that exception handling
-- can be used.
--
set verify off
declare
dir_ varchar2(4000) := '&1';
file_ varchar2(4000) := '&2';
begin
utl_file.fremove(dir_, file_);
exception when others then
if sqlcode = -29283 then
dbms_output.put_line('Could not delete file ' || file_ || '!');
elsif sqlcode = -29280 then
dbms_output.put_line('Invalid directory ' || dir_);
else raise;
end if;
end;
/