Wednesday, December 7, 2011

CALLING A FORM TO ANOTHER FORM IN ORACLE D2K/FORMS/10G


CALLING FORMS AT RUN TIME (FORM TO FORM CALLING IN ORACLE D2K)
This  topic is related to how you can call another form from existing form. Very important topic in Oracle d2k.
1.       We can call forms at run times in  three ways.
2.       Call_form:- it opens the form in model window mode i.e unless and until we close the called form , we cannot go back to the calling form.
3.       Open_form:- this open the form in modeless mode i.e we can work on both calling form and called form at the same time without closing any them.
4.       New_form:- this always close the calling form (ex. On successful login , the login screen closed and the application open).

All these three methods take on mandatory parameter i.e the complete path of FMX file to be called.

General points needs to be remember.
We need to add parameter for passing the values to different form. Four thing we need to be remember always.
1.       Declare parameter.
2.       Create parameter.
3.       Add parameter.
4.       Call form/open form/new form
5.       Destroy parameter.

Example :- I am writing a code which will illustrate you how we can call a form from another form.
DECLARE
P PARAMETER;
BEGIN
P:= CREATE_PARAMETER_LIST(‘ABC’);
ADD_PARAMETER(P,’P_EMPDEPTNO’,TEXT_PARAMETER,:DEPT.DEPTNO);
CALL_FORM(‘C:\EMP.FMX’,NO_HIDE,NO_REPLACE,NO_QUERY_ONLY,P);
DESTROY_PARAMETER_LIST(P);
END;
PARAMETER PASSING BETWEEN FORMS
WE CAN PASS VALUES FROM ONE FORM TO ANOTHER FORM WITH THE HELP OF PARAMETER.
STEPS:
1.       CREATE A FORM MODULE(CALLED FORM).
2.       CREATE A BASE TABLE BLOCK.
3.       CREATE A PARAMETER IN THE FORM.
4.       IN THE PROPERTY AND PARAMETER MENTION NAME, DATATYPE.
5.       IN A WHENNEW BLOCK INSTANCE TRIGGER OF THE BLOCK MENTION THE FOLLOWING
SET_BLOCK_PROPERTY(‘EMP’,DEFAULT_WHERE,’DEPTNO:=’||:PARAMETER.P_EMP_DEPTNO);
EXECUTE_QUERY;
6.       SAVE, COMPILE AND COMPILE THE FORM.
7.       CREATE THE NEW FORM .


POPUP MENU IN ORACLE D2K,10G,FORMS


POPUP MENU IN ORACLE D2K
1.       IN THE FORM MODULE , WE CAN CREATE A POPUP MENU.
2.       RIGHT CLICK ON THE POP UP MENU AND SELECT ‘MENU EDITOR’.
3.       DESIGN YOUR MENU IN THE MENU EDITOR.\
4.       WRITE CODE,SAVE AND COMPILE.
5.       NOW IN THE PROPERTY OF ANY FORM OBJECT UNDER THE PROPERTY ‘POP UP MENU’, SELECT OUR POPUP MENU.
6.       RUN YOUR FORM MODULE, RIGHT CLICK AT THE REQUIRED OBJECT AND SEE THE POP UP MENU.
NOTE:-
1.       TO MAKE THE MENU ITEM VISIBLE ON HORIZONTAL/VERTICAL TOOLBAR IN THE PROPERTY , MENU ITEM MENTION VISIBLE ON HORIZONTAL/VERTICAL TOOLBAR(YES/NO).

MENU IN ORACLE D2K/DEVELOPER 2000/ORACLE 10 G


MENU IN ORACLE D2K.
1.       IT IS ANOTHER IMPORTANT MODULE OF FORM BUILDER WHICH IS USED TO DESIGN MENU AND THE MENU CAN BE ATTACHED TO THE FORM MODULE.
2.       EXTEN IS MMB( ON GENRATION WE GET MMX AND ON CONVERT WE GET MMT).

STEP TO CREATE MENU IN ORACLE D2K.
1.CREATE A MENU MODULE.
2. UNDER THE MENU MOUDLE , CREATE A MENU.
3. RIGHT CLICK ON THE ‘MENU’ AND SELECT ‘MENU’ EDITOR.
4. DESIGN YOUR MENU IN THE MENU EDITOR USING THE DOWN AND RIGHT ARRAY KEY.
5. IN THE PROPERTY OF MENU ITEMS MENTION  NAME,LABEL, MENU ITEM TYPE(PLANE/CHECK/RADIO/MAGIC/SEPRATOR),COMMAND TYPE ,PL/SQL OR NULL,MENU ITEM CODE
6.NOW  SAVE,COMPILE , AND GENRATE THE MENU MODULE.
7. NOW, IN THE PROPERTY OF FORM MODULE ( WHEN EVER WE WANT TO ATTACHED THE MENU MODULE), USE A PROPERTY CALLED AS MENU MODULE. ( HERE WE NEED TO MENTION THE COMPLETE PATH OF MMX FILE).
8.SAVE,  COMPILE AND RUN OUR FORM MODULE.

