Oracle Technical – Oracle SQL Statement Types and Usage


let me intraduce SQL by statements type.here statements defines set of commands.

Generally types statements are as followed.

  1. Data definition Language(DDL)
  2. Data Retrieval or Query Language(DQL or DRL)
  3. Data Manipulation Language(DML)
  4. Data Control Language(DCL)
  5. Transaction Control Language(TCL)

let me continue in detail type.

DDL commands are used to define the data base objects.The following commands are used to define data base objects.

1. create
2. alter
3. truncate
4. rename
5. drop
1. create

create command defines a new database object, such as a database, user, table, view, trigger, index, macro, stored procedure, user-defined type, user-defined function, or user-defined macro, depending on the object of the CREATE request.

Syntax :

CREATE TABLE table_name
(column_1 column-definition,
column_2 column-definition,

column_n column_definition);
Examples :

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

2. alter

ALTER statement to add a column, modify a column, drop a column, rename a column or rename a table.
Syntax of Single Column Adding:

ALTER TABLE table_name
ADD column_name column-definition;

Example of Single Column Adding:

alter table dept add dept_type varchar2(10);

alter table emp add end_date date;

Syntax of Multiple Column Adding :

ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,

column_n column_definition);

Example of Multiple Column Adding :

alter table add(dob date,
gender varchar2(10));
Syntax of Single Column Modifying:

ALTER TABLE table_name
MODIFY column_name column_type;
Example of Single Column Modifying:

alter table dept modify dept_type varchar2(10) not null;
Syntax of multiple Column Modifying:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,

column_n column_type);

Example of multiple Column Modifying:

alter table modify(dob date NOT NULL,
gender varchar2(6));

Note : We can’t modify any column if table contains data means if you want to increase range of data then table should be empty.
Syntax of drop a column in an existing table :

ALTER TABLE table_name
DROP COLUMN column_name;

Example of drop a column in an existing table :

ALTER TABLE emp
DROP COLUMN DOB;
Syntax of rename a column in an existing table :

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Example of rename a column in an existing table :

ALTER TABLE emp
RENAME COLUMN dob to date_of_birth;
Syntax of To rename a table

ALTER TABLE table_name
RENAME TO new_table_name;

Example of To rename a table

ALTER TABLE emp
RENAME TO employee;

3. truncate

By using this command , we can delete data of the table but definition of the object will remain same.

Syntax :

truncate table table_name;

Example :

truncate table emp;

5. drop

By using this command we can delete table permanently from database.

Syntax :

drop table table_name;

Example :

drop table emp;
Few Important Points:

What is result of the following statements?

1. create table xx_dept as select *from dept;

Answer : xx_dept table will be create same as dept except constraints,here not null will be applicable.

2. create table xx_dept 1 as select *from dept where 1=1;

Answer : xx_dept table will be create same as dept except constraints,here not null will be applicable.

3. create table xx_dept2 as select *from dept where 1=2;

Answer : xx_dept table will be create same as dept with structure only.

Data Retrieval or Query Language(DQL or DRL) :

here SELECT clause will come under this category,main usage of select to identify the result set column list.

Syntax :
select *
from table_name;

select column1,column2…
from table_name;

Example :
select *from
dept;

this statement will fetch all columns of dept table.but it is not suggestible approach.

select deptno,
dname,
loc
from dept;

Data Manipulation Language(DML) 

These statements are used to manage the data with database objects.

Categorization of Commands :

INSERT :
insert data into a table :

Syntax :

INSERT INTO <table_name>
(<column_name>)
VALUES
(<value>);

Example 1 :

INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO DEPT VALUES (20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO DEPT VALUES (30, ‘SALES’, ‘CHICAGO’);
INSERT INTO DEPT VALUES (40, ‘OPERATIONS’, ‘BOSTON’);

Example 2:
INSERT INTO EMP VALUES
(7369, ‘SMITH’, ‘CLERK’, 7902,
TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, ‘ALLEN’, ‘SALESMAN’, 7698,
TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, ‘WARD’, ‘SALESMAN’, 7698,
TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, ‘JONES’, ‘MANAGER’, 7839,
TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, ‘MARTIN’, ‘SALESMAN’, 7698,
TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, ‘BLAKE’, ‘MANAGER’, 7839,
TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, ‘CLARK’, ‘MANAGER’, 7839,
TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, ‘SCOTT’, ‘ANALYST’, 7566,
TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, ‘KING’, ‘PRESIDENT’, NULL,
TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, ‘TURNER’, ‘SALESMAN’, 7698,
TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, ‘ADAMS’, ‘CLERK’, 7788,
TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, ‘JAMES’, ‘CLERK’, 7698,
TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, ‘FORD’, ‘ANALYST’, 7566,
TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, ‘MILLER’, ‘CLERK’, 7782,
TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

Important Note :
We can Insert data by using select statement also.

Example :
first create table without data.

create table employee as select *from emp where 1=2;

then insert data from emp to employee table.

INSERT INTO employee
SELECT *
FROM emp;

this statemet entire data of emp into employee.

INSERT INTO employee(empno,ename,job,deptno)
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno=10;

But here it will insert only specified columns for deptno 10 only.
UPDATE :
updates existing data within a table.

Syntax :
UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>;

Examples:

update emp
set sal=sal+2000;

it will update all records of emp,but it’s suggestible way in real time, surely updation can be done based on some condition.

update emp
set sal=sal+2000
where empno=7788;

update emp
set sal=sal+2000,
job=’Manager’
where empno=7788;

DELETE :
deletes all records from a table :

Syntax :
delete
from table_name
where <column_name>=<value>;

Examples:

delete
from emp
where deptno=10;

Note : We can’t delete specific column from table, like the following way.

delete empno
from emp;
MERGE :
Primary usage merge when moving large amounts of data from one table to another table.this kind work can be done in data warehouse.

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

Example :

CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10));

INSERT INTO employee VALUES (1, ‘Dan’, ‘Morgan’, 10, 100000);
INSERT INTO employee VALUES (2, ‘Jack’, ‘Cline’, 20, 100000);
INSERT INTO employee VALUES (3, ‘Elizabeth’, ‘Scott’, 20, 50000);
INSERT INTO employee VALUES (4, ‘Jackie’, ‘Stough’, 20, 40000);
INSERT INTO employee VALUES (5, ‘Richard’, ‘Foote’, 20, 30000);
INSERT INTO employee VALUES (6, ‘Joe’, ‘Johnson’, 20, 70000);
INSERT INTO employee VALUES (7, ‘Clark’, ‘Urling’, 20, 90000);
COMMIT;

CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
COMMIT;

MERGE INTO bonuses B
USING (
SELECT employee_id, salary
FROM employee
WHERE dept_no =20) E
ON (B.employee_id = E.employee_id)
WHEN MATCHED THEN
UPDATE SET B.bonus = E.salary * 0.1
WHEN NOT MATCHED THEN
INSERT (B.employee_id, B.bonus)
VALUES (E.employee_id, E.salary * 0.05);

Data Control Language(DCL) 

he Data Control Language (DCL) is a subset of the Structured Query Lanaguge (SQL) ,that allows database administrators to configure security access to relational databases.Two types of DCL commands are GRANT and REVOKE. Only Database Administrator’s or owner’s of the database object can provide/remove privileges on a database object.

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

GRANT :
gives user’s access privileges to database.

The Syntax for the GRANT command is:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
Examples:

grant select on
emp to scott;

grant select,insert,delete on
emp to scott;

REVOKE :
withdraw access privileges given with the GRANT command.

The Syntax for the REVOKE command is:

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name} ;

Example :
revoke insert, delete on
emp from scott;
Transaction Control Language(TCL) :

