Oracle Apps Technical

Oracle Apps Technical Blog

eBIZ Technics. Powered by Blogger.

Wednesday, April 29, 2015

Oracle SQL Loader

 SIMPLE CONTROL FILE USING SINGLE DATA FILE

1.create table in scott and give grant to apps then create synonym in app

 create table emp3(empno number(10),ename varchar2(10),job varchar2(10),sal number(10,2))

SQL> /

Table created.

SQL> grant all on emp3 to apps;

Grant succeeded.

SQL> conn apps/apps@prod
Connected.
SQL> create public synonym emp3 for scott.emp3;

Synonym created.

SQL> desc emp3;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                          NUMBER(10)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(10)
 SAL                                                            NUMBER(10,2)

2.data file

 3.control file
4.RUN CMD THEN GIVE COMMAND AS
D:\ORACLE\PRODDB\8.1.7\BIN> SQLLDR APPS/APPS@PROD
CONTROL =D:\DATA\EMP3.CTL

5.GOTO SQL PROMPT THEN VIEW THE OUT PUT:

SQL> SELECT * FROM EMP3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       100 'smith'    'mgr'            7000
       200 'ramu'     'clerk'          4000
       300 'raju'     'act'            6000

SQL>

          Multiple Data Files Into Single Database  table using single control file

1.Create second  data file:
         111,'RAVI','MGR',7000
         222,'KIRAN','CLERK',4000
         333,'CHANDRA','ASTMGR',10000

save this as EMP4.DAT  in same directory


2.Goto control file then assign this file as second infile to control file
   
 controlfile:


load data
infile 'd:\data\emp3.dat'
infile 'd:\data\EMP4.dat'
insert into table emp3
fields terminated by ','
(empno
,ename
,job
,sal
)

3.Again run cmd  as above

4.goto backend view data

   SQL> select * from emp3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       111 'RAVI'     'MGR'                    7000
       222 'KIRAN'    'CLERK'                4000
       333 'CHANDRA'  'ASTMGR'         10000
       100 'smith'    'mgr'                      7000
       200 'ramu'     'clerk'                    4000
       300 'raju'     'act'                         6000

6 rows selected.
Control file using sql functions

1) Assign the sql functions to the columns in Control file

load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'  
insert
into table emp3
(
empno   "S1.NEXTVAL",
ename    POSITION(4:7)  "UPPER(:ENAME)",
job       POSITION(8:10)  "INITCAP(:JOB)",
sal        POSITION(11:14) "NVL(:SAL*10,0)"
)
into table emp4 when projno!=' '
(  
empno   "S1.NEXTVAL",
ename    POSITION(4:7)  "UPPER(:ENAME)",
job        POSITION(8:10)   "INITCAP(:JOB)",
sal        POSITION(11:14)  "NVL(:SAL*10,0)",
projno   POSITION(15:17)
)
2
AGAIN RUN CMD:

2. THEN VIEW THE DATA  IN BACK END AS:

SQL> SELECT * FROM EMP3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       192 RAVI       Mgr             70000
       193 RAJU       Mgr             40000
       194 CHAN       Mgr             10000
       186 RAVI       Mgr             70000
       187 RAJU       Mgr             40000
       188 CHAN       Mgr             10000

6 rows selected.

SQL> SELECT * FROM EMP4;

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       195 RAVI       Mgr             70000        120
       196 RAJU       Mgr             40000        121
       197 CHAN       Mgr             10000        122
       189 RAVI       Mgr             70000        120
       190 RAJU       Mgr             40000        121
       191 CHAN       Mgr             10000        122

6 rows selected.
Using where clause in control file to update data into multiple tables
Multiple data files and Multiple tables


1.Create 2 tables and 2 data files
   first is emp3 which is already created
   Now created second table
     CREATE TABLE EMP4 AS SELECT * FROM  EMP3 WHERE 1=2
 
        1.1ALTER TABLE EMP4 ADD PROJNO NUMBER;                          /*for additing  additional column for secondtable */

        SQL> DESC EMP4;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------------
 EMPNO                                                          NUMBER(10)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(10)
 SAL                                                            NUMBER(10,2)
 PROJNO                                                         NUMBER


SQL> GRANT ALL ON EMP4 TO APPS;

Grant succeeded.

