Oracle Stored Procedure Issue

From: Brook Wallace <brook.wallace_at_gmail.com>
Date: 8 Feb 2007 12:44:52 -0800
Message-ID: <1170967492.174516.50060_at_v33g2000cwv.googlegroups.com>

[Quoted] 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 Received on Thu Feb 08 2007 - 21:44:52 CET