Simple Oracle stored procedure

Question

I have this SQL query:

select title
from DEPARTMENT;

I tried to write a stored procedure:

create PROCEDURE select_some
(whats VARCHAR2 ,c_select_some OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN c_select_some FOR
SELECT whats
FROM department;
END select_some;
/

But where I execute it with "title" parameter I got 8 rows with 'title' instead actual content. What's wrong?

Execution:

var whats varchar2(20)
variable whats = 'Title'
variable mycursor refcursor;

exec select_some (:whats, mycursor);

user_997660 · Answer

For this, you need to use dynamic SQL.

Something like this

create or replace procedure select_from_department(
  col_name in varchar2,
  c_res      out sys_refcursor
) 
is
  l_sql varchar2(300);
begin
  l_sql := 'select ' || dbms_assert.simple_sql_name(col_name) || ' from departments';
  open c_res for l_sql ;
end;

DEMO

Simple Oracle stored procedure

1 Answers