Oracle SQL
  • LICENSE

Monthly Archives: April 2013

SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier)

Posted on April 29, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 7,931 Page views 13 Comments

Sometimes I get tired of watching unformatted query text from v$sqlarea, dba_hist_sqltext in SQL*Plus, so I decided to include automatic query formatting in my scripts.
I thought that there are many libraries for such purposes on languages which i know, and it’s true, but it turned out that many of them are not appropriate for Oracle.
So I took the most appropriate – perl module SQL::Beautify and corrected it. Now i can share my first solution.

How it looks:
sql_textf

What you need to do it:
1. If you on Windows and you have not install Oracle RDBMS or cygwin, you need to install perl. It can be done for example with ActivePerl or StrawberryPerl, but i recommend cygwin
2. You need to save sql_format_standalone.pl within $SQL_PATH/inc directory.

inc/sql_format_standalone.pl

[sourcecode language=”sql”]
package OraTokenizer;

use warnings;
use strict;

use 5.006002;

use Exporter;

our @ISA = qw(Exporter);

our @EXPORT_OK= qw(tokenize_sql);

our $VERSION= ‘0.24’;

my $re= qr{
(
(?:–)[\ \t\S]* # single line comments
|
(?:<>|<=>|>=|<=|==|=|!=|!|<<|>>|<|>|\|\||\||&&|&|-|\+|\*(?!/)|/(?!\*)|\%|~|\^|\?)
# operators and tests
|
\(\+\)
# oracle join
|
[\[\]\(\),;.] # punctuation (parenthesis, comma)
|
\’\'(?!\’) # empty single quoted string
|
\"\"(?!\"") # empty double quoted string
|
"(?>(?:(?>[^"\\]+)|""|\\.)*)+"
# anything inside double quotes, ungreedy
|
`(?>(?:(?>[^`\\]+)|“|\\.)*)+`
# anything inside backticks quotes, ungreedy
|
‘(?>(?:(?>[^’\\]+)|”|\\.)*)+’
# anything inside single quotes, ungreedy.
|
/\*[\ \t\r\n\S]*?\*/ # C style comments
|
(?:[\w:@]+(?:[.\$](?:\w+|\*)?)*)
# words, standard named placeholders, db.table.*, db.*
|
\n # newline
|
[\t\ ]+ # any kind of white spaces
)
}smx;

sub tokenize_sql {
my ( $query, $remove_white_tokens )= @_;

my @query= $query =~ m{$re}smxg;

if ($remove_white_tokens) {
@query= grep( !/^[\s\n\r]*$/, @query );
}

return wantarray ? @query : \@query;
}

sub tokenize {
my $class= shift;
return tokenize_sql(@_);
}

1;

=pod

=head1 NAME

OraTokenizer – A simple SQL tokenizer.

=head1 VERSION

0.20

=head1 SYNOPSIS

use OraTokenizer qw(tokenize_sql);

my $query= q{SELECT 1 + 1};
my @tokens= OraTokenizer->tokenize($query);

# @tokens now contains (‘SELECT’, ‘ ‘, ‘1’, ‘ ‘, ‘+’, ‘ ‘, ‘1’)

@tokens= tokenize_sql($query); # procedural interface

=head1 DESCRIPTION

OraTokenizer is a simple tokenizer for SQL queries. It does not claim to be
a parser or query verifier. It just creates sane tokens from a valid SQL
query.

It supports SQL with comments like:

— This query is used to insert a message into
— logs table
INSERT INTO log (application, message) VALUES (?, ?)

Also supports C<”>, C<""> and C<\’> escaping methods, so tokenizing queries
like the one below should not be a problem:

INSERT INTO log (application, message)
VALUES (‘myapp’, ‘Hey, this is a ”single quoted string”!’)

=head1 API

=over 4

=item tokenize_sql

use OraTokenizer qw(tokenize_sql);

my @tokens= tokenize_sql($query);
my $tokens= tokenize_sql($query);

$tokens= tokenize_sql( $query, $remove_white_tokens );

C<tokenize_sql> can be imported to current namespace on request. It receives a
SQL query, and returns an array of tokens if called in list context, or an
arrayref if called in scalar context.

=item tokenize

my @tokens= OraTokenizer->tokenize($query);
my $tokens= OraTokenizer->tokenize($query);

$tokens= OraTokenizer->tokenize( $query, $remove_white_tokens );

This is the only available class method. It receives a SQL query, and returns an
array of tokens if called in list context, or an arrayref if called in scalar
context.

If C<$remove_white_tokens> is true, white spaces only tokens will be removed from
result.

=back

=head1 ACKNOWLEDGEMENTS

=over 4

=item

Evan Harris, for implementing Shell comment style and SQL operators.

=item

Charlie Hills, for spotting a lot of important issues I haven’t thought.

=item

Jonas Kramer, for fixing MySQL quoted strings and treating dot as punctuation character correctly.

=item

Emanuele Zeppieri, for asking to fix OraTokenizer to support dollars as well.

=item

Nigel Metheringham, for extending the dollar signal support.

=item

Devin Withers, for making it not choke on CR+LF in comments.

=item

Luc Lanthier, for simplifying the regex and make it not choke on backslashes.

=back

=head1 AUTHOR

Copyright (c) 2007, 2008, 2009, 2010, 2011 Igor Sutton Lopes "<IZUT@cpan.org>". All rights
reserved.

This module is free software; you can redistribute it and/or modify it under
the same terms as Perl itself.

=cut

###################################

package OraBeautify;

use strict;
use warnings;

our $VERSION = 0.04;

use Carp;

# Keywords from SQL-92, SQL-99 and SQL-2003.
use constant KEYWORDS => qw(
ABSOLUTE ACTION ADD AFTER ALL ALLOCATE ALTER AND ANY ARE ARRAY AS ASC
ASENSITIVE ASSERTION ASYMMETRIC AT ATOMIC AUTHORIZATION AVG BEFORE BEGIN
BETWEEN BIGINT BINARY BIT BIT_LENGTH BLOB BOOLEAN BOTH BREADTH BY CALL
CALLED CASCADE CASCADED CASE CAST CATALOG CHAR CHARACTER CHARACTER_LENGTH
CHAR_LENGTH CHECK CLOB CLOSE COALESCE COLLATE COLLATION COLUMN COMMIT
CONDITION CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONSTRUCTOR CONTAINS
CONTINUE CONVERT CORRESPONDING COUNT CREATE CROSS CUBE CURRENT CURRENT_DATE
CURRENT_DEFAULT_TRANSFORM_GROUP CURRENT_PATH CURRENT_ROLE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_TRANSFORM_GROUP_FOR_TYPE CURRENT_USER CURSOR
CYCLE DATA DATE DAY DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFERRABLE
DEFERRED DELETE DEPTH DEREF DESC DESCRIBE DESCRIPTOR DETERMINISTIC
DIAGNOSTICS DISCONNECT DISTINCT DO DOMAIN DOUBLE DROP DYNAMIC EACH ELEMENT
ELSE ELSEIF END EPOCH EQUALS ESCAPE EXCEPT EXCEPTION EXEC EXECUTE EXISTS
EXIT EXTERNAL EXTRACT FALSE FETCH FILTER FIRST FLOAT FOR FOREIGN FOUND FREE
FROM FULL FUNCTION GENERAL GET GLOBAL GO GOTO GRANT GROUP GROUPING HANDLER
HAVING HOLD HOUR IDENTITY IF IMMEDIATE IN INDICATOR INITIALLY INNER INOUT
INPUT INSENSITIVE INSERT INT INTEGER INTERSECT INTERVAL INTO IS ISOLATION
ITERATE JOIN KEY LANGUAGE LARGE LAST LATERAL LEADING LEAVE LEFT LEVEL LIKE
LIMIT LOCAL LOCALTIME LOCALTIMESTAMP LOCATOR LOOP LOWER MAP MATCH MAX
MEMBER MERGE METHOD MIN MINUTE MODIFIES MODULE MONTH MULTISET NAMES
NATIONAL NATURAL NCHAR NCLOB NEW NEXT NO NONE NOT NULL NULLIF NUMERIC
OBJECT OCTET_LENGTH OF OLD ON ONLY OPEN OPTION OR ORDER ORDINALITY OUT
OUTER OUTPUT OVER OVERLAPS PAD PARAMETER PARTIAL PARTITION PATH POSITION
PRECISION PREPARE PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURE PUBLIC RANGE
READ READS REAL RECURSIVE REF REFERENCES REFERENCING RELATIVE RELEASE
REPEAT RESIGNAL RESTRICT RESULT RETURN RETURNS REVOKE RIGHT ROLE ROLLBACK
ROLLUP ROUTINE ROW ROWS PIVOT UNPIVOT XMLTABLE XMLSEQUENCE XMLQUERY
SAVEPOINT SCHEMA SCOPE SCROLL SEARCH SECOND SECTION SEQUENCE
SELECT SENSITIVE SESSION SESSION_USER SET SETS SIGNAL SIMILAR SIZE SMALLINT
SOME SPACE SPECIFIC SPECIFICTYPE SQL SQLCODE SQLERROR SQLEXCEPTION SQLSTATE
SQLWARNING START STATE STATIC SUBMULTISET SUBSTRING SUM SYMMETRIC SYSTEM
SYSTEM_USER TABLE TABLESAMPLE TEMPORARY TEXT THEN TIME TIMESTAMP
TIMEZONE_HOUR TIMEZONE_MINUTE TINYINT TO TRAILING TRANSACTION TRANSLATE
TRANSLATION TREAT TRIGGER TRIM TRUE UNDER UNDO UNION UNIQUE UNKNOWN UNNEST
UNTIL UPDATE UPPER USAGE USER USING VALUE VALUES VARCHAR VARYING VIEW WHEN
WHENEVER WHERE WHILE WINDOW WITH WITHIN WITHOUT WORK WRITE YEAR ZONE
);

sub new {
my ($class, %options) = @_;

my $self = bless { %options }, $class;

# Set some defaults.
$self->{query} = ” unless defined($self->{query});
$self->{spaces} = 4 unless defined($self->{spaces});
$self->{space} = ‘ ‘ unless defined($self->{space});
$self->{break} = "\n" unless defined($self->{break});
$self->{wrap} = {} unless defined($self->{wrap});
$self->{keywords} = [] unless defined($self->{keywords});
$self->{rules} = {} unless defined($self->{rules});
$self->{uc_keywords} = 0 unless defined $self->{uc_keywords};

push @{$self->{keywords}}, KEYWORDS;

# Initialize internal stuff.
$self->{_level} = 0;

return $self;
}

# Add more SQL.
sub add {
my ($self, $addendum) = @_;

$addendum =~ s/^\s*/ /;

$self->{query} .= $addendum;
}

# Set SQL to beautify.
sub query {
my ($self, $query) = @_;

$self->{query} = $query if(defined($query));

return $self->{query};
}

# Beautify SQL.
sub beautify {
my ($self) = @_;

$self->{_output} = ”;
$self->{_level_stack} = [];
$self->{_new_line} = 1;

my $last;

$self->{_tokens} = [ OraTokenizer->tokenize($self->query, 1) ];

while(defined(my $token = $self->_token)) {
my $rule = $self->_get_rule($token);

# Allow custom rules to override defaults.
if($rule) {
$self->_process_rule($rule, $token);
}

elsif($token eq ‘(‘) {
$self->_add_token($token);
$self->_new_line;
push @{$self->{_level_stack}}, $self->{_level};
$self->_over unless $last and uc($last) eq ‘WHERE’;
}

elsif($token eq ‘)’) {
$self->_new_line;
$self->{_level} = pop(@{$self->{_level_stack}}) || 0;
$self->_add_token($token);
$self->_new_line;
}

elsif($token eq ‘,’) {
$self->_add_token($token);
$self->_new_line;
}

elsif($token eq ‘;’) {
$self->_add_token($token);
$self->_new_line;

# End of statement; remove all indentation.
@{$self->{_level_stack}} = ();
$self->{_level} = 0;
}

elsif($token =~ /^(?:SELECT|FROM|WHERE|HAVING)$/i) {
$self->_back unless $last and $last eq ‘(‘;
$self->_new_line;
$self->_add_token($token);
$self->_new_line if($self->_next_token and $self->_next_token ne ‘(‘);
$self->_over;
}

elsif($token =~ /^(?:GROUP|ORDER|LIMIT)$/i) {
$self->_back;
$self->_new_line;
$self->_add_token($token);
}

elsif($token =~ /^(?:BY)$/i) {
$self->_add_token($token);
$self->_new_line;
$self->_over;
}

elsif($token =~ /^(?:UNION|INTERSECT|EXCEPT)$/i) {
$self->_new_line;
$self->_add_token($token);
$self->_new_line;
}

elsif($token =~ /^(?:LEFT|RIGHT|INNER|OUTER|CROSS)$/i) {
$self->_back;
$self->_new_line;
$self->_add_token($token);
$self->_over;
}

elsif($token =~ /^(?:JOIN)$/i) {
if($last and $last !~ /^(?:LEFT|RIGHT|INNER|OUTER|CROSS)$/) {
$self->_new_line;
}

$self->_add_token($token);
}

elsif($token =~ /^(?:AND|OR)$/i) {
$self->_new_line;
$self->_add_token($token);
$self->_new_line;
}

else {
$self->_add_token($token, $last);
}

$last = $token;
}

$self->_new_line;

$self->{_output};
}

# Add a token to the beautified string.
sub _add_token {
my ($self, $token, $last_token) = @_;

if($self->{wrap}) {
my $wrap;

if($self->_is_keyword($token)) {
$wrap = $self->{wrap}->{keywords};
}
elsif($self->_is_constant($token)) {
$wrap = $self->{wrap}->{constants};
}

if($wrap) {
$token = $wrap->[0] . $token . $wrap->[1];
}
}

my $last_is_dot =
defined($last_token) && $last_token eq ‘.’;

if(!$self->_is_punctuation($token) and !$last_is_dot) {
$self->{_output} .= $self->_indent;
}

# uppercase keywords
$token = uc $token
if $self->_is_keyword($token) and $self->{uc_keywords};

$self->{_output} .= $token;

# This can’t be the beginning of a new line anymore.
$self->{_new_line} = 0;
}

# Increase the indentation level.
sub _over {
my ($self) = @_;

++$self->{_level};
}

# Decrease the indentation level.
sub _back {
my ($self) = @_;

–$self->{_level} if($self->{_level} > 0);
}

# Return a string of spaces according to the current indentation level and the
# spaces setting for indenting.
sub _indent {
my ($self) = @_;

if($self->{_new_line}) {
return $self->{space} x ($self->{spaces} * $self->{_level});
}
else {
return $self->{space};
}
}

# Add a line break, but make sure there are no empty lines.
sub _new_line {
my ($self) = @_;

$self->{_output} .= $self->{break} unless($self->{_new_line});
$self->{_new_line} = 1;
}

# Have a look at the token that’s coming up next.
sub _next_token {
my ($self) = @_;

return @{$self->{_tokens}} ? $self->{_tokens}->[0] : undef;
}

# Get the next token, removing it from the list of remaining tokens.
sub _token {
my ($self) = @_;

return shift @{$self->{_tokens}};
}

# Check if a token is a known SQL keyword.
sub _is_keyword {
my ($self, $token) = @_;

return ~~ grep { $_ eq uc($token) } @{$self->{keywords}};
}

# Add new keywords to highlight.
sub add_keywords {
my $self = shift;

for my $keyword (@_) {
push @{$self->{keywords}}, ref($keyword) ? @{$keyword} : $keyword;
}
}

# Add new rules.
sub add_rule {
my ($self, $format, $token) = @_;

my $rules = $self->{rules} ||= {};
my $group = $rules->{$format} ||= [];

push @{$group}, ref($token) ? @{$token} : $token;
}

# Find custom rule for a token.
sub _get_rule {
my ($self, $token) = @_;

values %{$self->{rules}}; # Reset iterator.

while(my ($rule, $list) = each %{$self->{rules}}) {
return $rule if(grep { uc($token) eq uc($_) } @$list);
}

return undef;
}

sub _process_rule {
my ($self, $rule, $token) = @_;

my $format = {
break => sub { $self->_new_line },
over => sub { $self->_over },
back => sub { $self->_back },
token => sub { $self->_add_token($token) },
push => sub { push @{$self->{_level_stack}}, $self->{_level} },
pop => sub { $self->{_level} = pop(@{$self->{_level_stack}}) || 0 },
reset => sub { $self->{_level} = 0; @{$self->{_level_stack}} = (); },
};

for(split /-/, lc $rule) {
&{$format->{$_}} if($format->{$_});
}
}

# Check if a token is a constant.
sub _is_constant {
my ($self, $token) = @_;

return ($token =~ /^\d+$/ or $token =~ /^([‘"`]).*\1$/);
}

