select statement using 'IN' and NVL 4

[JKrumins]

Hi
I want to write a query where the user can input either:
(a) a single customer_id, to return all records for that customer
or
(b) a list of several customer_ids, perhaps seperated by commas, to return all records for those customers
or
(c) Null, to return records for ALL customer_id's.

I've tried something like:
... where cust_id in NVL('&customer_id','%')
[that only works for (a) - nothing returned for (b) & (c)]

or maybe I should use LIKE instead of IN ?

Can anyone point me to a workable solution?

 

[sysrsh]

Hi Gunjan,

Thanks for replying so promptly. Coming from a SQL Server background, PLSQL is a real challenge. I'm getting the following error when I execute my stored proc.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "PARSELIST", line 23

Here's the code for parseString :
create or replace function parseList(p in varchar2)
return emplist
is
retval emplist := emplist();
nextPos integer := 0;
lastPos integer := 1;
ind binary_integer := 0;
begin
if p is null then
return null;
end if;

loop
nextPos := instr(p, ',' , lastPos);
exit when nextPos = 0;
ind := ind +1;
retval.extend;
retval(ind) := substr(p, lastPos, nextPos-lastPos);
lastPos := nextPos + 1;
end loop;
ind := ind +1;
retval.extend;
retval(ind) := substr(p, lastPos);
return retval;
end;
/

Here's my SP:
CREATE OR REPLACE
PROCEDURE testProc(p_user_id IN varchar2,
p_recordset OUT Types.cursor_type) AS
BEGIN
OPEN p_recordset FOR
select Trim(f.user_id) as "User ID",
Trim(f.shell_desc) as "Shell Desc",
Trim(f.tran_profile) as "Tran Profile",
Trim(f.field_profile) as "Field Profile",
Trim(f.port_profile) as "Port Profile",
Trim(f.supervisor_shell) as "Supervisor Shell",
Trim(f.lvl) as "lvl"
from fdr_profiles f
where ((p_user_id = '' OR p_user_id IS NULL)
OR lower(rtrim(f.user_id)) in
(select * from table( cast ( parseList('p_user_id')
as empList)))
)
order by f.user_id, f.field_profile;
END testProc;
/

I execute is as follows :

DECLARE
v_cursor Types.cursor_type;
user_id fdr_profiles.user_id%TYPE;
shell_desc fdr_profiles.shell_desc%TYPE;
tran_profile fdr_profiles.tran_profile%TYPE;
fld_profile fdr_profiles.field_profile%TYPE;
port_profile fdr_profiles.port_profile%TYPE;
sup_shell fdr_profiles.supervisor_shell%TYPE;
lvl fdr_profiles.lvl%TYPE;
x varchar2(50) :=null;
BEGIN
testProc(p_user_id => 'abc,def',
p_recordset => v_cursor);
LOOP
FETCH v_cursor INTO
user_id, shell_desc, tran_profile, fld_profile,
port_profile, sup_shell, lvl;
IF v_cursor%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('no records');
ELSE
DBMS_OUTPUT.PUT_LINE(user_id || ' | ' || shell_desc|| ' | ' || tran_profile);
END IF;
EXIT WHEN v_cursor%NOTFOUND;
END LOOP;
CLOSE v_cursor;
END;
/

 

[sem]

Sysrsh, pass 'abc,def'. Though you will not get in ('abc','def') but rather something similar to in (select...) where the values returned by that query are 'abc','def'.

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Hi,
I am working on Oracle 8.0.6. I am getting this error and do not know how to solve it. I have created a package and inside it one function which splits the comma separated values. But when I run the query, I get the error

Package *****
create or replace package cosddl_test_pkg
as

type tab_obj_class is table of number
index by binary_integer;

function split_comma_val
(
n_comma_char IN varchar2
) return tab_obj_class;

end cosddl_test_pkg;
/

create or replace package body cosddl_test_pkg
as

function split_comma_val
(
n_comma_char IN varchar2
) return tab_obj_class
is
var_obj_class tab_obj_class;
l_next_pos number := 0;
l_last_pos number := 1;
l_ind_tab number := 0;
begin

loop
l_next_pos := instr(n_comma_char, ',', l_last_pos);
exit when l_next_pos = 0;
l_ind_tab := l_ind_tab + 1;
var_obj_class(l_ind_tab) := substr(n_comma_char,
l_last_pos,
l_next_pos - l_last_pos
);
l_last_pos := l_next_pos + 1;
end loop;

l_ind_tab := l_ind_tab + 1;
var_obj_class(l_ind_tab) := substr(n_comma_char, l_last_pos);
return var_obj_class;
end split_comma_val;
end cosddl_test_pkg;
/
****************

define l_class=107,108,581,1226
select la_int_id
from utp_legacy
start with la_object_class in
(select *
from table( cast( cosddl_test_pkg.split_comma_val ('&l_class') as cosddl_test_pkg.tab_obj_class))
)
connect by prior la_int_id = la_parent_int_id;

old 5: from table( cast( cosddl_test_pkg.split_comma_val ('&l_class') as cosddl_test_pkg.tab_obj_class))
new 5: from table( cast( cosddl_test_pkg.split_comma_val ('107,108,581,1226') as cosddl_test_pkg.tab_obj_class))
from table( cast( cosddl_test_pkg.split_comma_val ('107,108,581,1226') as cosddl_test_pkg.tab_obj_class))
*
ERROR at line 5:
ORA-00907: missing right parenthesis

 

[sem]

8.0 syntax differs. Try
...
select *
from the( select cast( cosddl_test_pkg.split_comma_val ('&l_class') as cosddl_test_pkg.tab_obj_class) from dual)



Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

I am now getting the error


old 5: from the( select (cast( cosddl_test_pkg.split_comma_val ('&l_class') as cosddl_test_pkg.tab_obj_class)) from dual)
new 5: from the( select (cast( cosddl_test_pkg.split_comma_val ('107,108,581,1226') as cosddl_test_pkg.tab_obj_class)) from dual)
from the( select (cast( cosddl_test_pkg.split_comma_val ('107,108,581,1226') as cosddl_test_pkg.tab_obj_class)) from dual)
*
ERROR at line 5:
ORA-22907: invalid CAST to a type that is not a nested table or VARRAY


It seem that I have to use dynamic SQL.

 

[sem]

You should define your collection type as database-wide, not package-wide:

create or replace type tab_obj_class is table of number;

You should also make appropriate changes into your code to initialize collection and expand it (add rows) manually.

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Hi Dima,

The problem is that when I try to define using
create type
An error is thrown
SQL> create type tab_obj_class is table of integer;
2 /
create type tab_obj_class is table of integer;
*
ERROR at line 1:
ORA-00439: feature not enabled: Objects

And so I have declare it inside the package.

Thanks and Regards
Gunjan

 

[sem]

I think you have no chance to use this technique. Do you have SE?

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Yeah I have no chance of using this. And so I am converting my SQL into dynamic sql.
What's SE?

thanks and Regards
Gunjan

 

[sem]

SE stands for Standard Edition as an opposite to Personal Edition and Enterprize Edition

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Oh.....
No I have Enterprise Edition.

Thanks and Regards,
Gunjan

 

[sem]

So you've probably refused to install it
I think you may add it (launch installer and select it) Though you should stop (and probably back up) your database for this action.

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

could not do this. Forget about tinkering with the db now. Have to work with what ever is available. Somebody else has installed it quite long time back and I have come right now in the picture.

Thanks and Regards
Gunjan