SQL> CONN APPS/APPS@PROD
Connected.
SQL> CREATE PUBLIC SYNONYM EMP4 FOR SCOTT.EMP4;

Synonym created.

2.create datafile as emp3

111RAVIMGR7000120
222RAJUMGR4000121
333CHANMGR1000122

3.create data file as emp4

111RAVIMGR7000120
222RAJUMGR4000121
333CHANMGR1000122

4.create control file as


load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'
insert
into table emp3
(
empno  POSITION(1:3),
ename   POSITION(4:7),
job       POSITION(8:10),
sal        POSITION(11:14)
)
into table emp4 when projno!=' '
(empno  POSITION(1:3),
ename   POSITION(4:7),
job        POSITION(8:10),
sal        POSITION(11:14),
projno   POSITION(15:17)
)


save;

5.run cmd as usual


6.view the output in backend


SQL> select * from emp3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       111 RAVI       MGR              7000
       222 RAJU       MGR              4000
       333 CHAN       MGR              1000
       111 RAVI       MGR              7000
       222 RAJU       MGR              4000
       333 CHAN       MGR              1000

6 rows selected.

SQL> select * from emp4;

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       111 RAVI       MGR              7000        120
       222 RAJU       MGR              4000        121
       333 CHAN       MGR              1000        122
       111 RAVI       MGR              7000        120
       222 RAJU       MGR              4000        121
       333 CHAN       MGR              1000        122

6 rows selected.


Using commit in control files for each 10 records


1 .we will create a sequence as s1


SQL> CONN SCOTT/TIGER@PROD
Connected.
SQL> CREATE SEQUENCE S1 INCREMENT  BY 1 START WITH 100;

Sequence created.

SQL> conn apps/apps@prod
Connected.
2.then we will create control file as


load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'
insert
into table emp3
(
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job       POSITION(8:10),
sal        POSITION(11:14)
)
into table emp4 when projno!=' '
(  
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job        POSITION(8:10),
sal        POSITION(11:14),
projno   POSITION(15:17)
)
 
then save;

4.then run as usual.




5 .view the data from backend as
SQL> /

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       106 RAVI       MGR              7000
       107 RAJU       MGR              4000
       108 CHAN       MGR              1000
       100 RAVI       MGR              7000
       101 RAJU       MGR              4000
       102 CHAN       MGR              1000

6 rows selected.

SQL> ed
Wrote file afiedt.buf

  1* select * from emp4
SQL> /

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       109 RAVI       MGR              7000        120
       110 RAJU       MGR              4000        121
       111 CHAN       MGR              1000        122
       103 RAVI       MGR              7000        120
       104 RAJU       MGR              4000        121
       105 CHAN       MGR              1000        122

6 rows selected

  To Create Commit Statement in cmd Prompt

                               

GOTO RUN CMD AND GIVE COMMAND AS  TO FOR EVERY 2 RECORDS

 TO SKIP RECORDS FROM LOADING USING SKIP KEYWORDS  IN CONTROL FILE
GO TO RUN CMD:THEN GIVE THE COMMAND AS:

 Goto backend and view the data:

SQL> SELECT * FROM EMP3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       148 CHAN       MGR              1000

                                           
TO SKIP COLUMNS IN CONTROL FILES USING FILLER KEYWORD
Use the FILLER keyword in control files as:

load data
infile 'D:\DATA\emp3.dat'
infile 'D:\DATA\EMP4.dat'  
insert
into table emp3
(
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job       filler,
sal        POSITION(11:14)
)
into table emp4 when projno!=' '
(  
empno   "S1.NEXTVAL",
ename   POSITION(4:7),
job        POSITION(8:10),
sal        POSITION(11:14),
projno   POSITION(15:17)
)

Then goto run cmd give the command as usual:
Then view the data in backend :

SQL> select * from emp3;

     EMPNO ENAME      JOB               SAL
---------- ---------- ---------- ----------
       150   RAVI                     7000
       151  RAJU                                 4000
       152  CHAN                                1000
       156  RAVI                                  7000
       157  RAJU                                 4000
       158  CHAN                                1000

6 rows selected.

SQL> select * from emp4;

     EMPNO ENAME      JOB               SAL     PROJNO
---------- ---------- ---------- ---------- ----------
       153 RAVI       MGR              7000        120
       154 RAJU      MGR              4000        121
       155 CHAN      MGR              1000        122
       159 RAVI       MGR              1000        120
       160 RAJU      MGR               4000        121
       161 CHAN      MGR              1000        122

