Oracle Error - Missing Global Keyword

I have a create temporary table command in Oracle that has worked in the past but now I'm getting the following error:

SQL Error: ORA-14459: missing GLOBAL keyword
14459. 00000 -  "missing GLOBAL keyword"
*Cause:    keyword GLOBAL is missing while creating temporary table.
*Action:   supply keyword.

I am not an admin on the database, just a user with read only priviledges

Sql looks like this:
DROP TABLE IF EXISTS t1

CREATE TEMPORARY TABLE t1(
SELECT DATE(a.thedate) AS thedate, etc. etc.)

Thanks for any assistance!
kevbob650Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
your sql looks like sql server syntax

try something like this

CREATE GLOBAL TEMPORARY TABLE t1 as
 SELECT trunc(a.thedate) AS thedate, etc. etc.


if you have read only privileges though, you won't be able to create a temp table.
also, if you are coming from sql server,  check with your dba about creating the table for you.

In oracle,  creating and dropping temp tables is NOT a good way to work with temporary result sets.
0
kevbob650Author Commented:
Thanks for the quick response. I'm confused, this query has worked just fine for the past few months?  What would be the correct way to work with some temporary results that I can query against?
0
sdstuberCommented:
>>> I'm confused, this query has worked just fine for the past few months?

that's not possible.  This syntax
DROP TABLE IF EXISTS t1

 CREATE TEMPORARY TABLE t1(
 SELECT DATE(a.thedate) AS thedate, etc. etc.)

Open in new window

is not, nor has it ever been, legal for Oracle.

Drop table doesn't have an "if" clause
CREATE TEMPORARY TABLE isn't legal
DATE() isn't an Oracle function and it's not legal to create a function called "DATE" since it's a reserved word.



To use global temporary tables you create them exactly once,
Not once per session, or once per user, just once, ever.
And, you never drop them.

Then, you insert,update, delete and select from that table just as you would in sql server. or with other oracle tables.

The "trick" with them though is the contents are only visible to your session, regardless of whether you commit or not.

So, you can insert a row, I can insert the exact same row, we won't create a duplicate key violation and we each can only see one row, our version of that row.

Another thing to consider with Oracle global temporary tables - is what happens at the end of a transaction (i.e. when you commit or rollback)

They can either preserve the rows, or automatically truncate.

For more information I recommend reading the concepts section on them, which then has links to the syntax doc and examples
http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1138
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kevbob650Author Commented:
Thanks for your time! I appreciate it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.