A simple SQL*Plus parameter parser

3 Replies

Português/English

One of the things I like to do as a DBA is to write scripts to facilitate my daily work. Any experienced DBA has his own scripts toolset that is being built over time. Writing scripts help you to automate part of your job and also allow you to learn a lot.

Usually the scripts require or allow to pass some parameter values. For example, if a script show information about tables, I could build it to accept an OWNER and a TABLE_NAME and then show only those tables from that specific owner and with that kind of name.

My old scripts used to accept the parameters by using the ACCEPT SQL*Plus statement, like this:

accept table prompt 'Table Name: '

But this forces me to answer something that not always I want to. What if, for example, I want to see all the tables? I would need to press ENTER to bypass the parameter.

Well, recently I am changing my scripts and building new ones with a different approach: using command line parameters, that could be informed or not.

For example, I could call the script that show tables with a first parameter being the owner, and the second being the table. It would be called this way:

SQL> @tables HR EMP%

In this example I am asking to show the tables that starts with EMP from owner HR.

But there is a caveat in this approach: I need to pass the parameters in the exact order. Moreover, what if I want to define just the second parameter without providing the first?

This type of issue inspired me to write a short and simple script to parse SQL*Plus parameters. This is what I am presenting in this post.

The script simply process all the parameters received in the form “parameter=value” and define SQL*Plus variables with these values, that could then be used in your script. For example, if you provide a parameter as “SHOW=Y”, after the parser script to be executed you will have a variable SHOW with the value “Y”.

This allows you to provide parameters in any arbitrary order for your scripts.

In our tables script, I could provide just the table by calling that this way:

SQL> @tables table=EMP%

Of course, the script should call the parser to perform the “magic”, and then filter the tables with the new “&table” variable.

No more speech, let me show you the parser script and a sample of how to use it.

$cat parser.sql
/*
Parameter parser

This script transform all the parameters (&1-&16) into SQL*Plus variables
It allows you to pass parameters in no specific order in the format PAR=val

Created by Eduardo Claro, 2018-8-15
Last changes on 2018-03-20

Example: if you run the script and the values of &1 is "VAR1='X'", it will define the SQL*Plus variable VAR1 to 'X'

For this to work properly, the number variables should be pre-defined as NULL in the login.sql
*/

store set set.tmp replace
set term off
set feed off ver off echo off head off timing off
spool parser.tmp REPLACE
select
	case when q'!&1!'  is not null then q'!define &1!'  end || chr(10) ||
	case when q'!&2!'  is not null then q'!define &2!'  end || chr(10) ||
	case when q'!&3!'  is not null then q'!define &3!'  end || chr(10) ||
	case when q'!&4!'  is not null then q'!define &4!'  end || chr(10) ||
	case when q'!&5!'  is not null then q'!define &5!'  end || chr(10) ||
	case when q'!&6!'  is not null then q'!define &6!'  end || chr(10) ||
	case when q'!&7!'  is not null then q'!define &7!'  end || chr(10) ||
	case when q'!&8!'  is not null then q'!define &8!'  end || chr(10) ||
	case when q'!&9!'  is not null then q'!define &9!'  end || chr(10) ||
	case when q'!&10!' is not null then q'!define &10!' end || chr(10) ||
	case when q'!&11!' is not null then q'!define &11!' end || chr(10) ||
	case when q'!&12!' is not null then q'!define &12!' end || chr(10) ||
	case when q'!&13!' is not null then q'!define &13!' end || chr(10) ||
	case when q'!&14!' is not null then q'!define &14!' end || chr(10) ||
	case when q'!&15!' is not null then q'!define &15!' end || chr(10) ||
	case when q'!&16!' is not null then q'!define &16!'end statement
from dual;
spool off
@parser.tmp
@set.tmp
set term on

As you can see, the script simply creates some “DEFINE” statements and then executes them.

