A  cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.A  cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

divyesh

• Oct 13th, 2007

Ref cursors are used when you want cursor to hold any no. of columns in the row.If you are not sure about row type at complile time you use ref cursors.Sys_ref_cursors used when you want cursors to be passed from one procedures to another procedures or functions.

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Feb 13th, 2007

If we declare in package body then what is difference b/w cusrsor..........?how many types of refcursors..............?whata re the advantages of ref cursors....................?

Answer Question  Select Best Answer

amahanit

 Profile Answers by amahanit Questions by amahanit

• Oct 13th, 2007

1) make the trigger as a statement level trigger.2) if u want to use :old and :new qualifer then create 2 trigger ,1st before insert trigger and use PL/SQL table to select the values,2nd after insert trigger and insert the values from PL/SQL tables.

be17be

 Profile Answers by be17be Questions by be17be

• Dec 1st, 2006

Ashish, can you please give an example?

Answer Question  Select Best Answer

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

We can do either DDL or DML inside a function.1) Let us try keeping a DML inside a function  1  create or replace function test (a number) return number as  2  begin  3  ...

vaibhav Tyagi

• Sep 26th, 2007

The main difference is in Function you can perform only DDLbut in Stored
procedure you can perform DDL, DML both

Answer Question  Select Best Answer

How can I write a function which returns previous business date of given input date1. skip holidays when you find previous day2. skip Sunday and saturday's when you find previous dayUS holidays are defined in HOLIDAYS tabledate, holiday_desc1-Jan-2007 New Year

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

If you need to get the previous business date, just change the in_dt variable in declaration to input_dt date := '&Dt';in_dt date := input_dt - 1;

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

Did you find the solution?  if not then, here it is..try thisa) Create the sample table holiday : create table holiday (h_dt date, h_desc varchar2(100));b) let us feed some records;Inse...

Answer Question  Select Best Answer

vaibhav Tyagi

• Sep 26th, 2007

FOR- Sql ServerCreate trigger TestTriggeron tableaNameinstead of insertasbegin transactiondeclare @aid intset @aid=0select @aid = max([columnName]) from tableNameif @aid 0--do operationcommit transaction

madhuk17

 Profile Answers by madhuk17 Questions by madhuk17

• Feb 18th, 2007

Create or replace trigger {Before/After} {Insert/Update/Delete} on [Referencing [ New as ] [ Old as ][For each row [When ]]

Answer Question  Select Best Answer

Rushikesh

• Sep 25th, 2007

To be callable from SQL statements, a stored function must obey the following "purity" rules, which are meant to control side effects:1. When called from a SELECT statement or a paralle...

Answer Question  Select Best Answer

devi123

• Sep 25th, 2007

Declare the collection variable (varrys or PL/SQL tables)?as out parameter for the procedure and fetch the result set into collection variable.

Answer Question  Select Best Answer

Vaibhav Tyagi

• Sep 25th, 2007

You cannot move existing table into a cluster. You can create new tables though, including tables with the same contents as existing tables. In the second. form, query returns a set of rows which...

Answer Question  Select Best Answer

dearvandna

• Sep 21st, 2007

No. but maybe associative arrays can help

Answer Question  Select Best Answer

dearvandna

• Sep 21st, 2007

Normally use it as sql%type or sql%rowtype. But if you are using a cursor then use it cursorname%type or cursorname%rowtype.

Answer Question  Select Best Answer

Ravindra Reddy

• Sep 5th, 2007

Cursor is a private SQL area.. its a temporary buffer used to hold the
transactional data on it n used to manipulate multiple rows through PL/SQL block
statements.
Temporary table can hold the records permanently and we can do DML operations
on this..
ok
Cheers
Rav'i'ndra Reddy

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

utham

• Sep 4th, 2007

Identity is a?SQL Server concept The better way to do is use the alter command

Sandip Bhattacharjee

• May 12th, 2007

SQL> ALTER TABLE EMP ADD SR_NO INT IDENTITY(1,1) 2 ;ALTER TABLE EMP ADD SR_NO INT IDENTITY(1,1)*ERROR at line 1:ORA-01735: invalid ALTER TABLE optionThe above commands is not working. Pls specity what do you mean by identity(1,1)

Answer Question  Select Best Answer

archie123

• Aug 28th, 2007

Yes you can use commit or rollback in exception block;select * from test123;A----------51 Declare s varchar2(2); Begin update test123 set a = a+10; select a into s from test123 where a=20; exception...

Answer Question  Select Best Answer

jiten

• Aug 27th, 2007

%type will use for to define at column level.%rowtype is used to define for one or more than one columns ( like row or record or tuple?) means its user define type which hold one or more than one columns along with it. and generally it used?with Cursor.

srikanthvijay8

• Jul 2nd, 2007

If you use a cursor, you can return cursor values only by rowtype only.

Answer Question  Select Best Answer

 DECLARE  CURSOR  cursor  name,  OPEN  cursor  name,  FETCH cursor name INTO  or Record types, CLOSE cursor name.

fancyoracle

• Aug 15th, 2007

There are two ways of processing a cursor output. These mainly depend on the type of cursor used. Cursors can be1) Static      : Declared in declarations section with a defina...

geetha24

 Profile Answers by geetha24 Questions by geetha24

• Feb 13th, 2007

FOR record_index IN [cursor_name | (SELECT statement)]LOOP executable_statement(s)END LOOP;

Answer Question  Select Best Answer

askvenki

 Profile Answers by askvenki Questions by askvenki

• Aug 13th, 2007

By using cursor parameters we can pass the value dynamically. So that cursor can fetch data as per parameter

gopal

• Jan 11th, 2006

Dear sir,      declare       dep_num number;         cursor c(x number) is select * from emp where deptno...

Answer Question  Select Best Answer

For example: I have 100's of tables and I need to take the data from database and export as a xml file.

lieni

• Jul 30th, 2007

You add the psedocolumn rowid to the selected fields of the cursors body.This rowid identifies the selected record uniquely so that you can update with it, like with the primary key (just faster).Update tab where rowid=cur_rec.rowid;

nagurtilak

• Mar 15th, 2007

It can be used in cursorsIf we want to update the current record (i.e cursor holding the current? record) we can use WHERE CURRENT clauseEg. UPDATE empSET sal=sal*10FROM emp WHERE CURRENT of "cursor name"

Answer Question  Select Best Answer