Stored Proc / 'EXECUTE IMMEDIATELY' Help

From: Todd R. Haverstock <trhaverstock_at_yahoo.com>
Date: 18 Mar 2002 08:36:33 -0800
Message-ID: <9390905d.0203180836.c3bcfdd_at_posting.google.com>

In Oracle 8i, in the below stored procedure (which is part of a package) I am having some difficulty. I need to create the temp table, populate it, then return its contents. Oracle complains about my SELECT * FROM temp_tbl below, saying that it doesn't exist (PLS-00201: identifier 'TBL_TEMP_OBJ_ACCESS' must be declared).

Is there a better way to do this (with using a temp table or instead by using cursors)? There are actually 4 more selects that populate the temp table, I have removed them for simpilicity. The unique index is so that subsquent inserts of the same object_name will intentionally fail. This 'awkward' sql is a result of the strange business rules in how the data is stored more than poor table design in and of itself. Any suggestions greatly appreciated!

Todd Haverstock
trhaverstock_at_yahoo.com

P.S. wc.pkg_types.cursorType is simply this:

---

PACKAGE wc.pkg_types
AS
  TYPE cursorType IS REF CURSOR;
END;

---

---

  PROCEDURE sp_test (
    outrec OUT wc.pkg_types.cursorType)

  IS

  BEGIN     EXECUTE IMMEDIATE ('drop table tbl_temp_obj_access');     EXECUTE IMMEDIATE ('create global temporary table tbl_temp_obj_access (object_id NUMBER(9,0), object_name VARCHAR2(40), insert_flag NUMBER(1,0), update_flag NUMBER(1,0), delete_flag NUMBER(1,0))');     EXECUTE IMMEDIATE ('create unique index i_object_name on tbl_temp_obj_access (object_name)');

    EXECUTE IMMEDIATE ('INSERT INTO tbl_temp_obj_access (object_id, object_name, insert_flag, update_flag, delete_flag)

      SELECT object.object_id AS object_id,
        object.object_name AS object_name,
        object.object_description AS object_description,
        group_object.insert_flag AS insert_flag,
        group_object.update_flag AS update_flag,
        group_object.delete_flag AS delete_flag

      FROM group_object,
        object

      WHERE group_object.wc_group_id IN (SELECT wc_group_id
          FROM user_group
          WHERE wc_user_id = 410)
        AND group_object.object_id = object.object_id
        AND object.status_flag = 1');

    OPEN outrec FOR
      SELECT * FROM tbl_temp_obj_access;

    EXECUTE IMMEDIATE ('drop table tbl_temp_obj_access');

  END; Received on Mon Mar 18 2002 - 17:36:33 CET