CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
rcfmonarch
rcfmonarch

Reputation: 153

PL/SQL 11g > Getting Data into a Table of Records Object

I'm trying to pull data from a complex query (it's been simplified here for review) using custom RECORD and TABLE OF RECORD data types, but I can't get data into the table due to a "PLS-00308: This construct is not allowed as the origin of an assignment" Error. I've followed the examples carefully and don't understand the problem. Can anyone point me in a direction.

here's the code

TYPE CORE_REC IS RECORD 
      (
           OrgID CHAR(20 BYTE)
           , StoreNumber VARCHAR2(200 BYTE)
           , StoreName VARCHAR(200 BYTE)
           , AssociateName VARCHAR2(300 BYTE)
      );

 TYPE CORE_REC_CURSOR IS REF CURSOR RETURN CORE_REC;

 TYPE CORE_REC_TABLE IS TABLE OF CORE_REC INDEX BY BINARY_INTEGER;

  FUNCTION CORE_GETCURRS (
      OrgID IN CHAR
 ) RETURN HDT_CORE_MAIN.CORE_REC AS

 CurrTable HDT_CORE_MAIN.CORE_REC;
 i BINARY_INTEGER := 0;

 CURSOR CurrCursor IS
      WITH
           CoreCurrs AS
                (SELECT
                     busSTR.id AS OrgID
                     , busSTR.name AS StoreNumber
                     , busSTR.name2 AS StoreName
                     , emp.lname || ', ' || emp.fname || ' ' || emp.mname AS AssociateName
                     FROM tp2.tpt_company busSTR
                          INNER JOIN tp2.cmt_person emp
                               ON busSTR.ID = emp.company_id
                     WHERE
                          busSTR.id = OrgID
                )
      SELECT
           CoreCurrs.OrgID
           , CoreCurrs.StoreNumber
           , CoreCurrs.StoreName
           , CoreCurrs.AssociateName
           FROM CoreCurrs
      ;
 BEGIN
     DBMS_OUTPUT.ENABLE(1000000);
     OPEN CurrCursor;
     LOOP
          i := i + 1;
          FETCH CurrCursor INTO CurrTable(i);
          EXIT WHEN CurrCursor%NOTFOUND;
     END LOOP;
     CLOSE CurrCursor;
     RETURN CurrTable;

  END CORE_GETCURRS;

The error gets thrown at the FETCH statement.

Upvotes: 0

Views: 1896

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

Your variable is the wrong type, it should be:

 CurrTable HDT_CORE_MAIN.CORE_REC_TABLE;

At the moment you're trying to select into an element of a record, rather than element of a table, which doesn't make sense. When it's defined as CORE_REC, referring to CurrTable(i) doesn't mean anything.

Upvotes: 1

Related Questions