Icons Buttons in 11g Fusion Middleware.

1. Start the following Services
     Start Weblogic Admin Server
     Start Weblogic Server WLS_FORMS

2. copy the icon folder in following path
    D:\Oracle\Middleware\DeveloperSuite\forms\java

3. Switch to command Prompt

   cd D:\Oracle\Middleware\DeveloperSuite\forms\java\icons
   now apply the following command
   D:\Oracle\Middleware\DeveloperSuite\forms\java\icons>D:\Oracle\Middleware\DeveloperSuite\jdk\bin\jar -cvf icons.jar *.gif


4.copy the icons.jar file into D:\Oracle\Middleware\DeveloperSuite\forms\java


5.Open the browser and write the following address in address bar
  http://CC-PC:7003/em   where cc-pc is your pc name
  now enter username and password with weblogic username and password
   Open the Forms node you can see in blue words form (select this radio button), and click on web configuration option
   now you are in web configuration screen, select  'ALL' option in Section:Default and change the following parameter
  and press apply button
    imageBase=codeBase
    archive_jini=frmall.jar,icons.jar

6.Press back button on browser, click on Enviornment Configuration  and apply the following changes
  at the end of CLASSPATH parameter add the following value
  ;D:\Oracle\Middleware\DeveloperSuite\forms\java\icons.jar 
  and press apply button


7.Now Move to windows explorer, open the following path and folder, open the registery.dat file in wordpad, the path is
  D:\Oracle\Middleware\user_projects\domains\FormsClassicDomain\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_11.1.2\config\forms\registry\oracle\forms\registry
  you see the registery.dat file, first take the backup and apply the following parameters
  default.icons.iconpath=icons/
  default.icons.iconextension=gif

8. a block message appear from java, press do not block

9. now run your forms

The path and drive i used to this practical its created on my PC, but you must change the path and drive where you install the oracle forms.

Compound Triggers

 /*
 Oracle 11g offers a new twist on triggers, the compound trigger, a trigger that can act both before and after an update, insert or delete has occurred. This makes possible the ability in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.
 At least two of the sections must be included (including only one of the four would result in a traditional trigger) and it does not matter which two of the sections are used. For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be 'matched' (BEFORE, BEFORE EACH ROW, for instance). Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. The general syntax is:


 create or replace trigger <trigger name>
 for <insert|update|delete> <of column_name> on <tablename>
 COMPOUND TRIGGER
 <declare section>
 BEFORE
 <before section>
 BEFORE EACH ROW
 <before each row section>
 AFTER EACH ROW
 <after each row section>
 AFTER
 <after section>
 END;
 Since compound triggers are relatively new and many may not have had the opportunity to write or use them I have provided a working example. Setting the stage for this trigger, HR has set a restriction on the size of a raise to be given; based on the department the raise cannot exceed 12 percent of the department average salary. A compound trigger can be used to process the raise amounts assigned. Such a compound trigger is shown below, along with several ways of executing the raises
 */
 create or replace trigger check_raise_on_avg
 for update of sal on emp
 COMPOUND TRIGGER
 Twelve_Percent        constant number:=0.12;
 -- Declare collection type and variable:
 TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
 INDEX BY VARCHAR2(80);
 Department_Avg_Salaries     Department_Salaries_t;
 TYPE Sal_t IS TABLE OF Emp.Sal%TYPE;
 Avg_Salaries         Sal_t;
 TYPE Deptno_t IS TABLE OF Emp.Deptno%TYPE;
 Department_IDs              Deptno_t;
 BEFORE STATEMENT IS
 BEGIN
  SELECT AVG(e.Sal), NVL(e.Deptno, -1)
  BULK COLLECT INTO  Avg_Salaries, Department_IDs
  FROM Emp e
  GROUP BY e.Deptno;
  FOR j IN 1..Department_IDs.COUNT() LOOP
   Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
  END LOOP;
 END BEFORE STATEMENT;
  
 AFTER EACH ROW IS
 BEGIN
  IF :NEW.Sal - :Old.Sal >
     Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno) THEN
      Raise_Application_Error(-20000, 'Raise too large');
  END IF;
 END AFTER EACH ROW;
END Check_Raise_On_Avg;

select empno, sal from emp;

update emp set sal=sal*1.10 where empno = 7369;

select empno, sal from emp;

ROLLBACK;


select empno, sal,deptno from emp where deptno=20;

update emp set sal=sal*1.08 where deptno = 20;


