-
Why do we need to use 'WHERE CURRENT OF' clause
saginandkishore
- Jun 24th, 2008
Where current of is used for updating the data referenced by cursor which was created as "SELECT FOR UPDATE".DECLARECURSROR MY_CURR IS SELECT COL1, COL2,...
shamkohli
- Jun 17th, 2008
While using cusor in PL/SQL, when we update a record, we can write WHERE CURRENT OF along with cursor_name.This is done in place of the where condition while updating. If we gi...
-
What is stucture of Database
AshaMahesh
- May 15th, 2008
ther is Physical structure & Logical structurePhysical structure Consits of 3 files one or more data files ...
Mohammed Abdul Afroze
- Sep 28th, 2007
The Physical Structure of a Database are as follows:1.Controlfiles2.Redo Log Files3.Datafiles.
-
Whats the difference between PL/SQL and a Cursor?
jmriaz
- Aug 14th, 2007
A Pl/SQl is Procedural language extension for SQl.U can do certain thingslike creating blocks,procedures,functions,triggers etc..A cursor is a private work area(or u can say it a place)...
rakesh.apps
- Aug 8th, 2007
PL/SQL is a procedural language which is used to make set of operations on the database.Actually the code is written in the form of PL/SQL blockAnd Cursor is a mechanism which is used to retrieve the data(rows) from the table.
-
-
What are the differences between stored procedures and triggers?
Stored procedures are compiled collection of programs or SQL statements that live in the database. A stored procedure can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. But triggers are event-driven special procedures which are attached to a specific database object say a table. Stored procedures are not automatically run...
Amol Bawane
- Nov 15th, 2007
The basic difference between stored procedure and trigger is stored procedure can call explicitly, but trigger cannot call explicitly.Genarally trigger are used for Audit and trail. What should be the...
gvvskumaran
- Jan 15th, 2007
* procedures will be called by user or by programs* Trigger will be executed or triggered only when it is fired....
-
What is the main concept of Concurrent Program in Oracle Apps to register a report?
Pawan Ahuja
- Dec 9th, 2006
hi,The Frist step u have to define a EXECUTABLE for your program (for Ex.if your r woking with reports then define a EXECUTABLE NAME,SHORTNAME and the very important step is to select the EXECUTION ME...
bommana_vidyasagar
- Nov 26th, 2006
hi,The Frist step u have to define a EXECUTABLE for your program (for Ex.if your r woking with reports then define a EXECUTABLE NAME,SHORTNAME and the very important step is to select the EXECUTION ME...
-
-
(i)a table is classfied as a parent table and you want to drop and recreat it .how would you do this without affecting the children tables?(II)you want to group the following set of selected returns ,what can you grioup on?max(sum_of_cost),min(sum_of_cost),count(item_no),item_no(iii)"to delete duplicate records from table leaving on set of records"
shravanam
- Oct 24th, 2007
i. YOU CAN NOT DELETE A PARENT TABLE WITHOUT DELETING CHILDREN TABLES. BUT VICE VERSA CAN TAKE PLACE.II. IF YOU HAVE ITEM CATEGORY THEN U CAN HAVE GROUP BY EXPRESSION ON ITEM_CATEGORY.III. DELETE FROM...
sunil saini
- Feb 27th, 2007
(1) use cascade option in query
-
What is the different between Stand Alone Procedure and Package?
suresh.adqr
- Feb 18th, 2009
1. Procedure Overloading and Function Overloading Possible in Package. This? Overloading Concept Not Possible in Procedure.2.?Variables in Package we?Can use as a Global Variables. it's Not Possible in Procedure.
amikat
- Jun 3rd, 2008
Procedure is a named PL/SQL block tht is stored in the database.Package is a collection of functions and procedures stored within the database. Pkg consists of 2 parts 1. Specification - declares type...
-
How to write a query to retrieve only the months from certain given duration.
I will be getting this duration from the dual table. Provide query for this.Like for example, if suppose I am going to get value from the month of 01-Jan-2006 to 31-Apr-2007 then My result should be only 4 months like Jan, Feb, Mar, Apr.Btu not the whole dates. Only four rows has to be retrieved. This should be retrieved from the dual table but not from other tables.
Imran_Javed
- May 13th, 2008
SELECT TO_CHAR(ADD_MONTHS('01-jan-2007', ROWNUM-1), 'MON')FROM dualWHERE ADD_MONTHS('01-jan-2007',ROWNUM-1) < '30-apr-2007' CONNECT BY LEVEL <=12;
jamesravid
- Jul 18th, 2007
Use this query, select to_char(add_months('01-jan-2007',rownum-1),'MON') from user_objects where add_months('01-jan-2007',rownum-1) < '30-apr-2007';
-
Evaluate this query?Select e.emp_id from emp@wing e; What is "@wing" ? Is it a partition on emp table or a constraint on table emp?
Imran_Javed
- May 13th, 2008
Its a DB link which is used to access tables which reside on another server.From the above query you are trying to access emp table which is in "WING" schema.WING being on other server.
-
Oracle Dual
Select round(13.19,1) from dual will return 13.2Select round(13.19,-1) from dual ....what will be the output.....IN ORACLE
Imran_Javed
- May 13th, 2008
SELECT ROUND(13.19,-1) FROM DUAL;RETURN result IS 10; WHEN you pass second argument AS "-1" , rounding takes place BEFORE the DECIMAL point.That IS the reason IN the above example it rounds 13 TO 10. IF you USE 15 OR more than 15 it will ROUND TO 20
-
Oracle Dbms Pipe Commands
Give the dbms_pipe commands in oracle of there execution,which are used to send & recieve messages what is the max size can sent at one go
Read Best Answer
Editorial / Best Answer
saginandkishore- Member Since Jun-2008 | Jun 24th, 2008
dbms_pipe is a IPC methodology which allows processes to communicate with each other using pipes.
The important functions/procedures in this package are
(1) CREATE_PIPE to create a new pipe
==> dbms_pipe.create_pipe(pipename) where pipename can be Oracle supplied or user defined as below
dbms_pipe.create_pipe(dbms_pipe.unique_session_name) -- Creates a PIPE based on your unique Session ID
or
pipename constant varchar2(100) := 'TEST_PIPE';
dbms_pipe.create_pipe(pipename);
(2) Having created the pipes try sending some data through the pipe. To send data prepare the data using dbms_pipe.pack_message(mesg_buf) where mesg_buf can be varchar2, char, number. Basically this procedure is overloaded for Numbers, Varchar2 and date. For long and raw data use the corresponding DBMS_PIPE.PACK_MESSAGE_RAW method. This procedure basically places the data into buffer which can be send on any pipe using the dbms_pipe.send_message(pipename) function.
(3) At the other end of the pipe you can receive the data using dbms_pipe.receive_message function. This functions fetches the data into buffer from where you can unpack the data using dbms_pipe.unpack_message function. This function gets the data in the buffer into a message buffer.
These are the basic commands that you can use for working with pipes. For more details visit oracle documentation.
imranbaig.geek
- Dec 1st, 2009
------Funtions------DBMS_PIPE.CREATE_PIPE:- for creating a pipeDBMS_PIPE.SEND_MESSAGE:- For sending messageDBMS_PIPE.REMOVE_PIPE:- for removing messageDBMS_PIPE.RECEIVE_MESSAGE:- For receiving message...
saginandkishore
- Jun 24th, 2008
dbms_pipe is a IPC methodology which allows processes to communicate with each other using pipes. The important functions/procedures in this package are(1) CREATE_PIPE to create a new pipe ==> dbms...
-
Identify Instance Name
How we identify instance name, free space, used space in database & datafile that makes up the system tablespace?
kishore
- Nov 15th, 2011
How to find the instance name?
>select instance_name from v$instance; -
Use of Constraint Name
What is the use of mentioning of constraint_name along with NOT NULL Constraint _type while creating a table?
naizy_dba
- Feb 18th, 2012
Constraint are easy to reference if you give them a meaningful name. It is not necessary to assign name to constraint. If you do not assign any name to constraint Oracle will assign a name to constrai...
jimmy.katiyar11
- Nov 29th, 2011
Hi, Mainly use constraints for a businessmen rule that restrict enter the record in a table. IN oracle there are 5 type of constraints --- 1) Primary key, 2) foreign key 3) Unique key 4) Not NUll...
-
Combine Two Separate Tables
How to combine two separate table into one?
-
Base Table Update
How to update base table (data dictionary) automatically in every 5 sec or 5 min?
monika_iims
- Jun 18th, 2010
1.create a procedure with update statement2. create a job on that procedure and give the time interval
-
Data Integrity
How does Oracle guarantee data integrity?
kperumal75
- May 4th, 2011
Data Integrity is maintained by Triggers and various other constraints in Oracle.
kperumal75
- May 4th, 2011
Oracle Database enables you to define and enforce each type of data integrity rule defined in the previous section. Most of these rules are easily defined using either integrity constraints or datab...
-
Tablespace Management
What are the ways tablespaces can be managed and how do they differ?
Affrayam
- Mar 8th, 2013
The difference between LMTS & DMTS is if the extent management is local(LMTS) the information about free extents and free blocks will be maintained in DATAFILE HEADER it self AND if the extent managem...
ashdba
- Sep 5th, 2010
Tablespace can be managed by specifying Extent management local or Extent management dictionary, segment space management auto or manually by using the key words pctfree, pctused, pctincrease during tablepsace creation.
-
How to write query for getting non numeric values
hii i have a emp table .empno stores all the numeric valuesand accidentally i have added some values with combination of alphabets and special charactersnow i need a query which will return all the records which is having alphabets and characters in their value.assume tht table has records in lakhs.Please answer this questionthankx in advancesreekanth
damodharyadav
- May 23rd, 2011
Select non_num_col from sample_tablewhere length(non_num_col) -length(replace(translate(non_num_col,'qwertyuiopasdfghjklzxcvbnm',' '),' ','')) 0
neha.awasthi1
- May 19th, 2011
Select order_status from orders where TRANSLATE (order_status, CHR (1) || '1234567890', CHR (1)) is not null
Oracle Basics Interview Questions
Read Best Answer
Ans