0

I want to create a temp table in my trigger to store some of the data. I did some research online and most people suggest that I create it by using the following query：

create #tempTableName (x datatype, y datatype..);

or 

select * into #tempTableName from ...;

However, when I did it myself in oracle, it does not work and it seems to suggest me that I can name a table start with "#". What should I do in such circumstance? Also, what is the difference between a PL/SQL table and a temp table? Thanks.

smart_cookie
  • 85
  • 2
  • 4
  • 12
  • You cannot use special characters in object names unless you enclose the name in double-quotes: `"#tempTableName"`. Moreover, DDL-statements are strictly forbidden in triggers. Create global temporary table beforehand and then use it. Check [how-to-create-a-temporary-table-in-oracle](http://stackoverflow.com/questions/2671518/how-to-create-a-temporary-table-in-oracle). – Paul Feb 12 '16 at 22:49
  • 1
    `PL/SQL table` - you mean PL/SQL collection? If yes, it is possible and not so very hard. If you want to create an ordinary table inside the trigger, then you have VERY big problem with design. Better describe here, why do you want it. – Dmitriy Feb 13 '16 at 00:21
  • Thanks guys. So I want to create a temporary table inside my trigger. Is that possible to achieve @Dmitry? – smart_cookie Feb 13 '16 at 15:23
  • I think, it is better for you, if you will describe, what problem you want to solve with a temporary table. Because in any case it is a weird solution. But if you need table, you should create it before use, and then, in trigger you just insert/update data. Temporary table in oracle will be cleaned automatically after commit or session close: https://docs.oracle.com/database/121/CNCPT/tablecls.htm#CNCPT88820 – Dmitriy Feb 13 '16 at 15:53

0 Answers0