# Check if a token is punctuation.
sub _is_punctuation {
my ($self, $token) = @_;

return ($token =~ /^[,;.]$/);
}

1;

=pod

=head1 NAME

OraBeautify – Beautify SQL statements by adding line breaks indentation

=head1 SYNOPSIS

my $sql = OraBeautify->new;

$sql->query($sql_query);

my $nice_sql = $sql->beautify;

=head1 DESCRIPTION

Beautifies SQL statements by adding line breaks indentation.

=head1 METHODS

=over 4

=item B<new>(query => ”, spaces => 4, space => ‘ ‘, break => "\n", wrap => {})

Constructor. Takes a few options.

=over 4

=item B<query> => ”

Initialize the instance with a SQL string. Defaults to an empty string.

=item B<spaces> => 4

Number of spaces that make one indentation level. Defaults to 4.

=item B<space> => ‘ ‘

A string that is used as space. Default is an ASCII space character.

=item B<break> => "\n"

String that is used for linebreaks. Default is "\n".

=item B<wrap> => {}

Use this if you want to surround certain tokens with markup stuff. Known token
types are "keywords" and "constants" for now. The value of each token type
should be an array with two elements, one that is placed before the token and
one that is placed behind it. For example, use make keywords red using terminal
color escape sequences.

{ keywords => [ "\x1B[0;31m", "\x1B[0m" ] }

