Wednesday, January 6, 2010

Oracle 10g: Create Temporary Table (sort of)

I've searched around and found out that oracle does not have specific built in function to create temporary table like the SQL server.  We need a little bit of creativity to create temporary table in oracle (sort-of). 

1.  Create a so-called temporary table (not so temporary..I don't really have time to control it dynamically through stored procedure)

CREATE TABLE RPTSTATLICSTATE_TEMP(
NEGERI varchar2(100),
NEGERIID integer,
KPM14A integer,
KPM15A integer,
KPM16A integer,
KPM17A integer,
KPM18A integer,
KPM19A integer,
KPM20A integer,
KPM21A integer,
KPM22A integer,
KPM23A integer,
KPM24A integer,
KPM25A integer,
KPM26A integer,
JUMLAH integer
)
 
 
2.  Create a stored procedure that will drop table if the table exist and recreate the temporary table.  The so-called temporary table will be dropped and created everytime the stored procedure being executed.
 
PROCEDURE SP_IFTableExist(tablename_ IN varchar2)
AS
     vCreate_ integer;
BEGIN


--1. Check whether table exists or not.
select count(*) into vCreate_ from user_tables where TABLE_NAME = 'RPTSTATLICSTATE_TEMP' ;

--2. If vCreate_ = 1; it means table exists...so...drop the table.
if vCreate_ =1 then
    EXECUTE IMMEDIATE 'Drop Table RPTSTATLICSTATE_TEMP';

end if;
EXECUTE IMMEDIATE 'CREATE TABLE RPTSTATLICSTATE_TEMP(  ' || 

' NEGERI varchar2(100),  '  ||
' NEGERIID integer, ' ||
' KPM14A integer, ' ||
' KPM15A integer, ' ||

' KPM16A integer, ' ||

' KPM17A integer, ' ||

' KPM18A integer, ' ||

' KPM19A integer, ' ||

' KPM20A integer, ' ||

' KPM21A integer, ' ||

' KPM22A integer, ' ||

' KPM23A integer, ' ||

' KPM24A integer, ' ||

' KPM25A integer, ' ||

' KPM26A integer, ' ||

' JUMLAH integer ' ||

') ';

--3. exception to cater error found.
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - ' ||SQLCODE||' -ERROR- '||SQLERRM);


END;