Result of max(sysdate) - min(sysdate)
I have some confusion about this question, max(sysdate) - min(sysdate) I think the answer is zero but give some detail description about this.....!
SQL> select max(sysdate)-min(sysdate) from dual;
MAX(SYSDATE)-MIN(SYSDATE)
-------------------------
0
SQL>
result of Max(sysdate)-Min(sysdate),it means, it shows the date which is in between last date n first date of the month according to the system information.
What is the difference between round and trunc function in SQL?
Both truc and round are single value functions. trunc: trunc function truncates that is in other words cuts off to the digits specified. the general syntax of trunc function is: trunc(number, precision); for instance: suppose the value of salary in employee table for ename= ’exforsys’...
SQL> select ceil(14.1) ,round(14.1),ceil(14.01),round(14.4),trunc(14.6) from dual; CEIL(14.1) ROUND(14.1) CEIL(14.01) ROUND(14.4) TRUNC(14.6) ---------- ----------- ----------- ...
Round function will increase the value by 1 if decimal value is greater than or equal to 5 else it will return same integer value without decimal ex. select round(8.34) from dual; will return 8; sel...
What is mutating error?When we go to mutating error in Oracle?
mutating error is nothing but when we update a table at the same time when we retrieve the data from the same table then the mutating table error occurs.......to overcome we use autonomous transaction...
it occurs when ever we try to perform either select or dml operations on the same table in the trigger created for same table
select last_name,hiredate,to_char(hiredate,'day') day
eg. if use scott/tiger schema:
select ename, TO_CHAR(hiredate, 'DAY') as DAY from emp;
Using which language Oracle is developed?
Answered by: ACE_GEEK
Member Since Jul-2008 | Answered On : Jul 22nd, 2008
"C" ofcourse!!!
Oracle is developed using C and C++
Till 9i it was developed in using "C" and Java langauge. But from 10g it is fully developed in Java.
How does Oracle guarantee data integrity?
Data Integrity is maintained by Triggers and various other constraints in Oracle.
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...
SQL>declare 2 dayofweek varchar2(200); 3 begin 4 select to_char(sysdate,'day') into dayofweek from dual ; 5 6 if dayofweek = 'tuesday' 7 then 8 DBMS_output.Put_line('aloha!!! Today is tuesday'); 9 else 10 DBMS_output.Put_line('today is '||to_char(sysdate,'day'));...
It should be (sysdate,'day')
Locking a user from performing dml operation
Which command is used for locking a user from performing dml operation?
Revoke insert,update,delete privileges from the user
LOCK TABLE tablename IN EXCLUSIVE MODE;
EX:
LOCK TABLE emp IN EXCLUSIVE MODE;
Dictionary management tablespaces
How will you change the local management tablespaces into dictionary management tablespaces?
How can you prevent tablespace fragmentation? (rebuilding, moving)?
As rows are added to tables, the table expands into unused space within the space. Conversely, when rows are deleted, a table may coalesce extents, releasing unused space back into the tablespace. As ...
Explain about the chained / migrated rows
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, called chaining, the row is too large to fit into an empty data block. In this ...
Outline the names and function of Oracle background processes
The Oracle architecture has five mandatory background processes Mandatory background processes: DBWR (Database Writer) PMON (Process Monitor) CKPT (Check Point) LGWR (Log Writer) SMON (System Monitor)...
What are the different types of segments in Oracle?
Four types of segments are found in an Oracle database:1- Data segment every table in the database resides in a single data segment.2- Index segment each index is stored in its own index segment.3- Te...
There are following types of segments in Oracle
1. Data segments stores the data
2. Index segment stores the index data
3. Rollback segment : Stores undo data information for data consistency.
What is the difference between entity and attribute?
To put the definition of an entity is an entity is a single person, place, or thing about which data can be stored. That is in other words for example when one wants to store information about student then it has details like studentno, student name, address for communication, phone number, class of...
The question is: " is the attribute instance." The attribute itself is not an instance but the value "Exforsys" stored in the attribute is an instance.
An entity represents a real world object where as attribut reprsents the characteristics of an entity
What is the built-in that tells you the record that the user is placed the fouces?
What steps server process has to take to execute an update statement.
When a user issues a update statement the server process checks for weather the same type of command is executed recently or not if that command is executed very recently then it directly proceed to t...
During the execution of an update statement, the server process looks into library cache whether same kind of query is recently executed or not i.e. explain plan is ready for execution or no...
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
Answered by: saginandkishore
View all questions by saginandkishore View all answers by saginandkishore
Member Since Jun-2008 | Answered On : 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.
------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...
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...
Why is sys_context used for providing privileges in Oracle?
The sys_context function can be used to retrieve information about the Oracle environment.The syntax for the sys_context function is:sys_context( namespace, parameter, [ length ] )namespace is an Orac...
One time procedure, Its actually not a procedure but its called as one time procedure, Why because this used to initialize variable, calling function or procedure once the scope of package for user se...
And the primary purpose of one time procedure is for one time initialisation of package variables
select * from employees where hiredate in (1995,1996,1997,1998);
Exact query you required..... Try it out if you have any doubts.