How can we find the select statement is indexed or not?

Read Best Answer

Editorial / Best Answer

promisinganuj  

 Profile Answers by promisinganuj Questions by promisinganuj

• Member Since Sep-2009 | Sep 13th, 2009

---

1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.

promisinganuj

 Profile Answers by promisinganuj Questions by promisinganuj

• Sep 13th, 2009

1. using AUTOTRACESQL> SET AUTOTRACE ON TRACEONLYSELECT *    FROM emp WHERE emp_name = 'ABC';2. Using EXPLAIN PLANSQL> EXPLAIN PLAN forSELECT *    FROM...

praveen_geek123

 Profile Answers by praveen_geek123 Questions by praveen_geek123

• Sep 4th, 2008

Use "Explain Plan" command

Improve Answer

You are developing a PL/SQL block designed for bulk data operations. When attemping to store table data for multiple rows in a PL/SQL variable, which one of the following choices identifies the mechanism best suited for this task (choose one)? A. cursorB. varchar2C. recordD. table of records

rahulg5211

 Profile Answers by rahulg5211 Questions by rahulg5211

• Oct 25th, 2010

Table of record type is best option to perform this operation.becouse first thing pl/table has unlimited size.second thing for fetching bunch of record into pl/table,besides  of INTO clause we ne...

shukla.prashant76

 Profile Answers by shukla.prashant76 Questions by shukla.prashant76

• Apr 28th, 2010

Answer is D. We need multiple rows on a variable  so we use here Table of records .it uses just like as collection of records.Cursor is used for selecting multiple rows from stored table.Var...

Answer Question  Select Best Answer

I have 3 tables , same no of columns ,i want to insert whole the data from all these table into a single table,which also having same no of column and the same data type.

sateeshchandra

• Oct 14th, 2014

"sql INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, IBM) INTO suppliers (supplier_id, supplier_name) VALUES (2000, Microsoft) INTO customers (customer_i...

J. Ali (PL/Sql)

• Nov 29th, 2013

INSERT INTO DYMMY (VAL)
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (1, 2, 3))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (4, 5, 6))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (7, 8, 9))

Answer Question  Select Best Answer

Procedure also written the values using out parameter than why we need functions?

Rakesh057

 Profile Answers by Rakesh057 Questions by Rakesh057

• Jul 1st, 2014

Because procedure is pre compiler and function is not pre compiler so that the reason we go for an calculation part in function

Madhav

• Jul 1st, 2014

Procedure also written in out parameter but we can not use procedure in Sql statement.

But if we wrote function and ita cant contain DML operation then we can access this function in SQL queries.

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

 Objects  of  type  TABLE are called "PL/SQL  tables", which are modeled as (but  not the same as) database tables,  PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.  Cursors

Read Best Answer

Editorial / Best Answer

rkanth18  

 Profile Answers by rkanth18 Questions by rkanth18

• Member Since Sep-2005 | Nov 27th, 2005

---

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -2³¹-1 to 2³¹-1, so you have an awful lot of rows with which to work

 

neerajalatha

 Profile Answers by neerajalatha Questions by neerajalatha

• Jul 5th, 2010

A PL/SQL table is a database table having one column and rows are stored not in predefined order. Associative arrays and nested tables comes under category of PL/SQL. 

babu.sridharan

 Profile Answers by babu.sridharan Questions by babu.sridharan

• Jul 14th, 2008

Example for PL/SQL table:==================In the below block "typlsql" and "ty_plsql2" are PL/SQL tablesdeclaretype ty_plsql is record ( empno number, ename varchar2(50));TYPE ty_...

Improve Answer

Vishal R Bhadange

• Aug 12th, 2012

Yes.
Yes we can truncate some of the rows from the table, But table should be partitioned & all the rows to be deleted should be present in one single partition...

leelakrishna302

 Profile Answers by leelakrishna302 Questions by leelakrishna302

• Jun 15th, 2012

We cannot

Answer Question  Select Best Answer

Aniruddha Das

• Jun 11th, 2012

If you want to take care of performance tuning you need to take care of sub-queries as they takes a lot of time to executing in side the pl-sql blocks.

ptmich

 Profile Answers by ptmich Questions by ptmich

