RE: PL/SQL and Bind Variables / Literals

From: Christian Antognini <>
Date: Thu, 18 Jun 2009 08:03:56 +0200
Message-ID: <>

Hi Tom

> 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
> 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:

- 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.

Chris Antognini

Troubleshooting Oracle Performance, Apress 2008

Received on Thu Jun 18 2009 - 01:03:56 CDT

Original text of this message