Protect Restfull API with Basic Authentication

 1. Create new module from Restfull service.

2. Create Template..

3. Create Authentication User from ==> Manage Users & Group ==> Create User

   

4. From Privilage Create New privilage.

 

Now API is protected and when we want to call API then we need to use Basic Authentication.

 

Oracle Analytical Question

Index:
Scenario -1 

• Suppose I have a table EMP with three columns (EMP_ID, EMP_NAME, SALARY), and I create a unique composite index on this table's two columns (EMP_ID, EMP_NAME). Now I write a query in which the query's where clause contains two columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'). Which types of index scans use Oracle?

        Answer: Oracle will use INDEX UNIQUE SCAN.-TABLE ACCESS BY INDEX ROWID

• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_ID=100 ). Which types of index scans use Oracle?
  
  
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_NAME='BASIR'). Which types of index scans use Oracle?

      Answer: Oracle will use TABLE ACCESS FULL 

• If I write a query in which the query's where clause contains three columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'  AND SALARY=3000). Which types of index scans use Oracle?

        Answer: Oracle will use INDEX UNIQUE SCAN.- TABLE ACCESS BY INDEX ROWID

Scenario -2

• Suppose I have a table EMP with three columns (EMP_ID, EMP_NAME, SALARY), and I create a unique composite index on this table's two columns (EMP_ID, EMP_NAME). Now I write a query in which the query's where clause contains two columns (SELECT * FROM EMP WHERE   EMP_ID=100 AND NAME='BASIR'). Which types of index scans use Oracle?
        Answer: Oracle will use INDEX RANGE SCAN.
        TABLE ACCESS BY INDEX ROWID BATCHED
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_NAME='BASIR'). Which types of index scans use Oracle?
      Answer: Oracle will use TABLE ACCESS FULL 
• If I write a query in which the query's where clause contains only single columns (SELECT * FROM EMP WHERE   EMP_ID=100 ). Which types of index scans use Oracle?
      Answer: Oracle will use INDEX RANGE SCAN.-
                TABLE ACCESS BY INDEX ROWID BATCHED


Index fast full scan :
If we create primary key constraints or create a unique composite index and specify that particular column as a not null. Then if we write a query about which column is part of the composite index, then it will use-Index fast full scan.
Suppose: I have an EMP Table with three columns (id not null, name, salary)
Create a unique composite Index (ID, Name). 
My query was SELECT ID, NAME FROM EMP. Then it will use Index fast full scan.

SQL Loader & External File:

SQL Loader: SQL loader is a utility provided by Oracle to load data from external files into the Oracle database. This is a command line utility. To do this, first, we need to create database directories. Then need two files one is a data file, and another is a control file. The control file will have information about the name of the source file and metadata information about the source file. For example, The name of the source file, how the source file is formatted, and what should be a target table.

The sample Data File is source_data.txt, and the data format

ID, NAME, ADDRESS, SALARY

1001,Hasan Mahmud,Mirpur-1 Dhaka,100000,

1002,Rasel Hasan Mahmud,Mirpur-1 Dhaka,500000,

1003,Kabir Hasan Mahmud,Mirpur-1 Dhaka,800000,

Control file: control_file.txt

OPTIONS(SKIP=1)

LOAD DATA

INFILE 'SampleFile.txt'

TRUNCATE INTO TABLE EMP_DTL

FIELDs TERMINATED BY ','

(ID, NAME, ADDRESS, SALARY)

SQL Loader Command: Got to Directory Location. Then run this command

sqlldr hrms/hrms@orcl control=control_file.txt

External Table: External table is another way to read information from external files to Oracle Database. For this, first, we need to create directories and put source files here. Another one is to create an external table. This particular file reads data from an external file. Actually, the external table will not store any data. When we call an external table then, it will read data from external sources and show this data.Table Syntex:

CREATE TABLE EMP_DTL_EXTERNAL

(

   ID        NUMBER,

   NAME      VARCHAR2 (100),

   ADDRESS   VARCHAR2 (100),

   SALARY    NUMBER

)

ORGANIZATION EXTERNAL

   (TYPE ORACLE_LOADER

         DEFAULT DIRECTORY DB_BACKUP

            ACCESS PARAMETERS

               (RECORDS DELIMITED BY NEWLINE

 FIELDS TERMINATED BY ',')

         LOCATION ('SampleFile.txt'))

         REJECT LIMIT UNLIMITED;

SELECT * FROM EMP_DTL_EXTERNAL

Linux Command Line

 Shell: It's a CLI stand for command-line-interface.

Terminal: A terminal is a tool in which you can pass your shell commands. To open the terminal press ctrl-alt-T.

Oracle Apex Important Question

Oracle Apex Important Question :

1. Application Processes: Application Processes execute at the same point for every page in the application. However, we can apply conditions for specific pages to control when the process executes. If we want to show some value on every page, then we create an application process.
   
   
2. Application Computation: Application Computation are units of logic that set the value of a single page or application level item and are run at the same point across multiple pages in an application. Like page-level computation, application computation can be based on static value, item value, PLSQL or SQL. Suppose Application Name.
   
   
3. Authentication Schema:  An authentication scheme defines what is required for an authentication process. This includes the following: The login module stack determines whether a user is granted access to an application. The user interfaces are used to gather the information required to authenticate a user.