select empno, sal,deptno from emp where deptno=20;

rollback;


-- Does Trigger Raise Exception
-- Now Watch
select empno, sal,deptno from emp where deptno=30;


update emp set sal=sal*1.10 where deptno = 30;

System Development Life Cycle

A comprehensive process beginning with the systems definition of a problem and including all the subsequent development and implementation activities through final review and evaluation of the new system.

1.    Problem Definition

In the first step of the SDLC, the analyst is concerned with identifying problems. An English proverb “ A well defined problem is half solved” goes well in the favor of this step. So problem must be well defined either by the end user, management, or by the system analyst.

2.    Data Collection and Analysis

There are major tools and techniques for data collection and analysis. An important rule must be remembered while collecting data; information must be acquired accurately, methodically, under the right conditions, and with minimum interruption to user personnel.

3.    Analysis of System Alternatives

In many cases it is possible that the system analyst may have more than one alternative solutions of a problem or situation. In such cases he should analyze each and every one of these alternatives to find out the most suitable solution. This selection may itself determine that which solution is more describable.

4.    Determination of System Feasibility

It means that what are the possibilities that the system or the alternative, which is chosen, will work as effectively and efficiently as desired. Two most important things should be kept in mind while determining feasibility; Cost and Benefits. For this purpose a CBA (Cost and Benefit Analysis) is also often conducted.

5.    Development of System Proposal

In this step the system analyst works on the proposal selected in the previous stage. This development process is very important and should be carried out very carefully and with attention on the minute details.

6.    Pilot or Prototype System Development

Pilot or prototype system development means that when we start working on project and the project is too big so that we couldn’t implement the whole system at once because before implementing system we had to first check whether the system is going to work efficiently and effectively with its all parts or not for this purpose it is often found convenient to implement a pilot or prototype system, which is quite similar to the main system or we implement the system in modules of parts as convenient

7.    System Design

Now finally the designing of system takes place. This is perhaps a very important phase in the system life cycle. In this step the system analyst with the help of other persons such as programmers, managers and other persons design the system.

8.    Program Development

Now the actual work starts on the system with all the ideas that had been discussed in the preceding steps. In this step programming is done on different phases of the system with the help of programmers and managers.

9.    System Implementation

In the system implementation step the designed system is implemented with real data and facts. There are three ways in which system may be implemented depending on situation or requirements, they are as follows:

I)          Direct implementation

II)         Parallel implementation

III)        Partial implementation

10.    System Review and Evaluation

In most cases it is found that the designed system after implementation often needs some adjustments in the later stage. Therefore, it is important to plan for a continuing support service.

Life Cycle Of A Relational Database

THE ESSENCE OF THE 12 SPECIFIC RULES ARE AS FOLLOWS:12 RULES

1.   Representation of information. All information in a relational database is represented explicitly at the logical level and in exactly one way- by values in tables.

2.   Guaranteed logical accessibility. Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of the table name, primary key value, and column name.

3.   Systematic representation of mission information. Null values (distinct form the empty character string or a string of blank characters and distinct from zero or any other number) are supported in a fully relational DBMS for representing missing information and inapplicable information in a systematic way independent of data type.

4.   Dynamic online catalog. The database description is represented at the local level in the same way as ordinary data, so that authorized users can query it in the same relational language that they use in working with the regular data.

5.   Comprehensive data sublanguage. A relational system may support several languages and various modes of terminal use. For example the fill-in-the-blanks mode. However, there must be at least one language whose statements are expressible, in some well-defined syntax, as character strings. Also, it must be comprehensive in supporting all of the following items.

·    Data definition

·    View definition

·    Data manipulation (interactive and by program)

·    Integrity constraints

·    Authorization

·    Transaction boundaries (begin, commit, and rollback)

6.   Updateable views. All views that are theoretically updateable are also updateable by the database system.

7.   High-level insert, update, and delete. The capability of handling a base relation or a derived relation as a single operand applies not only to retrieving data but also to inserting, updating, and deleting of data.

8.   Physical data independence. Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.

9.   Logical data independence. Application programs and terminal activities remain logically unimpaired when information’s preserving appropriate changes for any kind are made to the base tables.

10. Integrity independence. Integrity constraints specific to a particular database must be definable in the relational data sublanguage and storable in the catalog. Not in the application program.

11. Distribution independence. Whether or not a system supports database distribution. It must have a data sublanguage that can support distributed databases without impairing the application programs or terminal activities.

