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

Question

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:

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
– Belayer, Commented Dec 9, 2019 at 18:22

MT0 · Accepted Answer · 2019-12-09 15:28:07Z

0

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

answered Dec 9, 2019 at 15:28

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Taylor Over a year ago

I have greatly simplified the query in my example. The query is actually in a cursor that is pulling multiple records back. I was hoping to find a way of having this all work within the single query.

MT0 Over a year ago

@DavidTaylor Associative arrays (i.e. INDEX BY arrays) and records are PL/SQL data types and can only be used in a PL/SQL scope. If you want to use "arrays" in an SQL scope then you will need to use either collections (a.k.a nested table types without the INDEX BY) or VARRAYs and Object data types defined in the SQL scope.

MT0 Over a year ago

Also, if you give us a simplified question then we'll answer for the simplified question; if you want a more complicated answer then you would be better giving a minimal reproducible example that is appropriate for the question you want answered with an appropriate example of the data and how you want to transform it and an example of the cursor your currently trying to use.

David Taylor Over a year ago

I understand that.. I wanted to understand why I was getting tthe error so decided to reduce to a very basic example. I think your previous answer point me in the direction I need to take. Many thanks

Prashant Sharma · Accepted Answer · 2019-12-09 16:09:41Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related