Sunday, November 1, 2009

How we can create a table in PL/SQL block. Insert records into it??? is it possible by some procedure or function?? please give example...

CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)
AS
l_stmt VARCHAR2(200);
BEGIN
DBMS_OUTPUT.put_line('STARTING ');
l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';
execute IMMEDIATE l_stmt;
DBMS_OUTPUT.put_line('end ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);
END;

We can create table in procedure as explained in above case. but we can't create or perform any DDL in functions.

2 comments:

Jenice said...

Is it possible to perform all these operations in a script ? I do know the limitation that we cannot create or perform any DDL in functions but is there any other alternative to do so. I do have copied the code of the function shared and will try to implement that. Thanks for sharing the code.
sap support pack upgrade

Unknown said...
This comment has been removed by the author.