Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.2K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 402 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Problems with Select with Into in Procedure

503412
Member Posts: 5
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;
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;
Answers
-
What error do you get?
-
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... -
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 -
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 likeEXCEPTION WHEN NO_DATA_FOUND THEN 'anything you like to do here' END;
Regards
Anurag Tibrewal. -
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.