prakash

• Nov 15th, 2006

Hi,        I would suggest not to pass all the records to you next procedure. You should look to redesign your next procedure so that it accepts one record and proce...

prasanna

• Nov 14th, 2006

hi friend       of course no need of sending 2lakh records but some records whatever i required

Answer Question  Select Best Answer

Raghavendra

• Nov 19th, 2006

Alter table disable all trigger;

Brijesh Mishra

• Oct 9th, 2006

SQL> Spool c:tempdisable_all_triggerSQL> Select 'ALTER TRIGGER '|| trigger_name ||' disable;' from user_triggers;SQL> spool offafter that open file disable_all_trigger.lst and remove header part and save file.SQL>@c:tempdisable_all_trigger.lst

Answer Question  Select Best Answer

asraf_pa

 Profile Answers by asraf_pa Questions by asraf_pa

• Oct 10th, 2010

You can use DBMS_SESSION to set & get session variable. Below is the example for the sameSQL> EXEC DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT', 'myvar', 'myvalue');PL/SQL procedure successfully co...

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 11th, 2006

session variable can not be declared in package, variable declared in package are called global variables.  create package mypack isnum number := 7;num2 number;end ;here num, num2 are global vari...

Answer Question  Select Best Answer

sankari

• Jan 9th, 2007

We can do as shown below... in the same way we can drop the table..

DECLARE
BEGIN

EXECUTE IMMEDIATE 'create table employee(empno number(3),ename varchar2(10))';

END;

gangadharam.p

 Profile Answers by gangadharam.p Questions by gangadharam.p

• Dec 26th, 2006

HI,  table can be created by using either dbms_sql or EXECUTE_IMMEDIATE in procedure or function.

Answer Question  Select Best Answer

joybanerjee

 Profile Answers by joybanerjee Questions by joybanerjee

• Dec 10th, 2007

The  purity level defines  what structure the  function reads  or  modifies.the  types  of  purity level:1)wnds--write  no database  stage  I.e&n...

malik.aman

 Profile Answers by malik.aman Questions by malik.aman

• Dec 4th, 2007

To be callable from SQL statements, a stored function (and any subprograms called by that function) must obey certain "purity" rules, which are meant to control side effects:When called from a SELECT ...

Answer Question  Select Best Answer

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 19th, 2008

Oracle9i introduced Flashback Query to provide a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time...

joybanerjee

 Profile Answers by joybanerjee Questions by joybanerjee

• Dec 26th, 2007

when ever  a  table  is  dropped  the  database  does  not  release  the  space  assotiated  with it the  table  is  kep...

Answer Question  Select Best Answer

srini

• Feb 6th, 2007

Actually explicit cursor refers only one work area in memory,incase of ref cursor refers different work areas in memory.

ranjeetapegu

 Profile Answers by ranjeetapegu Questions by ranjeetapegu

• Jan 12th, 2007

Difference between Pl/sql tables and record is that in pl/sql tables we can store more than one row whereas in record we store only one row.TYPE rec_emp is RECORD( ename   varchar2(10), empn...

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 3rd, 2008

Group functions returns one result per each group of the result setWhere as analytical functions  returns multi rows per each group i.e. using analytical functions we may display group results along with individual rows.

dev

• Dec 19th, 2006

Thank u Rampratap.Dev

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

igornem

• Jun 6th, 2015

There is no term "Atomic transaction". There is term "Transaction" which is must have ACID properties, and first "A" in "ACID" is - "Atomicity", which is defines that series of operations included in ...

madhaviguthula

 Profile Answers by madhaviguthula Questions by madhaviguthula

• Feb 28th, 2007

Autonomous transactions are independent transactions that can be called from within another transaction. An autonomous transaction lets you leave the context of the calling transaction, perform some S...

Answer Question  Select Best Answer

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

psingla

 Profile Answers by psingla Questions by psingla

• Apr 27th, 2010

2) Triggers cannot be called rather they are fired automatically based upon their specification.

MuralidharanR

 Profile Answers by MuralidharanR Questions by MuralidharanR

• Mar 17th, 2007

The maximum buffer size of DBMS_OUTPUT IS 1,000,000.  A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Trigger can not be executed manually

Answer Question  Select Best Answer

shriram

• Jan 5th, 2018

To add one more point to the disadvantages of packages :
We cannot declare ref cursor in a package.
also if a dependent object is dropped the entire package body is invalid until it is recompiled.

Kavitha

• Nov 19th, 2007

Disadvantages of Triggers    It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.    Triggers execu...

Answer Question  Select Best Answer

mittu

 Profile Answers by mittu Questions by mittu

• Jan 22nd, 2008

Right click on package and choose [execute]elseexecute package_name;

Larry

• Nov 6th, 2007

Execute package_name;

Answer Question  Select Best Answer

chakraborty_sarkar

 Profile Answers by chakraborty_sarkar Questions by chakraborty_sarkar

• Jul 10th, 2007

SELECT LEV FROM (SELECT LEVEL LEV FROM dual CONNECT BY LEVEL < 10) WHERE LEV NOT IN (SELECT COLUMN1 FROM TEMP);

Amit

• Jun 14th, 2007

If I have Emp Table and id column
1
2
4
5
7
SELECT 10-id FROM Emp WHERE id in (SELECT id FROM Emp WHERE id 5)

Answer Question  Select Best Answer

Kiran Birajdar

 Profile Answers by Kiran Birajdar Questions by Kiran Birajdar

• Mar 21st, 2016

EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,An error was encountered - ||SQLCODE|| -ERROR- ||SQLERRM); END; Or you could log the error to a table using the SQLERRM function ...

devi

• Sep 25th, 2007

You can print the 'sqlcode'?as followsException?When others thendbms_output.put_line('error code is'||sqlcode)end;?

Answer Question  Select Best Answer

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 19th, 2008

We cant use functions with OUT parameter in sql statements because there is no way for you to declare a variable in a SQL query and in order to call an IN OUT -- you need a local variable (to supply a value to be read from and to supply a place to put the out value into).

ramaswamy

• Sep 26th, 2007

Functions with "out" parameters?cannot be used in?SQL statments however I disagree with you?rajeev because functions without parameters can also be called in select statments as long as they havea return()?(Eg: SYSDATE)

Answer Question  Select Best Answer

Lakshmi84

 Profile Answers by Lakshmi84 Questions by Lakshmi84

• Aug 4th, 2009

There is no difference btwn IS and AS while creating procedure,function,package

samareshp

 Profile Answers by samareshp Questions by samareshp

• Aug 3rd, 2009

Don't think there is any difference

Answer Question  Select Best Answer

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

My example is not perfect but close."sql SELECT deptno, job, ename, sal ,CURSOR( SELECT ename, deptno, job, sal , DENSE_RANK () OVER (ORDER BY sal desc) rnk ...

dipanjan80

 Profile Answers by dipanjan80 Questions by dipanjan80

• Oct 6th, 2010

?Using LEAD analytical function its possible to get hierarchical set of data. Here is an example:?SELECT deptno, empno, sal,?LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) N...

Answer Question  Select Best Answer

Database has 100 tables and some of the tables dont have any data. I want to pullout those table names from database in Oracle.

sr_2407

 Profile Answers by sr_2407 Questions by sr_2407

• Jun 4th, 2008

Select  table_name ,num_rows from user_tableswhere num_rows=0

ravgopal

 Profile Answers by ravgopal Questions by ravgopal

• Oct 10th, 2007

Analyze and perform statistics on whole schema.  This will populate the statistics details about each table.  You can pull NUM_ROWS column from all_tables to check whether it ...

Answer Question  Select Best Answer