RE: PL/SQL and Bind Variables / Literals

> I am looking at options on forcing Oracle to use literals inside of a
> PL/SQL package.

There is no such feature. So, you have to use dynamic SQL and avoid using a bind variable for that particular value.

The only thing that you loose with dynamic SQL is the check performed at compilation. But, if the bind variable is causing you problems, don't be afraid to not use it.

> One option I thought of is to use execute immediate. I was wondering
if
> anyone had any ideas or thoughts if there is a better way to do this.

In PL/SQL dynamic SQL is supported in three ways:

- EXECUTE IMMEDIATE
- OPEN/FETCH/CLOSE
- dbms_sql

The possibilities are ordered according to the amount of code to be written for using them. So, if you are looking for conciseness, use the first one. The only advantage of the others (especially the last one) is that it gives a more control about the management of the cursor. But, in your case (if I correctly understood your problem), this is not important.

HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008 http://top.antognini.ch

--
http://www.freelists.org/webpage/oracle-l