What is the difference between "is" and "as" while creating procedure. Ex:- create procedure is or as?
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 ...
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.
A table has following layout ; city, name , sexhow do you write a query to get the count of male, count of female in a given city xxx.Query result should be in a single row with count of male , count of...
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
We have a trigger on data base.In the trigger body we have created a body using DBMS_output.Put_line(********) ;this should be firedwhen ever trigger executed;
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.
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 values8. 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.
What will you do when you are not able to get your message across to your audience?
Have you gone above and beyond your job description?
Do you have any questions that you want to ask?
How do you deal and overcome major challenges?
Has there been a time when you were presented a problem and solved it?
How to handle academic gap while applying for a job?
How productive are you?
Describe a time when you had to listen to someone.
Has anyone asked you for help? What did you do?
Describe a time when you had to convince a friend ?
Ask Interview Question?
Have Career Question?
Ask Only Career questions.