Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: stored procedure script

Re: stored procedure script

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Thu, 03 May 2001 23:49:55 -0700
Message-ID: <3AF25113.705AB30C@exesolutions.com>

Jim Poe wrote:

> I have a single script that defines many stored procedures. When I run this
> script, the first stored procedure is created and includes the entire
> script. Obviously, it compiles with errors. I can't see anything different
> about the first procedure when compared to the other procedures. The rest
> of the procedures are all created and compiled without errors.
>
> Any ideas what may be wrong.
>
> This is the first stored procedure and the beginning of the second:
>
> CREATE OR REPLACE procedure add_existing_party_addr
> ( p_addr_id in integer, p_addr_type_id in integer, p_key_id in integer,
> p_party_type in integer,
> p_result_id out integer ) is
> v_ctac_type integer := amscommon.ptCTAC; /* position in tftpartytype */
> v_office_type integer := amscommon.ptOFFICE;
> v_seq_value integer;
> e_nopartytype exception;
> begin
> if p_party_type = v_ctac_type then
> select ctac_addr_seq.nextval into v_seq_value from dual;
> insert into ctac_addr ( ctac_addr_id, ctac_id, addr_id, addr_type_id )
> values ( v_seq_value, p_key_id, p_addr_id, p_addr_type_id );
> elsif p_party_type = v_office_type then
> select office_addr_seq.nextval into v_seq_value from dual;
> insert into office_addr ( office_addr_id, office_id, addr_id,
> addr_type_id )
> values ( v_seq_value, p_key_id, p_addr_id, p_addr_type_id );
> else
> raise e_nopartytype;
> end if;
>
> p_result_id := v_seq_value;
> exception
> when others then
> p_result_id := 0;
>
> end add_existing_party_addr;
> /
>
> CREATE OR REPLACE PROCEDURE ADD_EXISTING_PARTY_CTAC
> ( p_CTAC_ID IN CTAC.CTAC_ID%TYPE,
> p_OFFICE_ID IN OFFICE.OFFICE_ID%TYPE,
> p_TAB_NAME IN ROLE.TAB_NAME%TYPE,
> p_SUCCESS OUT INTEGER ) IS
> v_CurrNumber INTEGER;
> v_NextOfficeRank OFFICE_ROLE.RANK%TYPE;
> v_NextCtacRank CTAC_ROLE.RANK%TYPE;
> v_OFFICE_ID OFFICE.OFFICE_ID%TYPE;
> v_CTAC_ID CTAC.CTAC_ID%TYPE;
>
> yadda, yadda, yadda
>
> --
> Jim Poe (jpoe_at_fulcrumit.com)

What is causing your problem is not jumping out at me ... but then again it is almost midnight and I'm tired and going to bed. But what does jump out is the following.

  1. You define an exception and don't test for it:

     raise e_nopartytype;

2. This line:

     select ctac_addr_seq.nextval into v_seq_value from dual;

serves no useful purpose. Just put your "ctac_addr_seq.nextval" into your insert statement as a value.

Daniel A. Morgan Received on Fri May 04 2001 - 01:49:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US