Passing parameters to SQL PLUS from Oracle Forms

articles: 

I was working on a Application 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

Comments

Good Idea for passing parameters to SQL. But, the codes lack proper concatenation and should be error free.

Thanks.

anoop_sachan2004's picture

Dear

This procedure is running dynamically.