Operating System - HP-UX
1747980 Members
3663 Online
108756 Solutions
New Discussion юеВ

Re: "select ... into" does not work

 
SOLVED
Go to solution
Thomas Schler_1
Trusted Contributor

"select ... into" does not work

I try to execute a quite simple select statement within PL/SQL. It is:
SELECT hypo_uid INTO v_hypo_uid FROM hypocenter
WHERE primehypo = 'y' AND ev_id = 2446;

It produces the 'no data found' exception.

But: Within SQL+, the following statement gives exactly one result (as expected):
SQL> SELECT hypo_uid FROM hypocenter
2 WHERE primehypo = 'y' AND ev_id = 2446;

HYPO_UID
----------
4424

SQL>

So, what is the problem???

I'm using Oracle 8.1.7.4.0, HP-UX 11.00.
no users -- no problems
8 REPLIES 8
Yogeeraj_1
Honored Contributor

Re: "select ... into" does not work

hi,

First of all, make sure that you are using the same user in both cases.

Is it on the same sqlplus session?

If yes, try the sql and the plsql in the same sqlplus session:

try the following:

set serveroutput on size 10000;
declare
v_hypo_uid varchar2(20);
begin
select hypo_uid
into v_hypo_uid
from hypocenter
where primehypo = 'y'
and ev_id = 2446;
dbms_output.put_line(v_hypo_uid);
end;
/

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Thomas Schler_1
Trusted Contributor

Re: "select ... into" does not work

Yogeeraj:

It is the same user in both cases, because it is on the same sql+ session (tried both within the same sql+ session).

Your code sequence executed within sql+ succeeds (except that type of v_hypo_uid must be number).

But still, it does not run within pl/sql.
no users -- no problems
Brian Crabtree
Honored Contributor
Solution

Re: "select ... into" does not work

Thomas,

Actually, the block that Yogeeraj posted was pl/sql. What do you mean when it doesn't run in pl/sql?

Also, you might want to consider putting the number in quotes.

Thanks,

Brian
Dilip Kumar_4
Advisor

Re: "select ... into" does not work

Hi Thomas:
One thing to check,
Is your PL/SQL program 'connecting as another user' before executing the SQL command?

HTH
Dilip
Yogeeraj_1
Honored Contributor

Re: "select ... into" does not work

hi,

the code that i posted above is PLSQL as Brian mentioned above. So if it worked, it should work anywhere. You need to check you coding. If possible, post the PLSQL you are trying to execute and the structure of the 2 tables (desc hupocenter)

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Kyri Pilavakis
Frequent Advisor

Re: "select ... into" does not work

Yogeeraj code should work ok...Are u sure u set the :

set serveroutput on ??
Bosses don't undestand..HP does
Yogeeraj_1
Honored Contributor

Re: "select ... into" does not work

hi,

without the the "set serveroutput on" command, dbms_output.put_line procedure will be executed without printing the desired message from a plsql block to the sqlplus session.
e.g. DBMS_OUTPUT.put_line('my text');

Note that it is the DBMS_OUTPUT package which collects the queue of messages in the PLSQL block..

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Thomas Schler_1
Trusted Contributor

Re: "select ... into" does not work

Hi, thanks to all,

it was my fault. The above code is part of a more complicated one. I did a logical mistake there, that had confused me. I did not take into account the rollback mechanism after an exception has raised. I'm correcting my source code, now.

Thank you and sorry.
no users -- no problems