• Authentication is the process of establishing user's identities before they can access an applications.
• Although we can define multiple authentication schemes for your application, only one scheme can be current at a time.
• The purpose of authentication is to determine the application user identity.
• Create custom authentication scheme. Create a custom authentication method we must provide a PL/SQL function the Application Express engine executes before processing each page request.The function will return boolean values.

1. 
   
2. Authorization Schemes: An authorization scheme extends the security of your application's authentication scheme. You can specify an authorization scheme for an entire application, page, or specific control, such as a region, item, or button.

     5.  Apex Collection : 

• The apex collection is a temporary structure, similar to a temporary table. In  Apex Collection we can store data temporarily and can be posted after the performing validation or calculation during specifice user session. 
• Collection in Oracle Apex are the temporary storage for the current session, In which you can add the data, access the data, and can do other lots of things.
• Collections enable we to temporary capture one or more nonscalar values. We can use collections to store rows and columns currently in session state so they can be accessed,manipulated, or processed during user's specific session. 
• Every collection contains a named list of data elements (or members) which can have up to 50 character attributes(VARCHAR3(4000)), five number attribures, five date attributes one XML type attribute, one large binary attribute(BLOB). We can insert,update,and delete collection information using PL/SQL API APEX_COLLECTION.

         apex_collection.collection_exist(p_collection_name=>'test');
         apex_collection.create_collection(p_collection_name=>'test');
         apex_collection.truncate_collection(p_collection_name=>'test');

         apex_collection.add_member(p_collection_name=>'test');

     6.  What is page 0 in oracle apex?

Page zero acts as a template page for the application. A global page is a special page. Unlike all other processes, validations or branches. It function as a master pages.

•  Page Zero acts as a template for the application.
• All components of page Zero will be rendered on all the pages in the application.
• The following component can be used on page zero: Computation,Branches,Regions(forms,reports,lists and HTML),Items,Dynamic action.
• Page Zero has no processing part.

Page Item: 

• Page items are created on page level.
• An item is part of an HTML form.
• An item can be a text field,text area,password,select list,check box,and so on.
• Item attributes affect the display of items on a page.
• These attributes can impact where a label display, how large an items is.

Application Item :

• Application Item are created on application level.
• We have to give name of the Application Item & Scope--Application/Global.
• If the oracle apex session is shared by more than one application and the Global item's value should be the same for all applications.
• Application items maintain sesson state.
• Application items can be set using computations,processes or by passing values on a URL.
• Application can share the same session if their authentications have the same session cookie attributes. the scope attribute of application items has to be the same across these application.
• Application level items do not display, but are used as global variavles to the application.
• Appllication item can not be use in Javascript to get the value.
• We can use application item as a global variable.

Global Page items:

• The Global page of application functions as a master page.
• We can add several globel page for each user interface.
• The application express engine renders all components we add to a Global page on every page within our application.

Did DML Operation allowed in Materialized view?

Answer: Users can perform DML operations on a writeable materialized view, but if we refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself.

Faceted Search : Faceted search allows we to filter different columns on the left side of the page and report on the right side of the page, to get only the data that we need.

Error Log: Create Error Log Table Statement

begin
  dbms_errlog.create_error_log (dml_table_name => 'dest');
end;

Create Error Log Table With custom table name:

begin
  dbms_errlog.create_error_log (dml_table_name     => 'dest',
                                err_log_table_name => 'dest_err_log');
end;

Insert Statement:

INSERT INTO TABLE_NAME

                  (UNIQUE_ID, MOBILE_NUMBER, MOBILE_EXCESS_BILL, CREATED_BY)

          VALUES (i.UNIQUE_ID, i.MOBILE_NUMBER, i.MOBILE_EXCESS_BILL, :APP_USER)

         LOG ERRORS INTO ERR$_MOBILE_BILL_EXCESS_EXCEL REJECT LIMIT UNLIMITED;

UPL Structure in Apex:  The sample structure will be :

http://www.gplhrms.com:8080/ords/f?p=100:1:3211767652771:::::

PC Address/Port Number/ords/f?p=Application ID/Page Number/Session ID

Insert data into multiple table using Interactive Grid in Oracle Apex:

Answer: Yes, Possible to insert multiple table from interactive Grid 

but we should get rid of the automatic row processing (created by Apex, by default) and write your own PL/SQL code which would do inserting/updating. Something like an instead-of trigger on a view. Example:

Begin 
  case when :apex$row_status = 'C' then 
         insert into emplocations (employee_id, location_id)
         values (:employee_id, :location_id);
       when :apex$row_status = 'U' then
         update emplocations set
           employee_id = :employee_id,
           location_id = :location_id
           where rowid = :rowid;
       end;
End;  

