Oracle pl/sql problem when trying to reference plsql table in query

1
votes

I am getting a pls-201 error when I try to reference a plsql table record in a query.

Here is an example of the issue:

DECLARE
 TYPE trxtypeinforec IS RECORD(
    NAME ra_cust_trx_types.NAME%TYPE
    );

  TYPE trxtypeinfotab IS TABLE OF trxtypeinforec
   INDEX BY PLS_INTEGER;

   g_inv_type        trxtypeinfotab;
   l_result VARCHAR2 (100);

BEGIN
    g_inv_type(1).NAME := 'Test';

    SELECT g_inv_type(qry.ID).NAME
    INTO l_result
    FROM (SELECT 1 ID
            FROM dual) qry;

END;

Error report - ORA-06550: line 15, column 23: PLS-00201: identifier 'QRY.ID' must be declared ORA-06550: line 15, column 23: PLS-00201: identifier 'QRY.ID' must be declared ORA-06550: line 15, column 12: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 15, column 5: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

2
oracleplsql
Your type definition for "TYPE trxtypeinforec IS RECORD( NAME ra_cust_trx_types.NAME%TYPE );" is local to your anonymous block and therefore cannot be seen by the SQL engine. Thus within a SQL statement it does not exist. Create this type at the schema level or in a package spec. – Belayer

2 Answers

0
votes

Retrieve the index from the SELECT first into a local variable and then assign the value from the associative array to l_result in a PL/SQL scope and not in an SQL scope:

DECLARE
  TYPE trxtypeinforec IS RECORD(
    NAME ra_cust_trx_types.NAME%TYPE
  );

  TYPE trxtypeinfotab IS TABLE OF trxtypeinforec INDEX BY PLS_INTEGER;

   g_inv_type  trxtypeinfotab;
   idx         PLS_INTEGER;
   l_result    ra_cust_trx_types.NAME%TYPE;

BEGIN
  g_inv_type(1).NAME := 'Test';

  SELECT 1
  INTO   idx
  FROM   DUAL;

  l_result := g_inv_type(idx).NAME;

  DBMS_OUTPUT.PUT_LINE( l_result );
END;
/

Outputs:

Test

db<>fiddle here

0
votes

Please try using a cursor.

SET SERVEROUTPUT ON;
DECLARE
 TYPE trxtypeinforec IS RECORD(
    NAME ra_cust_trx_types.NAME%TYPE
    );

  TYPE trxtypeinfotab IS TABLE OF trxtypeinforec
   INDEX BY PLS_INTEGER;

   g_inv_type        trxtypeinfotab;
   l_result VARCHAR2 (100);

   -- Query for retrieving index goes in the cursor or use existing cursor
   CURSOR cur_id  IS 
   SELECT 1 AS ID FROM DUAL;

BEGIN
    -- Populate records here or put inside loop as required
    g_inv_type(1).NAME := 'Test';

    -- Loop through the cursor to get the relevant index    
    FOR rec in cur_id
    LOOP
        SELECT g_inv_type(rec.ID).NAME
        INTO l_result
        FROM DUAL;
        dbms_output.put_line (l_result);
    END LOOP;

END;
/

It will be helpful if we could get some more information on how the existing cursor is fetching index values.

Output:

Test

PL/SQL procedure successfully completed.