Oracle - How to execute a query with parameters?

Question

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.

I am using Oracle SQL Developer. I want to have something like:declare paraName and using it instead of {0}. Or i can replace {0} with :ParamName, i have seen this is the right sintax. — Jake Manet
– Jake Manet, Commented May 26, 2017 at 10:07
How do you want to call it? ( SQLPlus, some editor, within a PLSQL block, ...) Also, will your parameter represent the table to query? If so, you may need dynamic SQL. — Aleksej
– Aleksej, Commented May 26, 2017 at 10:07

Aleksej · Accepted Answer · 2017-05-26 10:38:28Z

3

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>

edited May 26, 2017 at 10:38

answered May 26, 2017 at 10:13

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

2 Comments

Jake Manet Over a year ago

Good answer but considering i have the same parameter multiple times, how can i replace it in all places?

Aleksej Over a year ago

I believe you can not. If you need something complex, one way could be dynamic SQL from within a PLSQL block

Collectives™ on Stack Overflow

Oracle - How to execute a query with parameters?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related