• May 28th, 2012

Ref cursor is used to declare a pointer variable. When you return a ref cursor, you are returning a pointer variable.

Answer Question  Select Best Answer

Abhijit Musale

• Mar 23rd, 2015

We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.

veera

• Nov 25th, 2013

It is possible by using Dynamic SQL.
By using EXECUTE IMMEDIATE.

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

Can we issue rollback, commit in the trigger body. if we issue what is the result

Read Best Answer

Editorial / Best Answer

binurajnair  

 Profile Answers by binurajnair Questions by binurajnair

• Member Since Feb-2008 | Feb 28th, 2008

---

      An autonomous transaction is an independent transaction which can be committed independent of other transactions. An autonomous transaction will be committed with out committing the other non-autonomous transactions are committed.

The following exercise will help you to understand the difference between autonomous and normal transactions.

Create a table for the exercise.

create table test_table (a varchar2(50));

Create a non-autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE non_autonomous_proc
IS
BEGIN
   INSERT INTO test_table
   VALUES      ('Non Autnomous Insert');

   COMMIT;
END;

Now execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Non Autnomous Insert');

   non_autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Non Autnomous Insert');

   ROLLBACK;
END;

Now query the table

select * from test_table;

As expected 2 rows will be retrieved

A
--------------------------------------------------
Before Non Autnomous Insert
Non Autnomous Insert

Now we will see the case of autonomous transaction.

Clear the test_table

delete from test_table;
commit;

Create an autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE autonomous_proc
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO test_table
   VALUES      ('Autnomous Insert');

   COMMIT;
END;

Now Execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Autnomous Insert');

   autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Autnomous Insert');

   ROLLBACK;
END;

And query the table

select * from test_table;

This will show only one row

A
--------------------------------------------------
Autnomous Insert

Conclusion

1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without commiting the transactions before that.

2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back, but not the autonomous transaction

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 1st, 2008

We can use commit and rollback in trigers using autonomous transactions.- An atonomous transaction is an independent transaction initiated by another transaction.- When an atonomous transaction is cal...

arbind1982

 Profile Answers by arbind1982 Questions by arbind1982

• Mar 12th, 2008

we can use commit or rolback in trigger without error by using "PRAGMA AUTONOMOUS_TRANSACTION;" on declaration sectionCREATE TRIGGER anniversary_triggerBEFORE INSERT ON employees FOR EACH RO...

Improve Answer

What is call by value and call by reference in parameters (IN, OUT, INOUT)?

Read Best Answer

Editorial / Best Answer

lisha.varghese  

 Profile Answers by lisha.varghese Questions by lisha.varghese

• Member Since Nov-2008 | Nov 17th, 2008

---

Three types of parameter modes

1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.

2) OUT parameter mode -This mode is used to return a value to the main block.The value of OUT parameter can change anywhere in the program.

3)IN OUT parameter mode-This mode is used to pass values to the calling module and return a value to the main block.The value of IN OUT parameter can change anywhere in the program.

In Call By value ,the copy of actual parameter is passed to the formal parameter,So any changes to the formal parameter doesn't affect the actual parameter.

In Call By reference,the address of actual parameter is passed to the formal parameter,so any changes to the formal parameter will change the actual parameter also,because both of them are pointing to the same memory location.
Here  no copying is required.
 
The IN parameter is passe by reference,so we can't change the value of IN parameter inside the module,It acts as a constant,But the OUT and IN OUT parameters are passed by value,we can change the values of OUT &  IN OUT paremeters

samareshp

 Profile Answers by samareshp Questions by samareshp

• Apr 17th, 2009

In Parameter:: call by reference OUT Parameter: call by valueIN OUT Parameter: call by valueif you use the hint NOCOPY  with OUT Parameter and IN OUT Parameter then ::: call by reference

lisha.varghese

 Profile Answers by lisha.varghese Questions by lisha.varghese

• Nov 17th, 2008

Three types of parameter modes1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.2) OUT parameter mo...

Improve Answer

Malar somu

 Profile Answers by Malar somu Questions by Malar somu

• Sep 10th, 2013

Implicit Cursor:- It is Oracle implicit type.. when we fetch only one record then we need to use implicit cursor i.e when ever we performing select query or any dml operations termed as implicit curso...