12. No subversion. If a relational system has a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher level relation language (multiple-records-at-a-time).

Database Link Practical

DATABASE LINK PRACTICAL

STEP-1

      FIND  MACHINE NAME  &  DATABASE NAME

      OF  SECOND MACHINE (SERVER-2).

STEP-2

       CREATE A SERVICE ON FIRST MACHINE (SERVER-1)

       USING VALUES OF SECOND MACHINE (SERVER-2).

 STEP-3.

        CREATE DATABASE LINK  ON FIRST MACHINE (SERVER-1)

        USING ABOVE CREATED SERVICE.

 STEP-4.

          WRITE QUERIES USING DATABASE LINK TO RETRIEVE DATA

           FROM DATABASE ON SECOND MACHINE (SERVER-2)

          WHILE STAY CONNECTED TO FIRST MACHINE (SERVER-1)

• Create One Network Service Using Net Manager Tool.
• Press Start  Button
• Select ProgramsàOracle-OraHome90àConfiguration And Migration Toolsà Net Manager
•  Click On Naming Method Configuration And Press Next Button.
• Expand The Node Of Local And Click On Profile Option.

• >

  In Available Methods If You Found TNSNAMES Then Select It And Press

            Button.

• Press FileàSave Network Configuration.
• Select ProgramsàOracle-OraHome90àConfiguration And Migration Toolsà Net Manager
• Expand The Node Of Local, Then Again Expand The Node Of Service Naming.
• Click On Any Created Service Name Then Press Create Button (+)
• On Welcome Screen Write The Service Name On Net Service Name Option

Net Service Name: ABCL And Press Next Button.

• Select TCP/IP(Internet Protocol) And Press Next Button.
• Write Down The Host Name (Pc Name You Want to Connect) And Press Next Button.
• Before Writing The Service Check The Oracle Sid On Computer You Want To Connect By Following Query
• CONNECT SYS/ORACLE  AS SYSDBA
• SELECT INSTANCE_NAME FROM V$INSTANCE;
• Now Write Down The Name  Of Service on (Oracle 8i or later) Service Name :ORCL  And Press Next Button.
• Press Test Button
• If Message Appear Successful Then Press Close Button.
• Press Finish Button To Continue.
• You Can See The Service Name On Your Screen.
• Press FileàSave Network Configuration.
• Press Exit  Button.

 

•  

o   Simple Database Link…

§  Connect System/Manager.

§  Create Database Link DB_LINK Connect To Scott Identified By Tiger

Using ‘ABCL’           à Service Name Created In Net Manager.

 

§  SELECT * FROM  SCOTT.EMP @ DB_LINK;

o   Public Database Link…

§  Connect System/Manager

§  Create Public Database Link PUBLIC_LINK Using ‘ABCL’;

§  SELECT * FROM SCOTT.EMP@PUBLIC_LINK

§  SELECT * FROM HR.EMPLOYEES@PUBLIC_LINK

•  

DBMS_JOB EXAMPLE

Following are the example of DBMS_JOB, you can used this proceudre in DBMS_SCHEDULER also
with little bit change

STEP 1
CONN SYS/ORACLE AS SYSDBA
GRANT EXECUTE ON DBMS_DDL TO SCOTT;

STEP 2
CONN SCOTT/TIGER

CREATE OR REPLACE PROCEDURE ANALYZE_OBJECTS IS
 CURSOR C1 IS SELECT TABLE_NAME, USER OWNER FROM USER_TABLES;
 CNTR NUMBER:=0;
BEGIN
 FOR I IN C1 LOOP
  DBMS_DDL.ANALYZE_OBJECT('TABLE',I.OWNER,I.TABLE_NAME,'COMPUTE');
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('ALL TABLES ANALYZED..');
EXCEPTION
 WHEN OTHERS THEN
 &D(SQLCODE||CHR(10)||&ERRM);
END;

ALTER PROCEDURE ANALYZE_OBJECTS COMPILE;

STEP 3

VAR JOB_ID NUMBER;

EXECUTE DBMS_JOB.SUBMIT(:JOB_ID,'ANALYZE_OBJECTS;',SYSDATE, 'SYSDATE+1/1440');

ABOVE PROCEDURE REPEAT AFTER ONE MINUTE

SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES;

Using UTL_FILE

-- UTL FILE
--STEP 1
 CONN SYS/ORACLE AS SYSDBA

