Define, initialise and use variables in SQL developer and SSIS ODBC connection

Question

I am working on a script to be later used in my SSIS ETL, the source DB is oracle and I am using SQL Developer 20.0.2.75 .

I spent so much time declaring 100 variables but it doesn't see to work in SQL developer.

Define & Initialise:

      Declare 
              V1 number;
              V2 number;
               .
               .
               .
              V100 number;

     Begin 

       Select UDF(params1,param2) into V1 from dual; 
       Select UDF(params3,param4) into V2 from dual; 
      ...
       End;

I was hoping I'd be able to use these variables in my script like :

     select columns from table where Col1=:V1 and Col2=:V2

When used "Run Statement" prompts for values, "Run Script" doesn't see to like into Variable statements.

I even tried :

      select columns from table where Col1=&&V1 and Col2=&&V2

Now my query doesn't work !

After below responses, I changed my script to :

        Variable  V1 Number;
        Variable  V2 Number;

      exec select MyFunction(p1,p2) into :V1 from Dual; 
       /
      Select columns from table where col1=:V1 and col2=:V2

It still prompts for value

This is how I defined my function

       Create Function MyFunction(m IN Varchar, s IN Number) 
       Return Number
       IS c Number;

       select code into c  from table where col1=m and col2=s;
       Return(c);
       End;

Is there anything wrong with the function?

Does this answer your question? [How do I use variables in Oracle SQL Developer?](https://stackoverflow.com/questions/5653423/how-do-i-use-variables-in-oracle-sql-developer) — Abra, Sep 21 '20 at 07:00
@Abra Thanks, already seen that. Justin Cave's answer is the closes to my situation, the problem is he just simply prints the parameter and doesn't use it in a query. When I do, it still prompts for value — Ariox66, Sep 21 '20 at 07:23

Connor McDonald · Accepted Answer · 2020-09-22T06:54:47.777

1

You define variables as per you would in SQL Plus or SQLcl and then run it as a script

Text below

variable x1 number
begin
  select 123 into :x1 from dual;
end;
/
print x1

Similar example in SQL Plus (and will work in SQL Dev as well)

SQL> set serverout on
SQL> variable x1 number
SQL> begin
  2    select 5 into :x1 from dual;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print x1

        X1
----------
         5

SQL>
SQL> select rownum from dual
  2  connect by level <= :x1;

    ROWNUM
----------
         1
         2
         3
         4
         5

SQL>
SQL> begin
  2    dbms_output.put_line('X1 is '||:x1);
  3  end;
  4  /
X1 is 5

PL/SQL procedure successfully completed.

edited Sep 22 '20 at 06:54

answered Sep 21 '20 at 07:13

Connor McDonald

10,418
1
11
16

I am not trying to print the variable's value, I am using that variable in another script. it prompts for value when you use that variable with : in your query – Ariox66 Sep 21 '20 at 07:21
You can use that variable anywhere...I've appended an example to my original answer – Connor McDonald Sep 22 '20 at 06:54

score 1 · Answer 2 · answered Sep 21 '20 at 08:30

1

I spent so much time declaring 100 variables

To me, it looks like a wrong approach. OK, declare a few variables, but 100 of them?! Why wouldn't you switch to something easier to maintain. What? A table, for example.

create table params
  (var          varchar2(20),
   value        varchar2(20)
  );

Pre-populate it with all variables you use (and then just update their values), or just insert rows:

insert into params (var, value) values ('v1', UDF(params1, param2));
insert into params (var, value) values ('v2', UDF(params3, param4));
...

Fetch values through a function:

create or replace function f_params (par_var in varchar2)
  return varchar2
is
  retval varchar2(20);
begin
  select value
    into retval
    from params
    where var = par_var;
  return retval;
end;

Use it (in your query) as:

select columns 
from table 
where Col1 = f_params('v1')
  and Col2 = f_params('v2')

If many users use it, consider creating one "master" params table (which contains all the variables) and a global temporary table (which would be populated and used by each of those users).

answered Sep 21 '20 at 08:30

Littlefoot

131,892
15
35
57

Thanks, this is all going into an ETL and will run every day automatically. (and I don't have rights to create a table) Do you think I can do the same thing with a temp table? – Ariox66 Sep 21 '20 at 21:55
I don't know what you call a "temp table", but - if you opt to use what I suggested, ask a DBA to create a table for you. – Littlefoot Sep 22 '20 at 05:58
since we don't commit this query, the params table plays a temporary table role here. right? – Ariox66 Nov 11 '20 at 05:50
No, it wasn't my intention. You *would* commit if you wanted to keep those rows permanently stored in the database. That's pretty much obvious; what you don't commit is being *lost* once you terminate the session (unless autocommit is turned ON). So - yes, you'd have to COMMIT after INSERT. – Littlefoot Nov 11 '20 at 06:14
but I don't want to store those values permanently, every time the ETL runs I want to recreate that temp table. isn't better if I create a private temp table? – Ariox66 Nov 11 '20 at 06:19
From my point of view, no. If you use that table frequently (it seems you do), then **why** would you want to recreate it every time you use it? But hey, it is your database, do whatever you find appropriate. – Littlefoot Nov 11 '20 at 06:43
I don't like to keep create and insert the values, it's just they're called variable for a reason. they keep changing! that's why I had to create that UDF function to fetch the latest valid value – Ariox66 Nov 11 '20 at 06:55

Define, initialise and use variables in SQL developer and SSIS ODBC connection

2 Answers2