Returning a value from a select statement through EXECUTE IMMEDIATE

Question

I've searched for some examples of how return a value from a select statement using the execute immediate, but it keeps returning me an error "ORA-00903: invalid table name".

Here is a sample of the code:

DECLARE
  LAST_NUMBER NUMBER;

  CURSOR C_SEQUENCES IS
    SELECT
      A.SEQUENCE_NAME, B.TABLE_NAME, C.COLUMN_NAME
    FROM
      USER_SEQUENCES A
    INNER JOIN
      USER_CONSTRAINTS B ON A.SEQUENCE_NAME = REPLACE(B.CONSTRAINT_NAME, 'PK', 'SEQ_ID')
    INNER JOIN
      USER_CONS_COLUMNS C ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
    WHERE
      B.CONSTRAINT_TYPE = 'P';

BEGIN
  FOR REG IN C_SEQUENCES LOOP
    EXECUTE IMMEDIATE 'SELECT MAX(:1) FROM :2' INTO LAST_NUMBER USING REG.COLUMN_NAME, REG.TABLE_NAME;
  END LOOP;
END;

Yes, the table does exists.

Any ideas?

Unfortunately, table name cannot be a Bind Variable! You need to include it in the string formation itself! — Maheswaran Ravisankar, Aug 22 '14 at 10:31
Thanks @MaheswaranRavisankar, I don't know how could I forgot to try this. =) — Marco, Aug 22 '14 at 11:05

score 4 · Accepted Answer · edited May 23 '17 at 12:05

4

I think the only way is to concatenate those both variables:

EXECUTE IMMEDIATE 'SELECT MAX('||REG.COLUMN_NAME||') FROM '||REG.TABLE_NAME INTO LAST_NUMBER;

And I also suggest you to make a validation function for the column and table names in case of SQL injection.

edited May 23 '17 at 12:05

Community

1
1

answered Aug 22 '14 at 10:38

Jokke Heikkilä

918
1
8
17

Returning a value from a select statement through EXECUTE IMMEDIATE

1 Answers1