Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    2

    Unanswered: Pass a variable in sql script

    Hi,

    I am running a batch script that runs an sql script. How can i pass a variable in an sql script.
    i used the below commands in my sql script:

    set verify off;
    ACCEPT order prompt "enter order num : "

    My sql command: select * from table where order_no in (&order);

    When i run the batch file it asks to enter the order num. Once i enter the order num it does not give an output. Please advice.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Works OK for me.

    BAT script:
    Code:
    sqlplus scott/tiger@ora10 @p.sql
    SQL script:
    Code:
    set verify off
    
    accept dno prompt "Enter deptno: "
    select *
    from dept
    where deptno in (&dno);
    Execution:
    Code:
    M:\>p
    
    M:\>sqlplus scott/tiger@ora10 @p.sql
    
    SQL*Plus: Release 11.2.0.2.0 Production on Sri Lip 12 07:05:44 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    
    Session altered.
    
    
    Session altered.
    
    Enter deptno: 10
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
    
    SQL>
    Now, your turn. Show us what you did and how Oracle responded.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    since it is a string, doesn't it need to be

    select * from table where order_no in ('&order');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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