Doing an oracle conversion. I'm looking for the best practice to handle simple ETL steps like 'check if a table exists, if so drop it'
How do people generally do that in BTEQ?
In Oracle i would have a simple procedure to accept the schema_name, table_name and drop if it exists
How would you do that generically in BTEQ, ie how would you pass the current database name to a procedure owned by another database? Without getting ugly and writing out to a file etc
Alternatively I tried doing it directly in BTEQ, temporarily setting maxerror to ignore the error:
.SET MAXERROR 8
drop table nonexistent_table;
.SET MAXERROR 1
but that will set the return code to 8 and once the script completes it will terminate. Is there a way to 'reset' the errorlevel?
Thanks for any help
How about this:
.SET ERRORLEVEL 3807 SEVERITY 0
DROP TABLE this.doesNotExist;
.SET ERRORLEVEL 3807 SEVERITY 8
This SP will drop a table and ignore the table doesn't exist error. If you don't specify the database name it defaults to the session's default database.
/* Drop a table ignoring 3807 error (Table doesn't exist) */ REPLACE PROCEDURE drop_table_if_exists ( IN db_name VARCHAR(128) CHARACTER SET Unicode, IN tbl_name VARCHAR(128) CHARACTER SET Unicode, OUT msg VARCHAR(400) CHARACTER SET Unicode ) SQL SECURITY INVOKER BEGIN DECLARE full_name VARCHAR(361) CHARACTER SET Unicode; DECLARE sql_stmt VARCHAR(500) CHARACTER SET Unicode; DECLARE exit HANDLER FOR SqlException BEGIN IF SqlCode = 3807 THEN SET msg = full_name || ' doesn''t exist.'; ELSE RESIGNAL; END IF; END; SET full_name = '"' || Coalesce(db_name,DATABASE) || '"."' || tbl_name || '"'; SET sql_stmt = 'DROP TABLE ' || full_name || ';'; EXECUTE IMMEDIATE sql_stmt; SET msg = full_name || ' dropped.'; END;
How would i pass the db name to that procedure from a BTEQ script, assuming i'm running with a default database different to the procedure owner? As i understand it BTEQ doesn't support variables. Would i have to populate a volatile table or something? eg
select database; <-- returns 'DEV_1'
call etl_load.drop_table(<database_name>, 'my_table', oMsg);