Instead of Trigger on Views: INSTEAD OF triggers control insert, update, merge, and delete operations on views, not tables. They can be used to make no updateable views updateable and to override the default behavior of views that are updateable. Example:

  CREATE [OR REPLACE] TRIGGER trigger_name

   INSTEAD OF operation (Insert/Update/Delete)

   ON view_name

   FOR EACH ROW

   BEGIN

     ------------The code goes here...

   END;

Copy Item Value From One Item to Another Item In Oracle Apex:

We can copy one apex page item value to another apex item by using Javascript. The code will be like this:

First, you need to create Dynamic Action on the selected item. The Action of Dynamic Action will be to execute JavaScript code.

var x=$v("p2_name");

$s('P2_TARGETED_ITEM',X);

The Execute option will be=> Fire on initialization=>on

Add the check box in the classic report: To add a checkbox to the Classic report, use the apex_item.checkbox API function.

1. select  
2.     APEX_ITEM.CHECKBOX(p_idx=>1, p_value=>DEPTNO)  as select_dept,  
3.     DEPTNO as DEPTNO,  
4.     DNAME as DNAME,  
5.     LOC as LOC  
6. from DEPT  

Then you can access checked values (for example in a page process)

1. declare  
2. v_deleted_depts number := 0;  
3. begin  
4. FOR i in 1..APEX_APPLICATION.G_F01.count  
5. LOOP  
6.   v_deleted_depts := v_deleted_depts + 1;  
7.   delete from dept where deptno = APEX_APPLICATION.G_F01(i);  
8. END LOOP;  
9. :P1_DEPTCOUNT := v_deleted_depts;  
10. end;  

How can we debug PL/SQL code in Apex?

By using APEX_DEBUG

APEX_DEBUG.ENABLE(apex_debug.c_log_level_info);

apex_debug.message(p_message=>'clip enable debug.SQL below);

Error Show From Apex:

APEX_ERROR.ADD_ERROR(P_MESSAGE => 'Sorry, You are not Eligable for Medicine Application.', p_display_location => apex_error.c_inline_in_notification );

Most developers know and already have used the $s, $v and $x functions to work with form items in Javascript. $s sets an item value, $v retrieves it and $x determines whether an item with the given name exists on the current page.

apex.item("P2_NAME").getvalue().

apex.item("P2_NAME").setvalue("Sample")

Login One application to another application without any authentication In Oracle Apex:

For this, we need to setup in both application

=> shared component=> Authentication Schema=>Current Authentication=>Session Sharing

 Then select=> Type is workspace sharing or custom

Custom Tooltip Create In Apex Interactive & Classic Report

1. Create Hidden Item and Dynamic Action on Report Region.

2. HTML Expression

<span class="id-col" pk="#EMPLOYEE_ID_NUM#">#EMPLOYEE_ID#</span>

3.PLugin Sql:

SELECT
   '<script>alert("xss")</script><h4>Basic Information of : ' ||
    EMPLOYEE_NAME ||'</h4>'
  
       ||'<table>   
       <tr> 
           <td> Image </td>
           <td> : </td>
           <td> '||decode(nvl(dbms_lob.getlength(E.PICTURE),0),1,null,'<img src="'||apex_util.get_blob_file_src('P10_PICTURE',E.UNIQUE_ID)||'" height="75" width="75"/>')||' </td>
       </tr> 
       <tr> 
           <td> Name </td>
           <td> : </td>
           <td> '||E.EMPLOYEE_NAME||' </td>
       </tr>   
       <tr> 
           <td> Join Date </td>
           <td> : </td>
           <td> '||to_char(E.JOINING_Date,'fm dd-Mon-RRRR')||' </td>
       </tr>
       
       <tr> 
           <td> Phone </td>
           <td> : </td>
           <td> '||E.MOBILE_NO||' </td>
       </tr>
       <tr> 
           <td> Email </td>
           <td> : </td>
           <td> '||E.PERSONAL_EMAIL||' </td>
       </tr>
       <tr> 
           <td> Department </td>
           <td> : </td>
           <td> '||(SELECT CODE_DESC FROM CODE_MASTER WHERE HARD_CODE='DPT' AND SOFT_CODE=E.DEPARTMENT_ID)||' </td>
       </tr>
       <tr> 
           <td> Police Station </td>
           <td> : </td>
           <td> '||(SELECT LOCATION_NAME FROM AREA_INFORMATION WHERE LOCATION_CODE=E.PERMANENT_THANA)||' </td>
       </tr>
      
       <tr> 
           <td> District </td>
           <td> : </td>
           <td> '||(SELECT LOCATION_NAME FROM AREA_INFORMATION WHERE LOCATION_CODE=E.PERMANENT_DISTRICT)||' </td>
       </tr>
       
                '
       AS TOOLTIP,
    'floralwhite' AS BACKGROUNDCOLOR
    
    
FROM
     EMPLOYEE_INFORMATION E WHERE UNIQUE_ID=:P7_NEW

Database Connection & DMP File Restore In Oracle Cloud Autonomous database

 1. First we need to login Oracle Cloud Autonomous Database. 

2. Need to create Bucket to store DMP file that we want to restore in database.
            =>Storage----Bucket---Create Bucket
            => From Bucket Object Select Upload Button to Upload DMP File.
            => Create ATP (Autonomous Transaction Processing) Database.

3. Connect Autonomous Database Using SQL Developer :
            => From Autonomous Database -> DB Connection Download client credentials (Wallet).
           => From SQL Developer Select New Connect & Select Connection Type is Cloud Wallet.
           => IN SQL Developer DBA Section select Import Jobs from Data Pump.

4.Important SQL Command :

        =>create user mrhs  identified by Ripon#123456 account unlock;
        =>create user hpp  identified by Ripon#123456 account unlock;

        =>alter user mrhs identified by Ripon#123456 account unlock;
        =>alter user HRMS identified by Ripon#123456 account unlock;

        =>GRANT CONNECT TO HRMS;
        =>GRANT create table  TO HRMS;
        =>DROP USER MRHS CASCADE;

Oracle Database Important Topics

 

1. What are the components of the physical database structure of the Oracle database?

Components of the physical database structure are given below.

• One or more data files.
• Two or more redo log files.
• One or more control files.

2. What are the components of logical database structure in Oracle database?

Components of the logical database structure.

• Tablespaces
• Database's schema objects

3. What is a tablespace?

A database contains Logical Storage Unit called tablespaces. A tablespace is a set of
related logical structures. Actually, a tablespace group related logical structures together.

4. What is a SYSTEM tablespace and when it is created?

When the database is created in Oracle database system, it automatically generate a
SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains data dictionary
tables for the entire database.

5. What is an Oracle table ?

A table is basic unit of data storage in Oracle database. A table contains all the
accessible information of a user in rows and columns.

6. What is bulk copy or BCP in Oracle?

Bulk copy or BCP in Oracle, is used to import or export data from tables and views but it does not copy structure of same data.

The main advantage of BCP is fast mechanism for coping data and you can also take the backup of data easily.

7. What is the relationship among database, tablespace and data file?

An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store whole data of databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical structure that confirm with the operating system in which Oracle is running.

8. What is a snapshot in the Oracle database?

A snapshot is a replica of a target master table from a single point in time. In simple words you can say, snapshot is a copy of a table on a remote database.

9. What is the difference between hot backup and cold backup in Oracle? Tell about their benefits also.

Hot backup (Online Backup): A hot backup is also known as online backup because it is done while the database is active. Some sites can not shut down their database while making a backup copy, they are used for 24 hour a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as an offline backup because it is done while the database has been shutdown using the SHUTDOWN normal command. If the database is suddenly shutdown with an uncertain condition, it should be restarted with RESTRICT mode and then shutdown with the NORMAL option.

For a complete cold backup, the following files must be backed up.

All data files, All control files, All online redo log files(optional) and the init.ora file (you can recreate it manually).

10. How many memory layers are in the Oracle shared pool?

Oracle shared pools contain two layers:

1. library cache
2. data dictionary cache

11 What is a save point in the Oracle database?

Save points are used to divide a transaction into smaller parts. It allows the rolling back of a transaction. A maximum of five save points are allowed. It is used to save our data. You can roll back whenever you encounter an error from where you save your SAVEPOINT.

12. What is a hash cluster in Oracle?

Hash cluster is a technique to store data in a hash table and improve the performance of data retrieval. A hash function is applied to the table row's cluster key value and stored in the hash cluster.

13. What are the various Oracle database objects?

Tables: This is a set of elements organized in a vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is a virtual table derived from one or more tables.

Indexes: This is a performance-tuning method to process the records.

Synonyms: This is a name for tables.

14. What is the difference between pre-select and pre-query?

A pre-query trigger fire before the query executes and fire once while you try to query. With this trigger's help, you can dynamically modify the where clause part.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Pre-query trigger fires before the Pre-select trigger.

15. What are the different types of modules in Oracle forms?

Following are the different modules in Oracle forms:

• Form module
• Menu module
• Pl/SQL Library module
• Object Library module

16. Can you create a synonym without having a table?

Yes. We can create a synonym without having a base table.

17 What types of joins are used in writing SUBQUERIES?

• Self join
• Outer Join
• Equi-join

18. What is the usage of the control file in Oracle?

In Oracle, the control file is used for database recovery. The control file is also used to identify the database and redo log files that must be opened for database operation to go ahead whenever an instance of an ORACLE database begins.

19. What is a synonym?

A synonym is also known as an alias for a table, view, sequence or program unit.

20. What are the different types of synonyms?

There are two types of synonyms or alias:

Private: It can only be accessed by the owner.

Public: It can be accessed by any database user.

21. What is the usage of synonyms?

• Synonyms can be used to hide an object's real name and owner.
• It provides public access to an object.
• It also provides location transparency for a remote database's tables, views or program units.
• It simplifies the SQL statements for database users.

22. How do you store pictures in a database?

You can store pictures in a database using the Long Raw Data type. This data type is used to store binary data for 2 gigabytes of length. However, the table can have only one Long Raw data type.

23. What is the BLOB data type in Oracle?

BLOB data type is a data type with varying length binary string. It is used to store two gigabytes memory. For the BLOB data type, the length needs to be specified in bytes.

24. What is the difference between TRANSLATE and REPLACE in Oracle?

Translate is used to substitute a character by character while Replace is used to substitute a single character with a word.

25. What are the different types of database objects?

A list of different types of database objects:

• Tables: This is a set of elements organized vertically and horizontally.
• Tablespaces: This is a logical storage unit in Oracle.
• Views: It is a virtual table derived from one or more tables.
• Indexes: This is a performance-tuning method to process the records.
• Synonyms: This is a name for tables.

26. What is the usage of Save Points in the Oracle database?

Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There is a maximum of 5 save points allowed in the Oracle Database. Whenever an error is encountered, it is possible to roll back from where the SAVEPOINT has been saved.

27. What is the difference between a post-database commit and a post-form commit?

The post-database commit trigger is executed after Oracle forms issue the commit to the finalized transaction while the post-form commit is fired during the post-and-commit transactions process, after the database commit occurs.

28. What is Logical backup in Oracle?

Logical backup is used to read a set of database records and write them into a file. An Export utility is used to take the backup, while an Import utility is used to recover from the backup.

29. What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. It is done by creating a group of log files together. This ensures that LGWR automatically writes them to all the members of the current online redo log group. If the group fails, the database automatically switches over to the next group. It diminishes the performance.

30. What is the meaning of recursive hints in Oracle?

The number of times a dictionary table is repeatedly called by various processes is known as a recursive hint. The recursive hint is occurred because of the small size of the data dictionary cache.

31. What are the limitations of the CHECK constraint?

The main limitation of the CHECK constraint is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub-queries.

32. What is the use of the GRANT option in the IMP command?

GRANT is used to import object grants.

33. What is the use of the ROWS option in the IMP command?

The ROWS option indicates whether the table rows should be imported.

34. What is the use of the INDEXES option in the IMP command?

The INDEXES option is used to determine whether indexes are imported.

35. What is the use of IGNORE option in the IMP command?

The IGNORE option is used to specify how object creation errors should be handled.

36. What is the use of the SHOW option in the IMP command?

The SHOW option specifies when the value of show=y, the DDL within the export file is displayed.

---

37. What is the use of the FILE param in IMP command?

FILE param is used to specify the name of the export file to import. Multiple files can be listed, separated by commas.

38. What are the extensions used by Oracle reports?

Oracle reports are used to make business enable with the facility to provide information on all levels within or outside in a secure way. Oracle report uses REP files and RDF file extensions.

39. How does ROWID help in running a query faster?

ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row and results in a faster query.

RowNum: RowNum is pseudo column. A pseudo column means a column which not exist the table physically, but we can use this in any select statement. Roenum is used to get the row number of the select query. if we have 10 records in the SQL query, then rownum will be 1 to 10 for this query.

40. What are database links used for?

Database links are created in order to form communication between various databases or different environments like test, development and production. The database links are read-only to access other information as well.

Different Between Oracle & SQL Server :

• Oracle supports star query optimization, while SQL server doesn’t offer query optimization.
• In oracle, values do not change before committing, whereas in SQL Server, values are changed even before committing.
• Oracle allows rollback during the transaction process, whereas SQL server doesn’t allow rollback in the transaction process.
• Oracle supports many “Schemas” with the instance, whereas SQL server offers “Schemas” within each user database.
• Oracle allows database, full, file-level, incremental & differential backups, on the other hand, SQL server allows full, partial, and incremental backups.
• Oracle uses both “after” and “before” triggers, whereas SQL server mostly uses only “after” triggers.
• Oracle allows Query optimization.
• Values do not change before committing.
• Oracle allows rollback during transactions.
• Oracle use after and before trigger, but the SQL server allows only after the trigger.

Mutating Trigger error :

The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger.

A mutating table error (ORA-04091) occurs when a row-level trigger tries to modify or select data from the table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). If the  DML operation fire a trigger and from within the body of trigger if we are trying to read or write the same table then we will get a mutating trigger error. Mutating trigger will get only row level trigger and it is not from statement level trigger.

