Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: How to pipe with a parameter

    Hi there,

    I have a simple script that pipes a query to SQL*Plus like this:

    ne=`<< END_SQL
    SELECT
    Names
    FROM
    MyTable
    ;
    EXIT
    END_SQL`

    nq=`echo "$ne" |sqlplus -s usr/pwd`

    now I'm trying to modify the script in order to use a parameter in the query but I haven't found out a way to do it. Is it possible? Basically the problem is how to pipe into sqlplus a query like the following:

    ne=`<< END_SQL
    SELECT
    Names
    FROM
    MyTable
    WHERE
    Names = '&1'
    ;
    EXIT
    END_SQL`

    Thanks in advance.
    Giggi

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Giggi, try this:
    Code:
    #!/bin/ksh
    cat - << END_SQL >ne.sql
    set ver off
    SELECT EName
    FROM EMP
    WHERE Deptno = '&1'
    ;
    EXIT
    END_SQL
    
    nq=`sqlplus -s scott/tiger @ne.sql 10`
    
    echo "$nq"
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2005
    Posts
    3
    Hi LKBrwn_DBA,

    thanks for the reply, that works but I'd like to avoid saving the script to a file. Is it possible to have the sql statement stored in a variable and pass it to sqlplus together with the parameter?

    Giggi

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Can't you do it like this:
    Code:
    #!/bin/ksh
    sqlplus -s scott/tiger << END_SQL 
    set ver off
    SELECT EName
    FROM EMP
    WHERE Deptno = '$1'
    ;
    EXIT
    END_SQL

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    This will also work:
    Code:
    #!/bin/ksh
    sqlplus scott/tiger <<END_SQL
    def p1=$1
    set ver off
    SELECT EName
    FROM EMP
    WHERE Deptno = '&&p1'
    ;
    EXIT
    END_SQL
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Mar 2005
    Posts
    3
    Thanks for the hints! Now it's ok.

    Giggi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •