Why this stored procedure in oracle not working?-CodePudding

I am new to oracle.

I have this stored procedure

create or replace PROCEDURE MJ_GetDepartments
(
   mycursor OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN mycursor FOR 
    select rd.DEPT_PK, rd.long_fname from 
    ref_department rd
    WHERE rd.DEPT_PK between
    (
       select rur.dept_idf , rur.dept_idt
       from ref_user_role RUR
       WHERE rur.user_fk = 10005941
    )
END;

CodePudding user response：

Did you mean something like this?

create or replace PROCEDURE MJ_GetDepartments
  (mycursor OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN mycursor FOR 
    select rd.DEPT_PK, rd.long_fname 
    from ref_department rd join ref_user_role rur 
      on rd.DEPT_PK between rur.dept_idf and rur.dept_idt
    where rur.user_fk = 10005941;
END;

CodePudding user response：

My guess is you're trying to query using values for BETWEEN that come from a query?

You could do:

select rd.DEPT_PK, rd.long_fname from 
ref_department rd
WHERE rd.DEPT_PK between
(
   select rur.dept_idf
   from ref_user_role RUR
   WHERE rur.user_fk = 10005941
) and
(
   select rur.dept_idt
   from ref_user_role RUR
   WHERE rur.user_fk = 10005941
)

But I think I'd do:

select rd.DEPT_PK, rd.long_fname 
from 
  ref_department rd
  INNER JOIN 
  ref_user_role RUR 
  ON 
    rd.DEPT_PK BETWEEN rur.dept_idf AND rur.dept_idt
WHERE rur.user_fk = 10005941

select rd.DEPT_PK, rd.long_fname 
from 
  ref_department rd
  INNER JOIN 
  (
    select rur.dept_idf, rur.dept_idt
    from ref_user_role RUR
    WHERE rur.user_fk = 10005941
  ) x ON rd.DEPT_PK between x.dept_idf AND x.dept_idf

Or select your from/to into two variables and then between those..

Whichever makes more sense to you..