Formating date field in LOV in Oracle Designer 2000

I have a problem with formatting data field in list of values in Oracle Designer,even if i put format mask for the date field 'dd-mm-yyyy',it appears at runtime in the following format:
'YY-MM-DD'
To resove this problem i tried to add a string value named NLS_DATE_FORMAT to the oracle designer home under :
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\DESIGNER_HOME]
Create a string value named NLS_DATE_FORMAT and put the format you want to use for your date, DD-MM-YYYY , for example.
N.B: Replace DESIGNER_HOME by yours.
Review your forms and you will see the date field well formatted in the LOV
Happy coding

Disabling constraints before the import operation

Some times you need to disable user contraints before doing the import for some user,
for this reason i created a procedure that can be used to disable the constraints before the import,and than use it again to enable the constraints after doing the import,and also this procedure can be used to drop user constraints when needed.
I named it MANAGE_USER_FK_PK_UK,note the order in the name FK_PK_UK,because when you want to disable constraints you should disable the foreign keys after doing that for the primary keys,for the unique keys it can be done at first or not,i do it at last.
The procedure have 4 parameters:
- OPERATION VARCHAR2
- FK BOOLEAN DEFAULT TRUE
- PK BOOLEAN DEFAULT TRUE
- UK BOOLEAN DEFAULT TRUE
The first parameter OPERATION must be in ('ENABLE','DISABLE','DROP'),this indicates the operation desired to be run.
The second parameter,if true it will be applied to foreign keys else no
The third parameter,if true it will be applied to primary keys else no
The forth parameter,if true it will be applied to unique keys else no

-- MANAGE_USER_FK_PK_UK.SQL
CREATE OR REPLACE PROCEDURE MANAGE_USER_FK_PK_UK(OPERATION VARCHAR2 ,FK BOOLEAN DEFAULT TRUE,PK BOOLEAN DEFAULT TRUE,UK BOOLEAN DEFAULT TRUE) IS
ST VARCHAR2(255);
CURSOR R
IS
SELECT TABLE_NAME,CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R';

CURSOR P
IS
SELECT TABLE_NAME,CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';

CURSOR U
IS
SELECT TABLE_NAME,CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'U';

BEGIN
IF UPPER(OPERATION) IN ('DROP','DISABLE') THEN
IF FK THEN
BEGIN
FOR E IN R
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF PK THEN
BEGIN
FOR E IN R
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
BEGIN
FOR E IN P
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF UK THEN
BEGIN
FOR E IN U
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
ELSIF UPPER(OPERATION) IN ('ENABLE') THEN
IF PK THEN
BEGIN
FOR E IN P
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF FK THEN
BEGIN
FOR E IN P
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
BEGIN
FOR E IN R
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF UK THEN
BEGIN
FOR E IN U
LOOP
ST := 'ALTER TABLE 'E.TABLE_NAME' 'OPERATION' CONSTRAINT 'E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE(ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('the first parameter of the procedure must be DROP or ENABLE or DISABLE');
END IF;
END;
/

so for import,we want to disable the constraints of the user we disired to import,
create the procedure under this user,suppose it's SCOTT
usage:
MANAGE_USER_FK_PK_UK('DISABLE','TRUE','TRUE','TRUE');
Doing the import operation
MANAGE_USER_FK_PK_UK('ENABLE','TRUE','TRUE','TRUE');

This will prevent getting errors related to constraints during import

Happy coding

Passing parameters to SQL PLUS from Oracle Forms

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