OraFAQ Forum: Server Administration » Bind Variables: only possible through Dynamic SQL (EXECUTE IMMEDIATE or DBMS

Home » RDBMS Server » Server Administration » Bind Variables: only possible through Dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL)?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bind Variables: only possible through Dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL)? [message #60689]

Wed, 25 February 2004 23:42

Patrick Tahiri
Messages: 119
Registered: January 2004

Senior Member

Hi,

I'm using extensively PL/SQL with stored procedure in my packages.

Can we just apply the "bind variables" technique with dynamic SQL within EXECUTE IMMEDIATE or DBMS_SQL? Or can we bind variables using static SQL within my stored procedures?

In peak time, my processes performs heavy inserts (and select)on a couple of tables via these packages.stored_procerdures, so it's really important for me to use bind variables! So I can avoid to have many query plans, hard parses and latches�when I'm executing�exactely the same insert or select statements�(in peak time, the are executed many/a lot of times). Is then best� to use DBMS_SQL or EXECUTE IMMEDIATE method?

Thank you for your tips and advices!

Regards,

Patrick Tahiri.

Report message to a moderator

Re: Bind Variables: only possible through Dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL)? [message #60699 is a reply to message #60689]

Thu, 26 February 2004 10:24

andrew again
Messages: 2577
Registered: March 2000

Senior Member

pl/sql uses them automatically. p_emp_id is the "bind varaible". The term bind variable is usually refers to the syntax using a ":" e.g. ":b1" but it parameters, variables in PL/SQL behave in the same way.

procedure update_emp(p_emp_id in number)
is
begin
  update emp set sal = sal + 1 where emp_id = p_emp_id;
end;

Report message to a moderator

Re: Bind Variables: only possible through Dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL)? [message #60726 is a reply to message #60699]

Sun, 29 February 2004 22:14

Patrick Tahiri
Messages: 119
Registered: January 2004

Senior Member

Thank you so much!

Regards,

Patrick Tahiri.

Report message to a moderator

Previous Topic:	How to reclaim the space from a 25 GB table
Next Topic:	hi

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 20:09:06 CST 2025