CODEWITHSUNDEEP
sqloracle-databasevariablesplsql
Farhan Syed
Farhan Syed

Reputation: 326

How to define a variable using another variable in oracle sql

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;

Upvotes: 0

Views: 1395

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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;

Upvotes: 2

Related Questions