TCL statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT :
permanent save to work done.here we can’t roll back once applied commit.

Example :
COMMIT;

SAVEPOINT :
identify a point in a transaction and which you can later roll back also.

Example :

save point to sp1;

roll back to sp1;

ROLLBACK :
restore database to original since the last COMMIT

Example :

ROLLBACK;

Advertisements

Run Posting (GLPPOS), or other concurrent programs from the Command line


Run Posting (GLPPOS), or other concurrent programs from the Command line?

How to Run Posting (GLPPOS), or other concurrent programs from the Command line?

You can run any GL program from the command line. Follow these steps:

1) Verify that you run this at a time when there are no incompatible Programs running. To find the incompatible programs:

Responsibility = System Administrator

GUI Navigation = Concurrent/Program/Define

Query on GLPPOS in the Short Name field.

Click on the Incompatibilities button.

2) Change the profile “Concurrent Hold Request” to Yes.

Responsibility = System Administrator

GUI Navigation = Profile/System

Query on the profile option ‘Concurrent Hold Requests’.

Set it to Yes at the User level and Save.

3) Select a journal batch for posting. The request will be put on hold.

Responsibility = General Ledger Super User

GUI Navigation = Journals/Post

4) Go to the Concurrent Requests form and get the parameters for the Posting concurrent process.

The parameters for posting are:

set of books id

chart of accounts id

posting_run_id

5) Run posting from the command line.

$GL_TOP/bin/GLPPOS <username/pwd> 0 Y <parameters> X 2> <File name>

Where:   <username/pwd> : apps username and password

<parameters>   : The parameters for the program, separated by spaces. For NULL parameters you need to specify closed double quotes (“”).

X              : Indicates that you want to run the program in debug mode. This can be any character and is optional.

2> <File name> : Use this to redirect standard errors and output to a file. File name is the output file name it will create. It will be created in your current directory, if a full path is not specified. This is optional

but recommended.

Example:

If you go to the concurrent requests form and see in the Parameters field: 1,101,2546, you would enter the following command at the Unix prompt:

$GL_TOP/bin/GLPPOS <username/pwd> 0 Y 1 101 2546 X 2> <myfile.txt>

6) Change the profile option ‘Concurrent Hold Requests’ back to its original setting. Be sure to run the program from a directory for which you have write Privileges. That is where the log and out files will be created.

Also make sure that you are in the proper application environment.

Here is an example of the command and the output you will see.

$>$GL_TOP/bin/GLPPOS apps/apps 0 Y 8 50128 84 2 2> njpost2.out

DEBUG: started in debug_mode……

$> ls

L54536.log   L54540.log   O54536.out   O54540.out   njpost.out   post.out

L54538.logL54542.log   O54538.out   O54542.out   njpost2.out  test

$> more njpost2.out

Log filename    : L54542.log

Report filename : O54542.out

glpmai() fnd_user_id is -1

glpmai() fnd_user_name is ANONYMOUS

glpmai() req_id is 0

glpmai() sob_id is 8

etc.

selected General Ledger programs can be run in debug

mode by setting the profile option ‘GL: Debug Mode’ to Yes at the

appropriate level.

These programs are:

– GLCRVL – Revaluation

– GLPPOS – Posting

– GLTTRN – Translation

– GLCCON – Consolidation Transfer

– GLAMAS – Run MassAllocations

Important things to focus as you are 0 to 2 Year experience in Oracle Applications Technical


Important things to focus as you are 0 to 2 Year experience in Oracle Applications Technical

1.Learn SQL – Writing Queries

2.Be Proficient in PL/SQL Programming

3.Learn more or more on System Admin and Application Developer module

4.Learn Unix – at least basics !

Oracle Applications – Basics – Part 1


Hello Friends, here i am posting some of basic oracle applications questions , will try to collect more and put in future posts, here i am putting quite easy and basic questions. If you like to know Questions of any specific area or modules, plz communicate so i will post those as well.