=item B<uc_keywords> => 1|0

When true (1) all SQL keywords will be uppercased in output. Default is false (0).

=back

=item B<add>($more_sql)

Appends another chunk of SQL.

=item B<query>($query)

Sets the query to the new query string. Overwrites anything that was added with
prior calls to B<query> or B<add>.

=item B<beautify>

Beautifies the internally saved SQL string and returns the result.

=item B<add_keywords>($keyword, $another_keyword, \@more_keywords)

Add any amount of keywords of arrays of keywords to highlight.

=item B<add_rule>($rule, $token)

Add a custom formatting rule. The first argument is the rule, a string
containing one or more commands (explained below), separated by dashes. The
second argument may be either a token (string) or a list of strings. Tokens are
grouped by rules internally, so you may call this method multiple times with
the same rule string and different tokens, and the rule will apply to all of
the tokens.

The following formatting commands are known at the moment:

=over 4

=item B<token> – insert the token this rule applies to

=item B<over> – increase indentation level

=item B<back> – decrease indentation level

=item B<break> – insert line break

=item B<push> – push current indentation level to an internal stack

=item B<pop> – restore last indentation level from the stack

=item B<reset> – reset internal indentation level stack

=back

B<push>, B<pop> and B<reset> should be rarely needed.

B<NOTE>:
Custom rules override default rules. Some default rules do things that
can’t be done using custom rules, such as changing the format of a token
depending on the last or next token.

