aak

• Sep 15th, 2016

No, You can add the proc in body and not declare it in the spec.. this is ok as it would mean that that proc is a private procedure but visa versa is not possible. It would give compilation error.

Sudhanshu

• Jul 26th, 2016

"oracle8 Create or replace package Test_pkg as 2 procedure Proc1; 3 end; 4 / Package created create or replace package body Test_pkg as 2 3 procedure proc1 is...

Answer Question  Select Best Answer

Delete duplicate records without using sub query? Is That possible

Aak

• Sep 15th, 2016

Use a temp table, store distinct rows. Either delete the old table and rename this new table with the old table name or truncate old table and insert rows from temp table to the old table.

kadari ashok

• Jul 14th, 2016

We can do through co-related sub query

Answer Question  Select Best Answer

vinodkumar

• Jul 14th, 2016

In cursor we dont have the facility of moving directly to a nth record.
We have to loop through the cursor to reach the nth record but even though after reaching you cant move backward.
Its good to use PL/SQL tables for this kind of situation

Habib Ali

• May 10th, 2016

Cursor is bi-directional.... You have to start either from FIRST or LAST record. You can however access nth record directly too, if not required by any logic

Answer Question  Select Best Answer

How to optimise your pl/sql code? Not using explain plan which is for tuning the quey

Nikhil Inamdar

 Profile Answers by Nikhil Inamdar Questions by Nikhil Inamdar

• Jul 8th, 2016

You can use DBMS_PROFILER to check performance bottlenecks and then change the code where the PLSQL code is taking more time and improve performence.

For more details go through below link.
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_profil.htm

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

The best technique used is to avoid repeatative coding by putting repeated logic in a subroutine/function. Save the DML operations after a specific interval or records insertion/updation/deletions , depending on your archive log size.

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

PRAGMA is instruction to compiler
AUTONOMOUS TRANSACTION means running an SQL transaction inside a PLSQL block of code independently i.e Making changes to DB regardless of other transactions & their locks/latches

sujata

• Jan 19th, 2016

Pragma AUTONOMOUS_TRANSACTION instructs the compiler to treat the PL/SQL block following the pragma as autonomous (independent) from the calling transaction. Pragma can be applied on:- 1) Anonymous PL...

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

Normal Cursor > Your SELECT Query is determined already before BEGIN i.e. Table, Column Names
Ref Cursor > Your SELECT Query is determined at runtime after BEGIN i.e.Table, Column Names

syed_ansar

• Oct 18th, 2006

We can use Ref cursor as an IN OUT parameter .

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

Copy your REFCURSOR output to a RECORD datatype, and LOOP through it for each record

gouthami kodangal

• Aug 13th, 2006

by making use of sql%rowfound. 

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

NOCOPY suggests the PLSQL compiler to use Original Variable values instead of creating Temporary Variables & passing it on. Very useful, if your variable is an Array or other composite data type

ahamed79

 Profile Answers by ahamed79 Questions by ahamed79

• Mar 7th, 2007

Pls try this link:http://www.databasejournal.com/features/oracle/article.php/1558021Cheers- Faizal

Answer Question  Select Best Answer

What are the uses of Cursor?
What are its advantage and disadvantage?

Habib Ali

• May 10th, 2016

Advantage: Gets multiple-data inside the buffer cache, for faster DML operations

Disadvantage: Consumes memory, has to be freed properly to avoid Buffer cache congestion

WebSpider

• Feb 11th, 2016

Performance is reduced using cursor

Answer Question  Select Best Answer

Is Dual Table updatable? If we update, will it impact the health of the database?

Habib Ali

• May 10th, 2016

DUAL table is owned by SYS, and is updatable only under SYS account. But never make any changes to DUAL, as it is frequently used by Oracle optimizer to test Execution Plans.

vishal

• Mar 29th, 2016

No we can't update dual table because it is a dummy table. But it is possible to create a table named dual and you can insert, update and delete that table.

Answer Question  Select Best Answer

Habib Ali

• May 9th, 2016

Stand Alone Proc: Block of PLSQL code but not defined in the data dictionary, hence not reusable

Stored Procedure: Block of PLSQL code Defined in data dictionary, implements reusability. Check in user_source table

