sql%found problem [message #268215] |
Tue, 18 September 2007 00:15  |
ashish2345
Messages: 50 Registered: September 2007
|
Member |
|
|
oracle version 9i
Hi friends
i was doing a validation proble in which i am returning true/false from function if the update statement works..
plz see the function is returning true but control not going in false.. plz replyas tom my exam and this doubt is not clearing below is whatever i did
create or replace function fvalid(x number)
return boolean
is
begin
update emp1 set empno=99 where deptno=x;
if sql%found then
return true;
end if;
exception when others
then
return false;
end;
/
function created.
declare
x1 number:=&deptnumber;
l boolean;
begin
l:=fvalid(x1);
if l=true then
dbms_output.put_line('updated');
else
dbms_output.put_line('dept invalid');
end if;
end;
/
enter value for deptnumber: 33
2: x1 number:=&deptnumber;
2: x1 number:=33;
are
erroR at line 1:
06503: PL/SQL: Function returned without value
06512: at "SCOTT.FVALID", line 11
06512: at line 5
/
enter value for deptnumber: 10
2: x1 number:=&deptnumber;
2: x1 number:=10;
updated
QL procedure successfully completed.
select * from emp1 where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
------ ---------- --------- ---------- --------- ---------- ----------
DEPTNO
------
99 KING PRESIDENT 17-NOV-81 5000
10
99 CLARK MANAGER 7839 09-JUN-81 2450
10
99 MILLER CLERK 7782 23-JAN-82 1300
10
[Updated on: Tue, 18 September 2007 00:32] Report message to a moderator
|
|
|
|
Re: sql%found problem [message #268231 is a reply to message #268215] |
Tue, 18 September 2007 00:41   |
muzahid
Messages: 281 Registered: September 2004 Location: Dhaka, Bangladesh
|
Senior Member |
|
|
ashish2345 wrote on Tue, 18 September 2007 00:15 |
create or replace function fvalid(x number)
return boolean
is
begin
update emp1 set empno=99 where deptno=x;
if sql%found then
return true;
end if;
exception when others
then
return false;
end;
/
|
Function will be
CREATE OR REPLACE FUNCTION fvalid (x NUMBER)
RETURN BOOLEAN
IS
BEGIN
UPDATE emp
SET empno = 99
WHERE deptno = x;
IF SQL%FOUND
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;
/
[Updated on: Tue, 18 September 2007 00:42] Report message to a moderator
|
|
|
|
Re: sql%found problem [message #268288 is a reply to message #268215] |
Tue, 18 September 2007 02:13   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
OK, ignoring the fact that you seem to be going about the problem a kind of weird way, I'm assuming that this issue is purely academic, for practice.
Why test a boolean?
Why not just
Which completely gets rid of the unnecessary control structure.
The function would be more like
CREATE OR REPLACE FUNCTION fvalid (x NUMBER)
RETURN BOOLEAN
IS
BEGIN
UPDATE emp
SET empno = 99
WHERE deptno = x;
RETURN SQL%FOUND
EXCEPTION
/*HORRIBLE PIECE OF EXCEPTION HANDLING HERE. Do not use it
Use PROPER practices
WHEN OTHERS
THEN
RETURN FALSE;
*/
END;
|
|
|
|
Re: sql%found problem [message #268300 is a reply to message #268294] |
Tue, 18 September 2007 02:40   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Nope, it's not. I only supplied it because this appeared to be an academic question.
Create a basic validation function.
Validate the deptid by calling the function before calling any dml.
create or replace
FUNCTION fvalid (p_dptid NUMBER)
RETURN BOOLEAN
IS
l_sal NUMBER;
CURSOR c1 IS SELECT salary
FROM employees
WHERE department_id = p_dptid;
BEGIN
OPEN c1;
FETCH c1 INTO l_sal;
RETURN c1%FOUND;
--EXCEPTION
--SOME EXCEPTION HANDLING HERE
END;
[edit] Note that I used a different table, but the principle is the same
Quote: |
it is working onlyon dml statements . it raises its own exception when
sql%found is false;
it works fine on dml...
|
I cannot understand what this means. Please try to clarify
[Updated on: Tue, 18 September 2007 02:41] Report message to a moderator
|
|
|
Re: sql%found problem [message #268311 is a reply to message #268300] |
Tue, 18 September 2007 02:58   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This could be personal taste, but a test for sql%rowcount > 0 looks so much more 'natural'.
Each and every time I see this topic, the first thing I think is that %found is not meant for this.. (although I tested it and it works)
[Edit: typo]
[Updated on: Tue, 18 September 2007 02:58] Report message to a moderator
|
|
|
Re: sql%found problem [message #268319 is a reply to message #268311] |
Tue, 18 September 2007 03:04   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
To me, it seems just as if not more natural. %FOUND having a value of true is literally stating "I have found at least 1 row" Granted so does %ROWCOUNT and as you say it is a matter taste. I just figured that rather than introduce yet another method into the mix, I'd continue with the same 'flavour'
{Edit] Maybe that too was a waste of time. See below[/edit]
[Updated on: Tue, 18 September 2007 03:19] Report message to a moderator
|
|
|
Re: sql%found problem [message #268322 is a reply to message #268300] |
Tue, 18 September 2007 03:13   |
ashish2345
Messages: 50 Registered: September 2007
|
Member |
|
|
pablolee wrote on Tue, 18 September 2007 02:40 |
Quote: |
it is working onlyon dml statements . it raises its own exception when
sql%found is false;
it works fine on dml...
|
I cannot understand what this means. Please try to clarify
|
actually i was trying like this.. it is checking when dept no is valid ie 40 but raising exception when wrong no is fed. it shoul print dept invalid but it raises no data found exception
SQL> create or replace function ffvalid(y number)
2 return boolean
3 is
4 x number;
5 begin
6 select deptno into x from dept where deptno=y;
7 return sql%found;
8 end;
9 /
Function created.
SQL> create or replace procedure p2(dd number)
2 is
3 m boolean;
4 begin
5 m:=ffvalid(dd);
6 if m=true then
7 dbms_output.put_line('department valid');
8 else
9 dbms_output.put_line('dept invalid');
10 end if;
11 end;
12 /
Procedure created.
SQL> execute p2(40);
department valid
PL/SQL procedure successfully completed.
SQL> execute p2(45);
BEGIN p2(45); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.FFVALID", line 6
ORA-06512: at "SCOTT.P2", line 5
ORA-06512: at line 1
|
|
|
Re: sql%found problem [message #268325 is a reply to message #268322] |
Tue, 18 September 2007 03:17   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
How does Your function compare to mine? They are completely different functions. I use an explicit cursor, you use select into. Not the same thing.
|
|
|
Re: sql%found problem [message #268326 is a reply to message #268325] |
Tue, 18 September 2007 03:25   |
ashish2345
Messages: 50 Registered: September 2007
|
Member |
|
|
pablolee wrote on Tue, 18 September 2007 03:17 | How does Your function compare to mine? They are completely different functions. I use an explicit cursor, you use select into. Not the same thing.
|
thanks alot i got point . being a newbee i am in stage of clearing my concepts
thanks again
[mod-edit] removed illiterate IM speak.
[Updated on: Tue, 18 September 2007 08:41] by Moderator Report message to a moderator
|
|
|
Re: sql%found problem [message #268327 is a reply to message #268322] |
Tue, 18 September 2007 03:26   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
ashish2345 wrote on Tue, 18 September 2007 10:13 |
3 m boolean;
...
6 if m=true then
|
A former colleague of mine used to tell that if you did this at university, you would fail, because you clearly did not understand boolean logic.
|
|
|
Re: sql%found problem [message #268329 is a reply to message #268326] |
Tue, 18 September 2007 03:34  |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
OK, As I said, your function uses a SELECT INTO. This requires that EXACTLY one row is returned by the select. If more than one row is returned then it will raise a TOO_MANY_ROWS error, if no rows are returned it will raise a NO_DATA_FOUND error. By using an explicit cursor, I can open and fetch 0, 1 or many rows without raising an exceptions (other circumstances notwithstanding) and use cursor attributes (such as %FOUND, %ROWCOUNT, %NOTFOUND) to asses the state of that cursor at any point I choose.
Did you mention that you have a PL/SQL exam tomorrow? I have to say that you are seriously ill prepared for it and might want to consider rescheduling until you have more (basic) knowledge.
|
|
|