B<NOTE>:
I’m trying to provide sane default rules. If you find that a custom
rule of yours would make more sense as a default rule, please create a ticket.

=back

=head1 BUGS

Needs more tests.

Please report bugs in the CPAN bug tracker.

This module is not complete (known SQL keywords, special formatting of
keywords), so if you want see something added, just send me a patch.

=head1 COPYRIGHT

Copyright (C) 2009 by Jonas Kramer. Published under the terms of the Artistic
License 2.0.

=cut

########################################
package main;
use strict;

open (SQL, "<", $ARGV[0]) || die (‘File not found!’);
my $query = join("\n",<SQL>);

my $beautifier = OraBeautify->new;
$beautifier -> add_keywords(qw{
pivot unpivot
model dimension measures rules
xmltable xmlsequence columns});

$beautifier->query(
$query,
spaces => 4,
space => ‘ ‘,
break => "\n",
wrap => {‘$’,’$’}
);

my $nice_sql = $beautifier->beautify;

print $nice_sql ."\n";
__END__

[/sourcecode]

[collapse]

3. Create empty directory “tmp” within $SQL_PATH
4. Now you can use it for example like i did it in sql_textf.sql:
sql_textf.sql

[sourcecode language=”sql”]
set timing off head off
col qtext format a150
prompt ################################ Original query text: ################################################;
spool tmp/to_format.sql
select
coalesce(
(select sql_fulltext from v$sqlarea a where a.sql_id=’&1′)
, (select sql_text from dba_hist_sqltext a where a.sql_id=’&1′ and dbid=(select dbid from v$database))
) qtext
from dual
;
spool off

prompt ################################ Formatted query text #################################################;
host perl inc/sql_format_standalone.pl tmp/to_format.sql
prompt ################################ Formatted query text End #############################################;
set termout on head on
[/sourcecode]

