I want to replace argument or parameter used in procedure with : sign ahead of same parameter.
Like
procedure proc_name ( para_1 number,
para_2 varchar2,......)
select ..... from table_1 where col_name = para_1 ;
end;
Output is
procedure proc_name ( :para_1 number,
:para_2 varchar2,......)
select ..... from table_1 where col_name = :para_1 ;
end;
Procedure contains more complex code and more parameter so its difficult to replace each parameter with bind variable.
Because of some time to execute inside code in Toad environment so.
So you want to turn all the variables for SQL in PL/SQL to bind variables?
No need!
They
are bind variables. When you compile a PL/SQL program, the database canonicalizes the SQL within it. This:
- Removes unnecessary whitespace
- Uppercases the text
- Makes variables bind variables
For example:
create table t as
select mod ( level, 10 ) c1
from dual
connect by level <= 100;
create or replace procedure p ( v1 int ) as
c pls_integer;
begin
select count (*)
into c
from t
where c1 = v1;
dbms_output.put_line ( 'count = ' || c );
end p;
/
exec p ( 2 );
count = 10
select sql_text from v$sql
where upper ( sql_text ) like 'SELECT COUNT (*)%FROM T%';
SQL_TEXT
SELECT COUNT (*) FROM T WHERE C1 = :B1
Notice that the database transformed:
where c1 = v1 => WHERE C1 = :B1