Colega, o que acontece é que a substituição de valores no sqlplus é LITERAL :
o sqlplus substitui mesmo no texto a ser enviado para o banco EXATAMENTE o que
vc informou : o sql*plus é um programa-cliente, ele é INCAPAZ de analisar
textos de SQL, necessariamente NADA é executado nele, ele apenas ENVIA o texto
para o database, que é quem o pode interpretar ... Assim, se vc tiver no
script, digamos, uma condição de WHERE assim :
WHERE ...
AND sal >= NVL(&3, sal)
o texto que vai ser enviado para o banco é :
WHERE ...
and sal >= nvl( , sal)
o que vai acontecer é que :
a) o NVL *** NÃO *** vai ser executado, já que o sqlplus é incapaz disso
b) a variável 3 tem um valor de vazio, vazio é o que será substituído na
string de texto SQL a ser enviado para o database para posterior
interpretação... É ** LITERAL ** mesmo, pense na variável lexical como algo que
vai ser Substituído no texto do SQL pelo sqlplus ....
Há vários work-arounds possíveis , tais como (por exemplo) mandar um SQL para
o database que via comando COLUMN .... NEW_VALUE substitua o NULL por algo
apripriado (cfrme
http://www.freelists.org/post/oracle-l/sqlplus-substitution-variables,9
mostra), mas eu prefiro outra técnica : ter aspas-simples (plics) em volta das
variáveis, de modo que os valores todos sejam tratados como string... Dessa
forma, no meu exemplo anterior , o texto que iria para o database se a variável
3 fosse nula seria :
WHERE ...
and sal >= nvl('' , sal)
o que aí Sim funciona, strings vazias do tipo são de-nulificadas com NVL...
Uma demonstração :
set term off feedback off verify off pages 0 lines 500 trimspool on head off
spool result.txt
select deptno, empno, ename, job, sal from emp
where deptno = nvl(to_number('&1') , deptno)
and job = nvl('&2', job)
and sal >= nvl(to_number('&3') , sal)
;
spool off
exit
/
===> Execuções (como eu estou testando no Windows, uso "" para indicar string
vazia em prompt de comando) :
=> primeiro caso, não passando nenhum valor :
C:\Users\jchiappa>sqlplus -s scott/tiger @test_spool.sql "" "" ""
C:\Users\jchiappa>type result.txt
20 7369 SMITH CLERK 800
30 7499 ALLEN SALESMAN 1600
30 7521 WARD SALESMAN 1250
20 7566 JONES MANAGER 2975
30 7654 MARTIN SALESMAN 1250
30 7698 BLAKE MANAGER 2850
10 7782 CLARK MANAGER 2450
20 7788 SCOTT ANALYST 3000
10 7839 KING PRESIDENT 5000
30 7844 TURNER SALESMAN 1500
20 7876 ADAMS CLERK 1100
30 7900 JAMES CLERK 950
20 7902 FORD ANALYST 3000
10 7934 MILLER CLERK 1300
C:\Users\jchiappa>
=> segundo, passando um valor para param1 :
C:\Users\jchiappa>sqlplus -s scott/tiger @test_spool.sql "10" "" ""
C:\Users\jchiappa>type result.txt
10 7782 CLARK MANAGER 2450
10 7839 KING PRESIDENT 5000
10 7934 MILLER CLERK 1300
=> passando valores para params 1 e 2 apenas :
C:\Users\jchiappa>sqlplus -s scott/tiger @test_spool.sql "10" "PRESIDENT" ""
C:\Users\jchiappa>type result.txt
10 7839 KING PRESIDENT 5000
=> valorando params 1,2 e 3 :
C:\Users\jchiappa>sqlplus -s scott/tiger @test_spool.sql "10" "PRESIDENT"
"1000"
C:\Users\jchiappa>type result.txt
10 7839 KING PRESIDENT 5000
=> apenas params 2 e 3 , veja que a linha com JOB=CLERK com salario menor que
1000 não veio :
C:\Users\jchiappa>sqlplus -s scott/tiger @test_spool.sql "" "CLERK" "1000"
C:\Users\jchiappa>type result.txt
20 7876 ADAMS CLERK 1100
10 7934 MILLER CLERK 1300
=> valorando apenas param3, veja que os NVLs enviados para o banco funcionaram :
C:\Users\jchiappa>sqlplus -s scott/tiger @test_spool.sql "" "" "1000"
C:\Users\jchiappa>type result.txt
30 7499 ALLEN SALESMAN 1600
30 7521 WARD SALESMAN 1250
20 7566 JONES MANAGER 2975
30 7654 MARTIN SALESMAN 1250
30 7698 BLAKE MANAGER 2850
10 7782 CLARK MANAGER 2450
20 7788 SCOTT ANALYST 3000
10 7839 KING PRESIDENT 5000
30 7844 TURNER SALESMAN 1500
20 7876 ADAMS CLERK 1100
20 7902 FORD ANALYST 3000
10 7934 MILLER CLERK 1300
===> a prova final, os SQLs que o sqlplus mandou para o banco :
SYSTEM@O10GR2::SQL>select sql_text from v$sql where sql_text like 'select
deptno, empno, ename, job, sal from emp%' order by last_active_time;
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select deptno, empno, ename, job, sal from emp where deptno =
nvl(to_number('') , deptno) and job = nvl('', job) and sal >=
nvl(to_number('') , sal)
select deptno, empno, ename, job, sal from emp where deptno =
nvl(to_number('10') , deptno) and job = nvl('', job) and sal >=
nvl(to_number('') , sal)
select deptno, empno, ename, job, sal from emp where deptno =
nvl(to_number('10') , deptno) and job = nvl('PRESIDENT', job) and
sal >= nvl(to_number('') , sal)
select deptno, empno, ename, job, sal from emp where deptno =
nvl(to_number('10') , deptno) and job = nvl('PRESIDENT', job) and
sal >= nvl(to_number('1000') , sal)
select deptno, empno, ename, job, sal from emp where deptno =
nvl(to_number('') , deptno) and job = nvl('CLERK', job) and sal
>= nvl(to_number('1000') , sal)
select deptno, empno, ename, job, sal from emp where deptno =
nvl(to_number('') , deptno) and job = nvl('', job) and sal >=
nvl(to_number('1000') , sal)
=> EVIDENTEMENTE, isto é só um exemplo simples : fosse um script
completo/profissional, a ser executado num ambiente aonde vc Não Tem certeza
dos settings correntes, entre outras coisas (como sempre quando se trabalha com
Conversão), além de NUNCA confiar na ordem de conversão, indicando o que deve
ser convertido com TO_NUMBER/TO_CHAR/TO_DATE (o que eu fiz) , seria de bom tom
se ter ESPECIFICADO a ** máscara ** a ser usada, no caso de NUMBER ter indicado
quais caracteres de Group e Decimal sign se deseja, mas isso, creio, é Óbvio...
[]s
Chiappa
--- Em oracle_br@yahoogrupos.com.br, Leonardo Silva <leonardo.drums@...>
escreveu
>
> Boa tarde pessoal,
>
> Novamente nessito da ajuda de vocês, eu inseri a seguinte query dentro de
> um spool:
>
> SELECT A.CNREC CNREC
> ,A.ART_NO ART_NO
> ,A.SUPPL_NO SUPPL_NO
> ,A.QTD_ART QTD_ART
> ,A.DT_SHELF_LIFE DT_SHELF_LIFE
> ,A.NMUSR NMUSR
> FROM SHELF_LIFE_HIST A
> WHERE A.CNREC = NVL('&3' ,A.CNREC)
> AND A.ART_NO = NVL('&4' ,A.ART_NO)
> AND A.SUPPL_NO = NVL('&5' ,A.SUPPL_NO)
> AND A.QTD_ART = NVL('&6' ,A.QTD_ART)
> AND A.DT_SHELF_LIFE = NVL('&7' ,A.DT_SHELF_LIFE)
> AND A.NMUSR = NVL('&8' ,A.NMUSR);
> porém eu não consigo executar o spool se eu mandar os parametros em branco,
> alguém poderia me dar alguma dica de como fazer funcionar mesmo que eu
> mande os parametros em branco?
>
> Versão do banco: 10.1.0.3.0
>
> Desde já agradeço pela atenção.
> --
> Atenciosamente
>
> Leonardo Silva
>
>
> E da mesma maneira também o Espírito ajuda as nossas fraquezas; porque não
> sabemos o que havemos de pedir como convém, mas o mesmo Espírito intercede
> por nós com gemidos inexprimíveis.Romanos 8:26
>
>
> [As partes desta mensagem que não continham texto foram removidas]
>
