Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unanswered: SQL Plus Connect with Bind variable?

    Hello

    I am a bit rusty as far as SQL Plus and PL/SQL, so thought someone may be able to give me a hand. I have a PL/SQL program I wrote some time ago to build an output file, based on some entered criteria. The application creates a new DB for each year. I would like to have the user specify which year they would like the interface built off and then be logged on to that database.

    Example:

    PROMPT 'Enter the database you would like to run the EFT outbound against (2003,2004, etc..):';
    Accept YEAR Prompt ':>';

    variable dconnection varchar2(22);

    declare

    begin

    if &YEAR = 2001 then
    :dconnection := 'User/Pass@db';

    end if;

    ....


    end;
    /

    Can I refernce this bind variable in the Connect statement? Am I going about this the wrong way?

    I appreciate any help.

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What you can do is to have a SQL*Plus SQL script like below..
    Code:
    PROMPT 'Enter the database you would like to run the EFT outbound against (2003,2004, etc..):'
    Accept YEAR Prompt ':>'
    column pconn new_value connvar
    set termout off
    select decode(&YEAR,2001,'User/Pass@db') pconn from dual;
    set termout on
    conn &connvar

Posting Permissions

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