Hello,

I am having a problem that is easy to solve for an experienced professional.

Here it is: I have to develop a stored procedure where there's an input parameter of the varchar2-type which contains several values, separated by a comma.

A typical case would look like this: sTestPar := 'a23,b56,c78,d64'

As a MS-SQL-Server Programmer, I tried the following:

insert into teamdaten
(NTUser)
select
Username
from
Authentication
where
aktive=1 and NTGroup in (sTestPar);

Failure. In Oracle I only can put one Value in it.

So, I tried to put the Values into a collection like VARRAY or PL/SQL-Table.


TYPE recTestParType is table of authentication.NTGroup%type index by binary_integer;
recTestPar recTestParType;

insert into teamdaten
(NTUser)
select
Username
from
Authentication
where
aktive=1 and NTGroup in (recTestPar);

Failure: PLS-00383

It is possible to put a single value from the PL/SQL-Table there (recTestPar(1)) but not the whole Table.

Then I tried the VARRAY -> same Error

Has anyone got any Ideas how to solve the Problem? I am new in making SP for Oracle and got into lots of troubles but this was not solvable for me

This can be done, but is a little tricky. This links explains how:

http://asktom.oracle.com/pls/ask/f?p...D:146012348066

Hi Andrewst,

it works, thanks a lot for the quick help!

I don't know, if my brain really checks that, but for today it's ok :-)
(it was another long day)