Re: EXECUTE IMMEDIATE DDL - Concatenate a record
From: <amogh.r_at_gmail.com>
Date: Tue, 21 Jun 2016 00:14:31 -0700 (PDT)
Message-ID: <e21ec0c1-e2b0-45e1-a89a-ca8f8d8c1cc8_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.
Date: Tue, 21 Jun 2016 00:14:31 -0700 (PDT)
Message-ID: <e21ec0c1-e2b0-45e1-a89a-ca8f8d8c1cc8_at_googlegroups.com>
On Tuesday, June 21, 2016 at 10:53:30 AM UTC+5:30, Michel Cadot wrote:
> <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
Thanks for your response. I figured yesterday just after my post that the || operator doesn't work with collections, just like you mentioned. The issue is that the CTAS I posted was a sample to show the error, the real CTAS is much larger, and from legacy code. There are many variables concatenated into it. Rewriting it the way you mentioned will be a re-design of sorts :-). Thanks for your time again. Received on Tue Jun 21 2016 - 09:14:31 CEST