Package problem

From: Robert H. Thompson <rthomp41_at_ford.com>
Date: Tue, 12 Jan 1999 15:44:39 -0500
Message-ID: <369BB437.31B35DFE@ford.com>

Could someone give me a suggestion as to what might be wrong with the following code:
SQL> create or replace package GET_NEXT_VRD_PKG   2 AS
  3 function next_vrd_number (country_code_in IN varchar2, type_code_in IN varchar2) return varchar2;   4 pragma restrict_references (next_vrd_number,WNDS);   5 end GET_NEXT_VRD_PKG;
  6 /

Package created.

SQL> create or replace package body GET_NEXT_VRD_PKG   2 AS

  3    slash            constant     varchar2(1) := '/';
  4    percent_sign    constant     varchar2(1) := '%';
  5    first_slash                   number;
  6    second_slash                  number;
  7    total_count                   number;
  8    max_value_work                number;
  9    max_value_string              varchar2(15);
 10    max_value_out                 varchar2(15);
 11    cc                            varchar2(5);
 12    tc                            varchar2(1);
 13    cursor  c1 is 
 14     select vrd_number
 15       from vrd

 16 where vrd_number like
rtrim(upper(cc))||slash||rtrim(upper(tc))||slash||percent_sign  17 group by vrd_number;
 18 vrd_number_out vrd.vrd_number%type;  19 type vrd_number_type is

 20      table of vrd_number_out%type
 21     index by binary_integer;
 22    i                binary_integer := 1;

 23 vrd_number_array vrd_number_type;  24 function next_vrd_number (country_code_in IN varchar2, type_code_in IN varchar2)
 25 return varchar2
 26 is

 27     max_value_out                 varchar2(15);

 28 begin
 29 cc := country_code_in;
 30 tc := type_code_in;
 31 total_count := 0;
 32 open c1;
 33 loop

 34      fetch c1 into vrd_number_out;
 35      exit when c1%notfound;

 36 total_count := total_count + 1;
 37 vrd_number_array(total_count) := vrd_number_out;  38 end loop;
 39 close c1;
 40 if total_count > 0 then

 41       first_slash := instr(vrd_number_array(total_count),slash,1,1);
 42       second_slash :=
instr(vrd_number_array(total_count),slash,1,2);
 43       if second_slash > first_slash then 
 44          max_value_string :=
substr(vrd_number_array(total_count),second_slash + 1);
 45       max_value_work := to_number(max_value_string + 1);
 46       max_value_out :=
rtrim(upper(cc))||slash||rtrim(upper(tc))||slash||to_char(max_value_work);
 47       end if;
 48    else
 49       max_value_out :=

rtrim(upper(cc))||slash||rtrim(upper(tc))||slash||'1';  50 end if;
 51 return(rtrim(max_value_out));
 52 exception

 53     when NO_DATA_FOUND THEN 
 54      RAISE_APPLICATION_ERROR (-20100, 'COUNTRY CODE/VRD TYPE CODE

NOT in database.');
 55 end next_vrd_number;
 56 end GET_NEXT_VRD_PKG;
 57 /

Package body created.

SQL> execute get_next_vrd_pkg.next_vrd_number('us','a') begin get_next_vrd_pkg.next_vrd_number('us','a'); end;

*

ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00221: 'NEXT_VRD_NUMBER' is not a procedure or is undefined
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> commit;

Commit complete.

SQL> execute get_next_vrd_pkg.next_vrd_number('us','a') begin get_next_vrd_pkg.next_vrd_number('us','a'); end;

*

ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00221: 'NEXT_VRD_NUMBER' is not a procedure or is undefined
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

If I do a select * from user_source where name = 'GET_NEXT_VRD_PKG' the package does exist but it seems as though none of the functions are available. If I do a select * from user_errors where name = 'GET_NEXT_VRD_PKG' it shows no errors. I am using Oracle version 7.3.2 on a Sequent box using Dynix (there version of Unix). This one has go me stumped.

I'd like to give thanks in advance for any help I can get on this.

Yours Truly,

Rob Thompson
DBA
Ford Motor Company
work: rthomp41_at_ford.com
home: rthom40065_at_aol.com Received on Tue Jan 12 1999 - 14:44:39 CST