CODEWITHSUNDEEP
oracle-databaseplsql
FrenkyB
FrenkyB

Reputation: 7207

Table variable is filled only with one value

I have a stored procedure which should return several results - but it returns only one row. I think it's the last row in result set.

I am not sure, but I think the problem is in this line of code:

       select chi.id bulk collect into v_numbers from dual;

and that this line somehow overrides all previous results (there is several of them for each loop). How to insert into v_numbers without overriding previous results? I know that it's also wrong to insert only one row, but I haven't found solution to insert several rows from chi.

PROCEDURE GET_ATTRIBUTES(
      P_AUTH_USE_ID IN NUMBER,
      P_CATEGORY_ID IN NUMBER,
      P_VERSION_ID IN NUMBER,
      P_RESULT OUT TYPES.CURSOR_TYPE
  ) IS
    v_numbers sys.odcinumberlist := null;
  BEGIN

  FOR item IN
    (SELECT ID FROM INV_SRV WHERE SRV_CATEGORY_ID IN 
            (
                SELECT id
                FROM   inv_srv_category
                START WITH parent_category_id = P_CATEGORY_ID
                CONNECT BY PRIOR id = parent_category_id
            ) OR SRV_CATEGORY_ID = P_CATEGORY_ID)
    LOOP

        for chi in (select s.id
                  from inv_srv s
                    start with s.parent_srv_id = item.id
                    connect by prior s.id = s.parent_srv_id
                   )
        loop        
           select chi.id bulk collect into v_numbers from dual; --> here I should insert all rows from  that loop, but I don't know how
        end loop;    

    END LOOP;

    OPEN P_RESULT FOR SELECT t.column_value from table(v_numbers) t; --> only one row is returned


  END;

Upvotes: 1

Views: 254

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

Use BULK COLLECT and FORALL for bulk inserts and better performance. The FORALL statement will allow the DML to be run for each row in the collection without requiring a context switch each time, thus improving the overall performance.

CREATE OR REPLACE PROCEDURE get_attributes (
    p_auth_use_id  IN   NUMBER,
    p_category_id  IN   NUMBER,
    p_version_id   IN   NUMBER,
    p_result       OUT  types.cursor_type
) IS
    v_numbers sys.odcinumberlist := NULL;
BEGIN 
    SELECT s.id
    BULK COLLECT --> Bulk collect all values
    INTO v_numbers
    FROM inv_srv s
        start with s.parent_srv_id in (
          SELECT ID FROM INV_SRV 
          WHERE SRV_CATEGORY_ID IN 
            (
                SELECT id
                FROM   inv_srv_category
                START WITH parent_category_id = P_CATEGORY_ID
                CONNECT BY PRIOR id = parent_category_id
            ) 
            OR SRV_CATEGORY_ID = P_CATEGORY_ID)
        connect by prior s.id = s.parent_srv_id;

        FORALL i IN 1..v_numbers.COUNT   
        INSERT INTO your_table VALUES v_numbers ( i ); --> Bulk insert

END;

Upvotes: 2

Marmite Bomber
Marmite Bomber

Reputation: 21095

This may be considered as a improper use of the PL/SQL loops (often connected with a catastrophic performance) in a situation where a SQL solution exists.

Why don't you simple defines the cursor as follows:

OPEN P_RESULT FOR 
select s.id
 from inv_srv s
 start with s.parent_srv_id  in 
 (SELECT ID FROM INV_SRV WHERE SRV_CATEGORY_ID IN 
 (SELECT id
 FROM inv_srv_category
 START WITH parent_category_id = 1
 CONNECT BY PRIOR id = parent_category_id
 ) OR SRV_CATEGORY_ID = 1)
 connect by prior s.id = s.parent_srv_id
;

The query is constructed from your outer and inner loop so that it returns the same result. The transformation may not be trivial in generall case and must be carefully tested, but the performance profit may be high.

Upvotes: 1

djanoti
djanoti

Reputation: 303

Every time the loop executes v_numbers will be re populated again and again so, either 1) use v_numbers.extend; v_numbers(v_numbers.last) = "Your Value" or write everything in a single bulk collect.

select s.id 
bulk collect into v_numbers 
from inv_srv s
start with s.parent_srv_id in (SELECT ID FROM INV_SRV 
                                WHERE SRV_CATEGORY_ID IN 
                                                    (
                                                        SELECT id
                                                        FROM   inv_srv_category
                                                        START WITH parent_category_id = P_CATEGORY_ID
                                                        CONNECT BY PRIOR id = parent_category_id
                                                    ) 
                                  OR SRV_CATEGORY_ID = P_CATEGORY_ID)
connect by prior s.id = s.parent_srv_id

Upvotes: 1

Related Questions