8 Replies Latest reply: Sep 10, 2013 6:50 AM by Jeff Smith Sqldev Pm-Oracle RSS

    declaring an output table in stored procedure




      I'm a first time poster and I'm new to SQL Developer. I've worked a lot in SQL Management Studio but SQL Developer is different.


      I'm trying to create a stored procedure. Here's what I've got:


      create or replace
      procedure GetTaxSlipFieldsByType (
        TaxSlipType in varchar2,
        pk out number) as
        select TAX_SLIP_FIELD_KEY
        into pk
        where TAX_SLIP_TYPE = GetTaxSlipFieldsByType.TaxSlipType;


      I'll eventually be selecting all fields, but for now I want to keep it simple and get only the primary keys.


      The problem is that I get this error:


      ORA-01422: exact fetch returns more than requested number of rows


      I can see why: the output variable I declare is just a number. It will hold only one value. But the select statement no doubt returns a

      whole list of values (all the primary keys matching the criteria).


      What I would like to know is how to declare a table instead of a number for the output. If I could store the results of the select query in

      a table, number of rows should not be an issue.


      How is this done?


      Thanks and much appreciated.