Accessing a partitioned table in a Cursor

[sofiagomes]

I'm trying to define in a stored procedure a cursor to bring data at a specified partition (given by a parameter).

The problem is that I get ORA-02149: Specified partition does not exist, and that's the same partition name I use in the next statement:
>SELECT *
>FROM postps_successful_calls
>partition(D0703)
>WHERE
>tariff=3;

Anyone know how to do it?
Do I have to do it with dynamic sql?

Thanks

 

[kindus]

I donot see any problem with your query, nor it is to be done with dynamic sql. What I can think is your partition name may be misspelled.

check it with

select table_owner,table_name,partition_name from dba_tab_partitions
where table_name='POSTPS_SUCCESSFUL_CALLS'
/

Let us know.

 

[sofiagomes]

Sorry, but i'm sure I'm using the same partition name.

This is the statement I ran under SQL PLUS:
>SELECT *
>FROM postps_successful_calls
>partition(D0703)
>WHERE
>tariff=3;

This is the cursor definition under PL/SQL, I pass 'D0703' on parameter <particao>:
CURSOR cur_total_msisdn (data in DATE, tarifa in NUMBER, particao in VARCHAR2) IS
SELECT account, msisdn, SUM(call_price) valor, count(*) nr_reg
FROM postps_successful_calls
PARTITION (particao)
WHERE data_fecho IS NULL
AND tariff=tarifa
GROUP BY account, msisdn;

How about?
Thanks

 

[kindus]

The problem is with your passing the partition name, oracle sees the partition name as paticao not as its value. You need to treat the partition name just like table_name.You have to either hard code the partition name or use dynamic sql to resolve the issue.

 

[sem]

If you need to pass your partition as a parameter you do need dynamic sql. If you're on 8.1.6+ (not sure for 8.1.5)you may open REF CURSOR for string:

....
type rc is ref cursor;
c rc;
begin
open c for ' SELECT account, msisdn, SUM(call_price)
valor, count(*) nr_reg
FROM postps_successful_calls
PARTITION ('||particao||')
WHERE data_fecho IS NULL
AND tariff=tarifa
GROUP BY account, msisdn';

........
end;

Of course the productivity will suffer a bit.