Help - Search - Members - Calendar
Full Version: Table creation inside function
Oracle DBA Forums > Oracle > Oracle Forum
madhukar
Hi,

i am trying to create table inside function where in after creating table when am trying to access the table with select statement oracle is throwing error 'Table/view doesnot exist -00942', could any one pls help me, below is the code snippet


create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin

begin
execute immediate 'select * from example1';

exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/

select * from example;

burleson
Hi,

Does the user executing this have DBA privilege?
madhukar
QUOTE (burleson @ Feb 25 2012, 08:00 AM) *
Hi,

Does the user executing this have DBA privilege?



this is business proc executing within application schema, is this really requires a DBA privilage, culd you pls give me more idea for what reason we require DBA privilage in the below function
burleson
To create a table you need the create table privilege.
SteveC
CODE
create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin

begin
execute immediate 'select * from example1';

exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/


You pass in a parameter, m_key, and do nothing with it.

Your create table is incorrect - you need more than varchar2, as in, varchar2(10).

Storage in the buffer pool? For what?

The exception handling is nonsensical. Why bother with the pragma init if you are not going to do anything with it?

The last select statement - is that a typo? Your function is example1, and then you are selecting from a table named example.

Get a book on PL/SQL programming. Or, read any number of free tutorials on the internet. For a homework problem, you get an F.

And on top of that, you get an F-. What does a function do? Where is your return clause?
madhukar
hi,

create table privilage exist for the app schema, have tested same logic(table doesnot exist and create this table dynamically) in ananymous block it works fine, so just wanted to check whether any restirction on create table within function
aussie_dba
You may not put create table inside a fuction.

Besides being really stipid, it is not supported.

Restrictions on User-Defined Functions User-defined functions are subject to the following restrictions:

http://www.dba-oracle.com/t_plsql_function_restrictions.htm
madhukar
thanks for the update, could you pls help me any other alternative or workaround to create table inside store proc, as it is business reqmnt.
burleson
Hi Madhu,

>> create table inside store proc, as it is business reqmnt.

Tell whoever wrote this requirement that Donald K. Burleson sez that creating tables with DDL in an application is NOT a good practice.

It's never a good business requirement too create tables within an application.

Only the DBA should be creatring tables.

If you need a space to hold transient data use global temporary tables, that is what they are for:

http://dba-oracle.com/googlesearchsite_pro...emporary+tables

Good Luck!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.