0

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.

Nickpick
  • 6,163
  • 16
  • 65
  • 116
  • http://stackoverflow.com/questions/18620893/how-do-i-pass-arguments-to-a-pl-sql-script-on-command-line-with-sqlplus – OldProgrammer Sep 26 '16 at 11:21
  • Is this the only way to have the script run more than once? Do I actually have to call it from outside multiple times? – Nickpick Sep 26 '16 at 11:22

2 Answers2

1

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!

  • The script will actually need to run several hundred parameters so a pop up is not a solution. Also some of the parameters are lists (where in &var.). Not sure how this could be handled in bind variables – Nickpick Sep 26 '16 at 12:20
  • ?? How are you entering values for several hundred parameters now, using substitution variables? It sounds like the parameters shouldn't be parameters at all, they should be stored in a table (or generated by your application) and used that way. –  Sep 26 '16 at 12:21
  • If you need to run the query more than once, you need to write a procedure in PL/SQL (or perhaps in your organization's preferred language, like Java or C#) - that's a different topic altogether. The query should still use bind variables, and the values need to come from somewhere (table, generated by the app etc.) –  Sep 26 '16 at 12:24
  • Unfortunately for this I'm not allowed to use anything else than SQL, otherwise I could have solved it easily in python – Nickpick Sep 26 '16 at 12:56
  • @nickpick - then what do you mean by "script"? Do you mean SQL query? (A query is NOT a script!) In Oracle SQL, you can only run one query at a time; anything else runs a script, in something other than plain SQL. –  Sep 26 '16 at 13:16
0

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.

T.Z.
  • 2,092
  • 1
  • 24
  • 39
  • Ok I will try that. I want to execute it twice because my actual query creates a new tables, depending on the parameters. I will create a new table each time it is executed and the table name is a variable as well. – Nickpick Sep 26 '16 at 11:34
  • This do not sound good... ;) But I do not know enough too help or comment Your approach. Please provide code You are trying to execute. – T.Z. Sep 26 '16 at 11:38
  • I'm getting this error when running your example: PLS-00103: Encountered the symbol ";" when expecting one of the following: loop The symbol "loop" was substituted for ";" to continue. – Nickpick Sep 26 '16 at 11:39
  • I have forgotten about END LOOP. I have corrected the answer above. – T.Z. Sep 26 '16 at 11:42
  • still getting errors: ORA-06550: line 6, column 60: PL/SQL: ORA-00936: missing expression ORA-06550: line 6, column 9: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. – Nickpick Sep 26 '16 at 11:50
  • As I said before, please provide the code You are trying to execute. – T.Z. Sep 26 '16 at 12:00