NOTE:- 1.  FOR RADIO MENU ITEMS, MENTION
MENU ITEM TYPE : RADIO,
 MENU ITEM RADIO GROUP :-  MENTION ANY VALUE BUT IT HAS TO BE SAME FOR ALL THE RADIO MENU ITEM OF THE GROUP)
2. FOR CHECK MENU ITEM MENTION ITEM TYPE : CHECK.
3.  FOR MAGIC MENU ITEM , ITEM TYPE SHOULD BE :MAGIC
    MAGIC MENU ITEM :- ( SELECT THE MATIC MENU ITEM LIKE CUT,COPY,PASTE ETC AS PER OUR REQ.)

Wednesday, May 11, 2011

ORACLE SQL


SQL
1.       THIS WAS DEVELOPED BY IBM AND IT IS BASED ON C LANGUAGE.
2.       ‘SQL’ STANDS FOR STRUCTURE QUERY LANGUAGE AND IT IS USED BY ALL RDBMS IN THE WORLD LIKE ORACLE,SYSBASE,SQL.SERVER, ACCESS.
3.       PARTS OF SQL ARE.
A.      DDL :- DATA DEFINATION LANGUAGE(CREATE,ALTER,DROP).
B.      DML:- DATA MANUPILATION LANGUAGE. (INSERT,UPDATE,DELETE,SELECT)
C.      DCL:- DATA CONTROL LANGAUGE(GRANT,REVOKE).
D.      TCL:- TRANSCTION CONTROL LANGUAGE.

\* ALL  EXAM IN THIS TUTORIAL ARE REFRENCED TO SCOTT  USER
TABLE NAME IS EMP, DEPT.
*/

SELECT STATEMENT.

1.       THIS IS USED TO GET THE RECORDS FROM TABLE ON THE BASIS OF SOME ONDITION.
SYNTAX:-
SELECT ,,….
FROM

WHERE
GROUP BY

HAVING
ORDER BY


EXAMAPLE  BASED ON EMP AND DEPT TABLE.

DISPLAY DETAILS OF EMPLOYEE WORKING IN DEPTNO 10 OR 20 AND HAVING SAL>200
ANS. SELECT * FROM EMP
           WHERE DEPTNO =10 OR DEPTNO 20 AND SAL>200.
2.       TO GET ALL COLUM FROM ANY TABLE,WE NEED TO USE THE SYMBOL ‘*’.
3.       IF WE DO NOT USE ANY ‘WHERE’ CONDITION THEN ALL THE RECORDS FROM THE TABLE WILL BE SELECTED.
4.       WE CAN USE OPERATOR FOR FILTRATION.
5.       SOME OF THE OPERATORS IN SQL ARE
A.      AIRTHMETIC OPERATOR(+,-,*,/)
B.      LOGICAL OPERATOR(AND,OR,NOT)
C.      RELATIONAL  OPERATOR (<,>,<=,>=)
D.      SPECIAL OPERATOR (LIKE,IN,BETWEEN)


LIKE
THIS OPERATOR SEARCHES ON THE BASIS OF WILD CARD CHARS I.E
UNDERSCORE(_) :- CAN REPLACE ONLY ONE CHARATER.
PERCENTAGE(%) :- CAN REPLACE MORE THAN ONE CHARATER.

..TO BE CONTINUED...

Thursday, December 2, 2010

Triggle in Oracle 10g

Trigger in Pl-sql/oracle/Oracle 10g forms and reports

