How to find the max value in a Sequence?

mstreete

 Profile Answers by mstreete Questions by mstreete

• Sep 30th, 2008

If you don't own the sequence, you may look at all_sequences, which shows sequences in other schemas that you have access to:select max_valuefrom sys.all_sequenceswhere sequence_owner = '&sequence_owner' and sequence_name = '&sequence_name'

nandk.sharma

 Profile Answers by nandk.sharma Questions by nandk.sharma

• Jun 30th, 2008

We can get the MAX value of a sequence by using the data dictionary view 'USER_SEQUENCES' .i.e.  SELECT MAX_VALUE FROM USER_SEQUENCES       WHERE SEQUENCE_NAME='NAME_OF_SEQUENCE'

Answer Question  Select Best Answer

Procedure in package perfomes fastly over normal procedure, Explain.

monoranjang

 Profile Answers by monoranjang Questions by monoranjang

• Jan 24th, 2011

A Storeprocedure is a Set of transactional SQL statements which will do some operation. Storedprocedure is precomplied . When we use sp's in our application it will reduce network traffic because ...

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 8th, 2010

It's true. Procedures in the packages are faster than the stand alone procedures, because all the procedures in the package are compiled once and stored in the SGA at shared pool area. If we call the ...

Answer Question  Select Best Answer

Why are we not supposed to include TCL statements in a trigger?

manveshv

 Profile Answers by manveshv Questions by manveshv

• Dec 30th, 2009

The trigger is fired when an event occurs while manipulating the data in table.So the trigger applies a lock on it restricting others to modify it.Locks are released when a TCL command is executed. Th...

Vansat

 Profile Answers by Vansat Questions by Vansat

• Jun 16th, 2009

We cannot use TCL statements in triggers, because using them violates the integrity of the transaction. But if we want TCL statements to be used in triggers then that can be implemented by using Auton...

Answer Question  Select Best Answer

What is call by value and call by reference in parameters (IN, OUT, INOUT)?

Read Best Answer

Editorial / Best Answer

lisha.varghese  

 Profile Answers by lisha.varghese Questions by lisha.varghese

• Member Since Nov-2008 | Nov 17th, 2008

---

Three types of parameter modes

1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.

2) OUT parameter mode -This mode is used to return a value to the main block.The value of OUT parameter can change anywhere in the program.

3)IN OUT parameter mode-This mode is used to pass values to the calling module and return a value to the main block.The value of IN OUT parameter can change anywhere in the program.

In Call By value ,the copy of actual parameter is passed to the formal parameter,So any changes to the formal parameter doesn't affect the actual parameter.

In Call By reference,the address of actual parameter is passed to the formal parameter,so any changes to the formal parameter will change the actual parameter also,because both of them are pointing to the same memory location.
Here  no copying is required.
 
The IN parameter is passe by reference,so we can't change the value of IN parameter inside the module,It acts as a constant,But the OUT and IN OUT parameters are passed by value,we can change the values of OUT &  IN OUT paremeters

samareshp

 Profile Answers by samareshp Questions by samareshp

• Apr 17th, 2009

In Parameter:: call by reference OUT Parameter: call by valueIN OUT Parameter: call by valueif you use the hint NOCOPY  with OUT Parameter and IN OUT Parameter then ::: call by reference

lisha.varghese

 Profile Answers by lisha.varghese Questions by lisha.varghese

• Nov 17th, 2008

Three types of parameter modes1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.2) OUT parameter mo...

Improve Answer

1) Why we use pl/sql array rather than cursor?2) In which condition we use function overloading? 3) What is pipe function?4) What is the mutating trigger? What should be the condition?

renu_db_dev

 Profile Answers by renu_db_dev Questions by renu_db_dev

• Jul 2nd, 2010

1) Why we use PL/SQL array rather than cursor?PL/SQL array is a collection through which we can iterate just the way we can through cursor. But in case of Cursor the context switching is for every row...

avis_sarkar

 Profile Answers by avis_sarkar Questions by avis_sarkar

