Using a variable for the partition name in an insert. 1

[commatom]

I am trying to insert into a partitioned table while specifying the partition name.

The SQL looks like the following:

v_partition := 'ERG_GMT_'||part.gmtmonthid;

INSERT /*+ APPEND */ INTO dw.stg_erg_cdr partition (v_partition)
.....

when this runs I get a partition does not exist error.

I put in a DBMS OUTPUT to make sure I have the correct partition name and I do....Soooooooooo any suggestions?

Thanks in advance.

 

[SantaMufasa]

Try using "execute immediate":

declare
...
    v_sql_stm   varchar2(1000);
    v_partition varchar2(1000);
begin
    v_partition := 'ERG_GMT_'||part.gmtmonthid;
    v_sql_stm := 'INSERT /*+ APPEND */ INTO dw.stg_erg_cdr partition ('||v_partition||')';
    execute immediate v_sql_stm;
.....

Let us know your findings.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[commatom]

Success!

Thanks Dave!

 

[SantaMufasa]

My pleasure ! Thanks for the

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”