SQL & PL/SQL :: How To Use Ref Cursor In A Package Or In A Function
Sep 20, 2012what is ref cursor?
How to use ref cursor in a package or in a function?
ADVERTISEMENT
SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?
Mar 11, 2010What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View RelatedExecuting Function In A Package?
Mar 24, 2008I 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 .
SQL & PL/SQL :: Can Use Htp Package In Oracle Function
May 14, 2010can 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;
SQL & PL/SQL :: Calling A Function In Package
Mar 9, 2010I 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]...
SQL & PL/SQL :: Create Pipeline Function In Package
Feb 13, 2013Can we create a Pipelined function in A Package ? I know we can create it standalone function.
View 11 Replies View RelatedPL/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 :: 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.
Export / 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 RelatedClient Tools :: How To Add Function To Existing Package
Nov 11, 2010I want to add my function to the existing package.
View 8 Replies View RelatedSQL & PL/SQL :: Providing Execute Privileges To Function In A Package?
Jun 6, 2013I 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
SQL & PL/SQL :: ORA-06575 Package Or Function In Invalid State
Feb 15, 2012The 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;
PL/SQL :: Providing Execute Privileges To Function In A Package
Jun 6, 2013I 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
PL/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 ???
PL/SQL :: Unescape Data Of Table Column Using Package Function
Feb 8, 2013I'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].......
SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body
Jul 26, 2011CREATE 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?
SQL & PL/SQL :: Use Cursor As Parameter For Function?
May 4, 2011I was wondering if it's possible to use the cursor as a parameter for a function. Something like this is what I'm trying to do:
set serverouput on
declare
cursor csv_file
[Code]....
SQL & PL/SQL :: Passing Cursor To Function
May 6, 2010I am strugling hard to pass a cursor to my function as in parameter.here is my code
Function migrate_audits (sys_audit_ids SYS_REFCURSOR ) return number;
Function migrate_audits (sys_audit_ids in sys_refcursor ) return number
is
v_return number;
v_sys_audit_id number;
begin
LOOP FETCH sys_audit_ids INTO v_sys_audit_id;
[code]....
passing cursor to a function is not possible in oracle? what other option I have to pass collection to the function ?
SQL & PL/SQL :: Cursor With Parameter Inside Function
Mar 7, 2010I have code inside function
.....
cursor cur1 is
select *
from sarchkler
where sarchkler_appl_no = in_appl_no
begin
select max(saradap_appl_no) into in_appl_no from saradap;
for rec1 in cur1 loop
......
my question I get variable for cursor after cursor declaration
SQL & PL/SQL :: Can Use Temp Table In Function Which Uses Ref Cursor
Dec 6, 2012example to use temp table in function with only ref cursor.
I need to use for the reporting purpose.
SQL & PL/SQL :: How To Handle Ref Cursor Returned From Another Function
Dec 16, 2011declare
type osd_refone is ref cursor;
osd_ref osd_refone;
l_status number;
[code]......
abc_reports in this pack "ab_report" it is the function it having the ref cursor as out parameter . when am executing the above anonymous block am getting the below error,so how can i print the out ref cursor data in my block.
ERROR at line 8:
ORA-06550: line 8, column 12:
PLS-00221: 'OSD_REF' is not a procedure or is undefined
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
Windows :: Returning REF Cursor From PL/SQL Function?
Apr 5, 2011I've seen several code samples that show how to return a REFCURSOR from a Stored Function, but when I try it with C# it gives me allways WRONG NUMBER OF ARGUMENTS or somehing like that.
I presently don't have my code in here, but it is something like this:
cmd.Parameters.add ("parRefCursor", dbresult.RefCursor, direction.Result);
I'm being able to use it as a OUT parameter, but I would like to have it as a RETURN value from a Function.
SQL & PL/SQL :: Using Function Inside Query And Without Cursor?
Mar 17, 2013To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
NAME VARCHAR2(20)
M1 NUMBER
M2 NUMBER
How do I do that using PL/SQL and without Cursor.
Built-in Function To Covert CURSOR To VARCHAR?
Feb 13, 2007know if there's a built-in function to covert an Oracle CURSOR to VARCHAR? Or how about a XMLType to VARCHAR?
View 1 Replies View RelatedSQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function
Feb 17, 2011I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit
create or replace
PACKAGE BODY BILL AS
FUNCTION FILTERI RETURN BILL.refcursor IS
testcursor BILL.refcursor;
ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);
BEGIN
[code].........
Forms :: Two Cursor Record Block - Decode Function
Sep 16, 2010I have Two cursor record block..which is attached in form..
My TASK IS
In my first Block, When DBCR Column = 'D' Then in backend this column value should be save as a '1'
WHEN DBCR Column = 'C' Then Then in backend this column value should be save as a '2'
My Both Field is on Data Block...
In Property palette of this field can we write any decode condition..so it reflects directly on database.
PL/SQL :: Find Out In Database Package Where Package Is Installed
May 28, 2013I 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
SQL & PL/SQL :: Global Exception In Package Available Outside Package?
Jan 15, 2012I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
Precompilers, OCI & OCCI :: Pro*C - Cursor Leak With Cursor Array
Sep 7, 2007I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.