How to define a variable using another variable in oracle sql

Question

I am trying to define a variable with another variable. The purpose is to substitute the value in a variable at runtime.

I am not sure if I am asking the right questions or stating the right example.

DECLARE param1 VARCHAR(10);
DECLARE param2 VARCHAR(10);
DECLATE tempVar VARCHAR(1):= 'A';
DECLARE query VARCHAR(200) := 'select * from tmp_table where col1 = <variable>';

BEGIN
    IF tempVar = 'A' THEN
     -- EXECUTE IMMEDIATE query using param 1;
    ELSE
     -- EXECUTE IMMEDIATE query using param 2;
    END IF;
  END;

Justin Cave · Accepted Answer · 2013-03-22 01:08:57Z

2

DECLARE 
  type tmp_table_arr is table of tmp_table%type;
  l_temps tmp_tabe_arr;

  param1 VARCHAR(10);
  param2 VARCHAR(10);
  tempVar VARCHAR(1):= 'A';
  query VARCHAR(200) := 'select * from tmp_table where col1 = :val';

BEGIN
  IF tempVar = 'A' THEN
    EXECUTE IMMEDIATE query 
       BULK COLLECT INTO l_temps
      using param1;
  ELSE
    EXECUTE IMMEDIATE query 
       BULK COLLECT INTO l_temps
      using param2;
  END IF;
END;

is syntactically valid and will work if tmp_table exists and has a column col1. There is no reason in this case, however, to use dynamic SQL. Nor do you need two different SQL statements

DECLARE 
  param1 VARCHAR(10);
  param2 VARCHAR(10);
  tempVar VARCHAR(1):= 'A';
BEGIN
  FOR i IN (SELECT *
              FROM tmp_table
             WHERE col1 = (CASE WHEN tempVar = 'A'
                                THEN param1
                                ELSE param2
                            END))
  LOOP
    <<do something>>
  END LOOP;
END;

answered Mar 22, 2013 at 1:08

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Farhan Syed Over a year ago

This would work, but, what I am looking for specifically is run time variable substitution in a different variable. I have a query that I need to query 20 times on different tables. Except the table names, nothing changes. I have declared variables with my base query. What I think I need is a way to modify my variable to replace the table name and execute the resulting query.

Farhan Syed Over a year ago

Reading your first procedure, does :val get replaced with value of l_temps at runtime?

Justin Cave Over a year ago

@FarhanSyed - :val is a bind variable so the value in param1 is used at runtime. l_temps is the collection that stores the result of the SELECT statement. Bind variables are used to vary the values that you use in a predicate. In your question, you're talking about changing the value in a predicate dynamically. If you actually want to change what table is queried, that is a completely different question. You cannot use a bind variable for the table name. You would have to dynamically assemble the SQL statement before executing it.

Collectives™ on Stack Overflow

How to define a variable using another variable in oracle sql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related