CODEWITHSUNDEEP
sqloracle-databaseplsql
user3127462
user3127462

Reputation: 223

Execute immediate Oracle

I am trying to execute EXECUTE IMMEDIATE Statement. But I am getting the below error. I am trying this as new, I read the existing posts.

I am trying this after seeing the below examples. http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm

It says invalid table name, but the table is present

Connecting to the database Local
ORA-00903: invalid table name
ORA-06512: at "MMM.Maxtable", line 26
ORA-06512: at line 9
CNTRYCNTRYID
SELECT MAX(:1) FROM  :2  WHERE :1  <= 99999
Process exited.
Disconnecting from the database oraclesrv.local.

I am passing CNTRY AS TABLE NAME, CNTRYID AS Columnname

Create OR REPLACE PROCEDURE Maxtable
    (ITableName          VarChar2,
    IColumnName  VarChar2 )
AS
 Limit1 int;
 RESULT1 INT;
 Query1  varChar(255);
 TableName          VarChar(50);
    ColumnName VarChar(50);
BEGIN
Limit1 := 99999;
MaxTableId := 0;
Result1 := 0;

TableName := ltrim(rtrim(ITableName));
ColumnName := ltrim(rtrim(iColumnName));
DBMS_OUTPUT.PUT_LINE(TableName || ColumnName );

IF (TableName is not null and  ColumnName is not null) then
Query1 := 'SELECT MAX(:1) FROM  :2  WHERE :1  <= 99999' ;
DBMS_OUTPUT.PUT_LINE(Query1);

EXECUTE IMMEDIATE Query1 INTO Result1 USING ColumnName, TableName;                               
END IF;

DBMS_OUTPUT.PUT_LINE(Result1);

 MaxTableId := Result1;

 IF (MaxTableId = Limit1) THEN
    MaxTableId := -1;
 ELSE
    MaxTableId := MaxTableId + 1  ;
 END IF;

END adm_getMaxTableIdLimited;

I have tried this option also, its not working..

Connecting to the database. ORA-00905: missing keyword ORA-06512: at "mmm.Maxtable", line 19 ORA-06512: at line 9 SELECT max( CNTRYID) INTO Result1 FROM CNTRY WHERE CNTRYID <= 99999 Process exited. Disconnecting from the database oraclesrv.local.

Create OR REPLACE PROCEDURE Maxtable
    (TableName          VarChar2,
    ColumnName  VarChar2,
    MaxTableId OUT  Int )
AS
 Limit1 int;
 RESULT1 INT;
 Query1  varChar(255);
BEGIN
Limit1 := 99999;
MaxTableId := 0;
Result1 := 0;

IF (TableName is not null and  ColumnName is not null) then
Query1 := 'SELECT max( ' || ColumnName || ')  INTO  Result1'  || ' FROM ' || TableName || ' WHERE ' || ColumnName || ' <= ' || 99999 ;

DBMS_OUTPUT.PUT_LINE(Query1);

EXECUTE IMMEDIATE Query1;
END IF;

DBMS_OUTPUT.PUT_LINE(Result1);

 MaxTableId := Result1;

 IF (MaxTableId = Limit1) THEN
    MaxTableId := -1;
 ELSE
    MaxTableId := MaxTableId + 1  ;
 END IF;

END Maxtable;

Upvotes: 3

Views: 10247

Answers (2)

Shyam D
Shyam D

Reputation: 17

EXECUTE IMMEDIATE 'drop table tbl_proc_cre_ins purge';--using "EXECUTE IMMEDIATE";
EXECUTE IMMEDIATE 'CREATE TABLE tbl_proc_cre_ins(rollno varchar2(100),name varchar2(100))';

Upvotes: 0

Donato Szilagyi
Donato Szilagyi

Reputation: 4369

The link you added says the following:

You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

So unfortunately you cannot use bind variables for the table name and the column name. You have to string-concatenate them:

Query1 := 'SELECT MAX(' || ColumnName || ') FROM  ' || TableName || '  WHERE ' || ColumnName || ' <= 99999' ;

And use the EXECUTE IMMEDIATE the following way:

EXECUTE IMMEDIATE Query1 INTO Result1;

Upvotes: 2

Related Questions