[collapse]

I also use it in other scripts, like sqlid.sql:

[sourcecode language=”sql”]
@inc/input_vars_init;

REM ############### COMMON FORMATTING #######################
col SQL_ID for a13
col sql_child_number head CH# for 999
col SQL_PROFILE head PROFILE for a19
—————————————–
— params check:
set termout off timing off
def _sqlid=&1
col _child new_val _child noprint
select
case
when translate(‘&2′,’x0123456789′,’x’) is null
then nvl(‘&2′,’%’)
else ‘%’
end "_CHILD"
from dual;
—————————————–
set termout on

prompt ####################################################################################################;
prompt # Show SQL text, child cursors and execution stats for SQLID &1 child &2
prompt ####################################################################################################;

REM ################### SHOW SQL TEXT ############################
@sql_textf &_sqlid

REM ################### SHOW V$SQL ##############################
col proc_name for a30
col P_schema for a20

select
s.sql_id
,s.CHILD_NUMBER sql_child_number
,s.address parent_handle
,s.child_address object_handle
,s.PLAN_HASH_VALUE plan_hv
,s.hash_value hv
,s.SQL_PROFILE sql_profile
,decode(s.EXECUTIONS,0,0, s.ELAPSED_TIME/1e6/s.EXECUTIONS) elaexe
,s.EXECUTIONS cnt
,s.FETCHES fetches
,s.END_OF_FETCH_COUNT end_of_fetch_count
,s.FIRST_LOAD_TIME first_load_time
,s.PARSE_CALLS parse_calls
,decode(s.executions,0,0, s.DISK_READS /s.executions) disk_reads
,decode(s.executions,0,0, s.BUFFER_GETS /s.executions) buffer_gets
,decode(s.executions,0,0, s.DIRECT_WRITES /s.executions) direct_writes
,decode(s.executions,0,0, s.APPLICATION_WAIT_TIME/1e6/s.executions) app_wait
,decode(s.executions,0,0, s.CONCURRENCY_WAIT_TIME/1e6/s.executions) concurrency
,decode(s.executions,0,0, s.USER_IO_WAIT_TIME /1e6/s.executions) io_wait
,decode(s.executions,0,0, s.PLSQL_EXEC_TIME /1e6/s.executions) plsql_t
,decode(s.executions,0,0, s.java_exec_time /1e6/s.executions) java_exec_t
,s.ROWS_PROCESSED row_processed
,s.OPTIMIZER_MODE opt_mode
,s.OPTIMIZER_COST cost
,s.OPTIMIZER_ENV_HASH_VALUE env_hash
,s.PARSING_SCHEMA_NAME P_schema
,decode(s.executions,0,0, s.CPU_TIME/1e6/s.executions) CPU_TIME
,s.PROGRAM_ID
,(select object_name from dba_objects o where o.object_id=s.PROGRAM_ID) proc_name
,s.PROGRAM_LINE# proc_line
from v$sql s
where
sql_id = (‘&_sqlid’)
and child_number like ‘&_child’
order by
sql_id,
hash_value,
child_number
/
REM ##################### END V$SQL ##############################

REM ################### PLSQL OBJECT ##############################
col owner for a10
col object_name for a30
col text for a120

select
a.SQL_ID,a.SQL_PROFILE
,p.owner,p.object_name
,s.line
,rtrim(s.text,chr(10)) text
from
v$sqlarea a
left join dba_procedures p
on a.PROGRAM_ID=p.OBJECT_ID
left join dba_source s
on p.owner=s.owner
and p.OBJECT_NAME=s.name
and s.line between a.PROGRAM_LINE#-5 and a.PROGRAM_LINE#+5
where a.SQL_ID=’&_sqlid’
/
REM ################### EXECUTIONS IN SQL_MONITOR ######################
@if "’&_O_RELEASE’>’11.2’" then

col error_message for a40
@rtsm/execs "&_sqlid" "&_child"

/* end if */
REM ########################### clearing ############################
col SQL_PROFILE clear
col owner clear
col object_name clear
col text clear
col error_message clear
@inc/input_vars_undef;
[/sourcecode]

[collapse]

SQL*Plus

SQL*Plus tips #4: Branching execution

Posted on April 17, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 3,966 Page views Leave a comment

Today I’ll show a trick how we can use branching execution of SQLPlus scripts in SQL*Plus.
Although I previously showed the conditional execution of scripts and it really can be used for branching, but today I’ll show how to do it without splitting the script into several smaller scripts. In contrast to the conditional script execution, I’ll use a different method.

It is very simple, as usual – if you want to execute only one part of script, you can just comment out all unnecessary. So depending on the conditions, we can execute a script which will start a comment.

Suppose we need to create a script, which, depending on the input parameter will be required to execute a specific query.
See how this can be done:
1. “test.sql”:

def param = &1

@if &param=1
   select 1 from dual;
/* end_if */

@if &param=2
   select 2 from dual;
/* end_if */

@if &param=3
   select 3 from dual;
/* end_if */

2. “if.sql”:

col do_next new_val do_next noprint;
select 
      case 
         when &1 then 'inc/null'
         else 'inc/comment_on'
      end as do_next
from dual;
@&do_next

3. “inc/comment_on.sql” contains only 2 chars:

