SQL & PL/SQL :: Procedure Versus Function?
Dec 27, 2011Procedure and function. exact reason when we go for function or procedure?
View 3 Replies
ADVERTISEMENT
Function And Procedure Execution
Jul 7, 2010I 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.
SQL & PL/SQL :: Using NVL Function With Parameters In A Procedure?
May 16, 2011I 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;
SQL & PL/SQL :: Difference Between Procedure And Function
May 22, 2010I 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?
SQL & PL/SQL :: Procedure And Function Execution
Jul 8, 2010I 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 RelatedSQL & PL/SQL :: How To Call Outside Function In Procedure
Dec 3, 2011How to call outside function in procedure
View 11 Replies View RelatedSQL & PL/SQL :: Using Procedure Inside Function?
Nov 8, 2011I have question.Using procedure inside the function ?can I get better performance?
View 8 Replies View RelatedSQL & PL/SQL :: Does Procedure And Function Contain Data
Nov 10, 2011Does procedure and function contain data?
View 3 Replies View RelatedSQL & PL/SQL :: Stored Procedure Vs Function?
Jun 9, 2010Interviewer 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 RelatedSQL & PL/SQL :: Calling Procedure Into Function?
Aug 1, 2011for 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.
PL/SQL :: Difference Between Procedure And Function
Aug 25, 2012difference 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 View RelatedUsing WITH Statement In Oracle Procedure Or Function
May 24, 2011Is it possible to use WITH statement in an Oracle procedure or function?
I've had problems compiling a procedure with "WITH" statement.
Difference Between Stored Procedure And Function?
Jan 12, 2010I 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).
SQL & PL/SQL :: List Of Arguments Of A Function Or Procedure
Oct 18, 2011I 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.
SQL & PL/SQL :: Insert Function / Procedure Return New ID
Jun 22, 2010i'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].....
SQL & PL/SQL :: Wrap Stored Procedure Or Function?
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?
PL/SQL :: How To Test Procedure / Function / Package
Sep 16, 2012How 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 RelatedPL/SQL :: Binding Variable In Procedure And Function
May 28, 2013I 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]......
PL/SQL :: Get A Called Procedure / Function Name Within Package?
Jan 11, 2013is 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.
Use PRAGMA RESTRICT_REFERENCES In Either Procedure Or Function?
Jan 11, 2013Can we use PRAGMA RESTRICT_REFERENCES in either procedure or Function, instead of Package. i know in package , we can use.
my question is in individual procedure or Function , can we use.
SQL & PL/SQL :: Use A Procedure Or Function If Have To Return Only 1 Datatype
Feb 1, 2012how to decide whether to use a procedure or function if i have to return only 1 datatype.
View 5 Replies View RelatedExport / Import Of Procedure - Function / Package
Jun 9, 2012Can we Export & Import of Procedure, Function & Package selection by name, as we can export & import of one or more table by name
View 2 Replies View RelatedSQL & PL/SQL :: Stored Procedure Function - Numeric Or Value Error
Apr 16, 2010I have wrote a Stored Procedure Function that get all the rows from a Staging Table and assigns it to a CLOB and returns the CLOB. The issue is I'm getting the dreaded
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "F_CLOB_TEST", line 21
ORA-06512: at line 7
The error does not occur when I remove the 'PRC_ID', 'S_FIL_NAME' & 'exportDt' from the query. It works fine. The PRC_ID has data such as "700$702$7 05$706$707$708$709$710$711$712$713$714$715$294404$294405$294407$294408$294409$294410"
and S_FIL_NAM is the same for all columns - "SBENE_FILID810-2010-04-07-10.59.17"
The query returns 829 rows. Also I have to include a few more columns in the query which have data larger than the 'PRC_ID', but I have not included them here in the sample code, as this code by itself returns the ORA-6502 error.
create or replace
FUNCTION "F_CLOB_TEST" (job_id in Number)
return clob
is
c_clob clob;
[code]....
SQL & PL/SQL :: Reading Oracle Statements From A Procedure / Function?
Sep 15, 2010I want to read the oracle procedure/function and find out the no of valid sql statment lines in a procedure:--
Consider the procedure is like :--
PROCEDURE TESTVAR IS
l_local number;
i integer;
j integer;
BEGIN
SELECT real_id_number
[code]....
Now i want to read this procedure in such a way that it should return me below output :-
Seq_No Sql_Statements
1 SELECT real_id_number
2 into l_local
3 FROM master_var v
4 where v.variable_type = i
5 AND V.ID_NUMBER = j ;
how to achieve this output.
SQL & PL/SQL :: Procedure To Call User Defined Function?
Apr 12, 2012I have made a function to add two number create or replace function add_num(a in number, b in number) return number
as
begin
return a+b;
end;
/
i run it via
select add_num(2,5) from dual;
my question is how to call add_num in procedure.
Forms :: Calling Function Inside The Procedure
Jun 2, 2011I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?
View 4 Replies View RelatedPL/SQL :: Best Way To Send A Procedure / Function Output To The Application
Sep 8, 2013What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc.
View 49 Replies View RelatedPL/SQL :: Call VB Function From A Package Stored Procedure
Apr 30, 2013I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
View 5 Replies View RelatedHow To Audit Execute On Specified Procedure Or Function In Package
Oct 4, 2012My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
Executing Stored Procedure / Function From Database Link?
Feb 24, 2011I have 2 databases:
Database A
Database B
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.