• Oct 14th, 2009

Triggers are PLSQL block or Procedures which get executed whenever:1) an INSERT, DELETE or UPDATE happens on a table. 2) DDL like ALTER or CREATE happens on a table. 3) an INSTEAD OF trigger on a view...

Answer Question  Select Best Answer

How to identify missing records in a single-column table ?Column consists of numbers in a ordered manner but the some numbers are deleted from the table in random manner and need to identify those rows.p.s. This table consist of only 1 row.

Satya

• Sep 20th, 2011

"sql SELECT CASE WHEN ConNum - prev_id = 2 THEN TO_CHAR( prev_id +1) ELSE '[' || NVL( prev_id +1 ,1 ) || ' , ' || (ConNum-1) || ']' END AS missing_range , ConNum - NVL...

geetha

• Aug 25th, 2011

Code
 

where the test table is like colA 2 4 6 7 the result will be the missing numbers..

Answer Question  Select Best Answer

Malar somu

 Profile Answers by Malar somu Questions by Malar somu

• Sep 10th, 2013

Implicit Cursor:- It is Oracle implicit type.. when we fetch only one record then we need to use implicit cursor i.e when ever we performing select query or any dml operations termed as implicit curso...

hira

• Aug 8th, 2013

When ever any query execute by the server in database sever itself create a implicit cursor ,which can not any control to the programmer but in case explicit cursor the programmer has to create it.and used for the data manipulation by following some rule......

Answer Question  Select Best Answer

What is the impact of using a stored proc or a function on the performance? Which has a better performance? Also, why is it not possible to use DML in the functions called from a select query?

Nupur

• Apr 24th, 2015

Functions and procedures are both named blocks of statement . A function must return a value . Performance wise if you want the program to return a?single value use a ?function . You can use out par...

Suchith Shivali

• Apr 17th, 2015

1)In stored procedure you may or may not return value but in the function return is compulsory. 2)If you want to return more than one value go for Stored procedure. 3)Once SP or function is created,...

Answer Question  Select Best Answer

Can we give COMMIT or ROLLBACK within a trigger?

Abhijit Musale

• Feb 19th, 2015

Using PRAGMA AUTONOMOUS_TRANSACTION we can treat the child transaction as a separate transaction, committing which wont affect the parent transaction.

Tushar

• Feb 17th, 2015

No we cant used it.because it affected on logical processing. It is possible using Pragma

Answer Question  Select Best Answer

If have 5 procedures in my specification part and used 4 in my body part while compiling will it throw error or not?

Shridevi Bhat

• Jun 30th, 2017

Yes it will throw error. all the declared procedures and functions must be there in package body with implementation.

Error(15,22): PLS-00323: subprogram or cursor GET_REPORT_URL1 is declared in a package specification and must be defined in the package body

vishal pathak

• Jun 7th, 2017

Yes, If procedure declared in specification not defined in body will through compilation error saying:- "procedure declared in package specification must defined in body". but vice-versa will not throw any error

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 7th, 2008

A warning or error condition is called an exception. An exception may raise in a pl/sql block due to designing faults, coding mistakes or hardware failure. If an exception raises in a block ...

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Jan 31st, 2008

You can handle exception in two ways:– Trap it with a handler : If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler...

Answer Question  Select Best Answer

mohan

• Apr 9th, 2007

CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE   PRAGMA AUTONOMOUS_TRANSACTION;  i PLS_INTEGER; BEGIN   SELECT COU...

mohan kumar

• Apr 9th, 2007

Yes We can use commit statement in triggers with 10G.Here is example :CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE   PRAGMA AUTONOMOUS_TRANSA...

Answer Question  Select Best Answer

jabir.mkk

 Profile Answers by jabir.mkk Questions by jabir.mkk

• Oct 16th, 2009

The maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'

malik.aman

 Profile Answers by malik.aman Questions by malik.aman

• Dec 1st, 2007

Select * from dba_object_size where name = 'procedure_name';

Answer Question  Select Best Answer

