Re: EXECUTE IMMEDIATE DDL - Concatenate a record

<amogh.r_at_gmail.com> a écrit dans le message de news: c282f602-637a-45e7-9dbc-912bf94c6be1_at_googlegroups.com...
| Hello,
|
| I am trying to concatenate a collection into a DDL executed using
| execute immediate. Fails with an error -
|
| create or replace type vartab as table of varchar2(4000);
|
| create or replace function myfunc(var_tab vartab) return number
| is begin
| -- do something here
| return 1;
| end;
| /
|
| declare
| fmtmap vartab := vartab('123456');
| begin
| execute immediate 'create table tab1 as select myfunc('||fmtmap||') a
| from dual';
| end;
| /
|
| >> PLS-00306: wrong number or types of arguments in call to '||'
|
| Is this syntax not supported? Replacing fmtmap with any other scalar type
| (number, varchar) type works.
|
| Thanks much.

The problem is NOT in execute immediate it is in the concatenation (as the error message told you).
How do you concatenate a strong and an array? Your approach of your issue is wrong (without speaking about sql injection).

SQL> create or replace type vartab as table of varchar2(4000);   2 /

Type created.

SQL> create or replace function myfunc(var_tab vartab) return number   2 is
  3 begin
  4 return var_tab.count;
  5 end;
  6 /

Function created.

SQL> declare
  2 fmtmap vartab := vartab('123456');   3 val number;
  4 begin
  5 val := myfunc(fmtmap);
  6 execute immediate 'create table tab1 as select '||val||' a from dual';
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL> select * from tab1;

         A

         1

1 row selected.

But as I said don't do that; split the DDL into 2 statements:

SQL> drop table tab1;

Table dropped.

SQL> declare
  2 fmtmap vartab := vartab('123456');   3 begin
  4 execute immediate 'create table tab1 as select 1 a from dual where 1=2';
  5 execute immediate 'insert into tab1 values(:1)' using myfunc(fmtmap);   6 commit;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL> select * from tab1;

         A

         1

1 row selected.

Regards
Michel Received on Tue Jun 21 2016 - 07:23:22 CEST