Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    14

    Question Unanswered: Pls-00383 (select Where ... In)

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Pls-00383 (select Where ... In)

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

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

  3. #3
    Join Date
    Jan 2003
    Posts
    14

    Re: Pls-00383 (select Where ... In)

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •