Call a function in PLSQL with dynamic variable name

Question

is there a way to call a function in Oracle using a dynamic variable name? I am trying to call DBMS_LOB.COPY with dynamic column 1...10.

I can do this :

DBMS_LOB.COPY(dynamicColumn1 , l_clob, n_size_clob, 1,  clob_offset )

But I can't do

execute immediate  'select DBMS_LOB.COPY(dynamicColumn' || '1' ||  ', l_clob, n_size_clob, 1,  clob_offset ) from dual;`

what's the error you're getting? Exclude semicolon (;) at the end of your SQL statement — Sathyajith Bhat
– Sathyajith Bhat, Commented Jun 23, 2014 at 6:23
After removing semicolon as your suggested,it says ORA-00904: "DBMS_LOB"."COPY": invalid identifier — Bishnu Paudel
– Bishnu Paudel, Commented Jun 23, 2014 at 7:02
@Sathya, if that was the case, it should have thrown the same error when I try to use DBMS_LOB.COPY(dynamicColumn1 , l_clob, n_size_clob, 1, clob_offset ) — Bishnu Paudel
– Bishnu Paudel, Commented Jun 23, 2014 at 7:05

Wernfried Domscheit · Accepted Answer · 2014-06-23 07:32:33Z

1

I wonder why people always try to use SELECT ... FROM dual.

DBMS_LOB.COPY is a procedure, thus you cannot used it in a SELECT statement. You can use only functions in a SELECT.

answered Jun 23, 2014 at 7:32

60.4k10 gold badges92 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1