Problems with Select with Into in Procedure

503412

Why this procedure has an error in Select clausule?

CREATE OR REPLACE Procedure pr_purge_partitions (DATAREF number) is

MMDDREF varchar(4);
DATAREF_INI DATE;
TEXT varchar(9);

BEGIN

DATAREF_INI := TO_DATE(DATAREF,'YYYYMMDD') - 91;

MMDDREF := TO_CHAR(DATAREF_INI, 'MMDD');

SELECT
Tbl.PARTITION_POSITION INTO TEXT
FROM
USER_TAB_PARTITIONS Tbl
WHERE
Tbl.TABLE_NAME = 'USER'
AND
Tbl.PARTITION_NAME = 'USER_P' || MMDDREF;


dbms_output.put_line('Text: '|| TEXT );

END;

Andreas Weiden

What error do you get?

Hoek

Hi, and welcome to the forum,

By not posting the actual error-message you get, you've imo agreed to read
http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html
If that still doesn't ring any bell:
Post your error message as well...

503412

ORA-01403: no data found
ORA-06512: at "PR_PURGE_PARTITIONS", line 16
ORA-06512: at line 7

Edited by: user500409 on 02/09/2009 16:42

Anurag Tibrewal

Hi,

It is because there is no data pertaining to the input you have provided in user_tab_partitions view.
So syntax wise there is nothing wrong in your procedure you may like to add exception handling to handle the situations when there is no data

Something like

EXCEPTION
WHEN NO_DATA_FOUND THEN
'anything you like to do here'
END;

Regards
Anurag Tibrewal.

Frank Kulash

Hi,

"ORA-01403: no data found" occurs in PL/SQL when a query returns 0 rows.

You can put the SELECT statement into its own BEGIN block, with its own EXCEPTION section to check for just this error.

If you know that the query will never return more than one row, then you could also re-write the query to guarantee that it returns exactly one row.

SELECT  MIN (PARTITION_POSITION)
INTO 	TEXT
FROM 	USER_TAB_PARTITIONS
WHERE 	TABLE_NAME	= 'USER'
AND	PARTITION_NAME	= 'USER_P' || MMDDREF;

Using an aggregate function (like MIN) without a GROUP BY clause always produces exactly one row, even if the table is empty.
This will return NULL into text in cases where you had been getting ORA-01403.