Am back after a long time with an interesting question.
Thanks everybody for all your help in my previous emails, which really
helped me a lot with a good solution.
My query goes here.
I have a stored procedure which has an input parameter 'p_dept_nos' with
datatype as Pl Sql table and out parameters 'emp_ids' as ref cursor and
invalid_dept_ids as ref cursor
The input parameter has values of dept_nos.
So I have a select statement like below to send the result set as out ref
Open emp_ids (ref cursor) for
select empl_no from emp where dept_no in (select * from table (p_dept_nos)
On executing this procedure, am able to get the result set of employee ids
for the dept_nos in the input parameter value.
Before sending the result set to the output parameter 'empl_ids' , I have
validate dept_nos and and send output parameter for invalid dept ids as
First validation is to check dept_nos exist in the dept table.
So, I was trying to execute the below query first in the stored procedure
Select p_dept_nos from (select * from table(p_dept_nos) dept_nos) where
p_dept_nos not in (select dept_no from dept)
I am getting an oracle error saying 'inconsistent data types: expected
array_string but got Char.
My job here is to send the list of invalid dept_nos which are not in dept
table to invalie_dept_nos out parameter.
It looks like in the statement above (select * from table(p_dept_nos)
dept_nos) is sending array like strings and cannot be compared with
Hope, some body would have experimented similar issues. Please let me know the
exact solution as I have already run out of time.
Thanks in advance for your help and precious time.
You can view more tutorials about SAP Oracle