1.    it is a db object which is a collection of pl/sql codes and it automatically get executed whenever any dml event happens provided trigger has been written for that DML event.
2.    Trigger has three section i.e. events, restrictions and when condition (optional.
3.    Pl sql trigger is divided into two parts. 1. Row level 2. Statement level.


Row Level Statement Trigger.
1.    Row level trigger is executed for each and every row affected by DML stmt.
2.    we need to use for each row for row level trigger.
3.    used in those cases where we need to depend on the values of every record of any column of any table.
4.    eg. Any name begin inserted must be in letter capital.

Statement Level Trigger.

1.    Statement level trigger is executed only once irrespective of No. of records affected by the dml statement.
2.    Every trigger is by default STMT level trigger.
3.    Used for restrictions like No Deletion allowed on Sunday on emp table.

Syntax.

CREATE [ OR REPLACE]
TRIGGER

BEFORE | AFTER 
INSERT  [ OR DELETE]
OF (OPTIONAL) 
ON FOR EACH ROW (OPTIONAL) REFRENCING NEW AS  OLD AS WHEN () (OPTIONAL) Note. 1.    to follow any value of any col. Of any record of any table within the execution section of a trigger always used :old.and :new.2.    Theses two can be followed only with in a row level trigger. :new. Refers to new values or that col for that row (Applicable for updated and insert) :old.Refers to old values for that col for that record (Applicable for both delete and update) Example:- Create or replace  Trigger tr_ex Before delete On  insert or update On emp For each row Declare  V_msg varchar2(200) Begin If inserting then  V_msg := ‘inserting….’ Elsif deleting then  V_msg := ‘deleting………….’; Elsif updating then V_msg := ‘deleting…’; End if; Dbms_output.put_line(V_msg); End; Q.    Create a trigger which will automatic make the first letter of  every name inserted | updated into capital letter irrespective of any case enter by user. Create or replace trigger Tr_name Before update or insert Of ename On emp For each row Begin Dbms_output.put_line(‘before change name = ‘ ||.new.ename); End; / Example of Statement level trigger. CREATE OR REPLACE TRIGGER TR_DELETE BEFORE DELTE ON EMP BEGIN IF TRIM(TO_CHAR(SYSDATE,’DAY’)) = ‘SUNDAY’ THEN RAISE _APPLICATION_ERROR  ( -20001,’NO DELTETION ALLOW ON SUNDAY); END IF; END; INSTEAD OF TRIGGER. This is used to perform DML operation on base table through join view. Create or replace trigger tr_instead  Instead of  Insert or update or delete On v_join Referencing new as N old as O For each row Declare V_count number; Begin  If inserting then Select count(*) into v_count from dept Where deptno = :n.deptno; If v_count = 0 then  Insert into dept values(:n.deptno,:n.dname,:n.loc); End if; Insert into emp (empno,ename,sal) values (:n.empno,:n.ename,:n.sal); Elsif updating then Update emp Set sal = :n.sal where empno = :o.empno; Update dept Set dname = :n.dname where deptno = :o.deptno; Elsif deleting then  Delete from emp where empno = :o.empno; Delete from dept where deptno = :o.deptno; End if; End; / 

Friday, October 22, 2010

COde for Report calling from Oracle d2k

declare
p paramlist;
appid pls_integer;
curtimer timer;
begin
p := create_parameter_list('abc');
add_parameter(p,'dno',text_parameter,:block2.deptno);
add_parameter(p,'DESTYPE',TEXT_PARAMETER,'file');
ADD_PARAMETER(P,'DESNAME',TEXT_PARAMETER,'c:\'||:block2.deptno||'.PDF');
ADD_PARAMETER(P,'DESFORMAT',TEXT_PARAMETER,'pdf');
ADD_PARAMETER(P,'PARAMFORM',TEXT_PARAMETER,'NO');

run_product(REPORTS,'c:\6i\emp.rdf',asynchronous,runtime,filesystem,p);
-- host('cmd /C START C:\'||:block2.deptno||'.pdf',NO_SCREEN );
host('cmd /C START iexplore C:\'||:block2.deptno||'.pdf',NO_SCREEN);


/*host('C:\Program Files\Internet Explorer\iexplore.exe');
appid := dde.app_begin(' C:\ankur2.pdf',DDE.APP_MODE_MINIMIZED); */


destroy_parameter_list(p);


end;

Thursday, October 21, 2010

Diffrence between two date, Pl-sql program result in Years - Month-format

Helo frnds

I am uploading this code.
work in pl-sql
DECLARE
DATE1 DATE;
DATE2 DATE;
V_NO NUMBER;
V_YEAR NUMBER;
V_MONTH NUMBER;
BEGIN
DATE1 := TO_DATE('&DATE1');
DATE2 := TO_DATE('&DATE2');
V_NO := DATE1 - DATE2;
IF (v_NO<0) THEN v_NO := V_NO * (-1);
END IF;
V_YEAR := TRUNC(V_NO/365);
V_NO := MOD(V_NO , 365);
V_MONTH := TRUNC(v_NO/30);
V_NO := MOD(V_NO,30);
DBMS_OUTPUT.PUT_LINE(V_YEAR ||' YEARS ' || V_MONTH ||' MONTH ' || V_NO || ' DAYS');
END;
/