How to solved Mutating trigger ?

To avoid mutating error first we need to declare global variable within package specification. Then we need to create a statement level trigger and put the select statement into this trigger and value assign into global variable. After that we need to create low level trigger and use this global variable instead of select statement.

Beside this we can also avoid mutating error using compound trigger.

Compound Trigger: A compound trigger combines of the following triggers into one single trigger: 
1.Before statement trigger
2.Before row trigger
3.After row trigger
4.After statement trigger

We can find the trigger name from USER_TRIGGERS Table.

What is locking in SQL? Describe its types?

Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are: 

• Implicit Locking: This occurs for all SQL statements except SELECT.
• Explicit Locking: This can be done by the user manually.

Further, there are two locking methods:

1. Exclusive: Locks out other users
2. Share: Allows other users to access

Analytical Function :

Analytic functions calculate an aggregate value based on a group of rows. Analytical functions are 

aggregate functions, but we can apply the aggregate function on the table or the selected area in the where clause. The analytical function is an aggregate function but offers additional stuff.

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

Different Types of Join :

Inner Join: Inner Join return only matching record.

Left Outer Join: Inner Join+fetch any additional records from the left table which is not present

in the right table.

Right Outer Join: Inner Join+fetch any additional records from the right table which is not present

in the left table.

Full Outer Join: Inner Join

               +Fetch any additional records from the left table which is not present in the right table.

               +Fetch any additional records from the right table which is not present in the left table.

