CODEWITHSUNDEEP
sqloracle-databasesql-scripts
Nickpick
Nickpick

Reputation: 6587

Run an oracle SQL script twice with different parameters

I have an SQL statement in Oracle SQL developer that has some variables:

DEFINE custom_date = "'22-JUL-2016'" --run1
DEFINE custom_date = "'25-JUL-2016'" --run2

SELECT * FROM TABLE WHERE date=&custom_date

The real query is much more complicated and has many more variables and new tables are created from the results of the query. How can I create a script so that the query is executed twice, the first time with the custom date set as in the first line and the second time as in the second line.

Upvotes: 0

Views: 2372

Answers (2)

user5683823
user5683823

Reputation:

In Oracle, the &variable is a "substitution variable" and is not part of SQL; it is part of the SQL*Plus scripting language (understood by SQL Developer, Toad etc.)

The better option, and what you are asking about, is BIND VARIABLES. The notation is :variable (with a colon : instead of &), for example :custom_date.

The difference is that a substitution variable is replaced by its value in the front-end application (SQL Developer in your case) before the query is ever sent to the Oracle engine proper. A bind variable is substituted at runtime. This has several benefits; discussing them is outside the scope of your question.

When you execute a query with bind variables in SQL Developer, the program will open a window where you enter the desired values for the bind variables. You will have to experiment with that a little bit till you can make it work (for example I never remember if a date must be entered with the single quotes or without). Good luck!

Upvotes: 1

T.Z.
T.Z.

Reputation: 2162

Define is used in TRANSACT SQL. To do this Oracle way, You can create anonymus PL/SQL block, similar to this:

DECLARE
    p_param1 DATE;
    p_param2 NUMBER;
    CURSOR c_cur1(cp_param1 DATE,cp_param2 NUMBER)
    IS
        SELECT * FROM table WHERE date = cp_param1
    ;
BEGIN
    -- Execute it first time
    p_param1 := TO_DATE('2016-09-01','YYYY-MM-DD');
    FOR r IN c_cur1(p_param1)
    LOOP
        NULL;
    END LOOP;
    -- Execute it second time
    p_param1 := TO_DATE('2015-10-11','YYYY-MM-DD');
    FOR r IN c_cur1(p_param1)
    LOOP
        NULL;
    END LOOP;
END;

And in it, You create cursor with parameters and execute it twice with different parameter. I do not know why You want to execute this query twice, so the script abowe does nothing with results, but it certainly should execute Your query twice, with different params.

Upvotes: 0

Related Questions