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

nade_rs

 Profile Answers by nade_rs Questions by nade_rs

• Jun 25th, 2011

We can also  make use of compound trigger (new feature in  11 g to avoid mutating error)

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

The? row level trigger based on a table,the trigger body can't read thedata? from same table, if you are trying to this it gives mutating errorto avoid that you must use statement level trigger

Answer Question  Select Best Answer

Hi,My questions is " Can function return more than one value".I have gone through the all answers relevant to my question was been posted earlier. But all answers are confusing me.Few people said directly that function can't return more than one value and few are telling that function can return more that one value using OUT parameter or ref cursor.My understanding is if out can't use as a parameter...

sen_sam86

 Profile Answers by sen_sam86 Questions by sen_sam86

• Jul 23rd, 2009

Yes this can be possible by TABLE FUNCTION STEP - 1    CREATE TYPE ex_tabl_fun_obj AS OBJECT (emp_name varchar2(20), emp_id NUMBER(2));STEP - 2     CREATE TYPE ex_tabl_fu...

javedans

 Profile Answers by javedans Questions by javedans

• Jul 8th, 2009

Its a universal truth that function can return only one value, but there are some tricks by using that you can get more than one value , while function returning only one value.When you define functio...

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

What is the best technique of debugging large and fragmented dynamic SQL.

kumardba

 Profile Answers by kumardba Questions by kumardba

• Oct 7th, 2010

You can try to debug Dynamic SQL using SQL Advisor Tool or TKPROF tool

shivaranjani.p

 Profile Answers by shivaranjani.p Questions by shivaranjani.p

• Oct 6th, 2010

Hi,You need to store each and every line of the dynamic sql in a file using UTL_FILE Package before you could execute it, so that you can refer the same file after executing the code, this here serves...

Answer Question  Select Best Answer