6 rows selected.

Tuesday, April 28, 2015

Oracle Joins

Type of joins
-------------
(8i and Prior)                                                   Compliant Joins
------------------------                                         -----------------
- Equijoin                                                        - Cross Joins
- Non-Equijoin                                                 - Natural Joins
- Outer Join                                                     - Using Clause
- Self Join                                                        - Full or Two sided outer joins
                                                                        - Arbitrary join conditions for outer joins

1. Use a join to query data from more than one table
           
            SELECT table1.column, table2.column
            FROM   table1
            [CROSS JOIN table2]|
            [NATURAL JOIN table2] |
            [JOIN table2 USING { column_name } ] |
            [JOIN table2 ON (table1.column_name= table2.column_name)] |
            [LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name ) ];

2. Eg Cross join:

            SELECT ename,dname
            FROM emp
            CROSS JOIN DEPT;
3. NATURAL JOIN
---------------
- NATURAL JOIN clause is based on all columns in the two tables that have the same name.
- It selects rows from the two tables that have equal values in all matched columns
- If the columns having the same names have different data types , an error is returned

In oracle 9i it is possible to let the join be completed automatically based on columns in the
two tables which have matching data types and names using the keywords NATURAL JOIN.

Eg:       SELECT deptno,dname,ename,empno
            FROM DEPT
            NATURAL JOIN EMP
                        OR
            SELECT deptno,dname,empno,ename
            FROM EMP , DEPT
            WHERE EMP.DEPTNO= DEPT.DEPTNO

Natural Joins with WHERE clause

            Additional restrictions on a natural join are implemented by using a WHERE clause. The
example

SELECT deptno,dname,ename,sal
FROM DEPT
NATURAL JOIN emp
WHERE deptno IN (20,30)

4. NATURAL JOINS WITH THE USING CLAUSE
---------------------------------------

- If several columns have the same names but the data types do not match, The NATURAL JOIN
clause can be modified with the USING clause to specify the columns that should be used for
an equijoin
- Use the USING clause to match only one column when more thatn one column matches
- Do not use table name or alias in the referenced columns
- The NATURAL JOIN and USING clauses are mutually exclusive.

- The columns referenced in the USING clause should not have a qualifier (table name or alias)
anywhere in the SQL statement

SELECT E.ENAME,D.DNAME
FROM EMP E JOIN DEPT D USING (Deptno)
WHERE Deptno=30;

error coz we can use alias or depts refered in join in either select or where clause

SELECT E.ENAME,D.DNAME
FORM EMP E JOIN DEPT D
USING (DEPTNO);
      or
SELECT ENAME,DNAME
FORM EMP , DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO

5. CREATING JOINS WITH THE ON CLAUSE
--------------------------------------
- The join condition for the natural join is basically an equi join of all columns with the same
name
- To specify arbitrary conditions or specify columns to join, the ON clause is used.
- The ON clause makes code easy to understand

Eg
            SQL> SELECT E.ENAME,E.EMPNO,D.DNAME
              2  FROM EMP E
              3  JOIN DEPT D
              4  USING (DEPTNO);
                                    or
            SQL> ed
              1  SELECT ENAME,DNAME
              2  FROM EMP
              3  JOIN DEPT
              4* USING (DEPTNO)


THREE WAY JOINS WITH THE ON CLAUSE
----------------------------------

SELECT EMPNO,ENAME,DNAME,SALGRADE
FROM EMP E
JOIN DEPT D
ON D.DEPTNO = E.DEPTNO
JOIN  DEPT1 D1
ON D1.DEPTNO=E.DEPTNO

JOINS COMPARING SQL to oracle syntax
----------------------------------------------------
ORACLE                                            SQL 1999
---------------------------------------------------
EQUI-JOIN                            NATURAL/INNER JOIN
OUTER-JOIN                                    LEFT OUTER JOIN
SELF-JOIN                            JOIN ON
NON-EQUI-JOIN                              JOIN USING
CARTESIAN PRODUCT                 CROSS JOIN
---------------------------------------------------