deepakonroll

 Profile Answers by deepakonroll Questions by deepakonroll

• Dec 7th, 2007

DECLARECURSOR CUR_TABLES ISSELECT table_name from user_tables;V_TABLE NUMBER(5);v_table_name varchar2(100);BEGINFOR I IN CUR_TABLESLOOPSELECT COUNT(1)INTO V_TABLEFROM (SELECT table_name from user_tabl...

rajakumar_na

 Profile Answers by rajakumar_na Questions by rajakumar_na

• Nov 21st, 2007

Hi all plz understand the question.........Hi Ramki try this!SELECT COUNT (COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLENAME';FOR example;SQL> SELECT COUNT(COLUMN_NAME) FROM ...

Answer Question  Select Best Answer

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 8th, 2008

Please remove the commit from the begin statement and the it will execute properly.Thanks,

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 8th, 2008

DeclareCursor c1 is select * from emp FORUPDATE;Z c1%rowtype;BeginOpen C1;Fetch c1 into Z;Commit;Fetch c1 in to Z;end; SQL> start e1 Declare * ERROR at line 1: ORA-01002: fetch out of sequence...

Answer Question  Select Best Answer

psiva_oracle

 Profile Answers by psiva_oracle Questions by psiva_oracle

• Feb 12th, 2008

Hi,Database Server:It's contains the collection of objects (Table,procedure,trigger,sequence,index,etc)it's also contains the Schema and user detailswe can view the details which are in t...

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 4th, 2008

Database Server: A computer in a LAN dedicated to database storage and retrieval. The database server is a key component in a client/server environment. It holds the database management system (DBMS) ...

Answer Question  Select Best Answer

We can create a column with' not null+unique' with out using primary key

PRADEEP

• Oct 17th, 2012

Primary key: set of columns is uniquely identified a rows of a table. It does not allow both nulls and duplicate values. If we use null values it will allow duplicate values but it does not allow n...

Radha

• Jul 14th, 2011

We can create NOTNULL and UNIQUE constraints on single column. Which indirectly means a Primary Key. But only a Primary Key column can be used as Foreign key column. Primary key provides a default...

Answer Question  Select Best Answer

Radha

• Jul 13th, 2011

Flash Back query is used to get back the deleted rows & dropped tables from the database even they are committed. To get back the dropped table: Flashback table to before drop; To get back...

murthy 1053

 Profile Answers by murthy 1053 Questions by murthy 1053

• Jul 2nd, 2010

Flash back query is used to retrive the data.For ex if a table is dropped and we want the table again so at that time.syn:=FLASHBACK TABLE table_name TO BEFORE DROP.This will work only if recycled bin is not purged.

Answer Question  Select Best Answer

How will you debug PL/SQL Code?

geek.hari

 Profile Answers by geek.hari Questions by geek.hari

• Dec 14th, 2009

One conventional and popular method of debugging a pl/sql code is through printing the program execution path using a printable statement. For this you have to enable the output mode using the command...

OraclePassport

 Profile Answers by OraclePassport Questions by OraclePassport

• Apr 17th, 2009

The Best way to debug is by using an AUTONOMOUS TRANSACTION. Create a dummy table and insert into the table using the AT. This wont affect the main code, but one could know which part of the code the execution has reached while the program is running itself.

Answer Question  Select Best Answer

What is the main purpose of RETURN in functions when we can calculate a value and pass it through using OUT mode in procedure or functions?

Deepika S Verma

• Mar 14th, 2014

Its a myth that function returns value whereas procedure does not, actually procedure also return value (i.e. 0 or 1 ) at the time of exiting which indicates the success or error. Whereas Function ret...

shivigupta

 Profile Answers by shivigupta Questions by shivigupta

• Oct 9th, 2012

create or replace procedure proc1 as begin dbms_output.put_line(Before Return Clause); return; dbms_output.put_line(After Return Clause); end; set serveroutput on; begin proc1; dbms_output.put_line(I...

Answer Question  Select Best Answer