How do I pass parameters to an SQL file

This mini tip comes from my friend Guruswamy.
 
How do I pass parameters to a SQL-Script?
 
Well the answer is simple, simply separate them with a space. For example:

SQL> @script.sql parameter1 parameter2 parameter3
 
How do you access the parameters inside the SQL file?

Refer the parameters as &1, &2, and &3.

6 comments :

  1. Anantha,

    How do i pass parameter to sql script from inside pl/sql

    example:
    declare
    v_para1 varchar2(10);
    v_para1 := Florida ;
    begin
    @/tmp/test.sql v_para1
    end;


    The problem is test.sql picks up paramater value as v_para1 instead of Florida

    I have tried $v_para1 as well...it don't work.

    ReplyDelete
  2. This really saved my time. Thanks a lot!

    ReplyDelete
  3. Anantha,

    How do i pass parameter to sql script from inside pl/sql

    example:
    declare
    v_para1 varchar2(10);
    v_para1 := Florida ;
    begin
    @/tmp/test.sql v_para1
    end;


    The problem is test.sql picks up paramater value as v_para1 instead of Florida

    I have tried $v_para1 as well...it don't work.

    ReplyDelete
  4. Try this to pass parameter to sql script from inside pl/sql

    define vvar = 'Florida'

    begin
    @test.sql &&vvar
    end;
    /

    ReplyDelete
  5. How to get the parameter value in test.sql syntax pls

    ReplyDelete
  6. Hi , i want to pass a file containing values to in statement of asql using ashell script... can u suggest me a may to approach it..pls mail me the approach thanks pavan ....maild pavankiran56@gmail.com

    ReplyDelete