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


ADVERTISEMENT

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I have the following C code:

class Factorial {
  public:
  int getVal (int a);
};
[code]....

/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.

Just in case, listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....

View 6 Replies View Related

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

SQL & PL/SQL :: Calling A Function With Parameters?

Feb 27, 2011

When I replace the params manualy with dates in format of:

'27-feb-2011 08:00:00'

I get the real output.

But when I call the function with the TEST button in the PLSQL I don't get any output (Empty table).

Here is the original function (Bad output) and attaching a file with manuly added dates (Good output):

FUNCTION WhatRoomsTaken(dStartTime date, dEndTime date, dEventDate date)
RETURN Genrefcursor IS
Retval Genrefcursor;
BEGIN
open Retval for

[code].....

Why do I need to change in order to get the good output by calling the function with params?

View 5 Replies View Related

SQL & PL/SQL :: Calling Procedure Into Function?

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

SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary

May 8, 2010

I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?

Actual Output:::

DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600

I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column

View 4 Replies View Related

PL/SQL :: Privileges Required For Calling A Function

Sep 6, 2012

11.2

For procedures, we need to grant EXECUTE privilege.

For example:

grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.

Is it SELECT or EXECUTE ?

grant select on SCOTT.GET_EMPIDS to john;or

grant execution on SCOTT.GET_EMPIDS to john;

View 2 Replies View Related

Error While Calling External C Function From Oracle?

Apr 7, 2011

I tried to use external C procedure from the database and i did all required steps as below.
1. create a C program
2. compile and link the C program
3. copy it to the Oracle_home/bin directory
4. configure listener.ora and tnsnames.ora files

listener.ora
=======
callout_listener =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP) (HOST = ip_address)(PORT = 1521)

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Calling Pipelined Function In Merge Statement?

Dec 2, 2010

I am getting a (PL/SQL: ORA-00903: invalid table name) compile error in a procedure using a merge statement. I have seen many examples using this technique and am at a loss as to why I can't compile.

the pipelined function is:

FUNCTION f_crcli_pipe(pi_source_data IN sys_refcursor,
pi_limit_size IN PLS_INTEGER DEFAULT pkg_crcli_variables.c_cursor_limit_def)
RETURN CRCLI_AA
PIPELINED
PARALLEL_ENABLE(PARTITION pi_source_data BY ANY)
IS

[code].....

the error is pointing to the TABLE function in the USING clause of the merge statement.

View 7 Replies View Related

Forms :: Calling Function Inside The Procedure

Jun 2, 2011

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

SQL & PL/SQL :: Dynamic Calling Function With Type Record Parameter?

Jul 23, 2010

I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.

My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),

[code].....

View 5 Replies View Related

SQL & PL/SQL :: Connection Timeout When Calling Oracle Function From Java Program?

Jun 18, 2013

We have a daily batch job executing a oracle-plsql function. Actually the quartz scheduler invokes a java program which makes a call to the oracle-plsql function. This oracle plsql function deletes data (which is more than 6 months) from 4 tables and then commits the transaction.

This batch job was running successfully in the test environment but started failing when new data was dumped to the tables which happened 2 weeks ago (The code is supposed to go into production this week). Earlier the number of rows in each table was not more than 0.1 million. But now it is 1 million in 3 tables and 2.4 million in the other table.

After running for 3 hours, we are getting a error in java (written in the log file) "...Connection reset; nested exception is java.sql.SQLException: Io exception: Connection reset....". When the row-counts on the tables were checked, it was clear that no record was deleted from any of the tables.

Is it possible in oracle database, for the plsql procedure/function to be automatically terminated/killed when the connection is timed out and the invoking session is no longer active?

View 13 Replies View Related

PL/SQL :: Calling A Function In Remote Database Inside A Stored Procedure

Apr 9, 2013

There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:

CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN

[Code]...

There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................

where my mistake is?

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

JDeveloper, Java & XML :: Calling Function With (array Of) Complex Data Type

Oct 20, 2011

I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.

a) basic definitions (work and are ok)

CREATE OR REPLACE TYPE eventtype AS OBJECT
(
relevance NUMBER,
bid1 NUMBER,tid1 NUMBER,prob1 NUMBER,
bid2 NUMBER,tid2 NUMBER,prob2 NUMBER,
bid3 NUMBER,tid3 NUMBER,prob3 NUMBER,
bid4 NUMBER,tid4 NUMBER,prob4 NUMBER,
bid5 NUMBER,tid5 NUMBER,prob5 NUMBER,
bid6 NUMBER,tid6 NUMBER,prob6 NUMBER
);
CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype;
/

b) The following call works (but is not exactly that what I want):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal';
/
SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;

c) The following call doesn't work (but should have the intended semantics):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal';
/
SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;

d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.

The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.

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

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

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 :: 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

PL/SQL :: Find Out In Database Package Where Package Is Installed

May 28, 2013

I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').

What I would need is something like $$PLSQL_UNIT

View 8 Replies View Related







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