Oracle Apps Technical

Wednesday, May 6, 2015

Who Columns In Oracle Apps

Chandrasekhar Byna 10:48 AM Basics No comments :

Creation of New User:
1)Open the internet Explorer connect to Oracle Applications
2)Enter the User Name :OPERATIONS
Password :WELCOME
3)Select the Responsibility called 'System Administrator'
4)Open the User form.
Security => User =>Define
5)Enter User Name and Password attach the Responsibilities whatever we required
for ex System Administrator
Application Developer
6)Exit from the Appication
File => Exit Oracle Applications
7)Connect to Oracle apps enter new user name password system will shows the message
like 'Password Has Expired'
8)Enter the New Password Press Ok Button

Short Cuts:
===========

1)To Query All the Records Press CTRL+F11
2)To Query Specific Records
i)Open the Form
ii)Press F11 (Form will comes into Query mode)
iii)Enter Search Criteria in any field
iV)Press CTRL+F11
3)To Close Form = F4
5)To Save the Records CTRL+S

Effective Date From and To:
===========================
In most of the Oracle Application forms we will find two field like
Effective Date From
Effective Date To
In some of the forms once we create records and save. We can not delete from database
that time we can go for Disable/Enable the record by using these two fields
Finding Table NameS/Column Names:
=================================
1)Help => Record History which will shows the Table Name
2)Help Menu=>Diagnastics=>Examine=>Enter the Password(APPS)=>We can find the Column Name
WHO Columns:
=============
WHO Column Will be used to find out the History of the record
we can find from front End Also
Help=>Record History
CREATED_BY - Which User has created the Record(Userid)
CREATION_DATE - at what time user has created (SYSDATE)
LAST_UPATED_BY -Which User has updated recentley(UserID)
LAST_UPDATE_DATE -at what time user has Updated (SYSDATE)
LAST_LOGON_DATE - At what time user last Login Time

Find the Login UserName:
==========================
Help Menu=>About Oracle Applications

Wednesday, April 29, 2015

Oracle SQL Loader

Chandrasekhar Byna 5:02 AM No comments :

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

Chandrasekhar 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

Chandrasekhar 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

Chandrasekhar 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

Chandrasekhar 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

Chandrasekhar 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

Subscribe to: Posts ( Atom )

Oracle Apps Technical

About Me

Wednesday, May 6, 2015

Who Columns In Oracle Apps

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

Subscribe

Labels

Blog Archive

Contact Form