6. LEFT OUTER JOIN
-------------------

 SELECT  E.ENAME,E.DEPTNO,D.DNAME
 FROM EMP E
 LEFT OUTER JOIN DEPT D-> (+) will on this table
 ON (E.DEPTNO=D.DEPTNO)


                                    OR

  1  SELECT  E.ENAME,E.DEPTNO,D.DNAME
  2  FROM EMP E,DEPT D
  3* WHERE E.DEPTNO=D.DEPTNO(+)
                                    OR
  1  SELECT  E.ENAME,E.DEPTNO,D.DNAME
  2  FROM EMP E,DEPT D
  3* WHERE D.DEPTNO(+)=E.DEPTNO
           


7. RIGHT OUTER JOIN:
---------------------

   SELECT E.ENAME,E.DEPTNO,D.DNAME
   FROM EMP E->(+) will b on this table
   RIGHT OUTER JOIN DEPT D
   ON(E.DEPTNO=D.DEPTNO)
                       
                        or

  1  SELECT  E.ENAME,E.DEPTNO,D.DNAME
  2  FROM EMP E,DEPT D
  3* WHERE D.DEPTNO=E.DEPTNO(+)
SQL> /           

           
OUTER-JOIN:
-------------

-> To join n tables together, you need a minimum of n-1 join conditions. This rule may not
apply if your table has a concatenated primary key, in which case more than one column
is required to uniquely identify each row.
-> The operator is a plus sign enclosed in brackets and "it is placed on the side of the join
that is deficient in information" ie OUTER JOIN
This operator has the effect of creating one or more null rows, to which one or more row from
the nondeficient table can be joined.
Table.column = is the condition that joins the tables together
table.column(+)  is the outer join symbol, which can be placed on either side of the where
clause condition but not on both sides. place the outer join symbol follwing the name of the
column in the table without the matching rows.

- the outer join operator can appear on only one side of the expression the side that has
information missing. It returns those rows from one table that have no direct match in the
other table.
- A condition involving an outer join cannot use the IN operator or b linked to another condition
by the  OR operator.

Eg: SELECT E.ENAME,E.EMPNO,E.MGR,M.EMPNO,M.ENAME
FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO(+)

--The above query gives all the employee details whose empno = mgrno which implies they are
--managers and we want the details of all employee (empno) who have mgr no or not ie whether
-- that empno has mgr or not it displays all the empno's and if any empno doesnt have mgr then
-- implies that employee doesnt have manager like the empno=7839 ename =KING


8. FULL OUTER JOIN:
--------------------
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
FULL OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

Sunday, April 26, 2015

FlexField Concepts

OVERVIEW OF FLEXFIELD CONCEPTS

A flexfield is a field made up of sub-fields, or segments.  A flexfield appears on your form as a pop-up window that contains a prompt for each segment.   Each segment has a name and a set of valid values.  There are two types of flexfields: key flexfields and descriptive flexfields.

BASIC FLEXFIELD CONCEPTS

Segment - A segment is a single sub-field within a flexfield.  You define the appearance and meaning of individual segments when customizing a flexfield.  A segment is represented in your database as a single table column.

For a key flexfield, a segment usually describes a particular characteristic of the entity identified by the flexfield.  For example, you can have a key flex field that stores part numbers.  The key flexfield can contain the part number PAD-YEL-NR-8 1/2x14, which represents a yellow, narrow ruled, 8 1/2" x 14" note pad.  Each section in the part number, separated by a hyphen, describes a characteristic of the part.  The first segment describes the object, a note pad, the second segment describes the color of the object, yellow, and so on.

Note that we also refer to the fields in a descriptive flexfield pop-up window as segments even though they do not necessarily make up meaningful codes like the segments in key flexfields.  However, they do often describe a particular characteristic of the entity identified elsewhere on the form you are using.

Values, Validation and Value Sets - Your end user enters a segment value into a segment while using an application.  Generally, the flexfield validates each segment against a set of valid values (a "value set") that are usually predefined.  To "validate a segment" means that the flexfield compares the value a user enters in the segment against the values in the value set for that segment.

You can set up your flexfield so that it automatically validates segment values your end user enters against a table of valid values.  If your end user enters an invalid segment value, a list of valid values appears automatically so that the user can choose a valid value.  You can think of a value set as a "container" for your values.  You choose what types of values can fit into your value set:  their length, format, and so on.

A segment is usually validated, and usually each segment in a given flexfield uses a different value set.  You can assign a single value set to more than one segment, and you can even share value sets among different flexfields.  For most value sets, when you enter values into a flexfield segment, you can enter only values that already exist in the value set assigned to the segment.

Structure - A flexfield structure is a specific configuration of segments.  If you add or remove segments, or rearrange the order of segments in a flexfield, you get a different structure.

You can define multiple segment structures for the same flexfield (if that flexfield has been built to support more than one structure).  Your flexfield can display different prompts and fields for different end users based on a data condition in your form or application data.  Both key and descriptive flexfields may allow more than one structure.

In some applications, different users may need a different arrangement of the segments in a flexfield (key or descriptive).  Or, you might want different segments in a flexfield depending on, for example, the value of another form or database field.

Your Oracle General Ledger application, for example, provides different Accounting Flexfield (Chart of Accounts) structures for users of different sets of books.  The Oracle General Ledger application determines which flexfield structure to use based on the value of the GL Set of Books Name user profile option.

Friday, April 24, 2015

SUBSTR INSTR

SUBSTR

Oracle/PLSQL: SUBSTR Function
This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function with syntax and examples.
Description
The Oracle/PLSQL SUBSTR functions allow you to extract a substring from a string.

Syntax
The syntax for the SUBSTR function in Oracle/PLSQL is:

SUBSTR( string, start_position [, length ] )
Parameters or Arguments
string
    The source string.
start_position
    The starting position for extraction. The first position in the string is always 1.
length
    Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

Note:
 If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
 If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
 If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
 If length is a negative number, then the SUBSTR function will return a NULL value.
 
Applies To
The SUBSTR function can be used in the following versions of Oracle/PLSQL:
    Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example
Let's look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.

For example:
SUBSTR('This is a test', 6, 2)
Write Output
SUBSTR('This is a test', 6)
Write Output
SUBSTR('Chandra Sekhar', 1, 4)
Write Output
SUBSTR('Chadra Sekhar', -3, 3)
Write Output
SUBSTR('Chandra Sekhar', -6, 3)
Write ouput
SUBSTR('Chandra Sekhar', -8, 2)
Result: 'On'

INSTR

The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating the position of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.

Example Syntax:

INSTR( source_string, substring [, start_position [, occurrence ] ] )

Source_string is the string to be searched.

substring is the character string to be searched for inside of source_string.

Start_position is an optional argument. It is an integer value that tells Oracle where to start searching in the source_string. If the start_position is negative, then Oracle counts back that number of characters from the end of the source_string and then searches backwards from that position. If omitted, this defaults to 1.

Occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.

If the sub string is not found in source string, the Oracle INSTR function will return 0.

INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

Question on Instr/Sub string

There is a  path in obiee where it is showing as /shared/financials/. In this string,  want to get the value after second special character ('/') to before third special character ('/'). 

That means i should get the value "financials". 

Solution

select substr(:a,instr(:a,'/',1,2)+1,length (:a)) from dual

Pass your string for variable you can get the desired results




Notes On Date Data Type

YEAR    Year, spelled out
YYYY    4-digit year
YYY
YY
Y    Last 3, 2, or 1 digit(s) of year.
IYY
IY
I    Last 3, 2, or 1 digit(s) of ISO year.
IYYY    4-digit year based on the ISO standard
Q    Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM    Month (01-12; JAN = 01).
MON    Abbreviated name of month.
MONTH    Name of month, padded with blanks to length of 9 characters.
RM    Roman numeral month (I-XII; JAN = I).
WW    Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W    Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW    Week of year (1-52 or 1-53) based on the ISO standard.
D    Day of week (1-7).
DAY    Name of day.
DD    Day of month (1-31).
DDD    Day of year (1-366).
DY    Abbreviated name of day.
J    Julian day; the number of days since January 1, 4712 BC.
HH    Hour of day (1-12).
HH12    Hour of day (1-12).
HH24    Hour of day (0-23).
MI    Minute (0-59).
SS    Second (0-59).
SSSSS    Seconds past midnight (0-86399).
FF    Fractional seconds.

ex:
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL
It will return Month is this Format JAN,FEB,MAR,,,

Note while comparing need to TRUNC(SYSDATE) otherwise  data has Time stamp also

SQL functions
MIN
SELECT MIN(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT min(sal)
   FROM emp
it will return Minimum salary of the Employee table

MaX

SELECT MAX(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT MAX(sal)
   FROM emp
it will return Maximum salary of the Employee table

AVG
SELECT AVG(aggregate_expression)
FROM tables
WHERE conditions;

Example
SELECT AVG(sal)
   FROM emp
it will return Average salary of the Employees table
COUNT
Example
SELECT count(*)
   FROM emp
it will return no of employee of the Employees table


Wednesday, April 22, 2015

Oracle Dual Table

Oracle, DUAL is a table which is created with every installation of Oracle

The DUAL table's column, the column name, its datatype and even its value are not important. Technically it could be any datatype. DUAL exists just so we have a 1 row table we can reliably select from.

Don't EVER add rows to DUAL, fiddle with its column, or alter it in any way. If you do, you can expect some very strange and potentially destructive things to happen in your database, assuming the database doesn't just crash and burn completely

select sysdate from emp

1) select 1 from DUAL;
    returns 1

2) select 1+2 from DUAL;
    returns 3

3) select sysdate from DUAL;
    returns system date of oracle database.

4) select `sql` from DUAL;
    returns`sql`

5) select user from DUAL;
    returns oracle user logged in.

6) select
   (select empno from emp where empno = 10)
   from DUAL;
    returns 10

DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)

7)  select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.

8) select LOWER(`ORAclE`) from DUAL;
 returns`oracle` where LOWER is a in-built SQL function.

Interview tips

If DUAL is supposed to have only one row, why is it called DUAL? Why not SINGLE?!

According to this old article on the history of Oracle’s DUAL table, DUAL was originally not meant to be seen itself but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. In that context, the name DUAL seemed fine.

Is it valid or not??
CREATE TABLE xxemp1
AS
   SELECT   * FROM DUAL

*Read*Explore*Succeed*

Tuesday, April 21, 2015

Oracle Apps Basics

ERPs available in market
  • SAP
    • Oracle Applications
    • People soft
    • Siebel
    • JD Edwards
Oracle Applications are used to capture Business functionality information of the organization.
  • Instances
    • Development  - Development
      • Development stage 1
      • Development stage 2
    • Testing  - Testing
    • Production – Client testing
Involved personalities of Oracle Applications and their roles and responsibilities in brief
1)    DBA – Database Administrator
a.    Installation of Oracle Applications
b.    Applying patches
c.    Maintaining Multiple Instances
2)    Functional Consultant
a.    Interaction with the client
b.    Gathering the requirements for development / customization
c.    About oracle applications what available and what wasn’t available.
d.    Example invoice form – fields  - Or develop a invoice form from scratch
e.    Preparation of FDD (Functional Design Document)
3)    Technical Consultant – Input FDD
a.    Go through the FDD
b.    Prepare TDD (Technical Design Document) – Logic, Tables, Procedures, Forms, Menus, Packages – approval
c.    Development of Component
d.    Test the component
e.    Deliver to the client

All these sessions will go trough the below mentioned components

R I C E  -  Components for development 70%

      • R         -           Reports           -           Reports 6i/10g
      • I           -           Interfaces        -           Programs, SQL, PL/SQL
      • C         -           Conversion      -           Programs, SQL, PL/SQL
      • E          -           Extensions      -           Forms 6i/10g

Functions Knowledge 30%
  • Purchase Order Module
  • Accounts Payable Module
  • Order Management Module
  • Inventory Module

Saturday, April 18, 2015

Oracle Applications Architecture




Implementation

When you use an E-business suite, you need to engineer it to suit your business requirements. In this topic, you learn about the principles that form the basis of the business architecture of oracle 11i/R12 E-Business Suite.
Here we will discuss  how to implement a fresh version of oracle application R12/11i in your organization. We‘ll do the implementation of below requirements.

1. The organization makes electronic gadgets – Mobile, Television, Refrigerator

2. The organization is present in 3 regions – Japan, UK and India

Let’s consider that we have installed a production instance of R12 in the server.
What next?


Oracle Applications Architecture
The Oracle Applications Architecture is a framework for multi-tiered, distributed computing that supports Oracle Applications products. In this model, various servers or services are distributed among three levels, or tiers.

A tier is a logical grouping of services, potentially spread across more than one physical machine. The three-tier architecture that comprises an Oracle E-Business Suite installation is made up of the database tier, which supports and manages the Oracle database; the application tier, which supports and manages the various Applications components, and is sometimes known as the middle tier; and the desktop tier, which provides the user interface via an add-on component to a standard web browser.

