Oracle input parameter not able to get results

Question

I have oracle stored procedure where I pass an input parameter and get a cursor as an output parameter:

CREATE OR REPLACE PROCEDURE OS_DIVISIONDDL(
  p_dept_name       IN VARCHAR2,
  p_cursor_division OUT SYS_REFCURSOR
) IS
  tmpVar NUMBER;
BEGIN
  tmpVar := 0;

  OPEN p_cursor_division FOR
    select Division_code,
           Division_name 
      from DIVISION_TBL 
     where dept_code=p_dept_name;

EXCEPTION
 WHEN NO_DATA_FOUND THEN
   NULL;
 WHEN OTHERS THEN
   -- Consider logging the error and then re-raise
   RAISE;
END OS_DIVISIONDDL;
/

The input parameter I am able to see using dbms print, but where condition is not executed by server Am i going wrong anywhere

DIVISION_CODE   CHAR (10 Char)
DIVISION_NAME   VARCHAR2 (30 Char)
DEPT_CODE       CHAR (10 Char)
COMPANY_CODE    CHAR (3 Char)
LOCATION_CODE   CHAR (3 Char)
CREATED_DATE    TIMESTAMP(6)
CREATED_BY  VARCHAR2 (50 Char)
MODIFIED_DATE   TIMESTAMP(6)
MODIFIED_BY VARCHAR2 (50 Char)

could you, please, provide us with the result of "desc DIVISION_TBL"? or just list all the columns and their data types? — smnbbrv
– smnbbrv, Commented Feb 3, 2014 at 11:09
dept_code=RPAD(p_dept_name,10); CHAR always has all the allocated spaces filled. When you hard code the String the comparison will be right always. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Feb 3, 2014 at 11:22
I did hardcode the where condition i am able to get data when i Right click and execute with input param I am not able to get data — user3193026
– user3193026, Commented Feb 3, 2014 at 11:22
Never use CHAR. See e.g. stackoverflow.com/q/7747184/272735 — user272735
– user272735, Commented Feb 3, 2014 at 13:57

smnbbrv · Accepted Answer · 2014-02-03 11:21:46Z

1

Perhaps you have a problem of implicit convertions:

declare
  a             char(10) := '123';
  b             varchar2(100) := '123';

  dummy         integer;
begin
  select null
  into dummy
  from dual
  where a = b;
end;

this code raises "no data found" but looks totally the same.

The problem here is that char(10) is not equal "123" but equal to "123<7 spaces here>". And varchar2(100) remains "123".

Check for char and varchar2 differences e.g. in Oracle docs

answered Feb 3, 2014 at 11:21

smnbbrv

24.7k9 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle input parameter not able to get results

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related