Porting Oracle Procedure (With Specific Function Calls) To Postgres PL/PGSQL
(This is similar to a question I asked earlier: Porting Oracle Procedure to PostgreSQL)
I need to port:
/*
|| The following private procedure will execute a dynamic pl/sql
|| statement passed to it.
*/
CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.EXECUTE_STMT (stmt VARCHAR) IS
v_num_rows integer;
v_cursor_table integer;
BEGIN
v_cursor_table := dbms_sql.open_cursor;
dbms_sql.parse (v_cursor_table, stmt, dbms_sql.v7);
v_num_rows := dbms_sql.execute (v_cursor_table);
dbms_sql.close_cursor(v_cursor_table);
END execute_stmt;
and
/*
|| The following private procedure will write out to a system
|| file the statement passed to it.
*/
CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.write_log_info (p_path IN VARCHAR2,
p_file_name IN VARCHAR2,
stmt IN VARCHAR2 ) IS
log_file UTL_FILE.FILE_TYPE;
BEGIN
log_file := UTL_FILE.FOPEN (p_path, p_file_name, 'A');
UTL_FILE.PUT_LINE (log_file,stmt);
UTL_FILE.FCLOSE(log_file);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(log_file);
RAISE;
END write_log_info;
and
/*
|| The following procedure will drop the user passed to it
|| and then record its action by writing out to a system file
*/
PROCEDURE DROP_DB_PRO ( p_db_name IN VARCHAR2,
p_path IN VARCHAR2,
p_file_name IN VARCHAR2,
p_status OUT VARCHAR2 ) IS
v_stmt VARCHAR2(1500);
BEGIN
v_stmt := 'DROP USER '||p_db_name||' CASCADE';
execute_stmt (v_stmt);
p_status := 'USER '||p_db_name|| ' HAS BEEN DROPPED';
v_stmt := 'THE USER '||p_db_name||' HAS BEEN DROPPED';
write_log_info(p_path, p_file_name, v_stmt);
EXCEPTION
WHEN OTHERS THEN
v_stmt := 'EXCEPTION raised in DROP_DB_PRO';
write_log_info (p_path, p_file_name, v_stmt);
RAISE;
END DROP_DB_PRO;
From Oracle to PG/PLSQL...
I have gotten this far:
CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.EXECUTE_STMT (stmt VARCHAR)
RETURNS void as '
DECLARE
v_num_rows integer;
v_cursor_table integer;
BEGIN
v_cursor_table := dbms_sql.open_cursor;
dbms_sql.parse (v_cursor_table, stmt, dbms_sql.v7);
v_num_rows := dbms_sql.execute (v_cursor_table);
dbms_sql.close_cursor(v_cursor_table);
END execute_stmt;
' LANGUAGE plpgsql;
Which chokes on:
ERROR: syntax error at or near "dbms_sql"
LINE 1: dbms_sql.parse ( $1 , $2 , dbms_sql.v7)
and
CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.write_log_info (p_path VARCHAR,
p_file_name VARCHAR,
stmt VARCHAR )
RETURNS void as '
log_file UTL_FILE.FILE_TYPE;
BEGIN
log_file := UTL_FILE.FOPEN (p_path, p_file_name, ''A'');
UTL_FILE.PUT_LINE (log_file,stmt);
UTL_FILE.FCLOSE(log_file);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(log_file);
RAISE;
END write_log_info;
' LANGUAGE plpgsql;
which chokes on:
ERROR: syntax error at or near "log_file"
LINE 6: log_file UTL_FILE.FILE_TYPE
and
CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.DROP_DB_PRO ( p_db_name VARCHAR,
p_path VARCHAR,
p_file_name VARCHAR,
p_status VARCHAR )
RETURNS varchar as '
DECLARE
v_stmt VARCHAR(1500);
BEGIN
v_stmt := 'DROP USER '||p_db_name||' CASCADE';
execute_stmt (v_stmt);
p_status := 'USER '||p_db_name|| ' HAS BEEN开发者_如何学Go DROPPED';
v_stmt := 'THE USER '||p_db_name||' HAS BEEN DROPPED';
write_log_info(p_path, p_file_name, v_stmt);
return(p_status);
EXCEPTION
WHEN OTHERS THEN
v_stmt := 'EXCEPTION raised in DROP_DB_PRO';
write_log_info (p_path, p_file_name, v_stmt);
RAISE;
END DROP_DB_PRO;
' LANGUAGE plpgsql;
Help with any of these would be much appreciated (I am new to the world of functions/stored procedures)
For the DBMS_SQL one, look at Dynamic SQL in plpgsql
For UTL_FILE, you'll have to look beyond plpgsql or at Orafce
Orafce can UTL_FILE, just need to update documentation
精彩评论