0

I created a package and I want to create a temporary table inside stored procedure in this package body. But I can not do this because of giving an error message saying insufficient privileges. I can create tables but inside stored procedure, I can not do. Please anyone can help me to create a temporary table inside stored procedure?

create or replace PACKAGE BODY PKG_MY_TESTING AS

PROCEDURE PRC_MY_TESTING_DATA(DATE DATE, ID NUMBER)
IS

EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE all_muster_ids                                                         
    ON COMMIT DELETE ROWS AS                                                                                  
   SELECT *
      FROM MY_TABLE';                                                                                              
COMMIT;                                                                                         
EXCEPTION                                                                                        
WHEN OTHERS THEN                                                                                                      
ROLLBACK;                                                                                   

END PRC_MY_TESTING_DATA;                                                                                  
END PKG_MY_TESTING;
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Rosh
  • 730
  • 2
  • 12
  • 32
  • 3
    You shouldn't normally create database objects on the fly. If you are planning to create and then drop the temporary table, have you looked at PL/SQL collections instead? (Also, sounds like your create table privilege is granted via a role rather than directly to your user; and you can't rollback DDL, it commits implicitly; and catching `when others` is a dangerous habit to get into). – Alex Poole Jun 12 '17 at 08:47
  • Yes. I checked BULK Collect. But I do not need to iterate data for my usage. I just want to use data with some condition. After that, I do not need those data. – Rosh Jun 12 '17 at 08:50
  • 5
    In another query? Why not just join to `my_table` then, what's the point of the temporary table? Your problem is with how privileges are assigned to you, but fixing your methodology is probably better than getting your privileges changed. – Alex Poole Jun 12 '17 at 08:51
  • 1
    [Possibly related](https://stackoverflow.com/a/33774836/266304). – Alex Poole Jun 12 '17 at 08:57
  • 2
    Absolutely agree with @Alex - this is not the right way to solve the problem. Or if you really don't think you can use a join please edit your question to explain your extraordinary circumstances. – APC Jun 12 '17 at 16:43
  • My requirement is to delete some data in several tables.What I tried is to keep those data in a temporary table to use when deleting. Then I do not need to requery the data. If I use join, I have to use this join query every deletion. Is there any other way to keep my data where I can load data once and delete data after finish the work? – Rosh Jun 13 '17 at 10:51

0 Answers0