Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    8

    Smile Unanswered: passing variables to sqlplus scripts

    Hi,

    I am loading some tables using a batch file calling sqlplus scripts.
    I would like to pass variables into the sqlplus script ( key ids) so
    that the sqlplus script can pass the variables into stored procedure calls.
    How do I do this?

    example of what I am currently doing

    batch file call:
    sqlplus user/password@database @History_Load_Account_Trans_Daily

    where @History_Load_Account_Trans_Daily is the name of the sqlplus script. I would like to pass in @History_Load_Account_Trans_Daily 12345, 999999. or other numbers or values.

    so I can do this in the sqlplus script:

    execute pkg_acct_serv_acct_trans_hist.load_daily_account_t rans_id (12345, 999999);

    Thank you

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: passing variables to sqlplus scripts

    The call would be:

    sqlplus user/password@database @History_Load_Account_Trans_Daily 12345 999999

    Then in the script:

    execute pkg_acct_serv_acct_trans_hist.load_daily_account_t rans_id (&1, &2);

    Note: for a VARCHAR2 value you would write '&1' instead of &1

  3. #3
    Join Date
    Nov 2002
    Posts
    8

    Smile

    Thank you for the answer Andrew.
    Paul

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Smile

    You can do it also interactively with ACCEPT command in SQL*Plus:

    SET VERIFY OFF

    ACCEPT par1 prompt "ENTER PARAMETER #1: "

    ACCEPT par2 prompt "ENTER PARAMETER #2: "

    execute pkg_acct_serv_acct_trans_hist.load_daily_account_t rans_id ( &&par1, &&par2);



    Hope that helps,

    clio_usa
    OCP - DBA

Posting Permissions

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