Re: Trouble viewing compilation errors
Date: Tue, 17 Feb 2009 10:23:59 -0800 (PST)
Message-ID: <6b96b579-c756-4a69-9388-719de1188c1c_at_h16g2000yqj.googlegroups.com>
On Feb 17, 11:04 am, laredotornado <laredotorn..._at_zipmail.com> wrote:
> 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 ======================================
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 declaredSQL> 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
Received on Tue Feb 17 2009 - 12:23:59 CST