I get a ORA-02149

iget the following error with a pl/sql block...
Error: -2149;ORA-02149: Specified partition does not exist
can you plese help?
i have attached the script..
Test-Partition-Archiving.txt
Rao_SAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
Don't really need everything - select partition_name from dba_tab_partitions where owner = 'SCHEMA1' and table_name = 'TABLE1';
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
You're building a dynamic partition name based upon current date.

What is the output of: dbms_output.put_line ('lv_partition: '||lv_partition)

(You'll need to do set serveroutput on first in order to see it).

Does that partition exist ?
0
 
Rao_SAuthor Commented:
i get:
lv_partition: P_201112
Error: -2149;ORA-02149: Specified partition does not exist

i am building it dynamic because i want to scedule it later..
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Does that partition exist ?

select * from dba_tab_partitions
0
 
Javier MoralesConnect With a Mentor Oracle DBACommented:
Your code has an error.

Look:
The EXECUTE IMMEDIATE can't solve the value of lv_partition as its a variable of pl/SQL block

That's why "partition lv_partition does not exists". It works for the SQL sentence, but execute immediate is a literal.

    EXECUTE IMMEDIATE 'ALTER TABLE SCHEMA1.TABLE1 DROP PARTITION(lv_partition)';

You should write:

EXECUTE IMMEDIATE 'ALTER TABLE SCHEMA1.TABLE1 DROP PARTITION('''||lv_partition||''')';

To solve lv_partition value took from the previous select into statement.

Hope it helps,
Javier
0
 
Rao_SAuthor Commented:
thank you all, i also found some more example on line and the solutions worked.
0
All Courses

From novice to tech pro — start learning today.