SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies


ADVERTISEMENT

PL/SQL :: How Many OUT Parameters Can Be Declared In Function Header

Sep 4, 2012

How many OUT parameters can be declared in function header ?

A) None
B) Any Number
C) One for every return declared in the definition
D) Depends on what the RETURN datatype is

View 18 Replies View Related

Executing Function In A Package?

Mar 24, 2008

I have created one function in the package.

function : Tier_wh
package : Order_DESC

Function defined as

function TIER_WH( message in out Xorder_desc)
...
...
end;

Xorder_desc is defined as

create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);

how to execute this function which is defined in the package .

View 1 Replies View Related

SQL & PL/SQL :: Can Use Htp Package In Oracle Function

May 14, 2010

can we use htp package in function?

something like

if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;

View 4 Replies View Related

SQL & PL/SQL :: How To Use Ref Cursor In A Package Or In A Function

Sep 20, 2012

what is ref cursor?

How to use ref cursor in a package or in a function?

View 7 Replies View Related

SQL & PL/SQL :: Calling A Function In Package

Mar 9, 2010

I have this following pakg

CREATE OR REPLACE PACKAGE pkg_test AS

-- Create a table type of the table you want
TYPE tbl_test IS varray(100) of VARCHAR2(30);

-- Function that will return the table type
FUNCTION fnc_test RETURN tbl_test;

-- End package
END;

CREATE OR REPLACE PACKAGE BODY pkg_test AS
FUNCTION fnc_test RETURN tbl_test IS
-- type table_name_va is varray(100) of VARCHAR2(30);
-- Variable of the type tbl_test

[code]...

But i am having problem calling this to test it.

declare
TYPE tbl_test IS varray(100) of VARCHAR2(30);
var_tbl_test tbl_test;
begin
var_tbl_test:= pkg_test.fnc_test;

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Create Pipeline Function In Package

Feb 13, 2013

Can we create a Pipelined function in A Package ? I know we can create it standalone function.

View 11 Replies View Related

PL/SQL :: How To Test Procedure / Function / Package

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

PL/SQL :: Get A Called Procedure / Function Name Within Package?

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

SQL & PL/SQL :: Difference Between Procedure And Function

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

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 View Related

Export / Import Of Procedure - Function / Package

Jun 9, 2012

Can 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 Related

Client Tools :: How To Add Function To Existing Package

Nov 11, 2010

I want to add my function to the existing package.

View 8 Replies View Related

SQL & PL/SQL :: Providing Execute Privileges To Function In A Package?

Jun 6, 2013

I want to provide execute privilege to a function in a package to other schema.

Owner wedb
Package MASTER
function in a a package is convert_function

GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;

I got the blow error.

ORA-00905: missing keyword

View 2 Replies View Related

SQL & PL/SQL :: ORA-06575 Package Or Function In Invalid State

Feb 15, 2012

The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.

However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:

select GetDate ('010312', 'dd-mm-yyyy')
from dual

Here is the code I used to create the function:

create or replace function GetDate (vDate varchar2, vMask varchar2)
return varchar2
is

vReturnDate date;
begin
vReturnDate = to_date(vDate, vMask);
return vReturnDate;
exception
when value_error then
return 0;
end GetDate;
commit;

View 5 Replies View Related

PL/SQL :: Providing Execute Privileges To Function In A Package

Jun 6, 2013

I want to provide execute privilege to a function in a package to other schema.

Owner wedb
Package MASTER
function in a a package is convert_function

GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;I got the blow error.

ORA-00905: missing keyword

View 5 Replies View Related

PL/SQL :: Call VB Function From A Package Stored Procedure

Apr 30, 2013

I 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 Related

How To Audit Execute On Specified Procedure Or Function In Package

Oct 4, 2012

My 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 ???

View 1 Replies View Related

Difference Between Stored Procedure And Function?

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

SQL & PL/SQL :: Difference In Num_rows And COUNT Function?

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

SQL & PL/SQL :: Difference Between Concat Function And (||) Operator?

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

PL/SQL :: Difference Between Translate And Replace Function

Jan 6, 2013

Explain with the best example for difference between translate and replace function.

View 2 Replies View Related

PL/SQL :: Difference In Num_rows And Count Function

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

PL/SQL :: Unescape Data Of Table Column Using Package Function

Feb 8, 2013

I'm trying to un-escape the data of a table column using the package function - UTL_URL.Unescape . But the problem is my column is a CLOB and length of my data exceeds that acceptable by the function.

So, i'm just trying to simulate that function behavior normally. My logic is to scan all characters with pattern '%yy' (HEX) and then convert it into DECIMAL using TO_CHAR(,'XX') . But got stuck in this regexp-replace.

with xx as(
select '<p>%3Cp%3Ewefwef%3C/p%3E</p>' col from dual
)
select
UTL_URL.unescape(col) x1,
regexp_replace(col,'%([a-zA-Z0-9]){2}','2|.') x2
from
[code].......   

View 17 Replies View Related

SQL & PL/SQL :: Write A Function To Calculate Difference Between Two Dates?

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

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....

I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?

I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?

View 10 Replies View Related

SQL & PL/SQL :: Difference Between Deterministic Functions And Function Result Cache In 11g

Oct 30, 2012

difference between Difference between Deterministic functions and Function result cache in 11g?

View 4 Replies View Related

Reports & Discoverer :: Difference Between Formula Column And Function In Program Unit?

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

SQL & PL/SQL :: How To Reference Constants Declared In Package From Adhoc Query

Oct 25, 2010

We have a package that contains mostly constants like:

c_flag_true CONSTANT CHAR(1) := 'Y'

When I try to reference it in these constants in an adhoc query it doesn't work:

SQL> select * from FINANCIAL_BATCH_STATUS_HISTORY where active_flag = constants_pkg.c_flag_true;
select * from FINANCIAL_BATCH_STATUS_HISTORY where active_flag = constants_pkg.c_flag_true
ORA-06553: PLS-221: 'C_FLAG_TRUE' is not a procedure or is undefined

Is there a simple solution to make the reference work?

View 4 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved