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

hmounir

 Profile Answers by hmounir Questions by hmounir

• Nov 17th, 2009

In natural joins USING clause can be used to specify only those culomns that should be used for an equijion

ravikumarzee@gmail.com

 Profile Answers by ravikumarzee@gmail.com Questions by ravikumarzee@gmail.com

• Mar 9th, 2007

USING is also used while executing Dynamic SQL.e.g.,EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'      USING dept_id;

Answer Question  Select Best Answer

manoj_lanser

 Profile Answers by manoj_lanser Questions by manoj_lanser

• Jul 20th, 2011

%type is used when we have to define the datatype of a variable similar to another existing column data-type. eg- v_id employess.employee_id%type. In this example we have make a variable v_id similar...

Radha

• Jul 14th, 2011

We use %type and %rowtype in PL/SQL programming part. %Type: The variable will assign the datatype which contains in the column of that particular table we mentioned. Ex: v_eno employee.eno%type; ...

Answer Question  Select Best Answer

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

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

Strong ref cursor return value, but seek ref cursor it can't return any value

askvenki

 Profile Answers by askvenki Questions by askvenki

• Jul 20th, 2007

           A strong REF CURSOR type definition specifies a return type, but a weak definition does not.DECLARE   TYPE EmpCurTyp IS REF ...

Answer Question  Select Best Answer

Guest

• Jan 16th, 2007

A forward declaration looks like the package definition part but is inside the bode.e.g.procedure A isbegin  B;end A;procedure B isbegin  null;end B;will not work, because during call to B B...

Donald H. Kirschman

• Dec 27th, 2006

Scalar data types store one single element of data, as opposed to composite data types.  This is not a concept that is unique to Oracle PL/SQL; rather, it is common to any programming language.&n...

Answer Question  Select Best Answer

Pixie

• Feb 18th, 2014

PL/SQL tables are PL/SQLs way of providing arrays. They are temporary tables in memory and thus are processed very quickly. They are not database tables, and DML statements cannot be issued against...

sbagai2001

 Profile Answers by sbagai2001 Questions by sbagai2001

• May 30th, 2006

DECLARE   TYPE EmpCurTyp IS REF CURSOR;   // Reference Cursor - weak    TYPE NumList IS TABLE OF NUMBER;  // Table of Number   TYPE NameList IS TABLE OF VA...

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

Varun Tiwari

• Jul 4th, 2013

Any data transaction control statement can not be used in triggers. As trigger invokes due to any DML event and the transaction may not be successfully commit later and may need to complete rollback.

Pragma_autonomous can be used as an alternate way to commit.

prashant mhatre

• Jun 13th, 2013

Can not used ...if we want to use it inside the trigger then use pragma_autonomous.

Answer Question  Select Best Answer

Chinmay

• Jun 28th, 2007

Yes,you can do by using like with orlike select * from tab1where col1 like '%back%' or col2 like '%back%'or col3 like '%back%'or col4 like '%back%'

RoulRaj

• May 11th, 2007

Hello Guna Sagar,

Please give one example i.e without knowing the column name one can write a query.

Regards
Roul

Answer Question  Select Best Answer

mariashylaja

 Profile Answers by mariashylaja Questions by mariashylaja

• Aug 12th, 2006

During bulk collect you can save the exception and then you can process the exception.Look at the below given example:DECLARE TYPE NumList IS TABLE OF NUMBER; num_tab NumList := NumList(10,0,11,1...

mariashylaja

 Profile Answers by mariashylaja Questions by mariashylaja

• Aug 12th, 2006

DECLARE TYPE NumList IS TABLE OF NUMBER; num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1); errors NUMBER;BEGIN FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS DELETE *...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

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

---

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this   table. 
It contains only one row and  one column of VARCHAR2 datatype.
Used to refer an object which does not have any pysical reference in database table.
Ex:- Select sysdate from dual.

Aditya Kumar

• Dec 17th, 2017

If it contains only one column with varchar2 data type then how can it return a date through sysdate.

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 13th, 2008

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this   table. It contains only one row and  one column of VARCHAR2 datatype.Used to refer an object which does not have any pysical reference in database table.Ex:- Select sysdate from dual.

Improve Answer

gangadharam.p

 Profile Answers by gangadharam.p Questions by gangadharam.p

• Jan 6th, 2007

hi,  when u do so the view gets error and synonym becomes invalid. If u create a table again with same name but with different cols the synonym will work but view wont.bcz view is the stored...

raghgu

• Nov 16th, 2006

Hi friend, As per RDBMS rule we cannot have invalid synoym, but we can have a invalid view.

Answer Question  Select Best Answer

Sagar Sai

• Feb 4th, 2007

Hi,There are 3 types of triggers available in the Database. They are 1.DML Triggers :- These are again 12 types like .. Before INSERT,UPDATE,DELETE and After INSERT...

Pawan Ahuja

• Dec 3rd, 2006

there are three types of triggers in pl/sql1.)DML triggers2.)instead of triggers3.)system event triggersRegardsPawan Ahuja

Answer Question  Select Best Answer

Ravindra Reddy

• Sep 5th, 2007

The PL/SQL Blocks are 2 types1. Ananymous Block2. Named Block1. Ananymous Block means with out any name.Ex:- Declare<variables>Begin<Exec Statmts>ExceptionEnd.2. Names Block are ...

Sanjay Bharti

• Jun 13th, 2007

Hi All,I am new to this community. It seems the question is related to Begin End block of PL/SQL. See if you do not want to write any procedure and you want to do some sequence of operation that inclu...

Answer Question  Select Best Answer