Forum Stats

  • 3,817,447 Users
  • 2,259,334 Discussions
  • 7,893,776 Comments

Discussions

Problems with Select with Into in Procedure

503412
503412 Member Posts: 5
edited Sep 2, 2009 3:59PM in SQL & PL/SQL
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;
Tagged:

Answers

  • Andreas Weiden
    Andreas Weiden Member Posts: 10,871 Gold Crown
    What error do you get?
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    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
    503412 Member Posts: 5
    edited Sep 2, 2009 3:43PM
    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
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    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
    Frank Kulash Member, Moderator Posts: 41,935 Red Diamond
    edited Sep 2, 2009 3:59PM
    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.
This discussion has been closed.