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;

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

To create a table you need the create table privilege.

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?

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

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

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.

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!