Hi,

I'm using Oracle 10g and trying to compile a package. But my PL/SQL
Developer tool complains at this line:

show err package body RE_PKG

saying "Invalid SQL statement". What is the correct syntax? Below is
my complete package definition:


Thanks, - Dave

====================begin file ===============================
create or replace type FLOW_IDS_ARRAY as table of NUMBER;

create or replace package RE_PKG IS

PROCEDURE set_trial_corps(p_corp_sysprin IN VARCHAR2, p_flows_arr IN
FLOW_IDS_ARRAY, p_errorMsg OUT VARCHAR2);

END RE_PKG;
/

create or replace package body RE_PKG IS

PROCEDURE set_trial_corps(p_corp_sysprin IN VARCHAR2, p_flows_arr IN
FLOW_IDS_ARRAY, p_errorMsg OUT VARCHAR2) IS
BEGIN
DELETE FROM RE_TRIAL_CORP_FLOWS WHERE CORP_SYSPRIN = p_corp_sysprin;
FOR elem in 1 .. flows_arr.count LOOP
INSERT INTO RE_TRIAL_CORP_FLOWS (ID, CORP_SYSPRIN, FLOW_ID) VALUES
(RE_TRIAL_CORP_FLOWS_PK_SEQ.nextval, corp_sysprin, elem);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END set_trial_corps;

END RE_PKG;
/
show err package body RE_PKG
=======================End file ======================================

On 17.02.2009 18:04, laredotornado wrote:
I tend to distrust graphical tools. What happens if you feed that into
SQL Plus?

Cheers

robert

On Feb 17, 11:04*am, laredotornado <laredotorn... (AT) zipmail (DOT) com> wrote:
Your typing skill may need some work as you misspelled some of your
variable names in the package body:

SQL> create table re_trial_corp_flows(
2 id number not null,
3 corp_sysprin varchar2(40),
4 flow_id number
5 );

Table created.

SQL>
SQL> alter table re_trial_corp_flows
2 add constraint re_trial_corp_flows_pk
3 primary key(id);

Table altered.

SQL>
SQL> create sequence re_trial_corp_flows_pk_seq
2 start with 1 increment by 1 nomaxvalue nocycle nocache order;
create sequence re_trial_corp_flows_pk_seq
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>
SQL> create or replace type FLOW_IDS_ARRAY as table of NUMBER;
2 /

Type created.

SQL>
SQL> create or replace package RE_PKG IS
2
3
4 PROCEDURE set_trial_corps(p_corp_sysprin IN VARCHAR2,
p_flows_arr IN
5 FLOW_IDS_ARRAY, p_errorMsg OUT VARCHAR2);
6
7
8 END RE_PKG;
9 /

Package created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace package body RE_PKG IS
2
3
4 PROCEDURE set_trial_corps(p_corp_sysprin IN VARCHAR2,
p_flows_arr IN
5 FLOW_IDS_ARRAY, p_errorMsg OUT VARCHAR2) IS
6 BEGIN
7 DELETE FROM RE_TRIAL_CORP_FLOWS WHERE CORP_SYSPRIN =
p_corp_sysprin;
8 FOR elem in 1 .. flows_arr.count LOOP
9 INSERT INTO RE_TRIAL_CORP_FLOWS (ID, CORP_SYSPRIN,
FLOW_ID) VALUES
10 (RE_TRIAL_CORP_FLOWS_PK_SEQ.nextval, corp_sysprin, elem);
11 END LOOP;
12 COMMIT;
13 EXCEPTION
14 WHEN OTHERS THEN
15 ROLLBACK;
16 END set_trial_corps;
17
18
19 END RE_PKG;
20 /

Warning: Package Body created with compilation errors.
SQL>
SQL> show errors
Errors for PACKAGE BODY RE_PKG:

LINE/COL ERROR
--------
-----------------------------------------------------------------
8/17 PL/SQL: Statement ignored
8/34 PLS-00201: identifier 'FLOWS_ARR.COUNT' must be declared
SQL>

Using your actual variable names fixed the 'problem':

SQL> create or replace package body RE_PKG IS
2
3
4 PROCEDURE set_trial_corps(p_corp_sysprin IN VARCHAR2,
p_flows_arr IN
5 FLOW_IDS_ARRAY, p_errorMsg OUT VARCHAR2) IS
6 BEGIN
7 DELETE FROM RE_TRIAL_CORP_FLOWS WHERE CORP_SYSPRIN =
p_corp_sysprin;
8 FOR elem in 1 .. p_flows_arr.count LOOP
9 INSERT INTO RE_TRIAL_CORP_FLOWS (ID, CORP_SYSPRIN,
FLOW_ID) VALUES
10 (RE_TRIAL_CORP_FLOWS_PK_SEQ.nextval, p_corp_sysprin, elem);
11 END LOOP;
12 COMMIT;
13 EXCEPTION
14 WHEN OTHERS THEN
15 ROLLBACK;
16 END set_trial_corps;
17
18
19 END RE_PKG;
20 /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>


David Fitzjarrell