Thanks, I fixed the issues, I am new to stored procedures my biggest issue
is the stored procedure print out value at the end or its execution rather
than having to use print :someValue;
As you mentioned i fixed the issue
create or replace procedure check_dno (deptNum number, returnValue out
number)
is
v_deptNum number;
begin
select deptno into v_deptNum from dept where deptno = deptNum;
if deptNum = v_deptNum then
returnValue := -1;
else
returnValue := 1;
end if;
exception
when NO_DATA_FOUND THEN
dbms_output.put_line('Department Number does not exists');
returnValue := 1;
end check_dno;
/
But How can I avoid this process
SQL> variable gReturn number;
SQL> exec check_dno(60, :gReturn);
PL/SQL procedure successfully completed.
SQL> print :gReturn;
GRETURN
----------
1
SQL> exec check_dno(10, :gReturn);
PL/SQL procedure successfully completed.
SQL> print :gReturn;
GRETURN
----------
-1
I want to print the value at the end of the execution of the stored
procedure.
Thanks,
On Sat, Sep 27, 2008 at 2:09 PM, rob wolfe <[EMAIL PROTECTED]>wrote:
>
> This is exactly as it should behave. It is an exception for a select
> statement to not return a value at all (remember that this is different
> than returning null).
>
> You should trap the NO_DATA_FOUND exception similarly to this example
> from the searchable Oracle Documentation that you can find at
> http://tahiti.oracle.com
>
> DECLARE
> Emp_number INTEGER := 9999;
> Emp_name VARCHAR2(10);
> BEGIN
> SELECT Ename INTO Emp_name FROM Emp_tab
> WHERE Empno = Emp_number; -- no such number
> DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
> END;
>
>
>
>
>
> JNewMember wrote:
> > I have department table (dept) and I have return a stored procedure to
> > return value -1 if the department exist but if user enter other than a
> > department number thats in the table it should return 1.
> >
> > As I shown bellow... Stored procedure works fine when execute the
> > stored procedure with the exiting department number. When execute it
> > with non existing department number i get the fallowing error
> >
> > 1 create or replace procedure check_dno (deptNum number,
> > returnValue out number)
> > 2 is
> > 3 v_deptNum number;
> > 4 begin
> > 5 select deptno into v_deptNum from dept where deptno = deptNum;
> > 6 if deptNum = v_deptNum then
> > 7 returnValue := -1;
> > 8 else
> > 9 returnValue := 1;
> > 10 end if;
> > 11* end check_dno;
> > SQL> /
> >
> > Procedure created.
> >
> > SQL> variable gReturn number;
> > SQL> exec check_dno(80, :gReturn);
> > BEGIN check_dno(80, :gReturn); END;
> >
> > *
> > ERROR at line 1:
> > ORA-01403: no data found
> > ORA-06512: at "ANU.CHECK_DNO", line 5
> > ORA-06512: at line 1
> >
> > Stored Procedure works for the departments that are in the dept table.
> >
> > SQL> exec check_dno(20, :gReturn);
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL> print :gReturn;
> >
> > GRETURN
> > ----------
> > -1
> >
> >
> > >
> >
>
> >
>
--
Anuradha Uduwage
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
