Home » SQL & PL/SQL » SQL & PL/SQL » how to use variable to store comma seperated values (11G)
how to use variable to store comma seperated values [message #668977] Tue, 27 March 2018 00:45 Go to next message
satya_ora
Messages: 4
Registered: March 2018
Junior Member
Dear All,

I am writing a package which has two functions as mentined below.
I have following questions. Please suggest how to resolve these issues

1. How to create a variable which can be used as a table instead of regexp so that I can write query as follows

v_where_query := v_where_query || ' AND ID IN (SELECT ID FROM TEMP TABLE)';

2. How to handle date exception and raise own custom error message. I tried but got oracle error message only Sad

3. how to bind variable in regexp. I tried to use regexp as follows and passed this variable along with date in query when executing it but getting error that all variables are not bind.



v_where_query := v_where_query || ' AND ID IN (SELECT regexp_substr(:b,'[^,]+',1, level) from dual connected by regexp_substr(:b,'[^,]+',1, level) is not null );

================ sample code below ============================

CREATE TYPE TEST_TYPE AS OBJECT
(
ID VARCHAR2(100),
NAME VARCHAR2(100),
Hire_Date DATE
);
/
CREATE TYPE TEST_TYPE_TBL AS TABLE OF TEST_TYPE;
/
CREATE PACKAGE PKG_TEST AS
 function fun_main(ID VARCHAR2, HireDate Date)
 return TEST_TYPE_TBL pipelined;

function get_rec (P_ID VARCHAR2, P_HireDate Date)
return VARCHAR2;
END PKG_TEST;
/
CREATE PACKAGE BODY PKG_TEST AS

function fun_main(ID VARCHAR2, HIREDATE DATE)
 return TEST_TYPE_TBL pipelined
IS
v_query VARCHAR(4000);
v_rc sys_refcursor;

out_rs TEST_TYPE := TEST_TYPE (NULL, NULL, NULL);
BEGIN
v_query := get_rec('1,2,3')
open v_rc for v_query USING HIREDATE;
LOOP v_rc into out_rs.ID, out_rs.Name, out_rs.hire_date
EXIT WHEN v_rc%notfound;
pipe row(out_rs);
END LOOP
close v_rc;
return;
END fun_main;


function get_rec (P_ID VARCHAR2, P_HireDate Date)
return VARCHAR2
IS
v_select_query
v_where_query
begin
V_select_query = 'SELECT ID, NAME, HIREDATE FROM TABLE ';
v_where_query := ' WHERE 1=1';
if P_ID IS NOT NULL THEN
v_where_query := v_where_query  || ' AND ID IN (SELECT regexp_substr(''' || P_ID || ''',''[^,]+'',1, level) from dual connected by regexp_substr(''' || P_ID || ''',''[^,]+'',1, level) is not null )';
END IF;

IF P_HireDate is not null then
	v_where_query := v_where_query  || ' AND HIRE_DATE =:a';
ELSE
	v_where_query := v_where_query  || ' AND (1 =1 OR :a IS NULL)';
END IF;

return V_select_query || v_where_query ;
end get_rec ;

End PKG_TEST;

--moderator edit: added [code] tags, please do so yourself in future

[Updated on: Tue, 27 March 2018 01:06] by Moderator

Report message to a moderator

Re: how to use variable to store comma seperated values [message #668980 is a reply to message #668977] Tue, 27 March 2018 01:45 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read


I am havng trouble understanding what you are trying to do. It looks as though you are trying to construct a SQL statement and then use it to create an explicit cursor. Is that right?

This is definitely wrong,
V_select_query = 'SELECT ID, NAME, HIREDATE FROM TABLE ';
you would need to append a table function to that, not a predicate.
Re: how to use variable to store comma seperated values [message #668982 is a reply to message #668980] Tue, 27 March 2018 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suggest you have a read of this: varying in list
Re: how to use variable to store comma seperated values [message #668983 is a reply to message #668977] Tue, 27 March 2018 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
satya_ora wrote on Tue, 27 March 2018 06:45

2. How to handle date exception and raise own custom error message. I tried but got oracle error message only Sad
There are several date errors. Which did you have in mind and what did you try?
satya_ora wrote on Tue, 27 March 2018 06:45

3. how to bind variable in regexp. I tried to use regexp as follows and passed this variable along with date in query when executing it but getting error that all variables are not bind.



v_where_query := v_where_query || ' AND ID IN (SELECT regexp_substr(:b,'[^,]+',1, level) from dual connected by regexp_substr(:b,'[^,]+',1, level) is not null );
You might think that's one bind but oracle thinks it's two.
You need to supply a seperate value on the open for every bind.
Re: how to use variable to store comma seperated values [message #668988 is a reply to message #668977] Tue, 27 March 2018 16:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Your code contains a lot of errors. Please see the demonstration below using corrected code and a demo table to demonstrate the usage of the code.

-- demo table for demonstration:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM demo
  2  /

ID         NAME                           HIREDATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018
7654       MARTIN                         Tue 27-Mar-2018

5 rows selected.

-- object types you provided:
SCOTT@orcl_12.1.0.2.0> CREATE TYPE TEST_TYPE AS OBJECT
  2    (ID	   VARCHAR2(100),
  3  	NAME	   VARCHAR2(100),
  4  	Hire_Date  DATE);
  5  /

Type created.

SCOTT@orcl_12.1.0.2.0> CREATE TYPE TEST_TYPE_TBL AS TABLE OF TEST_TYPE;
  2  /

Type created.

-- corrected package:
SCOTT@orcl_12.1.0.2.0> CREATE PACKAGE PKG_TEST AS
  2    function fun_main
  3  	 (P_ID		  VARCHAR2  DEFAULT NULL,
  4  	  P_HireDate	  Date	    DEFAULT NULL)
  5  	 return TEST_TYPE_TBL pipelined;
  6    function get_rec
  7  	 (P_ID		  VARCHAR2  DEFAULT NULL,
  8  	  P_HireDate	  Date	    DEFAULT NULL)
  9  	 return VARCHAR2;
 10  END PKG_TEST;
 11  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE PACKAGE BODY PKG_TEST AS
  2    function fun_main
  3  	 (P_ID		  VARCHAR2  DEFAULT NULL,
  4  	  P_HireDate	  Date	    DEFAULT NULL)
  5  	 return TEST_TYPE_TBL pipelined
  6    IS
  7  	 v_query	  VARCHAR(4000);
  8  	 v_rc		  sys_refcursor;
  9  	 out_rs 	  TEST_TYPE := TEST_TYPE (NULL, NULL, NULL);
 10    BEGIN
 11  	 v_query := get_rec (P_ID, P_Hiredate);
 12  	 open v_rc for v_query USING P_ID, P_ID, P_HireDate;
 13  	 LOOP
 14  	   FETCH v_rc into out_rs.ID, out_rs.Name, out_rs.hire_date;
 15  	   EXIT WHEN v_rc%notfound;
 16  	   pipe row(out_rs);
 17  	 END LOOP;
 18  	 close v_rc;
 19  	 return;
 20    END fun_main;
 21  
 22    function get_rec
 23  	 (P_ID		  VARCHAR2  DEFAULT NULL,
 24  	  P_HireDate	  Date	    DEFAULT NULL)
 25  	 return VARCHAR2
 26    IS
 27  	 v_select_query   VARCHAR2(4000);
 28  	 v_where_query	  VARCHAR2(4000);
 29    begin
 30  	 V_select_query := 'SELECT ID, NAME, HIREDATE FROM demo ';
 31  	 v_where_query := ' WHERE 1=1';
 32  	 if P_ID IS NOT NULL THEN
 33  	   v_where_query := v_where_query  || ' AND ID IN (SELECT regexp_substr(:b,''[^,]+'',1, level) from dual connect by regexp_substr(:b,''[^,]+'',1, level) is not null) ';
 34  	 ELSE
 35  	   v_where_query := v_where_query || ' AND (1 =1 OR :b IS NULL OR :b IS NULL)';
 36  	 END IF;
 37  	 IF P_HireDate is not null then
 38  	   v_where_query := v_where_query  || ' AND HIREDATE =:a';
 39  	 ELSE
 40  	   v_where_query := v_where_query  || ' AND (1 =1 OR :a IS NULL)';
 41  	 END IF;
 42  	 return V_select_query || v_where_query;
 43    end get_rec;
 44  End PKG_TEST;
 45  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- test without ids or date:
SCOTT@orcl_12.1.0.2.0> COLUMN id   FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN name FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.get_rec () FROM DUAL
  2  /

PKG_TEST.GET_REC()
--------------------------------------------------------------------------------
SELECT ID, NAME, HIREDATE FROM demo  WHERE 1=1 AND (1 =1 OR :b IS NULL OR :b IS
NULL) AND (1 =1 OR :a IS NULL)


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.fun_main () FROM DUAL
  2  /

PKG_TEST.FUN_MAIN()(ID, NAME, HIRE_DATE)
--------------------------------------------------------------------------------
TEST_TYPE_TBL(TEST_TYPE('7369', 'SMITH', 'Mon 26-Mar-2018'), TEST_TYPE('7499', '
ALLEN', 'Tue 27-Mar-2018'), TEST_TYPE('7521', 'WARD', 'Mon 26-Mar-2018'), TEST_T
YPE('7566', 'JONES', 'Mon 26-Mar-2018'), TEST_TYPE('7654', 'MARTIN', 'Tue 27-Mar
-2018'))


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ())
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018
7654       MARTIN                         Tue 27-Mar-2018

5 rows selected.

-- test with id's, but without date:
SCOTT@orcl_12.1.0.2.0> COLUMN id   FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN name FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.get_rec ('7369,7499,7521') FROM DUAL
  2  /

PKG_TEST.GET_REC('7369,7499,7521')
--------------------------------------------------------------------------------
SELECT ID, NAME, HIREDATE FROM demo  WHERE 1=1 AND ID IN (SELECT regexp_substr(:
b,'[^,]+',1, level) from dual connect by regexp_substr(:b,'[^,]+',1, level) is n
ot null)  AND (1 =1 OR :a IS NULL)


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.fun_main ('7369,7499,7521') FROM DUAL
  2  /

PKG_TEST.FUN_MAIN('7369,7499,7521')(ID, NAME, HIRE_DATE)
--------------------------------------------------------------------------------
TEST_TYPE_TBL(TEST_TYPE('7369', 'SMITH', 'Mon 26-Mar-2018'), TEST_TYPE('7499', '
ALLEN', 'Tue 27-Mar-2018'), TEST_TYPE('7521', 'WARD', 'Mon 26-Mar-2018'))


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ('7369,7499,7521'))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018

3 rows selected.

-- test without id's and with date:
SCOTT@orcl_12.1.0.2.0> COLUMN id   FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN name FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.get_rec (null, TO_DATE('20180326','YYYYMMDD')) FROM DUAL
  2  /

PKG_TEST.GET_REC(NULL,TO_DATE('20180326','YYYYMMDD'))
--------------------------------------------------------------------------------
SELECT ID, NAME, HIREDATE FROM demo  WHERE 1=1 AND (1 =1 OR :b IS NULL OR :b IS
NULL) AND HIREDATE =:a


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.fun_main (null, TO_DATE('20180326','YYYYMMDD')) FROM DUAL
  2  /

PKG_TEST.FUN_MAIN(NULL,TO_DATE('20180326','YYYYMMDD'))(ID, NAME, HIRE_DATE)
--------------------------------------------------------------------------------
TEST_TYPE_TBL(TEST_TYPE('7369', 'SMITH', 'Mon 26-Mar-2018'), TEST_TYPE('7521', '
WARD', 'Mon 26-Mar-2018'), TEST_TYPE('7566', 'JONES', 'Mon 26-Mar-2018'))


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main (null, TO_DATE('20180326','YYYYMMDD')))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018

3 rows selected.

-- test with id's and date:
SCOTT@orcl_12.1.0.2.0> COLUMN id   FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN name FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.get_rec ('7369,7499,7521', TO_DATE('20180326','YYYYMMDD')) FROM DUAL
  2  /

PKG_TEST.GET_REC('7369,7499,7521',TO_DATE('20180326','YYYYMMDD'))
--------------------------------------------------------------------------------
SELECT ID, NAME, HIREDATE FROM demo  WHERE 1=1 AND ID IN (SELECT regexp_substr(:
b,'[^,]+',1, level) from dual connect by regexp_substr(:b,'[^,]+',1, level) is n
ot null)  AND HIREDATE =:a


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT pkg_test.fun_main ('7369,7499,7521', TO_DATE('20180326','YYYYMMDD')) FROM DUAL
  2  /

PKG_TEST.FUN_MAIN('7369,7499,7521',TO_DATE('20180326','YYYYMMDD'))(ID, NAME, HIR
--------------------------------------------------------------------------------
TEST_TYPE_TBL(TEST_TYPE('7369', 'SMITH', 'Mon 26-Mar-2018'), TEST_TYPE('7521', '
WARD', 'Mon 26-Mar-2018'))


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ('7369,7499,7521', TO_DATE('20180326','YYYYMMDD')))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7521       WARD                           Mon 26-Mar-2018

2 rows selected.
Re: how to use variable to store comma seperated values [message #668989 is a reply to message #668977] Tue, 27 March 2018 17:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
If you want to use some version of Tom Kyte's string to table methods, then you could do something like below.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM demo
  2  /

ID         NAME                           HIREDATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018
7654       MARTIN                         Tue 27-Mar-2018

5 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE TYPE TEST_TYPE AS OBJECT
  2    (ID	   VARCHAR2(100),
  3  	NAME	   VARCHAR2(100),
  4  	Hire_Date  DATE);
  5  /

Type created.

SCOTT@orcl_12.1.0.2.0> CREATE TYPE TEST_TYPE_TBL AS TABLE OF TEST_TYPE;
  2  /

Type created.

SCOTT@orcl_12.1.0.2.0> create or replace type myTabletype as table of varchar2(255)
  2  /

Type created.

SCOTT@orcl_12.1.0.2.0> create or replace function str2tbl
  2    (p_str	in varchar2,
  3  	p_delim in varchar2 default ',')
  4    return	 myTabletype pipelined
  5  as
  6    l_str	 varchar2 (32766) default p_str || p_delim;
  7    l_n	 number;
  8  begin
  9    loop
 10  	 l_n := instr (l_str, ',');
 11  	 exit when nvl (l_n, 0) = 0;
 12  	 pipe row (ltrim (rtrim (substr (l_str, 1, l_n - 1))));
 13  	   l_str := ltrim (substr (l_str, l_n + length (p_delim)));
 14    end loop;
 15    return;
 16  end str2tbl;
 17  /

Function created.

SCOTT@orcl_12.1.0.2.0> CREATE PACKAGE PKG_TEST AS
  2    function fun_main
  3  	 (P_ID		  VARCHAR2  DEFAULT NULL,
  4  	  P_HireDate	  Date	    DEFAULT NULL)
  5  	 return TEST_TYPE_TBL pipelined;
  6  END PKG_TEST;
  7  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE PACKAGE BODY PKG_TEST AS
  2    function fun_main
  3  	 (P_ID		  VARCHAR2  DEFAULT NULL,
  4  	  P_HireDate	  Date	    DEFAULT NULL)
  5  	 return TEST_TYPE_TBL pipelined
  6    IS
  7  	 v_rc		  sys_refcursor;
  8  	 out_rs 	  TEST_TYPE := TEST_TYPE (NULL, NULL, NULL);
  9    BEGIN
 10  	 OPEN v_rc FOR
 11  	   'SELECT id, name, hiredate
 12  	    FROM   demo
 13  	    WHERE  (id IN (SELECT * FROM TABLE (str2tbl (:b)))
 14  		    OR :b IS NULL)
 15  	    AND    (hiredate = :a OR :a IS NULL)'
 16  	   USING p_id, p_id, p_hiredate, p_hiredate;
 17  	 LOOP
 18  	   FETCH v_rc into out_rs.ID, out_rs.Name, out_rs.hire_date;
 19  	   EXIT WHEN v_rc%notfound;
 20  	   pipe row(out_rs);
 21  	 END LOOP;
 22  	 close v_rc;
 23  	 return;
 24    END fun_main;
 25  End PKG_TEST;
 26  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> -- tests:
SCOTT@orcl_12.1.0.2.0> COLUMN id   FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN name FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ())
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018
7654       MARTIN                         Tue 27-Mar-2018

5 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ('7369,7499,7521'))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main (null, TO_DATE('20180326','YYYYMMDD')))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ('7369,7499,7521', TO_DATE('20180326','YYYYMMDD')))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7521       WARD                           Mon 26-Mar-2018

2 rows selected.
Re: how to use variable to store comma seperated values [message #668990 is a reply to message #668977] Tue, 27 March 2018 17:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
You could simplify the whole thing, as shown below.


SCOTT@orcl_12.1.0.2.0> SELECT * FROM demo
  2  /

ID         NAME                           HIREDATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018
7654       MARTIN                         Tue 27-Mar-2018

5 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE TYPE TEST_TYPE AS OBJECT
  2    (ID	   VARCHAR2(100),
  3  	NAME	   VARCHAR2(100),
  4  	Hire_Date  DATE);
  5  /

Type created.

SCOTT@orcl_12.1.0.2.0> CREATE TYPE TEST_TYPE_TBL AS TABLE OF TEST_TYPE;
  2  /

Type created.

SCOTT@orcl_12.1.0.2.0> create or replace type myTabletype as table of varchar2(255)
  2  /

Type created.

SCOTT@orcl_12.1.0.2.0> create or replace function str2tbl
  2    (p_str	in varchar2,
  3  	p_delim in varchar2 default ',')
  4    return	 myTabletype pipelined
  5  as
  6    l_str	 varchar2 (32766) default p_str || p_delim;
  7    l_n	 number;
  8  begin
  9    loop
 10  	 l_n := instr (l_str, ',');
 11  	 exit when nvl (l_n, 0) = 0;
 12  	 pipe row (ltrim (rtrim (substr (l_str, 1, l_n - 1))));
 13  	   l_str := ltrim (substr (l_str, l_n + length (p_delim)));
 14    end loop;
 15    return;
 16  end str2tbl;
 17  /

Function created.

SCOTT@orcl_12.1.0.2.0> CREATE PACKAGE PKG_TEST AS
  2    function fun_main
  3  	 (P_ID		  VARCHAR2  DEFAULT NULL,
  4  	  P_HireDate	  Date	    DEFAULT NULL)
  5  	 return TEST_TYPE_TBL pipelined;
  6  END PKG_TEST;
  7  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE PACKAGE BODY PKG_TEST AS
  2    function fun_main
  3  	 (P_ID		  VARCHAR2  DEFAULT NULL,
  4  	  P_HireDate	  Date	    DEFAULT NULL)
  5  	 return TEST_TYPE_TBL pipelined
  6    IS
  7    BEGIN
  8  	 FOR i IN
  9  	   (SELECT id, name, hiredate
 10  	    FROM   demo
 11  	    WHERE  (id IN (SELECT * FROM TABLE (str2tbl (p_id)))
 12  		    OR p_id IS NULL)
 13  	    AND    (hiredate = p_hiredate OR p_hiredate IS NULL))
 14  	 LOOP
 15  	   PIPE ROW (test_type (i.id, i.name, i.hiredate));
 16  	 END LOOP;
 17  	 return;
 18    END fun_main;
 19  End PKG_TEST;
 20  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> -- tests:
SCOTT@orcl_12.1.0.2.0> COLUMN id   FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN name FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ())
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018
7654       MARTIN                         Tue 27-Mar-2018

5 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ('7369,7499,7521'))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7499       ALLEN                          Tue 27-Mar-2018
7521       WARD                           Mon 26-Mar-2018

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main (null, TO_DATE('20180326','YYYYMMDD')))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7521       WARD                           Mon 26-Mar-2018
7566       JONES                          Mon 26-Mar-2018

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (pkg_test.fun_main ('7369,7499,7521', TO_DATE('20180326','YYYYMMDD')))
  2  /

ID         NAME                           HIRE_DATE
---------- ------------------------------ ---------------
7369       SMITH                          Mon 26-Mar-2018
7521       WARD                           Mon 26-Mar-2018

2 rows selected.
Re: how to use variable to store comma seperated values [message #668991 is a reply to message #668990] Tue, 27 March 2018 20:26 Go to previous messageGo to next message
satya_ora
Messages: 4
Registered: March 2018
Junior Member
Thanks Barbara for explaining so nicely!!
Re: how to use variable to store comma seperated values [message #669523 is a reply to message #668991] Fri, 27 April 2018 01:57 Go to previous messageGo to next message
aliservi
Messages: 6
Registered: April 2018
Junior Member
hi everyone , could you help me ? . i have problem when i make a query
how can i slove this problem ?

"":qbe_var6" ."
Re: how to use variable to store comma seperated values [message #669525 is a reply to message #669523] Fri, 27 April 2018 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does your problem have anything to do with anything else in this thread?
If not then we should address your problem in the new thread you started.
If it does have something to do with this thread then you need explain what.
Re: how to use variable to store comma seperated values [message #669584 is a reply to message #669523] Mon, 30 April 2018 15:46 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
aliservi wrote on Fri, 27 April 2018 02:57
hi everyone , could you help me ? . i have problem when i make a query
how can i slove this problem ?

"":qbe_var6" ."
For one, you left off the SELECT, the table name and any where clause.

Is this the error you are getting, because this is what I get when I type exactly what you did:

SP2-0734: unknown command beginning """:qbe_var..." - rest of line ignored.
Previous Topic: roles granted to a role
Next Topic: Rollup master row
Goto Forum:
  


Current Time: Wed Jan 29 03:11:43 CST 2020