-- This is an example of using BASH environmental variables in a sqlplus script
-- ©2005 Edward Stoever 

-- First the setup:
CREATE TABLE DRINKS (drinks_id NUMBER, drinks_description VARCHAR2(100));

CREATE TABLE DRINKRELATIONS (drinkrelations_drinks_id NUMBER, drinkrelations_ingredients_id NUMBER);

CREATE TABLE INGREDIENTS (ingredients_id NUMBER, ingredients_description VARCHAR2(100));

ALTER TABLE DRINKS ADD (
 CONSTRAINT PK_DRINKS PRIMARY KEY (DRINKS_ID));

ALTER TABLE INGREDIENTS ADD (
 CONSTRAINT PK_INGREDIENTS PRIMARY KEY (INGREDIENTS_ID));

ALTER TABLE DRINKRELATIONS ADD (
 CONSTRAINT FK_DRINKRELATIONS_DRINKS_ID FOREIGN KEY (DRINKRELATIONS_DRINKS_ID)
   REFERENCES DRINKS (DRINKS_ID));

ALTER TABLE DRINKRELATIONS ADD (
 CONSTRAINT FK_DRINKRELATIONS_INGREDNTS_ID FOREIGN KEY (DRINKRELATIONS_INGREDIENTS_ID)
   REFERENCES INGREDIENTS (INGREDIENTS_ID));


CREATE SEQUENCE SQ_DRINKS
 START WITH 1
 MAXVALUE 999999999999999999999999999
 MINVALUE 0
 NOCYCLE
 NOCACHE
 NOORDER;

CREATE SEQUENCE SQ_INGREDIENTS
 START WITH 1
 MAXVALUE 999999999999999999999999999
 MINVALUE 0
 NOCYCLE
 NOCACHE
 NOORDER;

INSERT INTO DRINKS
    VALUES (sq_drinks.NEXTVAL, 'Harvey Wallbanger');

INSERT INTO DRINKS
    VALUES (sq_drinks.NEXTVAL, 'Shirley Temple');

INSERT INTO DRINKS
    VALUES (sq_drinks.NEXTVAL, 'Gin and Tonic');

INSERT INTO DRINKS
    VALUES (sq_drinks.NEXTVAL, 'Rum Volcano');

INSERT INTO INGREDIENTS
    VALUES (sq_ingredients.NEXTVAL, 'grenadine');

INSERT INTO INGREDIENTS
    VALUES (sq_ingredients.NEXTVAL, '7up');

INSERT INTO INGREDIENTS
    VALUES (sq_ingredients.NEXTVAL, 'Gin');

INSERT INTO INGREDIENTS
    VALUES (sq_ingredients.NEXTVAL, 'Tonic');

INSERT INTO INGREDIENTS
    VALUES (sq_ingredients.NEXTVAL, 'Rum');

INSERT INTO INGREDIENTS
    VALUES (sq_ingredients.NEXTVAL, 'Vodka');

COMMIT;

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Gin and Tonic'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = 'Gin'));

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Gin and Tonic'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = 'Tonic'));

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Shirley Temple'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = 'grenadine'));

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Shirley Temple'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = '7up'));

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Rum Volcano'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = 'Rum'));

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Harvey Wallbanger'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = 'Rum'));

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Harvey Wallbanger'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = 'Gin'));

INSERT INTO DRINKRELATIONS
    VALUES ((SELECT drinks_id
               FROM DRINKS
              WHERE drinks_description = 'Harvey Wallbanger'),
            (SELECT ingredients_id
               FROM INGREDIENTS
              WHERE ingredients_description = 'Vodka'));

COMMIT;

col drinks_description FOR a25 trun
col ingredients_description FOR a25 trun

break on drinks_description

SELECT   drinks_description, ingredients_description
   FROM DRINKS, DRINKRELATIONS, INGREDIENTS
  WHERE drinks_id = drinkrelations_drinks_id
    AND ingredients_id = drinkrelations_ingredients_id
ORDER BY drinks_description, ingredients_description;
DRINKS_DESCRIPTION        INGREDIENTS_DESCRIPTION  
------------------------- -------------------------
Gin and Tonic             Gin                      
                          Tonic                    
Harvey Wallbanger         Gin                      
                          Rum                      
                          Vodka                    
Rum Volcano               Rum                      
Shirley Temple            7up                      
                          grenadine                
8 rows selected


-- now the example:
[oracle@oracle oracle]$ export conn_str=edward@starter/xxxxx
[oracle@oracle oracle]$ export _whereclause=" AND ingredients_description in ('Gin','Vodka')"
[oracle@oracle oracle]$ echo $_whereclause
AND ingredients_description in ('Gin','Vodka')
[oracle@oracle oracle]$ sqlplus $conn_str << __EOF__
> SELECT drinks_description
> FROM DRINKS, DRINKRELATIONS, INGREDIENTS
> WHERE drinks_id = drinkrelations_drinks_id
>   AND ingredients_id = drinkrelations_ingredients_id
>   ${_whereclause}
> ORDER BY drinks_description;
> exit;
> __EOF__

SQL*Plus: Release 9.2.0.5.0 - Production on Thu Oct 6 10:28:49 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL>   2    3    4    5    6
DRINKS_DESCRIPTION
--------------------------------------------------------------------------------
Gin and Tonic
Harvey Wallbanger
Harvey Wallbanger

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
[oracle@oracle oracle]$

-- Note that the variable is not interpreted by sqlplus. 
-- It is interpreted by bash and passed to sqlplus as part of a single command.
--
-- Special thanks to  Braj Kishore Mahto, ( Senior Oracle App 11i DBA ) GIT,A.S. Watsons Ltd, Hong Kong 
-- for helping me out with this.