| Array as Cursor Variable [message #48424] |
Fri, 19 November 2004 04:57  |
Maverik
Messages: 6
Registered: November 2004
|
Junior Member |
|
|
Hi all,
I am trying to pass a multi valu parameter as a cursor variable.
Here is my cursor:
Cursor c1 (v_emp_no in varchar2[[or array type]]) is
Select * from emp
where empno in nvl((v_emp_no,empno);
If the value i passed is null value my cursor should have all the Emp nos (that is working fine), but if i want to pass more than one value as emp no like "empno in (1001,1002,1003)" , how should i approach ??
Appreciate your help..
Thanks,
|
|
|
|
|
|
| Re: Array as Cursor Variable [message #48429 is a reply to message #48425] |
Fri, 19 November 2004 07:47  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Note that the AskTom thread started back in 2000 so the first few examples use the (now obsolete) THE (subquery) syntax. Further down, it goes to the (still valid but not always necessary) TABLE(CAST collection_name AS collection_type)) 8i syntax.
The 9i parser is better at figuring out what type of collection it's dealing with, so in most cases you can just use TABLE(collection_name). For example, try this
SQL> desc integer_tt
integer_tt TABLE OF NUMBER(38)
DECLARE
v_empnos INTEGER_TT := INTEGER_TT(7369,7499,7521,7934);
CURSOR c_emp (cp_empnos INTEGER_TT)
IS
SELECT ename
FROM emp e
, TABLE(cp_empnos) t
WHERE e.empno = t.column_value;
BEGIN
FOR r IN c_emp(v_empnos)
LOOP
DBMS_OUTPUT.PUT_LINE(r.ename);
END LOOP;
END;
/ |
|
|
|
| Re: Array as Cursor Variable [message #48432 is a reply to message #48429] |
Fri, 19 November 2004 09:55 |
Maverik
Messages: 6
Registered: November 2004
|
Junior Member |
|
|
Thanks guys, Let me work on both the solutions ..i beleive second one looks very simple and easy to use..since i'm using Oracle 9i.
I really appreciate your help..
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
