PL/SQL :: Difference Between Procedure And Function
Aug 25, 2012
difference between Procedure and Function.We Know that Procedure is Basically Used to perform Actions and Functions are Basically Used for Calculations but what would be the exact difference since Actions/Calculations can be done in both Procedure and Functions.Which factors decide whether we need to write a Procedure and in which cases we need Function.
View 4 Replies
ADVERTISEMENT
May 22, 2010
I know difference between procedure and function.if we want a return value from procedure, we can have OUT parameter. Similarly with function, in addition to returning a value from function, it can also send an OUT parameter value as a return value. That means, in one or the other way we are able to get a return value from both program units. Normally, I would fill a OUT variable with error message when an exception occurs. I use this varaible,after procedure call, to detect if an exception occurred or not. Similar task can be performed by a function, it returns a true/false and a value thru OUT variables.
both program units return values in the form of OUT parameters. Where exactly should we use a function, where exactly should we use a procedure?
View 4 Replies
View Related
Jan 12, 2010
I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.
The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..
I want to know what is the difference betn stored procedure and function?
From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).
View 2 Replies
View Related
Mar 11, 2010
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies
View Related
Dec 7, 2012
SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402
This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.
SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998
But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.
View 2 Replies
View Related
May 6, 2013
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual;
--Output H S
Select Concat('H ',' S') A From Dual;
--Output H S
Select Length('H '||' S') A From Dual;
--Output 6
Select Length(Concat('H ',' S')) A From Dual;
--Output 6
View 5 Replies
View Related
Jan 6, 2013
Explain with the best example for difference between translate and replace function.
View 2 Replies
View Related
Dec 7, 2012
SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402
This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.
SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.
View 3 Replies
View Related
Sep 22, 2011
create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;
that i have worte..
View 3 Replies
View Related
Jul 7, 2010
what is difference between in procedure and trigger?
View 2 Replies
View Related
Oct 30, 2012
difference between Difference between Deterministic functions and Function result cache in 11g?
View 4 Replies
View Related
Jan 7, 2013
difference between oracle procedure & package.
View 1 Replies
View Related
Mar 10, 2011
Is there any difference behind that using of either IS or AS when writing Stored Procedure?
View 1 Replies
View Related
May 24, 2013
know when to use a formula column and when to use a function in program unit in oracle reports.
View 1 Replies
View Related
Jul 7, 2010
I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.
how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.
View 3 Replies
View Related
May 16, 2011
I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.
I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.
The code for the procedure is
CREATE OR REPLACE PROCEDURE add_new_classes
(p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number
p_course_id classes.course_id%TYPE,
p_period classes.period%TYPE,
p_frequency classes.frequency%TYPE,
[code]....
I then use this to test it
BEGIN
add_new_classes(1002,'first','daily',3002);
END;
and the error I get is
Quote:ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
1. BEGIN
2. add_new_classes(1002,'first','daily',3002);
3. END;
View 5 Replies
View Related
Jul 8, 2010
I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.
View 2 Replies
View Related
Dec 3, 2011
How to call outside function in procedure
View 11 Replies
View Related
Nov 8, 2011
I have question.Using procedure inside the function ?can I get better performance?
View 8 Replies
View Related
Nov 10, 2011
Does procedure and function contain data?
View 3 Replies
View Related
Dec 27, 2011
Procedure and function. exact reason when we go for function or procedure?
View 3 Replies
View Related
Jun 9, 2010
Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....
View 3 Replies
View Related
Aug 1, 2011
for my r&d purpose i create function which call procedure,
create table test_tab
(a NUMBER(10),
B NUMBER(10),
C NUMBER(10)
[Code]...
but it will gv me error ORA-06575:FUNCTION IS IN INVALID STATE that means function created with compilation error.
but when i complied fucntion it doesnt gv me any error.
View 15 Replies
View Related
May 24, 2011
Is it possible to use WITH statement in an Oracle procedure or function?
I've had problems compiling a procedure with "WITH" statement.
View 1 Replies
View Related
Oct 18, 2011
I would like to know if it is possible to get the arguments of a function (or procedure), in the same way as - for example - the "arguments" object in JavaScript.
I have seen the table ALL_ARGUMENTS, for example, that is providing a lot of interesting details, but I still miss the most important for me: the values of the arguments, not only the name, or type, etc...
Something that could be called as soon as the function (or procedure) is run, at the beginning of its process, and that would give a kind of array of arguments values.
View 5 Replies
View Related
Jun 22, 2010
i'm trying to make a sp/function for inserting a record and return the new index.The previous code used regular inserts and needed an additional round-trip to get the id before inserting,, since this is part of a import routine performance is an issue.
CREATE OR REPLACE PROCEDURE SaveAisHeader (
P_Id Out Number,
P_ImportedOn IN Date,
P_Aisimporttypeid In Number,
P_Description In Varchar2,
P_Importedby In Varchar2
[code].....
View 12 Replies
View Related
Feb 13, 2012
I am familiar with WRAP command.It is used in command prompt.
C:> wrap iname=filename.sql
and this thing will convert filename.plb file.
but now I want to wrap my stored Procedure or function.how can i do this thing?it is saved in my disk?
View 10 Replies
View Related
Sep 16, 2012
How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .
View 6 Replies
View Related
May 28, 2013
I have started reading ORACLE 11g R2 PL/SQL programming doc, and I am stuck at bind variable. I just copied the example in the books and found error -
First I have written below procedure and compiled successfully -
CREATE OR REPLACE PROCEDURE FORMAT_STRING ( string_in IN OUT VARCHAR2) AS
BEGIN
string_in := '[' || string_in || ']';
END FORMAT_STRING; Then I have written below function and also compiled successfully -
create or replace function join_strings (str1 varchar2, str2 varchar2)
[code]......
View 3 Replies
View Related
Jan 11, 2013
is it possible to obtain a called procedure/function name within package?
For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
For example:
CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS
PROCEDURE proc_1 IS
BEGIN
[Code]....
I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_2*
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_2*
I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.
View 7 Replies
View Related