Cross Join: Every record from the first table will try to match every record from the second table.

Natural Join: Natural join is not recommended. There is no need to mention the joining condition. It will

automatically calculate the joining column name if it is the same column.

If the same column name exists within two tables, then it will return Inner Join, and if the same.

If the column does not exist within two tables, it will return Cross Join.

Self Join: Usually a join happens between two tables which are based on certain conditions when instead of two different tables, we use the same table for joining which is referred to as a self join.

Index In Oracle :

Indexes are database objects which can be created on a table or table cluster and, if applied correctly, can

speed up data retrieval in certain cases. Indexes are database object which actually resides in memory.

The index is useful when we use 5%-10% of the data.

Query Explain: EXPLAIN PLAN FOR (Add This before select statement).

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

Analyze Index: analyze index idx_ind(index name) validate structure

select height, name,blks,br_blks from index_status;

Types of indexes: 1. B-Tree Index 2.Bitmap Index 3. Function-Based Index 4. Reverse key Index 5. Composite Index

1.BTree Index: Default Index. B-Tree shorts, for balanced trees, are the most common type of

database index. If we don't specify any keyword while creating an index, it will go ahead and

create a B-tree Index. By default, Oracle creates B-Tree Index. B-tree index scan types are-

Range Scan, Unique Scan, Full Scan, Full Scan, Fast full scan.

2. Bitmap Index

Oracle uses two methods to access the data when we submit a query. One is a Table scan, and

another is an Index scan. If Index is not there, then oracle uses a Table scan. Table scan searches each

and every record of the table.

Two types of Index scans -Index full scan and Index fast full scan. If all the columns that are part of

our select query should be part of our composite index, and if we use order by clause, then we should

use the column as taken in my composite index. Also, it will be a fas1.t full scan.

Different between B-Tree and BITMAP Index:

1. BTREE Store data in tree format. BITMAP indexes are stored as dimensional arrays.

2. BTREE Index is created on high cardinality columns. BITMAP  Index is created on 

    low cardinality (low number of distinct values in a column. Ex-Male, Female ).

3. we should create a BITMAP index only if the data is being modified by a single proc

BTREE Reverse key Index :

Does creating primary key constraints always create an index automatically?

When we create a constraint oracle will create an index if the column is not indexed already if the column is already indexed then oracle will not create a new index.

Will dropping constraints will drop the underlying index? 

Yes, but not always. Whenever we are dropping a constraint the associated index will get automatically dropped provided that index was created as part of the constraint creation otherwise the index will not drop.

Can a primary key create a NONUNIQUE index?

Normally when we create a primary key constraint oracle will automatically create a unique index. But in some scenarios, a primary key will create a non-unique index. By using 

DEFERRABLE NOVALIDATE keyword we can create a primary key that will create a non-unique index.

Different between Constraints & Index :

1. Unique Constraints are for data integrity, whereas Unique Index is for performance improvement.

Table Partition:

Partition subdivided our table into smaller pieces. Any table bigger than 2GB may be a possible contender for partitioning. So the first thing that partition gave us was an improvement in performance. The second thing is data manageability.

Local Index: If we create an index that is not part of the partitioning key, we should consider a local index.

Global Index: If we create an index which is part of the partitioning key, then we should consider a global index.

List Partition:

Range Partition:

Hash Partition: If we want to distribute data equally among all the nodes, then we can create a hash partition.

Composite Partition :

Advantages of Partition :

Performance: 

    1. Partition pruning 

    2. Partition wise join

Manageability: 

    1. Divide and conquer approach 

    2. Faster backup and restore

    3. Low storage usage

Example: CREATE TABLE TRANSACTION_DEMO (TRANSACTION_ID NUMBER,TRANSACTION_TYPE VARCHAR2(30),AMOUNT NUMBER, TRANSACTION_DATE DATE); CREATE UNIQUE INDEX IDX_TRAN_ID ON TRANSACTION_DEMO(TRANSACTION_ID); SELECT * FROM TRANSACTION_DEMO; INSERT INTO TRANSACTION_DEMO SELECT ROWNUM,'CREDIT',FLOOR(DBMS_RANDOM.VALUE(90,9000)),TRUNC(SYSDATE) FROM DUAL CONNECT BY LEVEL <=1000000; SELECT SEGMENT_NAME,BLOCKS,BYTES/1024/1024 AS MB FROM USER_SEGMENTS WHERE SEGMENT_NAME='TRANSACTION_DEMO'; ANALYZE INDEX IDX_TRAN_ID VALIDATE STRUCTURE; SELECT * FROM INDEX_STATS;

Dual Table: When the Oracle database is installed, then it will automatically create a DUAL table. It is

