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 |
|
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
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 #668983 is a reply to message #668977] |
Tue, 27 March 2018 03:45 |
cookiemonster
Messages: 13925 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
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 |
|
Barbara Boehmer
Messages: 9097 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 |
|
Barbara Boehmer
Messages: 9097 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 |
|
Barbara Boehmer
Messages: 9097 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 #669584 is a reply to message #669523] |
Mon, 30 April 2018 15:46 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
aliservi wrote on Fri, 27 April 2018 02:57hi 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.
|
|
|
Goto Forum:
Current Time: Tue Jun 18 00:15:17 CDT 2024
|