Chippa.. Mais uma vez muito obrigado..
Funcionou perfeitamente. Atenciosamente. Leonardo Silva. Em 26 de abril de 2012 19:08, J. Laurindo Chiappa <jlchia...@yahoo.com.br>escreveu: > ** > > > 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,9mostra), > 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] > > > > > -- 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] ------------------------------------ -------------------------------------------------------------------------------------------------------------------------- >Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de inteira >responsabilidade de seus remetentes. Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/ -------------------------------------------------------------------------------------------------------------------------- >Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package » Procedure » >Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO ESPAÇO! VISITE: >http://www.oraclebr.com.br/ ------------------------------------------------------------------------------------------------------------------------ Links do Yahoo! Grupos <*> Para visitar o site do seu grupo na web, acesse: http://br.groups.yahoo.com/group/oracle_br/ <*> Para sair deste grupo, envie um e-mail para: oracle_br-unsubscr...@yahoogrupos.com.br <*> O uso que você faz do Yahoo! Grupos está sujeito aos: http://br.yahoo.com/info/utos.html
