Bind Variables

[lesstjm]

I am having SGA problems where it is running out of memory, ORA-0431. I think it is due to the large amount of dynamic sql going on in the database. I have pinned all of the large objects that are being used in the SGA. I have also been reading a lot on Metalink about bind variables and I have a few questions.

If I create a PL/SQL package that does selects,inserts, updates and deletes using variables, are these considered bind variables?

Also, the database that is having problems has a lot of updating and selects and inserts from outside programs that log into oracle. These programs are unix scripts and c and power builder. How do you bind your variables in those programs?

[marist89]

Originally posted by lesstjm

If I create a PL/SQL package that does selects,inserts, updates and deletes using variables, are these considered bind variables?

Depends. If you are constructing your DML and passing it to Dynamic SQL, you are probably not using bind variables. Post a piece of your code so we can see.

Also, the database that is having problems has a lot of updating and selects and inserts from outside programs that log into oracle. These programs are unix scripts and c and power builder. How do you bind your variables in those programs?

C and Powerbuilder offer bind variables and each should have specific documentation addressing this. For something in a kshell or cshell script, you are probably better off creating a procedure on the server and then executing it multiple times from the unix script.