Home » SQL & PL/SQL » SQL & PL/SQL » Error while creating Package Body (Oracle 10g)
Error while creating Package Body [message #424907] Tue, 06 October 2009 08:24 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear All,

I am trying to create one package body to avoid mutating error in oracle , i created the package specification but while creating the body i am getting the error as below.i am not understanding it can anyone help.the code is below.

create or replace package inst_pack is
PROCEDURE init_inst_plsql_tab_pk;
PROCEDURE ins_inst_plsql_tab
( in_trn_flx1 in varchar2,
, in_trn_flx2 in varchar2,
,in_trn_at in varchar2);
PROCEDURE chk_inst_plsql_tab;
end inst_pack;


package body


create or replace package body inst_pack is
type inst_trn_flx1_type is table of fix_transfer.trn_flex_01%type
index by binary_integer;
inst_trn_flx1 inst_trn_flx1_type;

type inst_trn_flx2_type is table of fix_transfer.trn_flex_02%type
index by binary_integer;
inst_trn_flx2 inst_trn_flx2_type;



type inst_trn_at_type is table of fix_transfer.trn_at_no%type
index by binary_integer;
inst_trn_at inst_trn_at_type;



inst_pk binary_integer;

proceedure init_inst_plsql_tab_pk is
begin
inst_pk :=0;
end;

proceedure ins_inst_plsql_tab
( in_trN_flx1 in fix_transfer.trn_flex_01%type
, in_trN_flx2 in fix_transfer.trn_flex_02%type
,in_trN_at in fix_transfer.trn_at_no%type );
begin
inst_pk := inst_pk + 1 ;
inst_trn_flx1(inst_pk) := in_trn_flx1;
inst_trn_flx2(inst_pk) := in_trn_flx2;
inst_trn_at(inst_pk) := in_trn_at;

end;

proceedure chk_inst_plsql_tab is
dummy_number;
begin
while inst_pk >0 loop
begin
update fix_asset set at_asset_divn_code=inst_trn_flx1,inst_trn_flx2
where at_no = inst_trn_at(inst_pk);
exception
when no_data_found then
raise_application_error(-20000, 'less');
end;
inst_pk : = instk_pk -1;
end loop;
end;
end inst_pack;





SQL> sho errors;
Errors for PACKAGE BODY INST_PACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/48 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
:= . ( @ % ; not null range default character

16/14 PLS-00103: Encountered the symbol "PROCEEDURE"
17/40 PLS-00103: Encountered the symbol "FIX_TRANSFER" when expecting
one of the following:
(
The symbol "(" was substituted for "FIX_TRANSFER" to continue.

18/24 PLS-00103: Encountered the symbol "FIX_TRANSFER" when expecting
one of the following:
(
The symbol "(" was substituted for "FIX_TRANSFER" to continue.

19/39 PLS-00103: Encountered the symbol "FIX_TRANSFER" when expecting
one of the following:
(
The symbol "(" was substituted for "FIX_TRANSFER" to continue.

19/68 PLS-00103: Encountered the symbol ";" when expecting one of the

LINE/COL ERROR
-------- -----------------------------------------------------------------
following:
) , and or

26/14 PLS-00103: Encountered the symbol "PROCEEDURE" when expecting one
of the following:
end not pragma final instantiable order overriding static
member constructor map


Re: Error while creating Package Body [message #424908 is a reply to message #424907] Tue, 06 October 2009 08:29 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Don't you know the spelling of PROCEDURE?
And in package body, PROCEDURE does not contain semicolon (Wink after list of parameters.

regards,
Delna

[Updated on: Tue, 06 October 2009 08:32]

Report message to a moderator

Re: Error while creating Package Body [message #424909 is a reply to message #424907] Tue, 06 October 2009 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
arif_md2009 wrote on Tue, 06 October 2009 14:24
16/14 PLS-00103: Encountered the symbol "PROCEEDURE"


Does that look right to you?

And when posting code can you please use code tags, it makes it a lot easier to read. See the orafaq forum guide if you're not sure how.
Re: Error while creating Package Body [message #424910 is a reply to message #424908] Tue, 06 October 2009 08:33 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Delna - even after changing the word proceedure to procedure the message still appears.please check the first procedure.

Re: Error while creating Package Body [message #424911 is a reply to message #424909] Tue, 06 October 2009 08:34 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

cookiemonster - i am really sorry but what is the problem in this package body.
Re: Error while creating Package Body [message #424914 is a reply to message #424908] Tue, 06 October 2009 08:39 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i changed the code as suggested by you but now i am getting like this

1 create or replace package body inst_pack is
2 type inst_trn_flx1_type is table of fix_transfer.trn_flex_01%type
3 index by binary_integer;
4 inst_trn_flx1 inst_trn_flx1_type;
5 type inst_trn_flx2_type is table of fix_transfer.trn_flex_02%type
6 index by binary_integer;
7 inst_trn_flx2 inst_trn_flx2_type;
8 type inst_trn_at_type is table of fix_transfer.trn_at_no%type
9 index by binary_integer;
10 inst_trn_at inst_trn_at_type;
11 inst_pk binary_integer;
12 procedure init_inst_plsql_tab_pk is
13 begin
14 inst_pk :=0;
15 end;
16 procedure ins_inst_plsql_tab
17 ( in_trN_flx1 in fix_transfer.trn_flex_01%type
18 , in_trN_flx2 in fix_transfer.trn_flex_02%type
19 ,in_trN_at in fix_transfer.trn_at_no%type )
20 begin
21 inst_pk := inst_pk + 1 ;
22 inst_trn_flx1(inst_pk) := in_trn_flx1;
23 inst_trn_flx2(inst_pk) := in_trn_flx2;
24 inst_trn_at(inst_pk) := in_trn_at;
25 end;
26 procedure chk_inst_plsql_tab is
27 dummy_number;
28 begin
29 while inst_pk >0 loop
30 begin
31 update fix_asset set at_asset_divn_code=inst_trn_flx1,inst_trn_flx2
32 where at_no = inst_trn_at(inst_pk);
33 exception
34 when no_data_found then
35 raise_application_error(-20000, 'less');
36 end;
37 inst_pk : = instk_pk -1;
38 end loop;
39 end;
40* end inst_pack;
SQL> /

Warning: Package Body created with compilation errors.

SQL> sho errors;
Errors for PACKAGE BODY INST_PACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of
the following:
; is with authid as cluster order using external
deterministic parallel_enable pipelined
The symbol ";" was substituted for "BEGIN" to continue.

26/1 PLS-00103: Encountered the symbol "PROCEDURE"
27/13 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar
The symbol "exception" was substituted for ";" to continue.

32/1 PLS-00103: Encountered the symbol "WHERE" when expecting one of
the following:
. ( @ =

37/9 PLS-00103: Encountered the symbol ":" when expecting one of the
following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
:= . ( @ % ;
Re: Error while creating Package Body [message #424915 is a reply to message #424914] Tue, 06 October 2009 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Error while creating Package Body [message #424916 is a reply to message #424907] Tue, 06 October 2009 08:40 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
More than I can really be bothered to list. Here's a few to get you started:
1) You've misspelt procedure multiple times.
2) Missplaced semicolon in procedure declaration.
3) You've doubled up on commas in the package spec.
4) Variable declaration missing a datatype.

There are more besides. We're not really a syntax checking service you know.
While I normally tell people to make more use of sqlplus I strongly recommend you make use of a gui like toad or sql developer. The syntax highlighting will make your life a lot easier.
Re: Error while creating Package Body [message #424917 is a reply to message #424907] Tue, 06 October 2009 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
The error in your last post is most likely number 3 on my list.
And can you please use code tags - we've asked you several times already. It's not like it's difficult.

EDIT: fixed my own tags

[Updated on: Tue, 06 October 2009 08:42]

Report message to a moderator

Re: Error while creating Package Body [message #424918 is a reply to message #424917] Tue, 06 October 2009 08:44 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

how to use code tags.
Re: Error while creating Package Body [message #424920 is a reply to message #424910] Tue, 06 October 2009 08:46 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
20/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of
the following:
; is with authid as cluster order using external
deterministic parallel_enable pipelined
The symbol ";" was substituted for "BEGIN" to continue.

AS/IS keyword missing before line number 20.

dummy_number;

Is this a way of variable declaration?

update fix_asset set at_asset_divn_code=inst_trn_flx1,inst_trn_flx2
where at_no = inst_trn_at(inst_pk);

Is this valid UPDATE statement?

inst_pk : = instk_pk -1;

Remove space between : and =.

regards,
Delna
Re: Error while creating Package Body [message #424921 is a reply to message #424907] Tue, 06 October 2009 08:47 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

dear all now i am getting few errors

code

create or replace package body inst_pack is
type inst_trn_flx1_type is table of fix_transfer.trn_flex_01%type
index by binary_integer;
inst_trn_flx1 inst_trn_flx1_type;
type inst_trn_flx2_type is table of fix_transfer.trn_flex_02%type
index by binary_integer;
inst_trn_flx2 inst_trn_flx2_type;
type inst_trn_at_type is table of fix_transfer.trn_at_no%type
index by binary_integer;
inst_trn_at inst_trn_at_type;
inst_pk binary_integer;
procedure init_inst_plsql_tab_pk is
begin
inst_pk :=0;
end;
procedure ins_inst_plsql_tab
( in_trN_flx1 in fix_transfer.trn_flex_01%type
, in_trN_flx2 in fix_transfer.trn_flex_02%type
,in_trN_at in fix_transfer.trn_at_no%type ) is
begin
inst_pk := inst_pk + 1 ;
inst_trn_flx1(inst_pk) := in_trn_flx1;
inst_trn_flx2(inst_pk) := in_trn_flx2;
inst_trn_at(inst_pk) := in_trn_at;
end;
procedure chk_inst_plsql_tab is
--dummy_number;
begin
while inst_pk >0 loop
begin
update fix_asset set at_asset_divn_code=inst_trn_flx1,inst_trn_flx2
where at_no = inst_trn_at(inst_pk);
exception
when no_data_found then
raise_application_error(-20000, 'less');
end;
inst_pk : = instk_pk -1;
end loop;
end;
end inst_pack;
/




LINE/COL ERROR
-------- -----------------------------------------------------------------
31/1 PL/SQL: SQL Statement ignored
31/69 PL/SQL: ORA-00927: missing equal sign
37/9 PLS-00103: Encountered the symbol ":" when expecting one of the
following:
:= . ( @ % ;
Re: Error while creating Package Body [message #424923 is a reply to message #424920] Tue, 06 October 2009 08:53 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
delna.sexy wrote on Tue, 06 October 2009 15:46

update fix_asset set at_asset_divn_code=inst_trn_flx1,inst_trn_flx2
where at_no = inst_trn_at(inst_pk);

Is this valid UPDATE statement?

Answer is no.
Re: Error while creating Package Body [message #424924 is a reply to message #424921] Tue, 06 October 2009 08:54 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You do not follow forum guidelines,
Do not follow what other say,
And want whole solution from others!!!

regards,
Delna

[Updated on: Tue, 06 October 2009 08:56]

Report message to a moderator

Re: Error while creating Package Body [message #424926 is a reply to message #424920] Tue, 06 October 2009 09:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

create or replace package body inst_pack is
type inst_trn_flx1_type is table of fix_transfer.trn_flex_01%type
index by binary_integer;
inst_trn_flx1 inst_trn_flx1_type;
type inst_trn_flx2_type is table of fix_transfer.trn_flex_02%type
index by binary_integer;
inst_trn_flx2 inst_trn_flx2_type;
type inst_trn_at_type is table of fix_transfer.trn_at_no%type
index by binary_integer;
inst_trn_at inst_trn_at_type;
inst_pk binary_integer;
procedure init_inst_plsql_tab_pk is
begin
inst_pk :=0;
end;
procedure ins_inst_plsql_tab
( in_trN_flx1 in fix_transfer.trn_flex_01%type
, in_trN_flx2 in fix_transfer.trn_flex_02%type
,in_trN_at in fix_transfer.trn_at_no%type ) is
begin
inst_pk := inst_pk + 1 ;
inst_trn_flx1(inst_pk) := in_trn_flx1;
inst_trn_flx2(inst_pk) := in_trn_flx2;
inst_trn_at(inst_pk) := in_trn_at;
end;
procedure chk_inst_plsql_tab is
--dummy_number;
begin
while inst_pk > 0 loop
begin
update fix_asset set at_asset_divn_code=inst_trn_flx1(inst_pk),at_asset_dept_code=inst_trn_flx2(inst_pk)
where at_no = inst_trn_at(inst_pk);
exception
when no_data_found then
raise_application_error(-20000, 'less');
end;
inst_pk := inst_pk -1;
end loop;
end;
end inst_pack;

now i am getting this error only as below


LINE/COL ERROR
-------- -----------------------------------------------------------------
3/15 PLS-00323: subprogram or cursor 'INS_INST_PLSQL_TAB' is declared
in a package specification and must be defined in the package
body
Re: Error while creating Package Body [message #424927 is a reply to message #424926] Tue, 06 October 2009 09:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Error is self explainatory.

regards,
Delna
Re: Error while creating Package Body [message #424928 is a reply to message #424918] Tue, 06 October 2009 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
arif_md2009 wrote on Tue, 06 October 2009 14:44
how to use code tags.


cookiemonster wrote on Tue, 06 October 2009 14:31
And when posting code can you please use code tags, it makes it a lot easier to read. See the orafaq forum guide if you're not sure how.
Re: Error while creating Package Body [message #424929 is a reply to message #424926] Tue, 06 October 2009 09:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That means that the definition of INS_INST_PLSQL_TAB in the package header is different from the definiton in the package body.
Re: Error while creating Package Body [message #424930 is a reply to message #424907] Tue, 06 October 2009 09:05 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Package specification:
arif_md2009 wrote on Tue, 06 October 2009 15:24
PROCEDURE ins_inst_plsql_tab
                  ( in_trn_flx1 in varchar2,
		    , in_trn_flx2 in varchar2,
                    ,in_trn_at in varchar2);

Package body:
arif_md2009 wrote on Tue, 06 October 2009 16:00
procedure ins_inst_plsql_tab
( in_trN_flx1 in fix_transfer.trn_flex_01%type
, in_trN_flx2 in fix_transfer.trn_flex_02%type
,in_trN_at in fix_transfer.trn_at_no%type ) is

Is not the error message self-explanatory enough?
Are you unable to spot the difference between above declarations?
Previous Topic: Gregorian Date to Islamic Hijri Date and Hijri Date to Gregorian Date in Oracle using pl/sql
Next Topic: Add columns between tables
Goto Forum:
  


Current Time: Sun Sep 25 02:44:28 CDT 2016

Total time taken to generate the page: 0.16186 seconds