created on the sys user and available for all users. The column of this table is DUMMY value of this column is

X and Data Type is Varchar2(1);

Different between Delete, Truncate and Drop:

Delete: Can delete partial data.DML Operation. Recover using rollback.No impact on dependent objects.

Deleted data is temporary store in the redo log.

Truncate: Remove the entire data.DDL Operation. Data lost forever.No impact dependent objects.

Drop: Removes the object.DDL Operation. Data can be recovered (Flashback option).

Dependent objects will have to be created.

Different between Procedure and function: Functions are normally used for computations, while

Procedures usually execute business logic. The procedure can't be called from the SQL statement but

The function can be called from an SQL statement.

Can we use OUT and INOUT parameters in the function?

Yes, we can use OUT and INOUT parameters in a function. But there is a restriction. This type of function we can't use within select statements. We can call this type of function within PL/SQL block.

Can the function return more than one value?

From a function, we can have only one return statement. But we can send more than one value as a

part of the Return. That means if we want to return more than one value, we can use the collection and

Ref Cursor as a return statement.

Data Type :

Scalar Data Type: Contains only one value.

Composite Data Type :

Collection: A collection is an ordered group of logically related elements. In a collection, the internal component always has the same data type.

Records: The internal component can have different data types in a record.

SQL: Structured Query Language. SQL is used to communicate with the database. There are different types of SQL.,

DDL: Data Definition Language. Create, Alter, Drop, Truncate. All the DDL are auto-commit.

DML: Data Manipulation Language. Insert, Update, Delete, Merge.

DCL: Data Control Language. Grant, Revoke.

TCL: Transaction Control Language. Commit, Rollback, Savepoint.

DRL: Data Retrieval Language. Select.

Table Function & Pipelined Function: Both Table Function & Pipelined Function returns collection to the caller, and the function can be used as if it's a table.

The Table function loads all the values into the collection, and finally, the collection is returned to the caller.

The pipelined function returns the value as and when the value is ready to send back to the caller.

Commit Inside Trigger: Normally, we can't use Commit within the trigger. But if we use Pragma autonomous_transaction within the trigger, we use Commit within the trigger.

Calling a function having DML Statement in a Select statement: Normally, we can't call a function within a select statement with a DML statement. But if we use Pragma autonomous_transaction within the function, we can call this function within the select statement.

How can we get unique records without using Distinct in oracle?

We can use unique keywords in the select statement. (select unique roll, name from sts). We can use group by clause. We can also use unique records by using the Union keyword. We can also use intersect and Analytical functions (ROW_NUMBER(), RANK()).

Data Integrity: Data integrity adherence to business rules.

Delete duplicate records:
1. By using ROWID we can delete duplicate rows. For defining specific rows we can use the aggregate function max or min. Example :
select  * from emp where rowid not in(select max(rowid), empno, empname from emp 

group by empno, empname);

2. By using co-related subquery we can also delete duplicate rows.

Cursor: A cursor is a private memory area where data is stored temporarily and processing a specific select or DML statement.

LISTAGG: By using the LISTAGG function we can convert the Row value into a column value. But we can convert only 4000 bytes. If the value is greater than 4000 bytes then it will give an error. By using ON OVERFLOW TRUNCATE we can avoid this error. Syntex:

LISTAGG(EMP_NAME,',') GROUP WITHIN (ORDER BY EMP_NAME) 

LISTAGG(EMP_NAME,',' ON OVERFLOW TRUNCATE) GROUP WITHIN (ORDER BY EMP_NAME) 

PIVOT: By using PIVOT function we can convert row value into column value.

Example: SELECT * FROM(

SELECT EMPLOYEE_ID,FIRST_NAME,JOB_ID,SALARY,DEPARTMENT_ID FROM EMPLOYEES)

PIVOT(MAX(SALARY),COUNT(1) TOTAL_EMP FOR DEPARTMENT_ID IN(10,20,30,40,50,60,17));

IDENTITY COLUMN: If we use the IDENTITY  keyword in table creation then oracle will automatically generate an auto number. There are three options available for IDENTITY.

1. Generated always as identity (not allowed manually insert)

2. Generated by default as identity(It can allow manually insert value but not allow null value)

3. Generated by default on null as identity(It will allow null value but if we insert null value then it will automatically insert value)

Regular Expression: There are five Regular Expression support Oracle.

1.REGEXP_LIKE

2. REGEXP_COUNT

3. REGEXP_INSTR

4. REGEXP_REPLACE

5. REGEXP_SUBSTR

How SQL Statement works internally in Oracle?

When the Oracle database server receives the SQL statement the database server processes the statement in a sequence of steps:

1. Syntax Check: Check the SQL statement for syntactic validity(All keywords are present).

2. Semantic Check: (Priviledge check,Object existence,Dependancy Check) A semantic check determines whether objects used in the SQL statement is meaningful, for eg, whether accessed tables and column exists in the database. If the mentioned table or object is invalid or not present in the database the Oracle database throws an appropriate exception to the caller.

