Explain psudo column label with an example
LEVELYou use LEVEL with the SELECT CONNECT BY statement to organize rows from a database table into a tree structure. LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2, grandchildren are level 3, and so on.
Sorry, sorry, I am wrong. Rowid is a pseudocolumn.
select count(case when sex='male' then 1 end) "male", count(case when sex='female' then 1 end)"male", city from cnt where city = 'bangalore';
SELECT city,DECODE(sex,'M',COUNT(sex),null)male, DECODE(sex,'F',count(sex),null)female FROM test GROUP BY city,sex;
This will give the name of city along with corresponding number of male and female on that city.
Which function is used to trap the error code and error message of an exception
Raise_application_error(error number,error discription)
Hi,,
it is Raise_Application_error
Triggers fire when any condition on such trigger is satisfied...but we don't know when that trigger is going to be fired and if it is fired where can we see output of DBMS_output.put_line.
If we want to see output of trigger when it is fired we should mention Raise_application_error in trigger.
If you 'set serveroutput on' and execute the statement which fires the trigger the message will display on prompt otherwise it will not.
Can any one explain perforance tuning in PL/SQL
Performance tuning can be improved by using FOR ALL and BULK COLLECT clauses in place of FOR LOOP and CURSORS which makes the application faster and better.
Using Bulk binding concepts, we can tune the PL/SQL job that takes long time due to millions of records.
What is difference between a procedure & function ?
a function is always returns a value using the return statement. a procedure may return one or more values through parameters or may not return at all.
Read Best Answer
Editorial / Best Answer
Answered by: krishnaindia2007
View all answers by krishnaindia2007
Member Since Sep-2007 | Answered On : May 3rd, 2008
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
What is the difference between system procedure and system function?.
Also Procedure can be called from triggers but Function cannot be called from trigger. As function returns value. And no value can be returned to trigger.
User Panel
Categories
Interview Coaching
Interview Question
Career Counselling
NH
Both are same.
There is no difference as such, We use AS when Stored Procedure is defined in a package if SP is outside pkg we use IS. Here is the exampleEx: ASPakcage Headercreate package schema.testpkg ...