Re: ORA-01403 error, help!!!
Unless the stored procedure was created with invoker's rights, then it is probably executing using the permissions and schema of the account that owns it. Who is the owner of the stored procedure? Is it different from who you are logged into SQL*Plus as? The explanation might be that, through some reason like synonyms pointing in different directions from the different accounts, the name TTDSLS805501 might be resolving to different tables altogether... on 9/9/03 10:09 AM, Fermin Bernaus at [EMAIL PROTECTED] wrote: If logged in SQL Plus the following SQL returns just one row: SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; If I do use the same SELECT statement inside a stored procedure and have the returning value stored in a local variable: SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; where eskaria has been declared as: eskaria ttdsls805501.t$orno%TYPE; I get ORA-01403. I have no clue why I am getting this error, can you help please? Many thanks! . Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Weird behavior with VARCHAR fields (was: ORA-01403 error, help!!!)
Thank you all of you who answered to my first question. No, the problem has nothing to do with permissions. The record existed in the table, but here it is what I discovered. Can anyone explain please? It may be the normal Oracle behavior, but I believe it was not working like this under 7.3.4 (now we have 8.0.3). I can not confirm though. TTDSLS805501 table definition is as follows: NameNull? Type T$ORNO NOT NULLNUMBER T$USER NOT NULLCHAR(10) If I do an INSERT like the following: INSERT INTO TTDSLS805501 VALUES (151124 , 'exped9') The value 'exped9' for the field T$USER is 6 characters long, while the definition has got 10. After the insertion, I find that the field for the new record is 10 chars long, the last 4 characters being filled with blanks. But here comes the weird behavior. If I construct the 2 following SELECT on this table from SQLPlus, both work OK and return the expected row: SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9'; - 1 row returned SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9'; - 1 row returned But within a stored procedure and using a local variable the following will work OK: SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; where my_var is of type VARCHAR2 and has got the value 'exped9' Whilst the following will come up with the ORA-01403 error: SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; where my_var is of type VARCHAR2 and has got the value 'exped9' Is this normal behavior? why does not the SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9'; statement return 0 rows in the first place? Fermin. -Mensaje original- De: Tim Gorman [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 10 de septiembre de 2003 10:44 Para: Multiple recipients of list ORACLE-L Asunto: Re: ORA-01403 error, help!!! Unless the stored procedure was created with invoker's rights, then it is probably executing using the permissions and schema of the account that owns it. Who is the owner of the stored procedure? Is it different from who you are logged into SQL*Plus as? The explanation might be that, through some reason like synonyms pointing in different directions from the different accounts, the name TTDSLS805501 might be resolving to different tables altogether... on 9/9/03 10:09 AM, Fermin Bernaus at [EMAIL PROTECTED] wrote: If logged in SQL Plus the following SQL returns just one row: SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; If I do use the same SELECT statement inside a stored procedure and have the returning value stored in a local variable: SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; where eskaria has been declared as: eskaria ttdsls805501.t$orno%TYPE; I get ORA-01403. I have no clue why I am getting this error, can you help please? Many thanks! . Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-01403 error, help!!!
If logged in SQL Plus the following SQL returns just one row: SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; If I do use the same SELECT statement inside a stored procedure and have the returning value stored in a local variable: SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; where eskaria has been declared as: eskaria ttdsls805501.t$orno%TYPE; I get ORA-01403. I have no clue why I am getting this error, can you help please? Many thanks! .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01403 error, help!!!
bash-2.05a$ oerr ora 1403 01403, 0, no data found // *Cause: // *Action: Make up some data. Use your imagination -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin Bernaus Sent: Tuesday, September 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: ORA-01403 error, help!!! If logged in SQL Plus the following SQL returns just one row: SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; If I do use the same SELECT statement inside a stored procedure and have the returning value stored in a local variable: SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; where eskaria has been declared as: eskaria ttdsls805501.t$orno%TYPE; I get ORA-01403. I have no clue why I am getting this error, can you help please? Many thanks! .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01403 error, help!!!
Fermin, Are you sure it is failing here? As Mladen said, 1403 is No Data Found. Do you have any other select statements in the Procedure? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, September 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L If logged in SQL Plus the following SQL returns just one row: SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; If I do use the same SELECT statement inside a stored procedure and have the returning value stored in a local variable: SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; where eskaria has been declared as: eskaria ttdsls805501.t$orno%TYPE; I get ORA-01403. I have no clue why I am getting this error, can you help please? Many thanks! .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01403 error, help!!!
Fermin...: ORA-01403 means No data found... you should manage an EXCEPTION code in your procedure to catch this kind of error. HTH JL --- Fermin Bernaus [EMAIL PROTECTED] wrote: I get ORA-01403. I have no clue why I am getting this error, can you help please? Many thanks! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
