April 2015

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

Sekhar Byna 5:23 AM Basics No comments :

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

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);

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

SQL> SELECT E.ENAME,E.EMPNO,D.DNAME

2 FROM EMP E

3 JOIN DEPT D

4 USING (DEPTNO);

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)

1 SELECT E.ENAME,E.DEPTNO,D.DNAME

2 FROM EMP E,DEPT D

3* WHERE E.DEPTNO=D.DEPTNO(+)

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)

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

Sekhar Byna 5:49 AM Basics No comments :

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

Sekhar Byna 11:49 AM Basics No comments :

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

Sekhar Byna 7:34 AM Basics No comments :

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

Sekhar Byna 1:14 PM Basics No comments :

ERPs available in market

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

Sekhar Byna 7:04 PM Basics No comments :

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.

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.

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.

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
The db/tech_st/10.2.0 directory is located on the database node machine, and contains the ORACLE_HOME for the Oracle10g database
The apps/apps_st/appl (APPL_TOP) directory contains the product directories and files for Oracle Applications
The apps/apps_st/comn or (COMMON_TOP or COMN_TOP) directory contains directories and files used across products
The apps/tech_st/10.1.2 directory contains the ORACLE_HOME used for the Applications technology stack tools components
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

Subscribe to: Posts ( Atom )

Oracle Apps Technical

About Me

Wednesday, April 29, 2015

Oracle SQL Loader

Tuesday, April 28, 2015

Oracle Joins

Sunday, April 26, 2015

FlexField Concepts

Friday, April 24, 2015

SUBSTR INSTR

Wednesday, April 22, 2015

Oracle Dual Table

Tuesday, April 21, 2015

Oracle Apps Basics

Saturday, April 18, 2015

Oracle Applications Architecture

Subscribe

Labels

Blog Archive

Contact Form