Oracle : bind variables

1

Hi team,

Is it possible to send to an oracle database a select containing bind variables, and how to initialize those variables ? If so, could you provide an example ?

This is recommended to solve performance issues.

Thanks

2

Hi Romain!

It's currently not possible.

3

Hello Andrew,

It seems to me that you need to write the custom SQL query in EasyMorph's editor (or database command) using Oracle bind variables as parameters and iterate this module from another module with different values for this parameter.
This way, the query should be properly recognized in Oracle's shared memory, which should use the same execution plan for each iteration, right? Or am I missing something?

Something like this:

variable Firstname varchar2(50);
execute :Firstname := "{ParameterName}";
select * from table
where Firstname = :Firstname;

I guess reinitializing the variable on subsequent iterations might be a problem (I'm currently on SQL Server and can't test), so I would enumerate the rows in the main calling module, execute row 1 to the second
module with above SQL, and create a third module for rows from 2 to the end iterated with a call from the second module, with the following SQL:

execute :Firstname := "{ParameterName}";
select * from table
where Firstname = :Firstname;

(Be careful with the parameter type—it must be the same as declared in the first line for each value)

Don't you think this might work?

4

I don't think it's working. I tried your syntax but Easymorph does not accept words like variable and execute inside the oracle database command. That's my problem.

5

Looks like the variable keyword is a SQL*Plus syntax, and not PL/SQL.