Passing a Bind Variable Value via the Substitution Variable to the HOST Command

Passing a Bind Variable Value via the Substitution Variable to the HOST Command

PURPOSE
——-
The article explains how you can pass a bind variable value via the SQL*Plus
substitution variable to the HOST command.

SCOPE & APPLICATION
——————-
The intended audience for this article are SQL*Plus users.

Difference between a Bind variable and a Substitution variable
======================———–=============================
A clear distinction between SQL*Plus substitution variables and bind variables
needs to be made because they are treated differently.

Substitution variables are strictly client side variables, i.e. they are
replaced by actual values *before* the statement is sent to the server. If
you enable SQL trace, you will see that there is no variable reference
anymore in the SQL statement sent to the server. Usually DEFINE keyword is
used to create this. Substitution variables are used or referred to
using ampersand (& or &&).

E.g.
SQL> define my_sub = ‘MY substitution variable’
SQL> define my_sub_num = 12

SQL> select ‘&my_sub’ from dual;
SQL> select &my_sub_num from dual;
SQL> select ‘&my_sub_num’ from dual;

Bind variables on the other hand are part of the SQL statement that is sent
to the server, and get their values during the BIND phase (after OPEN cursor
and PARSE, before EXECUTE). VARIABLE keyword is used to create bind
variables. Bind variables are used or accessed using the colon (‘:’) ;

E.g.
SQL> variable my_bind varchar2(30)

In SQL*Plus SQL> select :my_bind from dual;
In PL/SQL block: begin
:my_bind := ‘bind variable’;
end;

Example 1 – Simple use of the substitution variable
—————————————————
To get a file listing of all files ending with ‘txt’ :

SQL> define my_sub = ‘*txt’

SQL> host dir &&my_sub /p (On NT: a DOS window is displayed with listing of
all filenames ending in ‘txt’)

SQL> host ls &&my_sub (On Solaris, a listing of all files
ending in ‘txt’ is displayed)

Example 2 – Assign substitution variable from Bind variable in PL/SQL
——————————————————————–

SQL> define my_sub=”
SQL> col my_sub new_value my_subnew
SQL> variable my_bind varchar2(50)

SQL> begin
select ‘*txt’ into :my_bind from dual;
end;
/

SQL> select :my_bind my_sub from dual;

SQL> host dir &&my_subnew /p (On NT: a DOS window is displayed with listing of
all filenames ending in ‘txt’)

SQL> host ls &&my_subnew (On Solaris, a listing of all files
ending in ‘txt’ is displayed)

The main issue here is getting the bind variable value into the SQL*Plus
substitution variable. This is done as follows :

SQL> define my_sub=”
SQL> col my_sub new_value my_subnew
SQL> select :my_bind my_sub from dual;

NOTE: the new variable my_subnew will contain the bind value data.

.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em VARIAVEL BIND. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s