/*

4. “inc/null.sql” is the same as in the previous examples – just empty file.

Ok, lets test it:

SQL> @test 1

         1
----------
         1


SQL> @test 2


         2
----------
         2


SQL> @test 3


         3
----------
         3

As you see, we got what we wanted. Please note that we have to close the multiline comments in the right places only(/* end_if */). So we cannot use in these parts another “*/”. But you can use it in another child scripts.

Same way we can make an analogue of switch/case:
“test2.sql”:

@switch &1

   @when 1 then
      select 1 from dual;
   /* end when */

   @when 2 then
      select 2 from dual;
   /* end when */

   @when 3 then
      select 3 from dual;
   /* end when */

/* end switch */

switch.sql

[sourcecode language=”sql”]
def switch_param=&1
[/sourcecode]

[collapse]

when.sql

[sourcecode language=”sql”]
col do_next new_val do_next noprint;
select
case
when &1 = &switch_param then ‘inc/null’
else ‘inc/comment_on’
end as do_next
from dual;
@&do_next
[/sourcecode]

[collapse]

Example:

SQL> @test2 2

         2
----------
         2

SQL> @test2 3

         3
----------
         3
SQL*Plus

SQL*Plus tips #3: Iterators and recursions

Posted on April 9, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 2,955 Page views 8 Comments

We all used to iterators, recursions and branching in programming, but sql*plus does not have such commands. Today I will show how to do iterators/recusions.

Suppose we want to call a script 3 times. So it would be convenient if we can do it like:

@iterate 3 @some_script

It is very easy: We can recursively call the script by reducing variable until it is greater than zero. and if a variable is zero, then call an empty “NULL” script.
iterate.sql:

-- initializing input parameters as i showed in previous tips:
@inc/input_params_init.sql;
-- for example max number of input parameters = 14:
def _INIT_PARAMS = '"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
def _ITER = "&1"

prompt ------   iteration &_ITER start: "@&2  -----------;
set termout off


col _SCRIPT    new_val _SCRIPT   noprint;
col _CONTINUE  new_val _CONTINUE noprint;
col _PARAMS    new_val _PARAMS   noprint;
select 
   replace('"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
          ,'${ITER}'
          ,'&_ITER'
          )          as "_PARAMS"
  ,case 
      when &1 > 0 then 'iterate '||(&1 - 1)
      else 'inc/null'
   end               as "_CONTINUE"
  ,case 
      when &1 > 0 then '&2' 
      else 'inc/null'
   end               as "_SCRIPT"
from dual;
set termout on

@&_script            &_PARAMS
@&_CONTINUE &_SCRIPT &_INIT_PARAMS

As you see i also added option to pass iteration_number as input parameter, so we can call it like: @iterate 3 script.sql ${ITER}
And null.sql in “inc” directory is just empty file.

Lets test it:

SQL> $cat test.sql
select &1 a,'&2' b,'&3' c from dual;

SQL> @iterate 3 test.sql 1 2 3
------   iteration 3 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 2 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 1 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 0 start: "@test.sql  -----------

SQL> @iterate 2 test.sql 1 2 ${ITER}
------   iteration 2 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 2

------   iteration 1 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 1

------   iteration 0 start: "@test.sql  -----------

Note that last string “iteration 0 start” just means that it is last empty call. I made this only for showing how we can do postprocessing after all iterations.
Ok, it works good, but with a little modification we can also add option to call such script with list of values too:

final iterate.sql

[sourcecode language=”sql”]
@inc/input_params_init.sql;

set termout off
def _INIT_PARAMS = ‘"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"’
col _ITER_CURR new_val _ITER_CURR noprint
col _ITER_NEXT new_val _ITER_NEXT noprint
select
case
— simple N iterations:
when translate(‘&1′,’x0123456789′,’x’) is null
then ‘&1’
— list iteration:
when substr(‘&1’,1,1)='(‘
then decode( instr(‘&1′,’,’)
,0,substr(‘&1’,2,length(‘&1’)-2)
,substr(‘&1’,2,instr(‘&1′,’,’)-2)
)
end "_ITER_CURR",
case
when translate(‘&1′,’x0123456789′,’x’) is null
then
case
when ‘&1’>1 then ‘iterate ‘||(‘&1’ – 1)
else ‘inc/null’
end
when substr(‘&1’,1,1)='(‘
then
case
when instr(‘&1′,’,’)=0 or ‘&1’='()’ then ‘inc/null’
else ‘iterate ‘
||'(‘
||decode( instr(‘&1′,’,’)
,0,’)’
,substr(‘&1’,instr(‘&1′,’,’)+1)
)
end
end "_ITER_NEXT"
from dual;

set termout on
prompt ****** iteration &_ITER_CURR start: "@&2 ***********;
set termout off

