Windows :: Returning REF Cursor From PL/SQL Function?

Apr 5, 2011

I'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.

View 4 Replies

SQL & PL/SQL :: Returning Updated Rows With REF CURSOR?

Sep 27, 2013

The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?

-- Incorrect indicative example 1.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;

-- Incorrect indicative example 2.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;

View 4 Replies View Related

SQL & PL/SQL :: Returning Cursor For Dynamic Database

Jul 20, 2011

I am creating a db function to select * from table_name to be used by the application for retrieving lookup data. I found many examples for how to return cursor as a result of executing a dynamic sql but don't know the optimum way.

following is my current trial:

CREATE OR REPLACE PACKAGE types AS
TYPE Cursortype IS REF CURSOR;
END;
/

CREATE OR REPLACE FUNCTION F_GEN_SELECT_CURS
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2)
/*This function maps S_ACC_MAJOR_ACCOUNT_LIST CoolGen service
[code].......

View 5 Replies View Related

SQL & PL/SQL :: Checking If Cursor Is Returning Empty List?

Sep 24, 2010

I am select list of ids;

cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;

now I need to return err_code and err_message if this combined list is empty ! Can not use exit%notfound.

View 5 Replies View Related

SQL & PL/SQL :: Function Returning Top Value If Not Exists Then Next One?

Nov 20, 2010

how to write a function that returns top value if not exists then next top for combination of customer_id and hierarchy.For instance :

If I've got table

customer_id ,hierarchy, function_code
123 |1 | Z1
123 |2 |67
123 |3 |5B
678 |10 |S2
678 |11 |Z2
345 |2 |11

For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11

Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...

but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.

View 9 Replies View Related

SQL & PL/SQL :: Optimizing Function Returning Integer?

Feb 5, 2012

I have the following function that I am using as a template for any function that executes a select statement and return a single value as an output.

The function is working but I wanted to take an expert opinion if it can be optimized.

CREATE OR REPLACE FUNCTION AFESD.F_AGR_GET_AGREEMENT_SERIAL
(I_NUMBER0 IN NUMBER, S_SUB_NUMBER VARCHAR2 DEFAULT NULL, I_TYPE_ID NUMBER)

[Code]....

In addition I want to use the parameter S_SUB_NUMBER that can be NULL and add it to the select statement of the cursor, but I dont know how to do that in one statement.

CURSOR C_AGREEMENT
IS
SELECT AGREEMENT_SERIAL
FROM VW_AGR_AGREEMENT
WHERE NUMBER0 = I_NUMBER0
AND TYPE_ID = I_TYPE_ID
-->and sub_number is null;
-->and sumb_number = s_sub_number

View 11 Replies View Related

Function Not Returning Data (Desired Results)

Jul 14, 2010

I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.

----Create Test Table
CREATE TABLE VC_WORKINGDAYS
(
WK_ID number NUMBER(10,0),
WK_DATE DATE,
);

-- Insert test Data
INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');

SQL
----- Result Should be WHY
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today

Attached File(s)

create_Function.zip ( 6.39K )
Number of downloads: 1

InsertData.txt ( 84.34K )
Number of downloads: 0

View 1 Replies View Related

SQL & PL/SQL :: Function Returning A Table Inside Where Clause?

Apr 5, 2010

DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').

View 22 Replies View Related

SQL & PL/SQL :: Returning Value Of Dynamic Select In Oracle Function?

Jul 20, 2011

I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!

CREATE TABLE AFESD.MAJOR_ACCOUNT
(
NUMBER0 NUMBER(2) NOT NULL,
SHORT_NAME CHAR(35 BYTE) NOT NULL,
FULL_NAME CHAR(50 BYTE)
)

--Actually any table can do

CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2)
RETURN NUMBER
AS
I_RETURN NUMBER;
S_SQL VARCHAR2(300);
--S_DB_ERROR VARCHAR2(100);

[code]....

B.S. I didnt delete the commented lines to have your review comments.

View 21 Replies View Related

SQL & PL/SQL :: Function Returning Table Of Dynamic Structure

Apr 24, 2013

I have a requirement to be coded like this:

A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,

means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type

func f return pl_tab
as
...
end;

note : pl_tab_type will vary for each run of function f

i.e.,for example, pl_tab_type can be changed to as follows:

type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))

how to return pl/sql table of dynamic type from func,

View 12 Replies View Related

SQL & PL/SQL :: Function Returning CLOB - Invalid Format

Jan 22, 2011

I have a problem when trying to create a PLSQL function based on an XML extraction query.

I have three dummy tables:

SQL> get create_address
1 create table ADDRESS
2 (
3 id NUMBER not null,
4 house_number NUMBER,
5 house_name VARCHAR2(20),
6 street_name VARCHAR2(30),

[code]....

And the following dummy data for these:

1 insert all
2 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
3 values (1, 1, '', 'Tube Street', 'Norwich', 'Norfolk', 'NF12', '2DF')
4 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
5 values (2, 5, '', 'Dave Street', 'Edlington', 'Kent', 'CT34', '8GH')
6 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)

[code]....

So far so good then. But, what I want to create is a function where I can pass in an id value and return the corresponding XML CLOB.

So I try, very simply, this:

SQL> get get_xml_data
1 create or replace function get_xml_data(p_id in number) return clob is
2 Result clob;
3 begin
4 select xmlroot(xmlelement("HomeData",

[code]....

And, alas, i'm greeted by this:

SQL> /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION GET_XML_DATA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5 PL/SQL: SQL Statement ignored
24/63 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
SQL>

I've tried to redo the query in several different ways but so far nothing.

View 2 Replies View Related

Application Express :: Function Returning Blob Content

Sep 10, 2012

I am using the below function to return a blob (CSV) content, this is working fine..But i need to modify this function to display headline for the report and fixed width column in CSV ( then user no need to expand the each and every column ).

create or replace function GET_BLOB (
p_query varchar2
) return blob as

l_cursor integer;
l_cursor_status integer;
l_col_count number;
l_desc_tbl sys.dbms_sql.desc_tab2;
l_col_val varchar2(32767);
[code]....

View 10 Replies View Related

Application Express :: Validation - PLSQL Function Returning Boolean Bug?

Mar 12, 2013

Application Express 4.1.1.00.23 ( plus all earlier versions that I've ever used)

When using the wizard to create a Validation of type "PLSQL Function returning Boolean", why is it mandatory to enter a value in the text field "Error Message" on the screen that follows? This message is never used as the message actually displayed comes from a PLSQL return statement.

View 5 Replies View Related

Application Express :: Using Variables In PL/SQL Function Body Returning SQL Query?

Nov 20, 2012

I have this procedure

POSTCODE_TO_LAT_LNG_GM_API(postcode IN VARCHAR2, lat OUT NUMBER, p_long OUT NUMBER)

to convert a postcode into lat/long values. I then need to add them to the returned SQL statement so I used the string concat operator || with to_char but it comes up with this error when I try to apply the changes: Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic column'' checkbox below the region source to proceed without parsing.

ORA-00936: missing expressionh4.

h4. btw I'm using Oracle 11g release 11.2.0.3.0 and Apex version 4.1.1.00.23

DECLARE
l_lat NUMBER;
l_lng NUMBER;
l_SDO_GEOMETRY SDO_GEOMETRY;

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

SQL & PL/SQL :: Use Cursor As Parameter For Function?

May 4, 2011

I 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]....

View 34 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 :: Passing Cursor To Function

May 6, 2010

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

View 14 Replies View Related

SQL & PL/SQL :: Cursor With Parameter Inside Function

Mar 7, 2010

I 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

View 7 Replies View Related

SQL & PL/SQL :: Can Use Temp Table In Function Which Uses Ref Cursor

Dec 6, 2012

example to use temp table in function with only ref cursor.

I need to use for the reporting purpose.

View 1 Replies View Related

SQL & PL/SQL :: How To Handle Ref Cursor Returned From Another Function

Dec 16, 2011

declare
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

View 6 Replies View Related

SQL & PL/SQL :: Using Function Inside Query And Without Cursor?

Mar 17, 2013

To 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.

View 4 Replies View Related

Built-in Function To Covert CURSOR To VARCHAR?

Feb 13, 2007

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

SQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function

Feb 17, 2011

I 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].........

View 12 Replies View Related

Forms :: Two Cursor Record Block - Decode Function

Sep 16, 2010

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

View 2 Replies View Related

Precompilers, OCI & OCCI :: Pro*C - Cursor Leak With Cursor Array

Sep 7, 2007

I'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.

View 1 Replies View Related

SQL & PL/SQL :: Cursor With Bind Variable And Cursor Record

Feb 25, 2011

Is it possible to:

-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause

Did'nt succeed as shown in the example.

SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Converting Numeric Cursor To Ref Cursor?

Sep 11, 2011

just looking around to use the new feature available in oracle 11g to convert the dbms_sql numeric cursor to reference cursor, how to do it?

parse and execute the sql string first with dbms_sql and then convert it to ref cursor?

View 1 Replies View Related

SQL & PL/SQL :: Open Ref Cursor From Explicit Cursor

Nov 23, 2011

I want to return ref cursor based on explicit cursors

create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;

[Code].....

The above procedure has compilation errors when I am trying to open ref cursor

LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>