Home » SQL & PL/SQL » SQL & PL/SQL » sql%found problem
sql%found problem [message #268215] Tue, 18 September 2007 00:15 Go to next message
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 #268218 is a reply to message #268215] Tue, 18 September 2007 00:16 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
sql%found


After opening cursor use this one. Error will raise if you don't open cursor.
Re: sql%found problem [message #268231 is a reply to message #268215] Tue, 18 September 2007 00:41 Go to previous messageGo to next message
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 #268235 is a reply to message #268215] Tue, 18 September 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: sql%found problem [message #268288 is a reply to message #268215] Tue, 18 September 2007 02:13 Go to previous messageGo to next message
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
RETURN sql%FOUND;


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 #268294 is a reply to message #268288] Tue, 18 September 2007 02:21 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
thanks a lot for solution. its superb indeed buti checked it is working onlyon dml statements . it raises its own exception when
sql%found is false;
it works fine on dml...

[Updated on: Tue, 18 September 2007 02:28]

Report message to a moderator

Re: sql%found problem [message #268300 is a reply to message #268294] Tue, 18 September 2007 02:40 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
its superb indeed

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Oracle not available and shared memory realm does not exist
Next Topic: COUNT NULL ROWS
Goto Forum:
  


Current Time: Wed Feb 19 03:11:53 CST 2025