What are Oracle Apps API?

Where APIs are physically store? n What is the latest version of API?

They are Application Program Interfaces, which are standard stored procedure, package or functions created for performing specific activities in the Oracle Apps E-Business Suite.

Ex: The Order Import API when called would create orders corresponding to the record in the Order Interface tables.

How to attach reports in Oracle Applications?

The steps are as follows:

1.          Design your report.

2.          Generate the executable file of the report.

3.          Move the executable as well as source file to the appropriate product’s folder.

4.          Register the report as concurrent executable.

5.          Define the concurrent program for the executable registered.

6.          Add the concurrent program to the request group of the responsibility.

How to attach Forms in Oracle Applications

1. Develop the form in Developer 2000.

2. Save the FORM and move it to UNIX. [Form to be moved to $CUSTOM_TOP/forms/US]

3. Define the FORM in Application Developer

4. Define FORM as FUNCTION.

5. Identify MENU, where the FORM to be attached. [SysadminResponsibilityDefine]

6.Copy the above MENU and go to Application Developer and query on MENU tab

What is the difference between Customization and Personalization?

Customization is done by making the changes in the cutom.pll whereas the Form Personalization is done through the Front End in Oracle Apps.

Difference between Key and Descriptive Flexfield?

key flex fields are used to identify particular entity like(accounts,objects) supoose take debit cards each debit card contain unique number to identify them. eg; 100-023-323 key flex fields are used to store this number along with description.

Unique Identifier

Key Flexfield are stored in segment

For key flexfield there are flexfield Qualifier and segment Qualifier

DFF’s are used to capture additional informtion required for our organization without any programming.

To capture extra information
Stored in attributes
Context-sensitive flexfield is a feature of DFF. (descriptive flexfield)

What is the difference between data conversion and data migration?

Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

What is the difference between Organization_id and Org_id ?

OrgId: Org Id is an unique ID for the Operating Unit.Organisation Id: The Organisation Id is an ID for the Inventory Organisation which is under an Operating Unit.

What are _ALL tables in Oracle Apps?

_all tables will store information about multiple oraganizations. these table contain ORG_ID column that  picks particular organizations from multiple organizations. _all tables will be created when we install multiorg.

What is one full life cycle implementation?

Full Life Cycle means implementing a project from the begining to the production and maintenance stage.

(1) System Planning:

(Scope & Budget of the Project)

(2) Business Analysis:

(Business Requirements, Mapping & GAP Analysis)

(3) System Analysis & Design

(Design Conceptual & Functional Data Models)

(4) System Development

(Programming & System/Integration/Validation Testing)

(5) System Implementation

(Data Conversions, Interfaces, Extensions,User Manuals & User Training)

(6) System Maintenance & Support

Thanks – Shivmohan Purohit

Oracle Applications – Interview Questions (All Modules)


Oracle Applications – Interview Questions (All Modules)

Question: What are the key benefits of forms personalization over custom.pll?
Answer: Multiple users can develop forms personalization at any given point in time.
It is fairly easy to enable and disable forms personalization.
–>A programmer is not required to do simple things such as hide/disable fields or buttons.
–>Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
Answer:
–>Can’t create record group queries, hence can’t implement LOV Query changes.
–>Can’t make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.

Question: How can you import invoices into Oracle Receivables?
Answer:

You can either use AutoInvoice by populating tables RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL & RA_INTERFACE_SALESCREDITS_ALL.Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice for Receivables Invoice Import.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?

Answer: yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations will get disabled by this profile option. This profile is also very useful when debugging your OA Framework based application in the event of some error. By disabling the personalization via profile, you can isolate the error, i.e. is being caused by your extension/substitution code or by Oracle’s standard functionality.

Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?

Answer: The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.

Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?

