How many procedures in one package

[JPCogAdmin]

Hi,

Was wondering if any of you know if there is a limit to the number of procedures in a package?

The reason I'm asking is because I have written two procedures in one package and the second procedure gives me an error saying:

Error(25,1): PLS-00382: expression is of wrong type

I' look at both procs and they're good. When I click on "go to source" it points me to the second procedure which goes like this:


create or replace
PACKAGE BODY TEST2 AS


procedure myfirst(goes_here_with_no_problems) as

begin
.......

end;

procedure promp(one in table1.ID%type,ret2 in out myrefcur) as

BEGIN
open ret2 for

select table1.ID,table1.field2,table1.field3 from user_schema.table1
where table1.ID=one;

--null;
END promp;

END TEST2;

 

[BJCooperIT]

You can have many procedures and functions in a package. The first thing that jumps out is that AS should be IS. Here is the proper syntax:

create or replace package body my_pkg is

  -- Private type declarations
  type <TypeName> is <Datatype>;
  
  -- Private constant declarations
  <ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
  <VariableName> <Datatype>;

  -- Function and procedure implementations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
    <LocalVariable> <Datatype>;
  begin
    <Statement>;
    return(<Result>);
  end;

begin
  -- Initialization
  <Statement>;
end my_pkg;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Actually, Barb, "IS" and "AS" are synonomous in this context and either should work fine.

JP, are you comfortable running your code in SQL*Plus? If so, we can identify your errors a bit easier, I believe.

Once you run your code, if SQL*Plus indicates that there are errors, then enter this command at the prompt:

SQL> show errors

It will then display all of the errors it detected, along with the line and column where it detected the error.

At this point, and without your actual code to peruse, I cannot isolate your error.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JPCogAdmin]

Hi BJCooperIT,

Here is my package definition.

create or replace
PACKAGE TEST2 AS


type r_allrecs is record(c2 table1%rowtype);
type c_allrecs is ref cursor return r_allrecs;

procedure promp(one in table1.ID%type,ret2 in out c_alldrgs);

END TEST2;

Here is the package body:

procedure promp(one in table1.ID%type,ret2 in out c_allrecs) as

BEGIN

open ret2 for

select * from table1
where table1.ID=one;


END promp;

END TEST2;


of course, it is complaining the the expression is of the wrong type. It's going to my select statement.

I want to return all the records in table1. So I'm trying to create a record and then a ref cursor that points to the record type. Then I return a ref cursor. So, right now my eyes have given up for a few minutes.
;-(


-J

 

[JPCogAdmin]

Thanks SantaMufasa,

I wrote most of the code just now.
;-)

-JP