Oracle to Teradata Migration

Database
Enthusiast

Oracle to Teradata Migration

This bit of code is part of a procedure Which is written in Oracle environment..
I haven't tested yet it in Teradata as it is pre-IT stage.But I think It will work in teradata environment also because it supports ANSI merge statement.Any suggestion regarding this is welcome.

MERGE INTO Upendra_best_customer_mail pl
USING
(SELECT
b.prs_cd_id, b.upendra_id, b.effective_update_date,
b.date_create, b.date_update, b.email_address,
a.prs_id
FROM exi_acd_best_email b , exi_acd_person a
WHERE b.prs_cd_id = a.prs_cd_id(+)
AND b.upd_ts >= ld_last_insert
AND b.upd_ts <= ld_max_dt) sl
ON (pl.prs_cd_id = sl.prs_cd_id)
WHEN MATCHED THEN
UPDATE SET
pl.prs_id = sl.prs_id,
pl.upendra_id = sl.upendra_id,
pl.prs_eff_upd_ts = sl.effective_update_date,
pl.prs_email_addr = sl.email_address,
pl.acd_cre_ts = sl.date_create,
pl.acd_upd_ts = sl.date_update,
pl.upd_ts = ld_max_dt
WHEN NOT MATCHED THEN
INSERT ( pl.prs_cd_id, pl.prs_id, pl.upendra_id,
pl.acd_cre_ts, pl.acd_upd_ts, pl.prs_eff_upd_ts,
pl.prs_email_addr, pl.cre_ts, pl.upd_ts )
VALUES ( sl.prs_cd_id, sl.prs_id, sl.upendra_id,
sl.date_create, sl.date_update, sl.effective_update_date,
sl.email_address, ld_max_dt, ld_max_dt );

The real is Problem is here.......
I used UTIL_file package to maintain logs.
But here in teradata ,no such ready made packages are there.

Sample code That I have used .......

IF uuv_status = 'SUCCESS' THEN
uuv_my_mesg := 'Procedure started successfully ' ;
UTL_FILE.PUT_LINE(uuv_log_handle,uuv_my_mesg,TRUE);
ELSE
uuv_my_mesg := 'Error in Opening log file';
END IF;

Can anyone guide me about this
5 REPLIES
Enthusiast

Re: Oracle to Teradata Migration

Teradata V2r6.x doesn't support ANSI merge, it's supported only from TD12.0......

As far as logs as concerned, most of the shops I have worked, uses a stored proc to insert into some "log tables" (some how I admit that I like a log table compared to log file, because you can do all kinds of "search" on a log table.... :) )

Enthusiast

Re: Oracle to Teradata Migration

Teradata's MERGE statement (as of 12.0) has some extended syntax that allows an "error table" to be specified, and then errors that are encountered during the merge (e.g. constraint violations) are automatically logged to the error table.

If that isn't what you want to log, perhaps a trigger would serve?
Enthusiast

Re: Oracle to Teradata Migration

log table is obviously an option,and it can be exported by bteq export to a data file.
But I am trying to use the external procedure feature to write the same UTIL_FILE pack in Teradata .

Enthusiast

Re: Oracle to Teradata Migration

It seems that you have to do it yourself
we can only get it from bteq log.
or you can use " case when "
but i thought it will need extra spaces
Enthusiast

Re: Oracle to Teradata Migration

There is a print statement which gives the output to stdout. In Unix environment ,stdout is having a file descriptor 1 as unix treats every device as a file. We can redirect it to any file using unix '>' operator. It can be done using a shell script. I think this may be a way to maintain log files.The shell script will accept the input which ever come as the input to the stdout and move the contents to a specified file.
like stdout>mylog. Can anyone give me any remarks regarding this.