Answer: “Gather Stats” will simply gather the stats against existing tables, indexes etc. However Gather Stats does not create any new indexes. But “Program – Optimizer[RGOPTM]” can create indexes on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag enabled,

Question: How do you know if a specific Oracle patch has been applied in apps to your environment?

Answer: Use table ad_bugs, in which column bug_number is the patch number.

SELECT bug_number ,to_char(creation_date, ‘DD-MON-YYYY HH24:MI:SS’) dated

FROM apps.ad_bugs

WHERE bug_number = TRIM(‘&bug_number’) ;

Question: How to make concurrent program end with warning?

Answer: If the concurrent program is of type PL/SQL, you can assign a value of 1 to the “retcode” OUT Parameter. For a Java Concurrent program, use the code similar to below ReqCompletion lRC; //get handle on request completion object for reporting status IRC = pCpContext.getReqCompletion(); lRC.setCompletion(ReqCompletion.WARNING, “WARNING”);

Question: Which table is used to provide drill down from Oracle GL into sub-ledger?

Answer: GL_IMPORT_REFERENCES

Question: You have just created two concurrent programs namely “XX PO Prog1” & “XX PO Prog2”. Now you wish to create a menu for Concurrent Request submission such that only these two Concurrent Programs are visible from that Run Request menu. Please explain the steps to implement this?

Answer:

a) Define a request group, lets say with name “XX_PO_PROGS”

b) Add these two concurrent programs to the request group “XX_PO_PROGS”

c) Define a new Form Function that is attached to Form “Run Reports”

d) In the parameter field of Form Function screen, enter REQUEST_GROUP_CODE=”XX_PO_PROGS” REQUEST_GROUP_APPL_SHORT_NAME=”XXPO” TITLE=”XXPO:XX_PO_PROGS” e) Attach this form function to the desired menu.

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not implement interactive messages, i.e. a message will give multiple options for Response. The best you can get from Forms Personalization to do is popup up Message with OK option.

Question: This is a very tough one, almost impossible to answer, but yet I will ask. Which Form in Oracle Applications has most number of Form Functions?

Answer: “Run Reports”. And why not, the Form Function for this screen has a parameter to which we pass name of the “Request Group”, hence securing the list of Concurrent Programs that are visible in “Run Request” Form. Just so that you know, there are over 600 form functions for “Run Reports”

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions from one environment to another without Keying? Will you use FNDLOAD?

Answer: FNDLOAD can not be used in the scenario. You can use migrator available in “Oracle iSetup” Responsibility

Question: How can an end-user be given control to run a script developed by a developer, given that an end user will never have access to apps password (and rightly so)?

Answer: This script can be attached to a Concurrent Program via a concurrent program executable. The user will then be given access to this Concurrent Program.

Question: But how will the end user or Oracle Apps make this script run every 10hours daily?

Answer: A concurrent program can be scheduled to run at desired intervals. The schedule is defined at the time of submission.

Question: What are the basic steps when defining a concurrent program?

Answer: Broadly speaking there are three steps when developing a concurrent program in Oracle Apps Step 1. Make Oracle Apps identify the executable Step 2. Provide a handle to the executable by means of defining a concurrent program Step 3. Make this concurrent program accesible to selected users via their responsibility.

thanks - shivmohan purohit

Oracle – Need help – One 2 One Support / help


  • Need help to update your resume’ / CV appropriately to reflect your skills
  • Looking to get help to identify your technical strength to face technical interviews.
  • Need someone to tell what you read/ study more on topic to get confident and cover it appropriately to deliver answers properly.
  • Want to know which certification exam you should go and what and how it will be beneficial for you.
  • Stuck in any problem technically and don’t know what to do NEXT

I am sure either you or your friends/ collegues get stuck in such things, Here you can reach out and ask your question /query or if needed then let’s talk out to resolve things.

Do write me at shivmohan(dot)purohit@gmail.com or on Contact link on top right side.

thanks – Shiv