Want to take back up at run time

Database
Enthusiast

Want to take back up at run time

Hi all,

I am having a unique requirement.
In it I need to take backup of a table as table_date( the date at which backyp has taken.).
For this I am useing macro and calling this macro in a BETEQ.

But when I want to take backup from following SQL it is not permitting because select is not an valid clause for create satatement.

create table table_ || select date as select * from table with data

So please give u'r valuable suggestions.

Regards,
gander_ss
1 REPLY
Enthusiast

Re: Want to take back up at run time

Macros doesn't allow you to use "dynamic" sqls (which includes generating a tablename/columnname/where clause etc at run time), you can only pass parameters where it expects a constant value

ie like

WHERE e.empid = :i_empid

etc...

what you need is a stored procedure that can execute dynamic sql. be aware that to create a stored procedure that makes use of dynamic sql you would need to create the stored procedure under the same userid that you are using to create the stored proc.