title: User defined table types in Oracle
body: <p>First of all usually I am working with MSSQL. But I have a stored procedure in MSSQL, which I need to use in Oracle now and since I am absolutely new to Oracle I have no idea at all how to do it correct.</p>
  
  <p>I needed to use user defined table types in my MS SQL stored procedure because I am using "logical" tables in my stored procedure, which I also need to pass them to a dynamic sql statement within this procedure (using column names of "physical" tables as variables/parameters).</p>
  
  <p>I've started to add the oracle function in a package I made before for another function. It looks like</p>
  
  <pre><code>TYPE resultRec IS RECORD
  (
     [result columns] 
  );
  
  TYPE resultTable IS TABLE OF resultRec;
  
  Function MyFunctionName([A LOT PARAMETERS]) RETURN resultTable PIPELINED;
  </code></pre>
  
  <p>I also described the layout of the tables (the user defined table types in MSSQL), which I want to use within this function in this package header. 
  So far so good, but now I don't really know where I have to declare my table variables or user defined table types. I also tried to put them in the package header, but if I am trying to use these tables in the package body, where I am describing my function, Oracle tells met, that the table or view does not exist.
  I also tried it to describe the tables within the package body or in the block of my function, which looks like that:</p>
  
  <pre><code>FUNCTION MyFunctionName
  (
     [MyParameters]
  )
  RETURN resultTable PIPELINED is rec resultrec;
  
  TYPE tableVariableA IS TABLE OF tableRecA;
  TYPE tableVariableB IS TABLE OF tableRecB;
  
  BEGIN
  
    INSERT INTO tableVariableA
    SELECT ColumnA, ColumnB FROM physicalTable WHERE[...];
  
    [A LOT MORE TO DO...]
  END;
  </code></pre>
  
  <p>But in this case Oracle also tells me, that it doesn't know the table or view.
  I also tried a few more things, but at the end I wasn't able to tell Oracle what table it should use...
  I would appreciate every hint, which helps me to understand how oracle works in this case. Thanks a lot!</p>