The Interview of Oracle Programmer

Oracle Database 

PL/SQL

SQL

ORACLE APEX

ORACLE FORMS

ORACLE REPORT 

SAP CRYSTAL REPORT

(Oracle Database)

 #What is an instance in Oracle database?

When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance

#What is the difference between an instance and a database?

Difference between a database and an instance [Oracle].. The database is the set of files where application data (the reason for a database) and meta data is stored. An instance is the software (and memory) that Oracle uses to manipulate the data in the database

#Definition of Enterprise Resource Planning (ERP)

Enterprise resource planning (ERP) refers to a type of software that organizations use to manage day-to-day business activities such as accounting, procurement, project management, risk management and compliance, and supply chain operations. A complete ERP suite also includes enterprise performance management, software that helps plan, budget, predict, and report on an organization’s financial results
ERP systems tie together a multitude of business processes and enable the flow of data between them. By collecting an organization’s shared transactional data from multiple sources, ERP systems eliminate data duplication and provide data integrity with a single source of truth.

#What is an API? (Application Programming Interface)

API is the acronym for Application Programming Interface, which is a software intermediary that allows two applications to talk to each other. Each time you use an app like Facebook, send an instant message, or check the weather on your phone, you’re using an API.

#What is ODBC stand for? 

Open Database Connectivity. An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data. ODBC permits maximum interoperability, which means a single application can access different DBMS.

(PL/SQL)

(SQL)

#Which one is better to find Nth highest salary (return null if no result)

select e.last_name, e.salary as HighestSalary

from (

      select last_name , salary,

      dense_rank() OVER (order by salary desc) dr from hr.employees) e

where dr < 5 

(ORACLE APEX)

#Oracle REST Data Services is a Java EE-based alternative for Oracle HTTP Server and mod_plsql.   

The Java EE implementation offers increased functionality including a command line based  configuration,

enhanced security, file caching, and RESTful web services. Oracle REST Data   Services also provides 

increased flexibility by supporting deployments using 

1. Oracle WebLogic Server

2. Glassfish Server

3. Apache Tomcat

4. Standalone mode.

 The Oracle Application Express architecture requires some form of web server to proxy requests between

a web browser and the Oracle Application Express engine. Oracle REST Data Services satisfies this need 

but its use goes beyond that of Oracle Application Express configurations. Oracle REST Data Services 

simplifies the deployment process because there is no Oracle home required, as connectivity is provided

using an embedded JDBC driver.

#What is the use of Oracle APEX?

Oracle Application Express (Oracle APEX), is the low code web application development tool for the Oracle Database.

Application Express enables you to design, develop and deploy beautiful, responsive, database-driven applications,

either on-premises or in the cloud.

(ORACLE FORMS)

#Simple 3 Tier Architecture

Main difference from procedure function in oracle

The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or

n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters.

Functions can be called from Procedure whereas Procedures cannot be called from a Function.

How to install oracle forms/ weblogic

1.install fmw infrastructure --weblogic 

2.install Forms & Reports Developer

3.install rcu.bat

4.install config.cmd

1.install fmw infrastructure

--C:\Oracle\app\OracleHomeUser\jdk\bin\java-jar E:\Software_Quium\Database_12C_full\wls\fmw_12.2.1.3.0_infrastructure.jar

2.install Forms & Reports dev

-- E:\Software_Quium\Database_12C_full\wls\setup_fmw_12.2.1.3.0_fr_win64.exe

3.install rcu 

-- C:\Oracle\Middleware\Oracle_Home\oracle_common\bin\rcu.bat

4.install config 

-- C:\Oracle\Middleware\Oracle_Home\oracle_common\common\bin\config.cmd

(ORACLE REPORT)

(CRYSTAL REPORT)

#What is Crystal Reports?

SAP Crystal Report is a BI tool which helps you to build reports from SAP and other non-SAP data sources like Oracle, SQL Server, MySQL, XML Data Source, Microsoft Excel, etc. The knowledge of this tool helps the business leaders in developing advanced level reports and take accurate/profitable decisions.

#Benefits of using Crystal Reports

Here, are benefits/pros of using Crystal Reports:

• Allows you to create highly pixel-perfect reports using SAP crystal report with easy design interface and efficient workflows.
• Helps you to build personalized reports in your preferred language and format.
• You can connect to information sources directly, which include: Native data, OLE DB, ODBC, JDBC connectivity to relational DB, OLAP, web services, etc.
• Helps you to export more data to a single worksheet, without extending it to multiple sheets.
• Crystal report tool allows you to open interactive reports through your mobile devices.
• Helps you to produce a report from virtually any data source.
• Allows you to analyze and interpret important information.
• Data connection procedures are straight forward.
• Helps you to access the trusted data
• Allows you to navigate to the lowest level of data granularity
• Helps you to create a customized reporting view you need for your business.

#History of Crystal Reports

• Terry Cunningham and his peers created the software in 1991
• In 1994, Crystal Services was acquired by Seagate Technology.
• Crystal Decisions software was acquired in December 2003 by BusinessObjects.
• In the year 2004, version 11 released.
• SAP acquired BusinessObjects on October 8, 2007
• Crystal Reports Version 14.0 released in 2011
• Version 14.1 released in 2013
• Version 14.2 released in 2016

#Components of Crystal Reports

The Report Design Canvas helps you to design the structure of your report. You can include different elements like charts, test, data objects in the reports.

Rules:

Rules option displays a rule above the report canvas. However, the tool allows you to change the unit of measurement by changing the unit option on the design canvas tab.

Structure mode:

Structure mode and Page mode helps you to define the layout of the reports. Each object in the structure mode is represented by a frame.

Important components of structure mode are given below:

#Structure mode Components

Component Name	Details
Report Header	It is used for adding Report title at the top or any other information you want to add at the starting of the report.
Page Header	Page header is used to present information at the top of each page. Header section generally includes document heading, chapter name, etc.
Page Footer	Use to present page numbers or any other information at the end of each page.
Body	This section displays the body of the report. All report data should be included in this section.
Page Mode	Page mode view represents the actual data in the report when the report will be published or printed.

#Features of Crystal Report

• Allows you to interact with various data sources
• It can avoid the intervention of middle layers like Universes
• Easy interaction with SAP HANA
• Powerful report design
• Flexible application development
• Report management and delivery
• Administration via Web Interface
• Easy navigation system
• Tracks information and keep our data organized
• Live Connection to External Data
• Multi-Data Source Reporting
• OLAP (Pre-processed cube representation)
• ROLAP (SQL-layer querying)

#Disadvantages of using Crystal Reports

Here, are drawbacks/cons of using Crystal report.

• Crystal report is not a very user-friendly tool for learning as error decryption is tedious and hard to figure out.
• Using sub-reports, which are embedded with main reports for different views of the same data sourced from different tables, makes the entire process very slow.
• Security is a big concern at times while accessing data.
• Crystal reports a new version doesn't work with older engines.
• Incorporating reports to webpages sometimes crashes.
• A stable internet connection is always needed for the software to function well.
• The company does not offer any free trial plan.