automate the table creation process daily

[scheler]

Hello all,
I need to create a table daily at the end of the day automatically. I need to write a script WHICH WOULD CREATE A TABLE and would run automatically daily at the end of day. This table is based on already existing tables.
I didn't create a view because I need to update this table after some process is done.
So , since its a table based on already existing tables I need this table to get created automatically daily at the end of the day so that it will have most recent data from those depenedent tables.

Plez let me know ASAP

Thanks

[stecal]

See "dbms_job." "DBMS_JOB is an interface into the Oracle job queue subsystem that allows automated, unattended scheduling and execution of PL/SQL programs." (O'Reilly "PL/SQL Built-ins")

[scheler]

Here is the deal.
T1:= Table1
T2 = Table2
V1 = View
T3 = table3
T4 = Table4 (This is a static table based on the query)

1)T1+T2 = V1;
2) V1 + T3 = T4;

The T4 table will have records populated only when you execute the script to create T4 table. If there are any new records added to T1 or T2 or T3 then T4 will not have most recent records. TO have most recent records the script which is used to create T4 table needs to be ran manually so that it has most recent recrods.
This all should occur before doing some process which is done daily.

I used FORMS.DDL('Create table T4 as (select col1.....from V1, T3)
But thsi didn't work either...

How does DBMS.JOB fit into this criteria?

Is there any way that I can call the script which creates T4 table from FORMS.

[stecal]

Forms? What forms?

Anyway, you can use a trigger to launch the dbms_job process, or you can simply use a trigger to do your updates for you when an event of interest occurs.

forms_ddl to create the table would work, assuming, of course, you are using forms. Are you? If not, kind of hard to make a forms built-in run outside of a form...

Read up on dbms_job. Pretty straightforward.

[mike9]

Hi Scheler,

I got the following questions:
Why do you create one table for each day?
How much data have you per day?
Why are you not simply using a range partitioned table?

[scheler]

Yes, I am using Oracle Forms I will use this procedure to create table in Pre-Form trigger.

Forms.ddl('Drop table Table1')
/
Forms.ddl('Create table table1 as(select 'XXXX' col1, 'This is my table' col2, col3 , col4 from table2)');
IF NOT Form_Success THEN
Message ('Table Creation Failed');
ELSE
Message ('Table Created');
END IF;

EXCEPTION
When others then
message('Error: Contact application administrator. '||sqlerrm||' '||sqlcode);


END;


This is what I wrote in Pre-Form trigger. Its gives me 'Table creation Failed'. It fails to create the table.

I guess the problem is With Single quotes. IF I need to populate the column with a static srting value.

How do I do that, whats wrong in the above code?

Thanks
saritha

[scheler]

What is Range partioned.
I create a table each becuase the table is dependent on the existing tables. If there is a new records inserted in any of those dependent tables then the table I created will not have those records. I need to ececute the script for creating T4 table everytime for getting most recent data.

[scheler]

Eveyrday its not a new table I am creating.I need to create the same table daily as to populate with most recent records.

[stecal]

1. Instead of creating the table everyday (is it dropped everyday as well?), truncate it. Why go through the process of dropping the table when truncate will serve as a better tool?

2. To use single quotes, you can concatenate chr(39) in the statement.

1 select chr(39)||'Example of'||chr(39)||' using single quotes.'||chr(39)
2* from dual
QL> /

CHR(39)||'EXAMPLEOF'||CHR(39)||'US
---------------------------------
'Example of' using single quotes.'

[mike9]

Originally posted by scheler
Eveyrday its not a new table I am creating.I need to create the same table daily as to populate with most recent records.

Why not using Materialized Views ?