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

RAVI KUMAR BURRI

• Jan 19th, 2020

SET AUTOTRACE ON EXPLAIN

machomanic

 Profile Answers by machomanic Questions by machomanic

• Jul 8th, 2010

You can use the command SQL> SET TIMING ON;This will show how much time your query took for the execution.

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

How to delete duplicate Keys through PL-SQL in single query.

anonymous

• Sep 27th, 2011

Code
 

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 21st, 2011

Assuming that ename and job is the same or First and Last name...

SELECT * FROM (SELECT empno, ename, job ,RANK() OVER (PARTITION BY ename, job ORDER BY empno) AS SeqNo FROM emp1) WHERE SeqNo > 1

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

During preparation of report in *.RDF , there are two things on date & to date

on date is the i/p value , but to date = 1st day of this month on date

how ?

pradeep

• Apr 24th, 2015

SELECT TRUNC(SYSDATE,MM) FROM DUAL

Praveen

• Apr 22nd, 2015

SELECT ADD_MONTHS(LAST_DAY(SYSDATE),-1)+1 FROM DUAL;

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

 It  is  not  possible.  As  triggers are defined for each table, if you use COMMIT   of    ROLLBACK  in  a  trigger,  it  affects  logical  transaction processing.

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Jan 31st, 2008

COMMIT, ROLLBACK, and SAVEPOINT statements are not allowed within the trigger body. It is possible to commit or rollback indirectly by calling a procedure, but it is not recommended because of side effects to transactions.

jockey

• Aug 2nd, 2007

Using autonomous_transaction it is possible

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 7th, 2008

Procedures Can have Parameters. But parameters is optional .i.e. we may defined procedures without parameters also.It can take three types of parametersIN , OUT and INOUT.

kanchan Patra

• Aug 7th, 2007

Yes, Procedures can have parameters.

Answer Question  Select Best Answer

samareshp

 Profile Answers by samareshp Questions by samareshp

• Apr 15th, 2009

Function  having a return type as Boolean ,can't be used in select statement.

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 7th, 2008

In functions we can use all three types of parametershere is a simple exampleCREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C  OUT NUMBER)  RETURN NUMBER IS   &n...

Answer Question  Select Best Answer

Amol Bakane

• Apr 2nd, 2018

The following hierarchy is followed when a trigger is fired. 1) BEFORE statement trigger fires first. 2) Next BEFORE row level trigger fires, once for each row affected. 3) Then AFTER row level tr...

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

The orders of triggers execution is like this???? ? 1.before statement level?? 2.before row level?? 3. after row level?? 4 after statement level

Answer Question  Select Best Answer

You have a package called A and one function in that packgae called XYZ.If you need to call that function in second packaged B, How will you call?

Raj_P

 Profile Answers by Raj_P Questions by Raj_P

• Sep 1st, 2010

1) VarName := PackageName.FunctionName(<Parameter List>).2) Select PackageName.FunctionName(<Parameter List>) From <TableName?     You cannot run this statement if y...

shekhar2010

 Profile Answers by shekhar2010 Questions by shekhar2010

• Aug 30th, 2010

Yes we can call a function which is there in a Package 'A' from Package 'B'. And it is very simple as stated belowPackage_Name.Function_Name(Parameter_List);

Answer Question  Select Best Answer

PL/SQL  is  a  procedural  language  that  has  both  interactive  SQL  and procedural  programming  language constructs such as iteration, conditional branching.

surya72446

 Profile Answers by surya72446 Questions by surya72446

• Sep 30th, 2009

PL/SQL stands for procedural languages extensions to SQL and it's theme is to hold a bunch of SQL statements as a single function and return a single output.PL/SQL is advantageous compared to other pr...

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 16th, 2008

PL/SQL is the procedural extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code. PL/SQL offers moder...

Answer Question  Select Best Answer

 Cursor is a named private SQL area from where information can be accessed. Cursors  are  required  to  process rows individually for queries returning multiple rows.