hira

• Aug 8th, 2013

When ever any query execute by the server in database sever itself create a implicit cursor ,which can not any control to the programmer but in case explicit cursor the programmer has to create it.and used for the data manipulation by following some rule......

Answer Question  Select Best Answer

What is the main purpose of RETURN in functions when we can calculate a value and pass it through using OUT mode in procedure or functions?

Deepika S Verma

• Mar 14th, 2014

Its a myth that function returns value whereas procedure does not, actually procedure also return value (i.e. 0 or 1 ) at the time of exiting which indicates the success or error. Whereas Function ret...

shivigupta

 Profile Answers by shivigupta Questions by shivigupta

• Oct 9th, 2012

create or replace procedure proc1 as begin dbms_output.put_line(Before Return Clause); return; dbms_output.put_line(After Return Clause); end; set serveroutput on; begin proc1; dbms_output.put_line(I...

Answer Question  Select Best Answer

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 23rd, 2011

A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. For example, if program A calls program B and program B calls program A,&nb...

KD09714

 Profile Answers by KD09714 Questions by KD09714

• Jun 26th, 2008

Example of forward declaration in package : CREATE OR REPLACE PACKAGE pkg_CBRef_Seq AS PROCEDURE sp_CBRCreateDropSeq_scd ( p_cod_CountryCode IN country.country_code%TYPE, ...

Answer Question  Select Best Answer

What is the difference between an explicit cursor and select into statement?

neerajalatha

 Profile Answers by neerajalatha Questions by neerajalatha

• Jul 5th, 2010

Select statement can return multiples rows at a time and there exists only one network round trip.Explicit cursors are memory areas which acts as a handle or pointer to context area and allows to fetc...

rashlikesu

 Profile Answers by rashlikesu Questions by rashlikesu

• May 12th, 2010

When select into query returns just one row, Oracle uses implicit cursor to execute and fetch the results for the query.But if the select statement returns more than one row, an Explicit cursor is created to fetch the data.

Answer Question  Select Best Answer

If a procedure is referring a table and if the structure of the table is changed, lets say data type of the any column is changed, then is it possible that the status of the procedure remains valid? Explain

Read Best Answer

Editorial / Best Answer

tar.goyal  

 Profile Answers by tar.goyal Questions by tar.goyal

• Member Since Aug-2010 | Aug 4th, 2010

---

It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.

This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.

tar.goyal

 Profile Answers by tar.goyal Questions by tar.goyal

• Aug 4th, 2010

It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.

anironic

 Profile Answers by anironic Questions by anironic

• Jul 21st, 2010

If PL/SQL stored SP %type and does not explicity declare a datatype, then it is quite possible that Stored procedure remains valid, even after changing the datatype in the table. Ex, you have a declar...

Improve Answer

sandeshshinde000

 Profile Answers by sandeshshinde000 Questions by sandeshshinde000

• Jun 4th, 2011

begin  DML 1  '  '  '  '  '  DML 10exceptionwhen others thandbms_output('error at location '||dbms_utility.format_error_backtrace);e...

BakulPatel

 Profile Answers by BakulPatel Questions by BakulPatel

• May 7th, 2011

To simplify  Remove exception block. Oracle will show line number with error thrownOther method:beginlocation :=1; dml1 location :=2; dml2exception    when others than&nb...

Answer Question  Select Best Answer

pankti

• Apr 6th, 2012

Nested table Unbounded ...

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 6th, 2008

A Table is a basic unit of storage in oracle.A nested table is a collection type. The main advantage of collections is instead of processing data sequentially, we may process all the date&nb...

Answer Question  Select Best Answer

Explain what is Simple Cursor and What is Parameterized Cursor? And Difference between both??

sandeep

• Feb 1st, 2018

In real time we are using more than one cursor in PL/SQL block. so in this we are sending one cursor values into another cursor so the receiving cursor must be parameterised cursor

Akash Mohan singh

• Dec 26th, 2017

A parameterized cursor are static cursor that can accept passed in parameter values when they are opened. Parameterized can only reference to its own parameter parameterized cursor cannot reference to local variable.

Answer Question  Select Best Answer