Praveen kumar

• Jan 8th, 2016

Both are same

Answer Question  Select Best Answer

Habib Ali

• May 9th, 2016

A view (typically a complex query containing Joins & Aggregates) which stores its result in a DB Table. Can be cached for faster performance. Its highly used in Data Warehousing Environments, but use it carefully as it has lot of restrictions based on clauses and the underlying query

sachin rasal

• Dec 11th, 2015

Materialised view - A table on a disk that contains the result set of a query

Non-materiased view - A query that pulls data from the underlying table

Answer Question  Select Best Answer

What are the advantages of packages over standalone procedure ?

Habib Ali

• May 9th, 2016

Packages can be cached inside the SGA, resulting in faster execution than a normal stored procedure.

abhijit musale

• Feb 18th, 2015

You can wrap/encrypt the package code .

Answer Question  Select Best Answer

Kiran Birajdar

 Profile Answers by Kiran Birajdar Questions by Kiran Birajdar

• Mar 21st, 2016

EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,An error was encountered - ||SQLCODE|| -ERROR- ||SQLERRM); END; Or you could log the error to a table using the SQLERRM function ...

devi

• Sep 25th, 2007

You can print the 'sqlcode'?as followsException?When others thendbms_output.put_line('error code is'||sqlcode)end;?

Answer Question  Select Best Answer

Pritty

• Feb 29th, 2016

The GROUP BY keyword helps to retrieve the unique values without using DISTINCT keyword.

Malyadri M

• Jun 22nd, 2015

Code
 

Answer Question  Select Best Answer

vivek

• Feb 23rd, 2016

1. Install ords.war (Download from Oracle site)
2. Configure Oracle rest data services. Using ords.war file. $ ords.war install advanced. (It will ask for database credentials)
3. Copy the images under i.war file.
4. Deploy the war files in weblogic.
5. Access the application

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | May 6th, 2008

---

A trigger may be a
1. DML Trigger on tables
2. Instead of triggers on views
3. System triggers on database or schema

Based on the way it executes statements  triggers are of two types
1. Statement leve trigger
2.  Row level trigger

A trigger fires for three actions
1. Insert
2. Delete 
3.Update

and the trigger can the fired
1. Before action
2. After action.

Pradeep

• Feb 20th, 2016

Basically 5 main types in Oracle Triggers
1. DML Trigger (3 statement * 2 timing * 2 level = 12 types).
2. DDL Trigger.
3. Instead of Trigger (Fired Complex View).
4. System Trigger.
5. Compound Trigger (Introduced by 11g)

tarini sankar das

• Feb 7th, 2014

Actual answer is we have 14 types of triggers in pl sql. For statement level :- 1-before insert 2-before update 3-before delete 4-after insert 5-after update 6-after delete For row level :- 7-before ...

Improve Answer

How to optimize SQL Statements?

Sudhir Kumar

• Jan 20th, 2016

Try to make use of mqts or temporary tables if possible ..try to transfer the conditions from non key columns to key columns

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

update the statistics of all the tables used in the query. Check the explain plan. Check whether query is using the existing indexes. If required add/modify new indexes. Check for the sequence of cond...

Answer Question  Select Best Answer

1. How to load excel data into oracle database by using sql loader?
2. What are the ways to load data from excel file in oracle database ?

Naresh Bandla

• Dec 1st, 2015

You can upload the Excel file into tables by many approaches, I will explain here in two methods. 1. Loading data using Loader programs: Convert your excel file into CSV file and create control as pe...

ashay

• Nov 13th, 2015

Best and most easy way is to use an external table. Keep the file in designated location and file will automatically loaded into table every time you put a new file or update the file. Create trigger on the given table to load the data into respective tables.

Answer Question  Select Best Answer

What are the advantages of bulk binding in PL/SQL?

sachin rasal

• Nov 12th, 2015

A new feature called "bulk binds" was added to PL/SQL back in Oracle 8i. Bulk binds enable a PL/SQL program to fetch many rows from a cursor in one call instead of fetching one row at a time. Bulk bin...

amala

• Jun 24th, 2015

It retreives multiple rows with a single fetch...It increases the performance by reducing no.of context switches

Answer Question  Select Best Answer