Insert the record in Type Record and extract the data from Type

Question

I want to insert the data into Type and want to extract the same in the procedure. its showing the table view doesn't exist even i have created the type with same name which i am using to insert the data. Please suggest.

SET SERVEROUTPUT ON;

DECLARE
FACTORYID       VARCHAR2(200);
TYPE FACTORY_INFORMATION
    IS   RECORD
        (FACTORYID             VARCHAR2(1000),
         BILLINGDAY            VARCHAR2(1000),
         DUE_DAY               VARCHAR2(1000),
         FACTORYNAME           VARCHAR2(1000)
        );

    TYPE TT_FACTORY_INFORMATION   IS TABLE OF FACTORY_INFORMATION INDEX BY BINARY_INTEGER;

    FACTORY_INFO TT_FACTORY_INFORMATION;

BEGIN
INSERT INTO FACTORY_INFO (
SELECT 11,
       25,
       20,
       'xyz'
       FROM dual);
);

SELECT FACTORY_INFO INTO FACTORYID FROM FACTORY_INFO;
DBMS_OUTPUT.PUT_LINE(FACTORYID);
END;

Getting the below error.

Error report -
ORA-06550: line 16, column 13:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 16, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 23, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 23, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

FACTORY_INFO is a PL/SQL variable not a table. So you can't use it as target for a SQL INSERT statement.

Likewise, because FACTORY_INFORMATION is a PL/SQL record type you can't select from it in SQL (not even using a table() function). You need to use PL/SQL collection operations.

You can do this:

DECLARE
  FACTORYID       VARCHAR2(200);
  TYPE FACTORY_INFORMATION
    IS   RECORD
        (FACTORYID             VARCHAR2(1000),
         BILLINGDAY            VARCHAR2(1000),
         DUE_DAY               VARCHAR2(1000),
         FACTORYNAME           VARCHAR2(1000)
        );

    TYPE TT_FACTORY_INFORMATION   IS TABLE OF FACTORY_INFORMATION INDEX BY BINARY_INTEGER;

    FACTORY_INFO TT_FACTORY_INFORMATION;

BEGIN
  SELECT 11,
         25,
         20,
         'xyz'
  bulk collect into FACTORY_INFO
  FROM dual;

  DBMS_OUTPUT.PUT_LINE(FACTORY_INFO(1).FACTORYID);

END;
/

There is an entire chapter of the Oracle PL/SQL Reference devoted to Records and Collections, with plenty of examples of how to use them. Find out more