Re: Oracle Stored Procedure Issue

Brook Wallace wrote:
> I have a Package on that has several stored procedures. Each stored
> procedure takes a
> 'table type', cursor and an integer. Each of the stored procedure
> definitions look like this.
>
>
>
> PROCEDURE proc (typ1 IN OUT NOCOPY
> proc_type,
> cur1 OUT NOCOPY main_cursor,
> rc OUT NUMBER);
>
>
>
> typ1 - the table type
> cur1 - ref cursor
> rc - return code
>
>
>
> The 'object type' associated with the 'table type' looks like this.
> create or replace TYPE obj_proc AS OBJECT
> (
> SEQUENCE_NUMBER NUMBER,
> STR1 VARCHAR2(16),
> STR2 VARCHAR2(16),
> STR3 VARCHAR2(16),
> STR4 VARCHAR2(16),
> STR5 VARCHAR2(9),
> LAST_NAME VARCHAR2(30),
> FIRST_NAME VARCHAR2(30),
> DATE_OF_BIRTH VARCHAR2(8),
> PHONE_NUMBER VARCHAR2(7),
> AREA_CODE VARCHAR2(3),
> STATE VARCHAR2(2),
> DL_NO VARCHAR2(23)
> );
>
>
>
> Here is one of the procedures in which I am experiencing the problem.
>
> PROCEDURE proc (typ1 IN OUT NOCOPY proc_type,
> cur1 OUT NOCOPY main_cursor,
> rc OUT NUMBER)
> IS
> BEGIN
> OPEN cur1 FOR SELECT c.sequence_number, a.first_name,
> a.last_name, a.phone_number
> FROM schema.table1 a,
> schema.table2 b,
> (SELECT t.sequence_number, t.id FROM TABLE(typ1) t) c
> WHERE a.id = c.id
> AND a.area_code = b.area_code (+)
> AND substr(a.phone_number, 1, 3) = b.phone_number (+);
>
>
>
> rc := SQLCODE;
> EXCEPTION
> WHEN NO_DATA_FOUND
> THEN
> rc := SQLCODE;
> WHEN OTHERS
> THEN
> rc := SQLCODE;
> END proc;
>
>
>
> Here is the problem. In the stored procedure I am passing in the
> value of sequence_number through the table type.
> The reason I am doing this is to keep track of the unique identifier
> for each transaction that is being passed in.
> The select statement selects the sequence_number and then returns it
> through the cursor. If there is a 1 to 1
> correspondence then the sequence_number is returned correctly. But if
> there is a 1 to many correspondence, the then sequence_number will be
> set to the first sequence_number in the the table type(typ1). Here is
> an example.
>
> One to One example
> Input:
>
> 0, '', '1234123412341234', '1234123412341234', '1234123412341234',
> '123121234','doe', 'john', '01011910',
> '1231234', '123', 'AR', '12345678901234567890123'
>
> 1, '', '1234123412341234', '1234123412341234', '1234123412341234',
> '123121234','doe', 'jane', '01011910',
> '1231234', '123', 'AR', '12345678901234567890123'
>
> 2, '', '1234123412341234', '1234123412341234', '1234123412341234',
> '123121234','doe', 'bob', '01011910',
> '1231234', '123', 'AR', '12345678901234567890123'
>
>
> The first element is the sequence_number)
>
> output:
>
> 0, 'john', 'doe', '1111111'
> 1, 'jane', 'doe', '2222222'
> 2, 'bob', 'doe', '3333333'
>
>
>
> One to Many example
>
> Input:
> 0, '', '1234123412341234', '1234123412341234', '1234123412341234',
> '123121234','doe', 'john', '01011910',
> '1231234', '123', 'AR', '12345678901234567890123'
>
> 1, '', '1234123412341234', '1234123412341234', '1234123412341234',
> '123121234','doe', 'jane', '01011910',
> '1231234', '123', 'AR', '12345678901234567890123'
>
> 2, '', '1234123412341234', '1234123412341234', '1234123412341234',
> '123121234','doe', 'bob', '01011910',
> '1231234', '123', 'AR', '12345678901234567890123'
>
>
>
> (For simplicity I have reduce the output of the above procedure to
> only 3 elements.
> The first element is the sequence_number. )
>
> In this case input transaction number 1 finds 3 numbers associated
> with it.
>
> Output:
>
> 0, 'john', 'doe', '1111111'
> 0, 'john', 'doe', '2222222'
> 0, 'john', 'doe', '3333333'
> 0, 'jane', 'doe', '4444444'
> 0, 'bob', 'doe', '5555555'
>
>
>
> Notice that the first element in the return list(the sequence number)
> is always 0.
> If any of the input transactions have more than one return value
> associated with it
> then the sequence_number of the very first transaction passed in is
> always used.
>
>
>
> Sorry for the long post and sorry if it is vague or confusing.
>
>
> The version of the database I am running is 10g-10.2.0.1-2

Just guessing but it appears your use of "sequence number" bears no relationship to a sequence object and that what you are using is the array nomenclature.

Consider a test such as:

IF array.COUNT <> 1 THEN

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org