Re: insufficient privilege to create temp table from stored proc

The problem, most likely, is that you come from a SQL Server background. There is almost no need, I'd say no excuse, for temporary tables in Oracle.

If you are trying to create a temporary table because that is what you did in SQL Server or Sybase ... stop it ... and do it the Oracle way. All you are doing is killing the performance of scalability of your app.

but to answer your question ... temporary tables are not created in Oracle in stored procedures as they are in SQL Server. They are created like any other table using DDL, are a permanent part of the data dictionary, and are not dropped at the end of a session.

Daniel Morgan

Jon Waterhouse wrote:

> I am trying to create a temporary table, which works fine in SQL-PLUS, but
> gives me an insufficient privilege error when run as below in a stored
> procedure.
> Any idea what privilege I'm missing. I've been going round and round int he
> help for about half an hour.
>
> Thanks,
>
> Jon
>
> PROCEDURE create_temp_tables IS
> BEGIN
> execute immediate 'create GLOBAL TEMPORARY table temp_client
> (id decimal(15), sin int, dob date, sex char(1), fil int,role
> char(2),age int,realdob int)';
> END;
Received on Mon Feb 11 2002 - 11:17:21 CST