1

i want to use sql to query an object like following code.

PACKAGE:


PACKAGE DRAWING AS 
TYPE AWARD_NUMBER_ROW IS RECORD (
      A_NUMBER    VARCHAR2 (10),
      A_TYPE      char (1)
  ); 

TYPE AWARD_ROW IS RECORD (
      A_NUMBER    VARCHAR2 (10),
      A_NAME      VARCHAR2 (50)
  ); 

TYPE AWARD_NUMBER_TABLE IS TABLE OF AWARD_NUMBER_ROW  INDEX BY PLS_INTEGER; 
TYPE AWARD_TABLE IS TABLE OF AWARD_ROW  INDEX BY PLS_INTEGER; 

AWARD AWARD_TABLE;
AWARD_T AWARD_TABLE; 

PROCEDURE DRAWING (AWARD_NUMBER IN AWARD_NUMBER_TABLE);

END DRAWING;

BODY:


create or replace 
PACKAGE BODY DRAWING AS
PROCEDURE DRAWING (AWARD_NUMBER IN AWARD_NUMBER_TABLE) AS
BEGIN
    EXECUTE IMMEDIATE
    'SELECT * FROM AWARD_INF'
    BULK COLLECT INTO AWARD_T;

    /*
          error is from here. AWARD_T does not exist or is marked for delete
    */
    SELECT T.* bulk collect into AWARD FROM TABLE(AWARD_T ) T WHERE T.A_NAME = '123456789' ;

END DRAWING; 
END INVOICE_DRAWING

here is exception message.

ORA-21700: object does not exist or is marked for delete "object does not exist or is marked for delete" *Cause: User attempted to perform an inappropriate operation to an object that is non-existent or marked for delete. Operations such as pinning, deleting and updating cannot be applied to an object that is non-existent or marked for delete. *Action: User needs to re-initialize the reference to reference an existent object or the user needs to unmark the object.

how do i fix it??

  • possible duplicate of [How to use a table type in a SELECT FROM statement?](http://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement) – Tony Andrews Jun 05 '13 at 10:08

0 Answers0