Toad World® Forums

lists in plsql oracle


#1

Hello,

Obviously this example is working

declare
cursor c_salary is
select first_name, salary from hr.employees
where salary in(9000, 6000, 4800);
begin
for s in c_salary loop
dbms_output.put_line(s.first_name || ’ ’ || s.salary);
end loop;
end;

Alexander 9000
Bruce 6000
David 4800
Valli 4800
Daniel 9000
Peter 9000
Allan 9000
Pat 6000

PL/SQL procedure successfully completed.

But I need another example which I can not see the data obtained

set serveroutput on;
declare
v_salary varchar2(50):= ‘9000,6000,4800’; – I NEED THIS LIST
cursor c_salary is
select first_name, salary from hr.employees
where to_char(salary) in(v_salary);
begin
for s in c_salary loop
dbms_output.put_line(s.first_name || ’ ’ || s.salary);
end loop;
end;

PL/SQL procedure successfully completed.

I can not see the printing or output of data, do you have any idea that allows me to see the printing of data

Thanks a lot

:slight_smile:


#2

You can’t just replace the v_salary as a string. Here is the SQL captured from SGA from you PL/SQL.

SELECT FIRST_NAME, SALARY
FROM HR.EMPLOYEES
WHERE TO_CHAR (SALARY) IN (:B1)

Oracle will replace the v_salary as the only expression to compare, so your SQL actually compare with the whole string of ‘9000,6000,4800’.

Or you may try this

declare
v_salary1 varchar2(50):= ‘9000’;
v_salary2 varchar2(50):= ‘6000’;
v_salary3 varchar2(50):= ‘4800’;
cursor c_salary is
select first_name, salary from hr.employees
where to_char(salary) in(v_salary1, v_salary2, v_salary3);
begin
for s in c_salary loop
dbms_output.put_line(s.first_name || ’ ’ || s.salary);
end loop;
end;

And the SQL get from SGA would be like below.

SELECT FIRST_NAME, SALARY
FROM HR.EMPLOYEES
WHERE TO_CHAR (SALARY) IN (:B3, :B2, :B1)

Thanks,

Tony Ng


#3

There is another way to keep the list. However, you have to create a type of table first.

CREATE TYPE salary_type AS TABLE OF VARCHAR2(50);

declare
v_salary varchar2(50):= ‘9000,6000,4800’;
comma_pos INTEGER;
cur_pos INTEGER := 1;
salary_tab salary_type := salary_type();
cursor c_salary is
select first_name, salary from hr.employees
where to_char(salary) in(select * from table(salary_tab));
BEGIN
v_salary := v_salary||’,’;
LOOP
comma_pos := INSTR(v_salary, ‘,’, cur_pos);
EXIT WHEN comma_pos = 0;
salary_tab.EXTEND;
salary_tab(salary_tab.COUNT) := SUBSTR(v_salary, cur_pos, comma_pos - cur_pos);
cur_pos := comma_pos + 1;
END LOOP;
for s in c_salary loop
dbms_output.put_line(s.first_name || ’ ’ || s.salary);
end loop;
end;

Here is the SQL from SGA

SELECT FIRST_NAME, SALARY
FROM HR.EMPLOYEES
WHERE TO_CHAR (SALARY) IN (SELECT * FROM TABLE (:B1))