CODEWITHSUNDEEP
oracle-databasestored-procedures
user2380844
user2380844

Reputation: 307

oracle stored procedure not working

the below procedure complies fine,

 CREATE PROCEDURE PROCEDURE1
          (v_MGR int,
          v_empid IN OUT int)
    AS
    BEGIN
    v_empid :=0;
    IF (v_mgr IS NOT NULL AND v_mgr <> '') then
    EXECUTE IMMEDIATE 'SELECT EMPNO
          FROM EMP
       WHERE MGR = Rtrim(v_MGR)' into v_empid;
    END IF;
    END PROCEDURE1;

but when i run 

DECLARE
  V_MGR NUMBER;
  V_EMPID NUMBER;
BEGIN
  V_MGR := 7902;
  V_EMPID := NULL;

  PROCEDURE1(
    V_MGR => V_MGR,
    V_EMPID => V_EMPID
  );
  DBMS_OUTPUT.PUT_LINE('V_EMPID = ' || V_EMPID);
END;

the output should be v_empid =2356

but its always showing v_empid = 0 please help to get proper answer

Upvotes: 0

Views: 2537

Answers (3)

Nick Krasnov
Nick Krasnov

Reputation: 27251

  1. Why is the out parameter is 0? Take a look at the if condition in the procedure 

    IF (v_mgr IS NOT NULL AND v_mgr <> '')

    especially at its second part AND v_mgr <> ''. Oracle treats empty string '' as null and any comparison to null leads to unknown result, thus the above IF condition always evaluates to false so execute immediate statement never executes and as a result the value of v_empid will never be overwritten.

  2. In this particular situation there is absolutely no need of using dynamic SQL(native dynamic sql execute immediate), because there is no dynamic construction of the query - table and columns are known at compile-time. You simply could use static sql instead:

  3. If your query returns more than one row, you will hit too_many_rows exception. You either should guarantee that your query returns exactly one row, by including rownum=1 in the where clause of the query(if there is a change of returning multiple rows) or you use a collection as the out parameter, to return the result set:

    create or replace type T_EmpNums is table of number;
/

create or replace procedure procedure1(
    v_mgr  int,
    v_emps out T_empnums
)
as
begin
  if v_mgr is not null  
  then
     select empno
       bulk collect into v_emps
       from emp
      where mgr = v_mgr;
  end if;
end;
/

declare
  v_mgr number;
  v_empids T_EmpNums;
begin
  v_mgr := 7902;
  procedure1(v_mgr, v_empids);
  if v_empids is not empty
  then 
    for empno in v_empids.first .. v_empids.last
    loop
      dbms_output.put_line('v_empid = ' || to_char(v_empids(empno)));
    end loop;
  end if;
end;

Upvotes: 5

itmitica
itmitica

Reputation: 511

Well, you declare v_MGR int and then you go and test this v_mgr <> '' and use this Rtrim(v_MGR) in your condition.

Which is it, (var)char or number for mgr and v_mgr?

Upvotes: 1

Harshit
Harshit

Reputation: 560

try this

CREATE PROCEDURE PROCEDURE1
      (v_MGR int,
      v_empid IN OUT int)
AS
BEGIN
v_empid :=0;
IF (v_mgr IS NOT NULL AND v_mgr <> '') then
SELECT EMPNO into v_empid
      FROM EMP
   WHERE MGR = Rtrim(v_MGR) ;
END IF;
END PROCEDURE1;

Upvotes: 0

Related Questions