Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    30

    Unanswered: SQL PLUS: Passing parameters between sql files

    Hi,

    I want to pass a variable value from a pl/sql block as a parameter to another sql file.

    For e.g.
    temp1.sql

    declare
    key Integer := 4545;
    begin
    @temp2.sql key
    end;

    I tried : & && all options. But I am getting a string as key,:key etc.
    I am not able to pass the value to the sql file.

    What is the option that I need to use for passing the value?

    Thanks
    Santhi

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Unfortunatelly, nothing rings the bell in my head about that.

    However, you could write pl/sql code in your files in order to create procedures/functions and pass parameters easily by the mean of their parameters.
    Also, you could write a package and use a global variable do achieve this goal.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    use a sqlplus bind variable in the pl/sql, followed by a sqlplus substitution variable for passing. I'm assuming that your example is oversimplified, and you don't just declare the variable to a static.

    var key number

    declare
    begin
    :key := ....
    end;

    column key new_value s_key
    select :key key from dual;

    @temp2.sql &s_key
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Nov 2003
    Posts
    30
    Yes, you are right. I just gave a simple e.g.
    My actual requirement is as below:
    I have a function which returns a number.
    I have to use this number as part of a select query to retrieve the results.
    I want to spool this data to a file.
    I do not want to create any other procedures or functions for this purpose.

    As you suggested I tried this sample.

    sql1.sql
    --------
    var key number;
    Declare
    begin
    :key := 23456;
    end;
    /

    sql2.sql
    -------
    column keycol new_value nv noprint
    select :key keycol from dual;
    select -- from table where col=&nv;

    temp.sql
    --------
    spool c:\temp.lst
    @sql1.sql
    @sql2.sql
    spool off

    It works!.

    Thank you very much!.
    Santhi.

Posting Permissions

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