Oracle dynamic sql

[suityou01]

Trying to do summat like this

OPEN-FOR-USING Statement

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

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?

[TheFaQQer]

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:

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:

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

[DiscoStu]

**Waits for scope creep**

[mudskipper]

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.

[suityou01]

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.

[suityou01]

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

sp_executesql (Transact-SQL)

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

[lilelvis2000]

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.

[suityou01]

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.

[DimPrawn]

Another Suity classic

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

[suityou01]

Another Suity classic

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

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

Edit : Don't forget to look closely