Centralizing the Oracle Applications software on the application tier eliminates the need to install and maintain application software on each desktop client PC, and also enables Oracle Applications to scale well with an increasing load. Extending this concept further, one of the key benefits of using the Shared Application Tier File System model (originally Shared APPL_TOP) is the need to maintain only a single copy of the relevant Applications code, instead of a copy for every application tier machine.
EBS R12 Architecture


The Desktop Tier
The client interface is provided through HTML for HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based applications.

R12 Database tier

The client interface is provided through HTML for HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based applications.
In Oracle Applications Release 12, each user logs in to Oracle Applications through the E-Business Suite Home Page on a desktop client web browser. 

The E-Business Suite Home Page provides a single point of access to HTML-based applications, Forms-based applications, and Business Intelligence applications.

Once logged in via the E-Business Suite Home Page, you need not sign on again to access other parts of the system. Oracle Applications does not prompt again for user name and password, even when you navigate to other tools and products.

Oracle Applications also retains preferences as you navigate through the system. For example, if you registered in the E-Business Suite Home Page that German is your preferred language, this preference carries over whether you access Forms-based or HTML-based applications.

The Forms client applet is a general-purpose presentation applet that supports all Oracle Applications Forms-based products, including those with customization and extensions. The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run the presentation layer of Oracle Applications forms.

The Application Tier
The application tier has a dual role: hosting the various servers and service groups that process the business logic, and managing communication between the desktop tier and the database tier. This tier is sometimes referred to as the middle tier.
Four servers or service groups comprise the basic application tier for Oracle Applications:
  • Web services
  • Forms services
  • Concurrent Processing server
  • Admin server
Note: In Release 12, the Web and Forms services are provided by  Oracle Application Server (OracleAS) 10g. They are no longer servers in the sense of being a single process, as was the case in previous Applications releases.  It is advisable to avoid using a mixture of different platforms on your application tier. This makes maintenance easier, since only one set of patches needs to be downloaded.

Applications File System
An Oracle Applications Release 12 system utilizes components from many Oracle products. These product files are stored below a number of key top-level directories on the database and application server machines.

Note: No Applications files are installed on desktop client machines, although JAR files and their supporting utilities are downloaded as required
.
Depending on how you chose to install Applications, these product directories may be located on a single machine (the simplest case) or on multiple machines (the most common type of deployment). Operating system environment settings indicate the location of the various files in the file systems of the database and application server machines.

This chapter discusses the association between these environment settings and the corresponding files and directories.

R12 File System
  1. The db/apps_st/data (DATA_TOP) directory is located on the database node machine, and contains the system table spaces, redo log files, data table spaces, index table spaces, and database files
  2. The db/tech_st/10.2.0 directory is located on the database node machine, and contains the ORACLE_HOME for the Oracle10g database
  3. The apps/apps_st/appl (APPL_TOP) directory contains the product directories and files for Oracle Applications
  4. The apps/apps_st/comn or (COMMON_TOP or COMN_TOP) directory contains directories and files used across products
  5. The apps/tech_st/10.1.2 directory contains the ORACLE_HOME used for the Applications technology stack tools components
  6. The apps/tech_st/10.1.3 directory contains the ORACLE_HOME used for the Applications technology stack Java components
Types of users

Forms-based users are involved in the transnational operations of an organization. These users are usually full-time users and require a robust interface with maximum features.
The desktop tier is only responsible for displaying forms. To forms-based users, the forms are displayed using java.

Therefore, the desktop tier needs java virtual machine (JVM) and java archive (JAR) files to operate in the forms-based architectural mode

Self-service users are infrequent users of oracle. They require a simple and quick interface. Typically, most users in an instance are self-service users

Business Intelligence (BI) users are mainly senior executives, who require a simple interface to reveal critical business information. Application designed for BI users provide a browser to eliminate the need for these users to learn a new system

Mobile Users are users whose jobs prevent them from using network-connected computers. By using a mobile interface, they can send or receive information at convenient locations. Usually, sales and inventory personnel belong to this class of users.

To self-service, business intelligence, and mobile users, forms are displayed using HTML or JavaScript. The browser is inherently capable of handling HTML and JavaScript. As a result, additional downloads are not required

For jobs
you can visit Latest Jobs

You can get more information on oracle

Oracle Info