3. Shared Pool Check: In case if exactly the same statement was previously executed by Oracle, then all the execution-related information will be available in the shared pool memory then oracle will reuse the same information for executing the statement again otherwise, oracle will proceed with the next set of processes.

4. Optimization: Generate multiple execution plans, and compare and pick the optimal plan for execution.

5. Row source generation: Oracle will generate the step wise execution plan which will be given as an input to the executor.

6. Execution : Executor Execute the statement and send back the result to user.

Data Type In Oracle: Three types of datatype in oracle. Scalar ,Composite & REF Cursor.

 Type Declaration:
  Record: type  record_type of record(Name varchar2(10),ID number,salary number).--V_RECORD  record_type.

  Varray: type varray_type is varray(10) of varchar2(10). v_varray varray_type .

  Nested Table : type nested_table_type is table of varchar2(20).
  Associated Array : type associated_array_type is table of varchar2(10) index by varchar2(10).

  REF Cursor : Type ref_cursor_type IS REF CURSOR.---v_ref_cursor ref_cursor_type .

                          Type ref_cursor_type SYS_REFCURSOR.

  %ROWTYPE : Type type_row_type is table of EMP%ROWTYPE;

What is cursor?

Answer: A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement. There are two types of cursor. Implicit and Explicit.

Implicit cursor is a session cursor that is constructed and managed by oracle. PL/SQL opens an implicit cursor every time when we run a SELECT or DML statement. we cannot control implicit cursor,but we can get information from its attributes.

Explicit cursor is a session cursor that is contruct and managed by user.

Cursor Attributes are ISOPEN,FOUND,NOTFOUND,ROWCOUNT

What Is REF Cursor?

Answer: A REF CURSOR is a data type in the Oracle PL/SQL Language. REF Cursor are used for dynamic SQL. we will be able to dynamically assign a different and completely different set of SQL at the runtime. There are two different ways by which we can define a REF cursor,-One is using TYpe definitation and another one is using sys_refcursor. SYS_REFCURSOR is oracle predefined type. SYS_REFCURSOR is always weakly type.

There are two types of REF cursor, Strongly Type and Weakly type REF Cursor. In Strongly type REF Cursor have Return Type but weakly type REF cursor does not have a Return Type.

DBMS_ERRLOG:

DBMS_ERRLOG.CREATE_ERROR_LOG(DML_TABLE_NAME=>'EMPLOYEES',

                                                        ERR_LOG_TABLE_NAME=>'ERROR_EMPLOYEES);

Insert Statement: INSERT INTO EMPLOYEES SELECT * FROM EMPLOYEES_TEMP

LOG ERRORS INTO ERROR_EMPLOYEES REJECT LIMIT UNLIMITED;

Virtual Column: A virtual column in oracle is a column in table whose value is calculated form other column in the same table instead of being stored in the database.

• Indexes can be defined on virtual column and these are similer to function based index.
• Virtual column can be referenced on any select statement such as where clause, however DMLs are not allowed on virtual column.
• Virtual column cannot refer to other virtual column.It should refer to the columns of the same table.
• Virtual column can be used as partition key.

Interview questions for SQL Developer:

1) what is the query optimization technique in Oracle?
2) What are the types of SQL joins, and when would you use each one?
3) what are the global temporary tables?
4) what are the constraints? what are its types?
5) Diff between union and union all?
6) Query to display an even number of records?
7) Query to display the employee whose name starts with S?
8) Query to display the name of the employee, which contains M in any position in their name?
9) How to delete duplicate records?
10) What is an index, and how can it improve query performance?
11) What is normalization? Explain the first normal form and second normal form?
12) Diff between varchar and varchar2?
13) Write a query to display the current date?
14) Refresh types in materialized view?-dbms_mview.refresh
15) Diff between a clustered and a non-clustered index in SQL?
16) Diff between delete and truncate?
17) What are views? Types of views?
18) Explain the candidate key, primary key, and super key?
19) Explain the concept of a self-join in SQL and when it might be used?
20) What is the dual table?
21) What is Explain plan?
22) What are Oracle hints?
23) Different types of SQL commands?
24) Diff between exists and in?
25) Oracle 11g, 12c, 19c features?
26) What are window functions? Explain with examples?
27) What is synonyms? Types of synonyms?
28) Diff between decode and case?
29) Diff between view and materialized view?
30) What is inline view?
31) What are the analytical functions?
32) Diff between left outer join and right outer join?
33) What are the different types of subqueries in SQL and how are they used?
34) what are the joins? types of joins?
35) what is diff between DDL and DML?
36) what are the union, unionall, intersect, minus operators?
37) how to find list of the indexes for a given table?
38) what are the benefits and drawbacks of indexes?
39) what is function based index?
40) what are partitions? explain diff types?
41) what is substr() , instr() functions?
42) query to find nth highest salary?
43) diff between nvl, nvl2 and coalsce function?
44) diff between replace and translate?
45) what is with check option clause in view? what is force view?
46) Explain on delete cascade?
47) what is pivot function?
48) what is explain plan?
49) how to update complex view?
50) how to get unique records without using distinct clause?