Re: Spool in SqlPlusa nd my doubt is How to send values to the variables in the WHERE clause.

On Tuesday, January 13, 2015 at 10:52:56 AM UTC-8, 32442 wrote:
> ALL Experts
>
> I am testing the
> spool procedure in sqlplus
> and my doubt is how to send values to the variables in the WHERE clause.
>
> As in the example below;
> =============================================================
> sqlplus -S /nolog <<EOF
> CONNECT user/pwd_at_host:port/service_name;
>
> set echo on;
> set serveroutput on;
> set termout off;
>
> set head on;
> set pagesize 10000;
> set linesize 10000;
>
> spool /opt/oracle/xxxxyyyy.csv REPLACE
>
> select x, y, z
> FROM r inner join d.d on r.id = d.id
> inner join s on d.id = s.id
> inner join dm on r.id = dm.id
> WHERE
> TO_CHAR(s.date,'DD/MM/YYYY') >= ('&StartDate')
> AND
> TO_CHAR(s.date,'DD/MM/YYYY') <= ('&EndDate')
> spool off;
> set echo on
> EOF
> exit
> ==========================================================
>
> How could send linux sheel values for the variables (WHERE), &StartDate and &EndDate ?
>
> Thanks for any help

See http://www.orafaq.com/wiki/SQL*Plus_FAQ#What_is_the_difference_between_.26_and_.26.26.3F and https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:430819636473#43031707677401

One way is to use a permanent variable and select it from dual, so that it goes into the shell wrapper.

jg

-- 
_at_home.com is bogus.
http://www.v3.co.uk/v3-uk/news/2389956/supreme-court-asks-obama-for-help-with-google-v-oracle-android-java-case