I was working on a Application Backup Form using oracle forms,where i need to call a sql script file with dynamic schema (dynamic owner), so i need to pass the owner from the oracle forms,
i came with the following procedure:
----------------------------------------------
-- PROCEDURE PR_RUN_SCRIPT_PARAM
----------------------------------------------
PROCEDURE PR_RUN_SCRIPT_PARAM( P_USER VARCHAR2,
P_PASS VARCHAR2,
P_FILE VARCHAR2,
PAR1 VARCHAR2,
PAR2 VARCHAR2) IS
V_CONNECT_STRING VARCHAR2(30) := GET_APPLICATION_PROPERTY(CONNECT_STRING);
V_PROGRAM VARCHAR2(15) := 'SQLPLUS.EXE';
V_TAIL VARCHAR2(255);
BEGIN
IF PAR1 IS NOT NULL AND PAR2 IS NULL THEN
V_TAIL := ' "'PAR1'"';
ELSIF PAR1 IS NULL AND PAR2 IS NOT NULL THEN
V_TAIL := ' "'PAR2'"';
ELSIF PAR1 IS NOT NULL AND PAR2 IS NOT NULL THEN
V_TAIL := ' "'PAR1'"'' "'PAR2'"';
ELSE
V_TAIL := '';
END IF;
IF P_USER = 'SYS' THEN
HOST(V_PROGRAM' "'P_USER'/'P_PASS'@'V_CONNECT_STRING' AS SYSDBA"'' @'P_FILEV_TAIL,NO_SCREEN);
ELSE
HOST(V_PROGRAM' 'P_USER'/'P_PASS'@'V_CONNECT_STRING' @'P_FILEV_TAIL,NO_SCREEN);
END IF;
END PR_RUN_SCRIPT_PARAM;
And this is a sample sql script that you can used it with dynamic parameter:
-- C:\Sample.sql
insert into &1..table(column1) values(&2.);
commit;
now how to use PR_RUN_SCRIPT_PARAM:
PR_RUN_SCRIPT_PARAM('SYSTEM','MANAGER', 'C:\Sample.sql', 'VALUE1');
Happy coding
Friday, October 06, 2006
Subscribe to:
Post Comments (Atom)

No comments:
Post a Comment