Skip navigation.

Passing parameters to SQL PLUS from Oracle Forms

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

The codes could be better with less errors.

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

Thanks.

procedure running

Dear

This procedure is running dynamically.