• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Oracle dynamic sql

suityou01

Potty-trained
Joined
Jan 20, 2009
Messages
21,122
Likes received
773
Thanks received
222
Location
Clown World
Trying to do summat like this

OPEN-FOR-USING Statement

Code:
DECLARE
   TYPE MyCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   myCursor   MyCurTyp;  -- declare cursor variable
   someTable NVARCHAR2(100);
BEGIN
   someTable := 'FFS';
   OPEN myCursor FOR  -- open cursor variable
      'SELECT col1,col2 FROM :a ' USING someTable;
   ...
END;

Error, invalid table name
:throw:

Feel like I have read and re-read this article and am not trying anything special. Why would it fail to inject the bind variable into the placeholder?
 
Because you cannot use that syntax for passing in the table name. It works fine if you are using the variable in the where clause, but not as a table name:

Code:
DECLARE
  TYPE myCurTyp IS REF CURSOR;
  myCursor MyCurTyp;
  theTable VARCHAR2(100);
BEGIN
  theTable := 'FFS';
  OPEN myCursor FOR 'SELECT col1, col2 FROM FFS WHERE col1 = :s' USING theTable;
  CLOSE myCursor;
END;
/

PL/SQL procedure successfully completed

If you are needing to change the table name dynamically, then you need to put that table name in a variable containing your SQL statement and use that instead:

Code:
DECLARE
  TYPE myCurTyp IS REF CURSOR;
  myCursor MyCurTyp;
  theTable VARCHAR2(100);
  theSQL   VARCHAR2(4000);
BEGIN
  theTable := 'FFS';
  theSQL   := 'SELECT col1, col2 FROM '||theTable;
  OPEN myCursor FOR theSQL;
  CLOSE myCursor;
END;
/

PL/SQL procedure successfully completed
 
Because you cannot use that syntax for passing in the table name. It works fine if you are using the variable in the where clause, but not as a table name:

Same with other DBs (e.g. sql server). Build the whole query as a string, and execute that. Remembering to double your quotes and all the other hassle that goes with it.
 
Same with other DBs (e.g. sql server). Build the whole query as a string, and execute that. Remembering to double your quotes and all the other hassle that goes with it.

Not the same as other DBs if you take a mo to read the Oracle doc :)

And the the doc doesn't explain that you cannot use placeholders for table names.

And there are examples on tinterweb showing you how to do exactly that.

And I tried the suggestion of building the string and it gives me compilation errors when trying to use it in conjunction with cursor open from. Comment out that line and it compiles.

Thanks all for your help.
 
This example for SQL Server shows exactly how to use dynamic table names with parameterised statements.

sp_executesql (Transact-SQL)

Code:
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
                 @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
       CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
       'Sales' +
       /* Build a VALUES clause. */
       ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
       ' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)

EXEC sp_executesql @InsertString,
     N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
     @PrmOrderID, @PrmCustomerID, @PrmOrderDate,
     @OrderMonth, @PrmDeliveryDate

GO
 
Look closely. The table name is built into the string. So therefore not substituted into the parameter list.

And then - of course - you are mixing t-sql with p/sql.
 
Another Suity classic :laugh

Databinding arrays of arrays of arrays of objects.....
Serializing winforms........
PL/SQL for real dummies

:tongue
 
Another Suity classic :laugh

Databinding arrays of arrays of arrays of objects.....
Serializing winforms........
PL/SQL for real dummies

:tongue

Heeeeey. How am I the butt of the joke after what MS and LE posted? :laugh

Edit : Don't forget to look closely ;)
 

Advertisers

Back
Top