Rounak037

• Jan 22nd, 2020

Cursor is used when the user needed to update record in a single or in row buy row manner in a database table, Cursor is required to process rows individually for queries returning multiple rows.
Basically cursor are two types:
1. Implicit Cursor.
2. Explicit Cursor.

rahulg5211

 Profile Answers by rahulg5211 Questions by rahulg5211

• Oct 4th, 2009

Cursor is an small memory allocation or workstation where operation or processing is done on the data.Types of attribute of cursor are-:1)ROWCOUNT2)ISOPEN3)FOUND4)NOTFOUND

Answer Question  Select Best Answer

Suppose Input table is COL1 COL2 COL 3A 1 -3B -2 6c 3 -4D 9 10In sql query I want the desired output as follows COL1 COL nA 1B 6C 3D 10Please write the query to get this output

fire_starter

 Profile Answers by fire_starter Questions by fire_starter

• Sep 15th, 2011

Hello, For my query example I have used the table test_tbl, and the columns col, col1, col2. It also does consider NULL value in comparison, so it will show value on comparing with NULL, and for ev...

BhushanNag

 Profile Answers by BhushanNag Questions by BhushanNag

• Aug 24th, 2011

SELECT COL1, GREATEST(COL2, COL3) FROM max_value_column

Regards
Bhushan

Answer Question  Select Best Answer

Divisha

• Dec 30th, 2019

Can You please elaborate in what way can we do it ?

psingla

 Profile Answers by psingla Questions by psingla

• May 6th, 2010

In 10g we can

Answer Question  Select Best Answer

NehaChanda

 Profile Answers by NehaChanda Questions by NehaChanda

• May 22nd, 2009

We can not use MAX on PLSQL table. Check if it works.Create a PLSQL Table Sorted_Mark.SNO_MARK(3) := 0; - Replace ASorted in another Collection Typeselect culumn_valuebulk collect into Sorted_MARKfrom table (cast(SNO_MARK as PLSQL_DATATYPE))order by column_valueSorted_MARK(1) - First Value

samareshp

 Profile Answers by samareshp Questions by samareshp

• Apr 15th, 2009

I guess the the question should be replace a with zero then sort the mark and
then show first mark, Let me know if i am wrong.

select max(mark)
from (
SELECT sno, mark
FROM (select sno, decode(mark, 'A', 0, mark) x from t) xt
ORDER BY x
)

Answer Question  Select Best Answer

chiatanya

• Nov 5th, 2012

Implicit cursors are automatically generated,when a sql statement is processed where as

Explicit cursors are user defined.

VASU

• Oct 20th, 2012

Implicit cursors returns only single record..where as explicit cursor multiple records. But it process record by record.

Answer Question  Select Best Answer

Vinod Tuteja

• Jun 14th, 2013

Main Difference : Referential
Index is created in both Cases (Unique and Primary key Constraint )

But can't references Columns with combination of UNIQUE and NOT NULL. Like in Primary key ,we Can references primary key to make foreign key constraint .

z_ashwini

 Profile Answers by z_ashwini Questions by z_ashwini

• Jan 13th, 2012

Eg: Consider table emp with EMP_ID,EM|NAME,ADD columns You can define the column e_name as Unique constraint. DECLARE e_name number(10) NOT NULL; BEGIN ... .. END: Primary key cannot have NU...

Answer Question  Select Best Answer

binurajnair

 Profile Answers by binurajnair Questions by binurajnair

• Aug 2nd, 2008

We can overload functions/procedures inside packages.

jagadeesh9

 Profile Answers by jagadeesh9 Questions by jagadeesh9

• Feb 24th, 2008

Package: It is an collection of related variables, cursors,procedures and functions stored at one location. They supports OOPS features like function over loading, encapsulation.ADV:   ...

Answer Question  Select Best Answer