CODEWITHSUNDEEP
oracle-databaseplsqlcursor
SatyajeethT
SatyajeethT

Reputation: 1

How to use cursor field in a select query

I am trying to use a cursor here, I would like to know how to i access the cursor field in the select column?

I have an implementation as below,

create or replace TYPE  "TABLE_TYPE_SAMPLE" AS OBJECT(
   ENTITY_NAME            VARCHAR2(100)
);

 

create or replace TYPE  "TABLE_SAMPLE" AS TABLE OF TABLE_TYPE_SAMPLE;

 

    CREATE OR REPLACE FUNCTION segmentFields(
   txnId            VARCHAR2)
    RETURN TABLE_SAMPLE
IS
attValue VARCHAR2(20);
 curStr   VARCHAR2(20);

  flexTable  TABLE_SAMPLE := TABLE_TYPE_SAMPLE();

 CURSOR cur_seg
 IS
   (SELECT colA
   FROM table1 -- (table name has column colA)
   WHERE id = txnId
   );
BEGIN
 FOR cur_recd IN cur_seg
 LOOP
   curStr := cur_recd.colA;
   SELECT curStr into attValue FROM PER_PEOPLE_GROUPS;

flexTable.EXTEND;
   flexTable(flexTable.count) := (TABLE_TYPE_SAMPLE(attValue)) ;
 END LOOP;
RETURN flexTable;
END;

The function complied without errors. but when I try to run below query

select * from table(segmentFields(480));

I get the below error,

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SEGMENTFIELDS", line 19
01422. 00000 - "exact fetch returns more than requested number of rows"
*Cause: The number specified in exact fetch is less than the rows returned.
*Action: Rewrite the query or change number of rows requested

I want to understand, what is wrong with this implementation.

Thank you.

Upvotes: 0

Views: 312

Answers (3)

Artem Kharkunov
Artem Kharkunov

Reputation: 66

error in row:

SELECT curStr into attValue FROM PER_PEOPLE_GROUPS;

that executes the code? how many rows in PER_PEOPLE_GROUPS?

error indicates that more than one. You may need to put a condition in where clause?

Upvotes: 0

APC
APC

Reputation: 146329

This query has no WHERE clause:

SELECT curStr into attValue FROM PER_PEOPLE_GROUPS;

That means it will return hits for all the rows in PER_PEOPLE_GROUPS. The SELECT ... INTO construct populates a single variable and so requires a query which returns exactly one row. The ORA-01422 message indicates that you're not executing an exact fetch, obviously because PER_PEOPLE_GROUPS has more than one row.

Several possible solutions, depending on what you're trying to achieve:

  1. Add a restriction of some kind so that you only return one row from PER_PEOPLE_GROUPS.
  2. Use BULK COLLECT to populate any array instead.
  3. Replace the SELECT with a simple assignment flexTable(flexTable.count) := (TABLE_TYPE_SAMPLE(cur_recd.colA))

On the face of it, discarding the SELECT seems the best option as it doesn't provide you with any information. However, it also seems likely that you are trying to implement some other business logic which isn't expressed in the posted code, so probably you need to make several changes.

Upvotes: 0

Rene
Rene

Reputation: 10551

The problem is with the select into. Not sure why that's there in the first place. The value from your cursor is available in cur_recd.cola and you can use it directly.

create or replace function segmentfields(txnid varchar2) return table_sample is

   flextable table_sample := table_type_sample();

   cursor cur_seg is(
      select cola
        from table1 -- (table name has column colA)
       where id = txnid);
begin
   for cur_recd in cur_seg
   loop
      flextable.extend;
      flextable(flextable.count) := (table_type_sample(cur_recd.cola));
   end loop;
   return flextable;
end;

Upvotes: 0

Related Questions