col _SCRIPT new_val _SCRIPT noprint;
col _PARAMS new_val _PARAMS noprint;
select
replace(‘"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"’
,’${ITER}’
,’&_ITER_CURR’
) as "_PARAMS"
,case
when nvl(‘&_ITER_CURR’,0) in (‘0′,'()’) then ‘inc/null’
else ‘&2’
end as "_SCRIPT"
from dual;
set termout on

@&_script &_PARAMS
@&_ITER_NEXT &_SCRIPT &_INIT_PARAMS
[/sourcecode]

[collapse]

See how it works:

-- iterate by list of values  (7,3,10):
SQL> @iterate (7,3,10) test.sql 1 2 ${ITER}
******   iteration 7 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 7

******   iteration 3 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 3

******   iteration 10 start: "@test.sql  ***********

         A B C
---------- - --
         1 2 10

-- now with simple 2 iterations:
SQL> @iterate 2 test.sql 1 2 ${ITER}
******   iteration 2 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 2

******   iteration 1 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 1

So if you call script with “iterate N …” it will iterate N times, and if you specify “iterate (X,Y,…,Z)” it will iterate by this list.

PS. About a branching i will wrote later…

SQL*Plus

SQL*Plus tips. #2

Posted on April 2, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 3,551 Page views 10 Comments

I think you know the famous print_table procedure by Tom Kyte. It is really great, but a little hard(it requires create procedure or place it in anonymous block) and in last oracle versions we can do same with one simple query with xmltable/xmlsequence:

SQL> select *
  2  from
  3     xmltable( '/ROWSET/ROW/*'
  4               passing xmltype(cursor(select * from hr.employees where rownum<3))
  5               columns
  6                  col varchar2(100) path 'name()'
  7                 ,val varchar2(100) path '.'
  8        );

COL                            VAL
------------------------------ ------------------------------------------------------
EMPLOYEE_ID                    198
FIRST_NAME                     Donald
LAST_NAME                      OConnell
EMAIL                          DOCONNEL
PHONE_NUMBER                   650.507.9833
HIRE_DATE                      21-JUN-07
JOB_ID                         SH_CLERK
SALARY                         2600
MANAGER_ID                     124
DEPARTMENT_ID                  50
EMPLOYEE_ID                    199
FIRST_NAME                     Douglas
LAST_NAME                      Grant
EMAIL                          DGRANT
PHONE_NUMBER                   650.507.9844
HIRE_DATE                      13-JAN-08
JOB_ID                         SH_CLERK
SALARY                         2600
MANAGER_ID                     124
DEPARTMENT_ID                  50

20 rows selected.

It is very easy, but for conveniency we need to add “rownum”:

SQL> select *
  2  from
  3     xmltable( 'for $a at $i in /ROWSET/ROW
  4                   ,$r in $a/*
  5                     return element ROW{
  6                                       element ROW_NUM{$i}
  7                                      ,element COL_NAME{$r/name()}
  8                                      ,element COL_VALUE{$r/text()}
  9                                      }'
 10               passing xmltype(cursor(select * from hr.employees where rownum<3))
 11               columns
 12                  row_num   int
 13                 ,col_name  varchar2(30)
 14                 ,col_value varchar2(100)
 15        );

   ROW_NUM COL_NAME                       COL_VALUE
---------- ------------------------------ ------------------------------------------
         1 EMPLOYEE_ID                    198
         1 FIRST_NAME                     Donald
         1 LAST_NAME                      OConnell
         1 EMAIL                          DOCONNEL
         1 PHONE_NUMBER                   650.507.9833
         1 HIRE_DATE                      21-JUN-07
         1 JOB_ID                         SH_CLERK
         1 SALARY                         2600
         1 MANAGER_ID                     124
         1 DEPARTMENT_ID                  50
         2 EMPLOYEE_ID                    199
         2 FIRST_NAME                     Douglas
         2 LAST_NAME                      Grant
         2 EMAIL                          DGRANT
         2 PHONE_NUMBER                   650.507.9844
         2 HIRE_DATE                      13-JAN-08
         2 JOB_ID                         SH_CLERK
         2 SALARY                         2600
         2 MANAGER_ID                     124
         2 DEPARTMENT_ID                  50

20 rows selected.

Now we can create simple script for it with formatting:

print_table.sql

[sourcecode language=”sql”]
— show output
set termout on
— but without echo
set echo off
— without newpage on start:
set embedded on
— scrolling control
set pause on
— two lines between rows:
set newpage 2
— text for prompt after each page:
set pause "Press Enter to view next row…"
— new page on new "row_num"
break on row_num skip page

— main query:
select *
from
xmltable( ‘for $a at $i in /ROWSET/ROW
,$r in $a/*
return element ROW{
element ROW_NUM{$i}
,element COL_NAME{$r/name()}
,element COL_VALUE{$r/text()}
}’
passing xmltype(cursor( &1 ))
columns
row_num int
,col_name varchar2(30)
,col_value varchar2(100)
);
— disabling pause and breaks:
set pause off
clear breaks
[/sourcecode]

[collapse]

Usage example:print_table example
As you see script works fine, but it is require to pass query as parameter, though sometimes it is not so convenient. For example if we want started query and later decided to show it with print_table. In that case we can create scripts with tricks from previous part:
print_last.sql

[sourcecode language=”sql”]
store set settings.sql replace
— saving previous query:
save tmp.sql replace

— OS-dependent removing trailing slash from file, choose one:
— 1. for *nix through head:
!head -1 tmp.sql >tmp2.sql

— 2. for for *nix through grep:
–!grep -v tmp.sql >tmp2.sql

— 3. for windows without grep and head:
— $cmd /C findstr /v /C:"/" tmp.sql > tmp2.sql

— 4. for windows with "head"(eg from cygwin)
–$cmd /C head -1 tmp.sql > tmp2.sql

— 5. for windows with "grep":
–$cmd /C grep -v "/" tmp.sql > tmp2.sql

— same setting as in print_table:
set termout on echo off embedded on pause on newpage 2
set pause "Press Enter to view next row…"
break on row_num skip page

— main query:
select *
from
xmltable( ‘for $a at $i in /ROWSET/ROW
,$r in $a/*
return element ROW{
element ROW_NUM{$i}
,element COL_NAME{$r/name()}
,element COL_VALUE{$r/text()}
}’
passing dbms_xmlgen.getxmltype(
q'[
@tmp2.sql
]’
)
columns
row_num int
,col_name varchar2(30)
,col_value varchar2(100)
);
— disabling pause and breaks:
set pause off
clear breaks
@settings.sql
[/sourcecode]

[collapse]

Example:

[sourcecode language=”sql”]
SQL> select * from hr.employees where rownum<3;

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
———– ——————– ————————- ————————- ——————– ——— ———- ———- ————– ———- ————-
198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK 2600 124 50
199 Douglas Grant DGRANT 650.507.9844 13-JAN-08 SH_CLERK 2600 124 50

Elapsed: 00:00:00.01
SQL> @print_last
Wrote file settings.sql
Wrote file tmp.sql

ROW_NUM COL_NAME COL_VALUE
———- —————————— —————————————————————————————————-
1 EMPLOYEE_ID 198
FIRST_NAME Donald
LAST_NAME OConnell
EMAIL DOCONNEL
PHONE_NUMBER 650.507.9833
HIRE_DATE 21-JUN-07
JOB_ID SH_CLERK
SALARY 2600
MANAGER_ID 124
DEPARTMENT_ID 50
Press Enter to view next row…
[/sourcecode]

[collapse]

PS. if you will use xmltype(cursor(…)) on versions less that 11.2.0.3 you can get errors with xml rewriting. In this case you need to disable it:

alter session set events '19027 trace name context forever, level 0x1';

Update:
Vladimir Przyjalkowski rightly pointed out that such approach will be suboptimal in case of big amount of data:

A nice toy, but i’m sure it will not tolerate big data.

It is absolutely true, because at first, “xmltype(cursor(…))” aggregated data as xmltype, and only then xmltable returns data.
But I use print_table for small amount of data only, and in case of when i want to see sample of data, i usually add limitation by rownum, which also convenient because it automatically changes optimizer mode with enabled parameter “_optimizer_rownum_pred_based_fkr” (it is default):

SQL> @param_ rownum

NAME                               VALUE   DEFLT  TYPE     DESCRIPTION
---------------------------------- ------- ------ -------- ------------------------------------------------------
_optimizer_rownum_bind_default     10      TRUE   number   Default value to use for rownum bind
_optimizer_rownum_pred_based_fkr   TRUE    TRUE   boolean  enable the use of first K rows due to rownum predicate
_px_rownum_pd                      TRUE    TRUE   boolean  turn off/on parallel rownum pushdown optimization

However, we can easily change the our query to make it optimally with a lot of data too:

select row_num
      ,t2.*
from 
   (select rownum row_num
         , column_value x
    from table(xmlsequence(cursor( &1 )))
   ) t1
  ,xmltable( '/ROW/*'
             passing t1.x
             columns
                col_num for ordinality
               ,col_name  varchar2(30) path 'name()'
               ,col_value varchar2(100) path '.'
      )(+) t2;

Lets test it with pipelined function which will pipe rows infinitely and log count of fetched rows:

-- drop table xt_log purge;
-- drop function f_infinite;

create table xt_log(n int);

create function f_infinite 
   return sys.ku$_objnumset pipelined 
as
   i int:=0;
   pragma autonomous_transaction;
begin
   loop 
      i:=i+1;
      insert into xt_log values(i);
      commit;
      pipe row(i);
   end loop;

exception 
   when NO_DATA_NEEDED then
      commit;
end;
/

New version of print_table: print_table2.sql

[sourcecode language=”sql”]
— set arraysize for minimal value, so sqlplus did not fetch extra rows:
set arraysize 2
— show output
set termout on
— but without echo
set echo off
— without newpage on start:
set embedded on
— scrolling control
set pause on
— two lines between rows:
set newpage 2
— text for prompt after each page:
set pause "Press Enter to view next row…"
— new page on new "row_num"
break on row_num skip page

— main query:
select row_num
,t2.*
from
(select rownum row_num
, column_value x
from table(xmlsequence(cursor( &1 )))
) t1
,xmltable( ‘/ROW/*’
passing t1.x
columns
col_num for ordinality
,col_name varchar2(30) path ‘name()’
,col_value varchar2(100) path ‘.’
)(+) t2;
— disabling pause and breaks:
set pause off
clear breaks
[/sourcecode]

[collapse]

And look final test:

SQL> select * from xt_log;

no rows selected

SQL> @print_table2 "select * from table(f_infinite)"
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         1          1 COLUMN_VALUE                   1
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         2          1 COLUMN_VALUE                   2
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         3          1 COLUMN_VALUE                   3
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         4          1 COLUMN_VALUE                   4
Press Enter to view next row...

   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
         5          1 COLUMN_VALUE                   5
Press Enter to view next row...


6 rows selected.
-------------------------------
SQL> select * from xt_log;

         N
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

So we got what we wanted!

PS. Full scripts:
1. Simple: print_table.sql
2. For big queries: print_table2.sql

oracle SQL*Plus
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro Alumni

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • Oracle Telegram Bot
  • Partition Pruning and Global Indexes
  • Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
  • Interval Search Series: Simplified, Advanced, and Custom Solutions
  • Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Popular posts

Recent Comments

  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Optimizing Date Range Queries – Part 1
  • Oracle SQL | Interval Search Series: Simplified, Advanced, and Custom Solutions on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 2. Dynamic Range Segmentation – Simplified on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin
©Sayan Malakshinov. Oracle SQL