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
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
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
Post built is restricted with post-change:
Use the below procedure (PR_POST_TIMER) instead of post to work-around the Illegal restricted procedure error message
POST-CHANGE:
BEGIN
PR_POST_TIMER
END;
PROCEDURE PR_POST_TIMER IS
T_POST TIMER;
BEGIN
T_POST := CREATE_TIMER('T_POST',1);
END;
In the WHEN-TIMER-EXPIRED trigger write the following:
DECLARE T_TIMER TIMER;
BEGIN
T_TIMER := FIND_TIMER('T_POST');
IF NOT ID_NULL(T_TIMER) THEN
/* stop timer firing again */
delete_timer('T_POST');
POST;
END IF;
END;
NB: The WHEN-TIMER-EXPIRED trigger should be at the FORM level
You can use the same work-around to call most of the illegal restricted procedure error message,for example you can now call go_block in WHEN-VALIDATE-ITEM
Happy coding
This is a generic function usefull for retriving the parent in a self relationship:
CREATE OR REPLACE FUNCTION GET_ROOT (P_TABLE_NAME VARCHAR2,P_CHILD_COLUMN VARCHAR2,P_PARENT_COLUMN VARCHAR2,P_ID NUMBER) RETURN NUMBER IS
V_P_ID NUMBER := 0;
V_ID NUMBER := 0;
V_PARENT_ID NUMBER;
V_SQL VARCHAR2(3000);
BEGIN
BEGIN
V_SQL := 'SELECT 'P_PARENT_COLUMN
' FROM 'P_TABLE_NAME
' WHERE 'P_CHILD_COLUMN' = 'P_ID;
EXECUTE IMMEDIATE V_SQL
INTO V_P_ID;
V_SQL := NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_P_ID := -1;
WHEN OTHERS THEN
V_P_ID := -1;
END;
IF V_P_ID <> - 1 THEN
WHILE V_P_ID IS NOT NULL LOOP
BEGIN
V_SQL := 'SELECT 'P_CHILD_COLUMN','P_PARENT_COLUMN
' FROM 'P_TABLE_NAME
' WHERE 'P_CHILD_COLUMN' = ' V_P_ID;
EXECUTE IMMEDIATE V_SQL
INTO V_ID,V_P_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
END LOOP;
V_PARENT_ID := V_ID;
ELSE
V_PARENT_ID := P_ID;
END IF;
IF P_ID = V_PARENT_ID THEN
RETURN NULL;
ELSE
RETURN (V_PARENT_ID);
END IF;
END;
/
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
Let's try this function SCOTT.EMP Table:
SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/80 800 20
7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30
7521 WARD SALESMAN 7698 22/02/81 1250 500 30
7566 JONES MANAGER 7839 02/04/81 2975 20
7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/81 2850 30
7782 CLARK MANAGER 7839 09/06/81 2450 10
7788 SCOTT ANALYST 7566 19/04/87 3000 20
7839 KING PRESIDENT 17/11/81 5000 10
7844 TURNER SALESMAN 7698 08/09/81 1500 0 30
7876 ADAMS CLERK 7788 23/05/87 1100 20
7900 JAMES CLERK 7698 03/12/81 950 30
7902 FORD ANALYST 7566 03/12/81 3000 20
7934 MILLER CLERK 7782 23/01/82 1300 10
14 rows selected.
Now let's try to get the root parent of the EMPNO = 7369
select GET_ROOT ('EMP' ,'EMPNO','MGR',7369) AS GET_ROOT_PARENT
from Dual;
GET_ROOT_PARENT
---------------
7839
select GET_ROOT ('EMP' ,'EMPNO','MGR',7839) AS GET_ROOT_PARENT
from Dual;
GET_ROOT_PARENT
---------------
As you can see the MGR of EMPNO = 7839 was NULL in the SELECT * from EMP.
There is a useful function in SQL Server called TOP N With Ties. It is like TOP N (which is like the Oracle ROWNUM function) but if you are ordering by a non unique column, it retrieves all common rows associated with the last row in the set.
The code below will achieve this functionality,this function return a date if you want to use it with other type for example,just change the return of the function from date to number:
CREATE OR REPLACE FUNCTION TOP_DATE(P_TABLE_NAME VARCHAR2,P_COLUMN_NAME VARCHAR2,P_RANGE NUMBER ,P_DISTINCT VARCHAR2 DEFAULT 'DISTINCT',P_ORDER_BY VARCHAR2 DEFAULT 'DESC',P_WHERE VARCHAR2 DEFAULT NULL) RETURN DATE IS
V_DATE DATE;
V_DISTINCT VARCHAR2(10);
V_SQL VARCHAR2(3000);
V_WHERE VARCHAR2(1000);
BEGIN
IF P_RANGE <= 0 THEN V_DATE := NULL; DBMS_OUTPUT.PUT_LINE('Range must be greater than 0'); ELSIF NVL(LTRIM(RTRIM(UPPER(P_DISTINCT))),'DISTINCT') <> 'DISTINCT' THEN
V_DATE := NULL;
DBMS_OUTPUT.PUT_LINE('P_DISTINCT must be null or = DISTINCT');
ELSIF LTRIM(RTRIM(UPPER(P_ORDER_BY))) NOT IN ('ASC','DESC') THEN
V_DATE := NULL;
DBMS_OUTPUT.PUT_LINE('P_ORDER_BY must be ASC or DESC');
ELSE
BEGIN
IF P_DISTINCT IS NULL OR P_DISTINCT = '' OR P_DISTINCT = ' ' THEN
V_DISTINCT := ' ';
ELSE
V_DISTINCT := 'DISTINCT';
END IF;
IF P_WHERE IS NULL THEN
V_WHERE := ' ';
ELSE
V_WHERE := P_WHERE;
END IF;
V_SQL := 'select 'P_COLUMN_NAME' from (select rownum range,'P_COLUMN_NAME
' from (select 'V_DISTINCT' 'P_COLUMN_NAME' from 'P_TABLE_NAME' 'V_WHERE
' order by 'P_COLUMN_NAME' 'P_ORDER_BY'))'
' where range = 'P_RANGE;
EXECUTE IMMEDIATE V_SQL
INTO V_DATE;
EXCEPTION WHEN OTHERS THEN V_DATE := NULL;
END;
END IF;
RETURN V_DATE;
END;
/
