SQL & PL/SQL

I wrote this test procedure to read a single value from a single row from a single table partition.

create or replace PROCEDURE READ_PARTITION_VAL(PARTITION_NAME in VARCHAR2) is

TEST_VAL NUMBER;

BEGIN /**************************

author....: mdockery

purpose...: Read a single val from a single row in a single partition

usage.....: execute READ\_PARTITION\_VAL('SYS\_P51931', '201801');

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

DBMS\_OUTPUT.ENABLE;

DBMS\_OUTPUT.PUT\_LINE('Reading PARTITION:'||PARTITION\_NAME);

SELECT SPEED\_DEFICIT into TEST\_VAL

    FROM DW\_MOBILITY\_FACT

    PARTITION(PARTITION\_NAME)

    fetch first 1 row only;

end READ_PARTITION_VAL;

When I try to run it:

execute READ_PARTITION_VAL('SYS_P51931');

It gives this error:

Error starting at line : 10 in command -

BEGIN READ_PARTITION_VAL('SYS_P51931'); END;

Error report -

ORA-02149: Specified partition does not exist

ORA-06512: at "READ_PARTITION_VAL", line 11

ORA-06512: at line 1

02149. 00000 - "Specified partition does not exist"

*Cause: Partition not found for the object.

*Action: Retry with correct partition name.

However when I run this sql directly it works fine

(and has no trouble finding the partition, ...which does in fact exist)

SELECT SPEED_DEFICIT FROM DW_MOBILITY_FACT

PARTITION(SYS_P51931) fetch first 1 row only;

I suspect I have the wrong syntax in my proc above, and I welcome advice/assistance.