Passing parameters to SQL PLUS from Oracle Forms
Submitted by mshehadeh on Tue, 2008-05-20 02:29
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
»
- mshehadeh's blog
- Log in to post comments
Comments
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.