One important thing: in order for this to work properly, all the SQL*Plus variables from &1 until &16 need to be initially defined. You can do this by including in your login.sql script the following rows (as in SQL*Plus tips. #1):

-- Reset parameters
set termout off
COLUMN 1 NEW_VALUE 1 noprint
COLUMN 2 NEW_VALUE 2 noprint
COLUMN 3 NEW_VALUE 3 noprint
COLUMN 4 NEW_VALUE 4 noprint
COLUMN 5 NEW_VALUE 5 noprint
COLUMN 6 NEW_VALUE 6 noprint
COLUMN 7 NEW_VALUE 7 noprint
COLUMN 8 NEW_VALUE 8 noprint
COLUMN 9 NEW_VALUE 9 noprint
COLUMN 10 NEW_VALUE 10 noprint
COLUMN 11 NEW_VALUE 11 noprint
COLUMN 12 NEW_VALUE 12 noprint
COLUMN 13 NEW_VALUE 13 noprint
COLUMN 14 NEW_VALUE 14 noprint
COLUMN 15 NEW_VALUE 15 noprint
COLUMN 16 NEW_VALUE 16 noprint
SELECT '' "1", '' "5", '' "9",  '' "13"
      ,'' "2", '' "6", '' "10", '' "14"
      ,'' "3", '' "7", '' "11", '' "15"
      ,'' "4", '' "8", '' "12", '' "16"
FROM dual;
set termout on;

Here is the tables script that shows how to use the parser:

$cat tables.sql
-- Define the default parameters values
define OWNER=""
define TABLE=""

-- Call the parser to set the parameters received
@@parser

select owner, table_name, tablespace_name, temporary
from dba_tables
where owner like nvl(upper('&OWNER'),owner)
and table_name like nvl(upper('&TABLE'),table_name)
/

In the upcoming days I will present some interesting scripts I created that use this parser.

That’s it! I hope you enjoy 😉 .

Definindo parametros para scripts SQL*Plus

Português/English

Uma das coisas que eu gosto de fazer como DBA é escrever scripts que facilitam meu trabalho diário. Qualquer DBA experiente tem seu próprio conjunto de scripts que vai sendo construído ao longo do tempo. Escrever scripts ajuda a automatizar parte do trabalho diário e também nos permite aprender bastante.

Normalmente os scripts exigem ou permitem que se passe alguns valores como parâmetros. Por exemplo, se um script mostra informações de tabelas, eu poderia construí-lo para aceitar um OWNER e um TABLE_NAME e então mostrar somente as tabelas daquele “owner” específico e com aquele tipo de nome.

Meus scripts antigos aceitam parâmetros usando o comando ACCEPT do SQL*Plus, assim:

accept table prompt 'Table Name: '

Mas isto me força a responder algo que nem sempre quero. Por exemplo, e se eu quiser ver todas as tabelas? Eu teria que pressionar ENTER para ignorar o parâmetro.

Ultimamente eu tenho alterado meus scripts e escrito novos com uma abordagem diferente: usando parâmetros na linha de comando, que podem ser informados ou não.

Por exemplo, eu poderia chamar o script que mostra as tabelas com um primeiro parâmetro sendo o owner, e o segundo sendo a tabela. Ele seria chamado assim:

SQL> @tables HR EMP%

Neste exemplo estou pedindo pra ver as tabelas que começam com EMP do owner HR.

Mas há um problema com esta abordagem: eu preciso passar os parâmetros na ordem certa. Mais, como fazer se eu quiser definir apenas o segundo parâmetro sem fornecer o primeiro?

Este tipo de problema me inspirou para escrever um script (parser) simples e curto que simplesmente define parâmetros no SQL*Plus. É isto que estou apresentando neste post.

O script simplesmente processa todos os parâmetros recebidos na forma “parâmetro=valor” e define variáveis SQL*Plus com estes valores, que podem em seguida ser usadas pelo seu script. Por exemplo, se você fornece um parâmetro “SHOW=Y”, você terá uma variável SHOW com o valor “Y”.

Isto permite que os parâmetros sejam passados em qualquer ordem para os seus scripts.

Em nosso script de tabelas, eu poderia fornecer simplesmente a tabela chamando-o assim:

SQL> @tables table=EMP%

Claro, o script precisa chamar o parser para fazer a “mágica”, e então filtrar as tabelas com a variável “&table”.

Sem mais conversa, deixe-me mostrar o script parser e um exemplo de como usá-lo.

$cat parser.sql
/*
Parameter parser

This script transform all the parameters (&1-&16) into SQL*Plus variables
It allows you to pass parameters in no specific order in the format PAR=val

Created by Eduardo Claro, 2018-8-15
Last changes on 2018-03-20

Example: if you run the script and the values of &1 is "VAR1='X'", it will define the SQL*Plus variable VAR1 to 'X'

For this to work properly, the number variables should be pre-defined as NULL in the login.sql
*/

store set set.tmp replace
set term off
set feed off ver off echo off head off timing off
spool parser.tmp REPLACE
select
	case when q'!&1!'  is not null then q'!define &1!'  end || chr(10) ||
	case when q'!&2!'  is not null then q'!define &2!'  end || chr(10) ||
	case when q'!&3!'  is not null then q'!define &3!'  end || chr(10) ||
	case when q'!&4!'  is not null then q'!define &4!'  end || chr(10) ||
	case when q'!&5!'  is not null then q'!define &5!'  end || chr(10) ||
	case when q'!&6!'  is not null then q'!define &6!'  end || chr(10) ||
	case when q'!&7!'  is not null then q'!define &7!'  end || chr(10) ||
	case when q'!&8!'  is not null then q'!define &8!'  end || chr(10) ||
	case when q'!&9!'  is not null then q'!define &9!'  end || chr(10) ||
	case when q'!&10!' is not null then q'!define &10!' end || chr(10) ||
	case when q'!&11!' is not null then q'!define &11!' end || chr(10) ||
	case when q'!&12!' is not null then q'!define &12!' end || chr(10) ||
	case when q'!&13!' is not null then q'!define &13!' end || chr(10) ||
	case when q'!&14!' is not null then q'!define &14!' end || chr(10) ||
	case when q'!&15!' is not null then q'!define &15!' end || chr(10) ||
	case when q'!&16!' is not null then q'!define &16!'end statement
from dual;
spool off
@parser.tmp
@set.tmp
set term on

Como você pode ver, o script simplesmente cria alguns comandos “DEFINE” e depois os executa.

Importante: pra que isso funcione corretamente, todas as variáveis SQL*Plus de &1 até &16 precisam ser definidas inicialmente. Você pode fazer isso incluindo no seu login.sql as seguintes linhas (como em SQL*Plus tips. #1):

-- Reset parameters
set termout off
COLUMN 1 NEW_VALUE 1 noprint
COLUMN 2 NEW_VALUE 2 noprint
COLUMN 3 NEW_VALUE 3 noprint
COLUMN 4 NEW_VALUE 4 noprint
COLUMN 5 NEW_VALUE 5 noprint
COLUMN 6 NEW_VALUE 6 noprint
COLUMN 7 NEW_VALUE 7 noprint
COLUMN 8 NEW_VALUE 8 noprint
COLUMN 9 NEW_VALUE 9 noprint
COLUMN 10 NEW_VALUE 10 noprint
COLUMN 11 NEW_VALUE 11 noprint
COLUMN 12 NEW_VALUE 12 noprint
COLUMN 13 NEW_VALUE 13 noprint
COLUMN 14 NEW_VALUE 14 noprint
COLUMN 15 NEW_VALUE 15 noprint
COLUMN 16 NEW_VALUE 16 noprint
SELECT '' "1", '' "5", '' "9",  '' "13"
      ,'' "2", '' "6", '' "10", '' "14"
      ,'' "3", '' "7", '' "11", '' "15"
      ,'' "4", '' "8", '' "12", '' "16"
FROM dual;
set termout on;

Aqui está o script tables que mostra como usar o parser:

$cat tables.sql
-- Define the default parameters values
define OWNER=""
define TABLE=""

-- Call the parser to set the parameters received
@@parser

select owner, table_name, tablespace_name, temporary
from dba_tables
where owner like nvl(upper('&OWNER'),owner)
and table_name like nvl(upper('&TABLE'),table_name)
/

Nos próximos dias eu vou apresentar alguns scripts interessantes que eu criei e usam o parser.

É isso! Espero que gostem 😉 .

3 thoughts on “A simple SQL*Plus parameter parser”

  2. Pingback: Generate AWR / ASH reports sem conectar no servidor | EDUARDO CLARO

  3. Pingback: How to run (and debug) my scripts :-) | EDUARDO CLARO

Leave a comment