On Sep 27, 2:43 pm, "Anuradha Uduwage" <[EMAIL PROTECTED]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
You create a function, not a procedure:
SQL> create or replace function check_deptno (deptNum number)
2 return number
3 is
4 v_deptNum number;
5 returnValue number;
6 begin
7 select deptno into v_deptNum from dept where deptno = deptNum;
8 if deptNum = v_deptNum then
9 returnValue := -1;
10 end if;
11 return returnValue;
12 exception
13 when NO_DATA_FOUND THEN
14 dbms_output.put_line('Department Number does not exists');
15 returnValue := 1;
16 return returnValue;
17 end check_deptno;
18 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> select check_deptno(10) from dual;
CHECK_DEPTNO(10)
----------------
-1
SQL> select check_deptno(30) from dual;
CHECK_DEPTNO(30)
----------------
-1
SQL> select check_deptno(70) from dual;
CHECK_DEPTNO(70)
----------------
1
SQL> select check_deptno(40) from dual;
CHECK_DEPTNO(40)
----------------
-1
SQL> select check_deptno(90) from dual;
CHECK_DEPTNO(90)
----------------
1
SQL> select check_deptno(60) from dual;
CHECK_DEPTNO(60)
----------------
-1
SQL>
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
