CODEWITHSUNDEEP
oracle-database
Jake Manet
Jake Manet

Reputation: 1252

Oracle - How to execute a query with parameters?

I am new to Oracle and i am trying to execute a simple select with some parameters but i cant get it to work. For

SELECT idl.column_value clientguid
FROM TableName idl 
LEFT JOIN :ParamName_Type olt ON olt.clientguid = idl.column_value
WHERE (olt.flag = 0)

But declare does not work. I could not find any help on internet. Thanks.

Upvotes: 0

Views: 20337

Answers (1)

Aleksej
Aleksej

Reputation: 22949

Oracle SQL Developer should handle variables the same way SQLPlus does, that is with the &.

For example ( in SQLPlus for simplicity):

SQL> select 1 from &tableName;
Enter value for tablename: dual
old   1: select 1 from &tableName
new   1: select 1 from dual

         1
----------
         1

What you can not do is use the parameter as a part of a table name, assuming that Developer "knows" which part is the parameter name and which one is the fixed part. For example:

SQL> select * from &ParamName_Type;
Enter value for paramname_type:

that is, all the string ParamName_Type wil be interpreted as a variable name and substituited with the value you enter.

Also, consider that this is a client-specific behaviour, not an Oracle DB one; so, the same thing will not work in a different client (Toad for Oracle for example).

Consider that you are trying to use a "parameter" that represents a table name, and you only can do this by the means of some client, because plain SQL does not allow it. If you need to do such a thing in some piece of code that has to work no matter the client, you need dynamic SQL

If you need something more complex, you may need some dynamic SQL; for example:

SQL> declare
  2      vTableName varchar2(30) := '&table_name';
  3      vSQL       varchar2(100):= 'select 1 from ' || vTableName || 
                                    ' union all select 2 from ' || vTableName;
  4      type tResult is table of number;
  5      vResult    tResult;
  6  begin
  7      execute immediate vSQL bulk collect into vResult;
  8      --
  9      -- do what you need with the result
 10      --
 11      for i in vResult.first .. vResult.last loop
 12          dbms_output.put_line(vResult(i));
 13      end loop;
 14  end;
 15  /
Enter value for table_name: dual
old   2:     vTableName varchar2(30) := '&table_name';
new   2:     vTableName varchar2(30) := 'dual';
1
2

PL/SQL procedure successfully completed.

SQL>

Upvotes: 3

Related Questions