-
Ora-01422
Can anyone help with the following problem
Ive created a package with the following SQL
IF title_level = 'P' THEN
BEGIN
SELECT od.description
INTO description
FROM object_descriptions od,
V_P_TITLE_LEVEL ptl
WHERE od.obj_id = ptl.prg_auk
AND od.type = 1
AND ptl.ss_box_cod = TO_CHAR(ss_box_cod);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
RETURN;
END IF;
running it results in error ORA-01422
'exact fetch returns more than the requested number of rows'
I can understand this error if more than one row is returned, but running the sql as follows
SELECT od.description
FROM object_descriptions od,
V_P_TITLE_LEVEL ptl
WHERE od.obj_id = ptl.prg_auk
AND od.type = 1
AND ptl.ss_box_cod = TO_CHAR(1711634270);
returns just one row
**ss_box_cod is equal to 1711634270 when run inside the package.
V_P_TITLE_LEVEL is a view
tia
-
SELECT od.description
INTO description
FROM object_descriptions od,
V_P_TITLE_LEVEL ptl
WHERE od.obj_id = ptl.prg_auk
AND od.type = 1
AND ptl.ss_box_cod = TO_CHAR(ss_box_cod);
The above select statement returns more than one row. You got error because you are assigning more than one values to description variable.
It seems your logic or data may be a problem.
Tamil
-
Ora-01422
I realised that more than one row is being returned and thats why I get ORA-01422, but when I run the sql as in the second example, I only get 1 row. I cant see why both bits of code return different numberrs of rows, as they should effectively be the same.
-
The problem is here :
AND ptl.ss_box_cod = TO_CHAR(ss_box_cod);
You are matching the column ss_box_cod to the column ss_box_cod and all the rows match. When run explictly, you are using the number
AND ptl.ss_box_cod = TO_CHAR(1711634270);
The moral of this story is NEVER use PL/SQL variable names that are the same as column names. Prefix your variables with a V_
-
Thanks!!
Couple of days ago I see that problem first time.
Googling have no result.
Answers like this
http://www.sql.ru/forum/actualthread.aspx?tid=156187
was too complex for me.
And now I see the post of gamyers about "moral of this story"
It helps me!!!
Tnx!! ))))
Sorry for my poor English
-
Originally Posted by Comandor
Sorry for my poor English
No problem, we forgive both your English and the fact that you answered a three years old dead thread
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|