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

    declaring an output table in stored procedure

    3fca34d0-9ad2-464a-adcb-67111ce9c5a9

      Hello,

       

      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
      begin
        select TAX_SLIP_FIELD_KEY
        into pk
        from FBC_DBA.TAX_SLIP_FIELDS
        where TAX_SLIP_TYPE = GetTaxSlipFieldsByType.TaxSlipType;
      end;

       

      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.