--STEP 2
 -- MAKE A FOLDER AT ANY DRIVE, WE CREATE FOLDER ON DRIVE D NAMELY D:\B67_M
 -- MOVE TO FOLDER C:\app\admin\product\11.2.0\dbhome_1\database
 -- MAKE A BACKUP FOLDER AND COPY SPFILE.ORA IN BACKUP FOLDER

--STEP 3
 SHOW PARAMETER SPFILE
 SHOW PARAMETER UTL_FILE_DIR

--STEP 4
SQL> CREATE PFILE FROM SPFILE;

--STEP 5
 -- MOVE TO FOLDER  C:\APP\ADMIN\PRODUCT\11.2.0\DB HOME_1\DATABASE, MODIFY THE INITORCL.ORA FILE,
 -- ADD THE PARAMETER UTL_FILE_DIR = D:\B67_M
 -- AFTER ADDING THE PARAMETER RESTART THE DATABASE WITH FOLLOWING COMMAND

STARTUP FORCE PFILE=C:\app\admin\product\11.2.0\dbhome_1\database\initorcl.ora
show parameter utl_file_dir

--STEP 6
 -- CREATE SPFILE FROM PFILE
CREATE SPFILE FROM PFILE;

STARTUP FORCE;

-- SETP 7 CREATING DIRECTOY AND GIVE THE GRANT TO THE USER FOR ACCESS
CREATE DIRECTORY B67M AS 'D:\B67_M';
GRANT READ,WRITE ON DIRECTORY B67M TO SCOTT;

-- SETP 8  CREATING PROCEDURE OR FUNCTION TO UTLIZE DIRECTORY
conn scott/tiger

desc utl_file

CREATE OR REPLACE PROCEDURE WRITE_TO_FILE(DIR_NAME IN VARCHAR2 DEFAULT 'B67M',
   FILE_NAME IN VARCHAR2 DEFAULT 'EINFO.TXT') IS
   F1 UTL_FILE.FILE_TYPE;
   PRESENT BOOLEAN; FLENGTH NUMBER; BSIZE PLS_INTEGER;
   CURSOR C1 IS SELECT * FROM EMP ; CNTR NUMBER:=0;
BEGIN
  -- Checking the file exist in folder or not
  UTL_FILE.FGETATTR(LOCATION=>DIR_NAME,FILENAME=>FILE_NAME,
                    FEXISTS=>PRESENT,FILE_LENGTH=>FLENGTH,
                    BLOCK_SIZE=>BSIZE);
 -- Making Decision Open the file Write mode or Append mode
 IF PRESENT THEN
   F1:=UTL_FILE.FOPEN(DIR_NAME,FILE_NAME,'a');
   UTL_FILE.PUT_LINE(F1,RPAD('*',LENGTH(CURRENT_TIMESTAMP),'*'));
 ELSE
   F1:=UTL_FILE.FOPEN(DIR_NAME,FILE_NAME,'w');
   UTL_FILE.PUT_LINE(F1,RPAD('-',LENGTH(CURRENT_TIMESTAMP),'-'));
 END IF;
 FOR I IN C1 LOOP
  CNTR:=CNTR+1;
  UTL_FILE.PUT_LINE(F1,RPAD(CNTR,10,' ')||RPAD(I.EMPNO,10,' ')||RPAD(I.ENAME,10,' ')||RPAD(I.SAL,'10',' ')|| RPAD(I.DEPTNO,10,' '));
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('FILE CREATED...'||FILE_NAME||' AS ON'||TO_CHAR(SYSDATE,'FMDAY')||','||CURRENT_TIMESTAMP);
 UTL_FILE.FCLOSE(F1);
EXCEPTION
 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
  RAISE_APPLICATION_ERROR(-20001,' INVALID FILE MODE..');
 WHEN UTL_FILE.WRITE_ERROR THEN
  RAISE_APPLICATION_ERROR(-20010,'UNABLE TO WRITE DATA..');
 WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20420,SQLERRM);
END WRITE_TO_FILE;

DESC WRITE_TO_FILE

EXEC WRITE_TO_FILE(FILE_NAME=>'B67M_EMP.TXT');

-- FOR DISPLAYING ON SCREEN
declare
 f utl_file.file_type;
 s varchar2(2000);
begin
 dbms_output.enable(100000);
 f := utl_file.fopen(UPPER('b67M'),'B67M_EMP.TXT','r');
 loop
  utl_file.get_line(f,s);
  dbms_output.put_line(s);
 end loop;
exception
 when NO_DATA_FOUND then
  utl_file.fclose(f);
end;