Error while creating Package Body [message #424907] |
Tue, 06 October 2009 08:24  |
|
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 #424909 is a reply to message #424907] |
Tue, 06 October 2009 08:31   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
arif_md2009 wrote on Tue, 06 October 2009 14:2416/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 #424914 is a reply to message #424908] |
Tue, 06 October 2009 08:39   |
|
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 #424916 is a reply to message #424907] |
Tue, 06 October 2009 08:40   |
cookiemonster
Messages: 13963 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 #424921 is a reply to message #424907] |
Tue, 06 October 2009 08:47   |
|
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   |
flyboy
Messages: 1903 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 #424926 is a reply to message #424920] |
Tue, 06 October 2009 09:00   |
|
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 #424928 is a reply to message #424918] |
Tue, 06 October 2009 09:03   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
arif_md2009 wrote on Tue, 06 October 2009 14:44how to use code tags.
cookiemonster wrote on Tue, 06 October 2009 14:31And 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 #424930 is a reply to message #424907] |
Tue, 06 October 2009 09:05  |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Package specification:
arif_md2009 wrote on Tue, 06 October 2009 15:24PROCEDURE 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:00procedure 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?
|
|
|