How can i call a Oracle Function which return value is user defined record? SOS !!!!!

Package and Function:

SPEC

CREATE OR REPLACE PACKAGE PACKAGE_SUHUATEST AS
type resp is record

(resp_key varchar(30),

app_name varchar2(15));

type resp_tab is table of resp INDEX BY BINARY_INTEGER;


FUNCTION MyFunction(invar in varchar) return resp_tab ;
 

END PACKAGE_SUHUATEST;
/

 

BODY

CREATE OR REPLACE PACKAGE BODY MVL_SOX.PACKAGE_SUHUATEST AS


  FUNCTION MyFunction(invar in varchar) return resp_tab  IS
  record1 resp;
  record2  resp;
  allresult resp_tab;
  BEGIN
    record1.resp_key :='key1';
    record1.app_name :='name1';
   
    record2.resp_key :='key2';
    record2.app_name :='name2';
   
    allresult(1) := record1;
    allresult(2) := record2;
   
    RETURN allresult;
   
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END MyFunction;

 

 


DO my best.
0
suhua
5/30/2008 6:21:54 AM
asp.net.oracle 2269 articles. 0 followers. Follow

3 Replies
1043 Views

Similar Articles

[PageSpeed] 31

Hi ,

i think return is not supported in Oracle as in Sql Server,

instead use the output parameter.

 

 

Regards

Suresh Kumar Goudampally

0
suresh_g_v2002
5/30/2008 7:25:30 AM

Thank you very much,

 can you give me a example.

 

i want to call it using c#.

Best regards.

 

Suhua


DO my best.
0
suhua
5/30/2008 8:35:24 AM

Hi,

  The Oracle Call Interface doesnt support PLSQL Record type, so you cant return one to client side programs, only plsql.  You could use an Object type instead, and return a Varray of Objects (as either Return or Out param) but you'd have to use Oracle's 11g ODP for that as I dont believe System.Data.OracleClieint supports User Defined Types and Oracle's only recently does (as of 11g) .  There are numerous examples that install with the product.

Hope it helps,

Greg

0
greg
5/30/2008 3:07:20 PM
Reply:

Similar Artilces:

Problem calling an Oracle function with a return value
Hi, Vitals.... Win2000 PB6.5 Oracle 8.05 I am attepting to fire off an oracle function, via PB, that will do an insert (among other things) and then return the primary key of the transaction it just inserted. I have attempted (unsuccessfully!) to code it using the Dynamic SQL Format #3 (code below). I keep receiving "Procedure has not been executed or has no results". Code: DECLARE mo_load DYNAMIC CURSOR FOR SQLSA; ls_sql = "imf_create_im_master_order(" + string(idb_location_id) + ", " + & string(ldb_order_frequency_pk) + ",...

How to call an oracle function with a user defined type as parameter
OracleCommand cmd2 = new OracleCommand();cmd2.Connection = conn;cmd2.CommandText = "<package.functionname>";cmd2.CommandType = CommandType.StoredProcedure;   cmd2.Parameters.AddWithValue("org_id", ???);cmd2.Parameters.AddWithValue("date_from", 26-11-2008 16:56:29);cmd2.Parameters.AddWithValue("date_to", 26-11-2008 16:56:29);cmd2.Parameters.AddWithValue("id", 10); cmd2.Parameters.Add("crs_kzlpers", OracleType.Cursor).Direction = ParameterDirection.Output;etc.. The ??? parameter has a should look something like this:&n...

How call a Oracle Function using Microsoft .Net Oracle Client (System.Data.OracleClient)?
I try to call Oracle Function in .net C# by using System.Data.OracleClient. But i am not successful. Here is my code FUNCTION TTHAO_MONTT_EXISTS ( vten varchar2) RETURN boolean IS ret boolean; montt_id integer; BEGIN ret:=false; montt_id:= -1; select monthethao_id into montt_id from TTHAO_MONTHETHAO where ten = vten; if montt_id!=-1 then ret:=true; end if; return ret; Exception when others then return false; END; I do not know, when I call function TTHAO_MONTT_E...

Problem in getting Oracle function return value using ODP.net
 Dear AllI fall a problem by getting value from oracle function in asp.net. the code is as follows...the oracle function is ...start... create or replace function ultimus.FXN_GET_TRAN_DATE(pBRANCH_ID IN VARCHAR2) return date is-- ********************************************************************************-- Description: Get Branch Working Date-- ================================================================================  Result DATE;begin     Select  DECODE(a.day_status_id, 2, a.TRAN_DT, NULL) AS TRAN_DT      Into Resu...

Calling MSSql User Defined Functions from Powerbuilder not returning proper decimal value.
Help Sqlserver 2005, Powerbuilder 9.0.2 and connected via the older MSS DB driver I have a Sqlserver user defined scalar function , dbo.fc_bto_agreement_calculated_amt(), that you pass in an ID and returns a MONEY datatype. and for a given agreement id of say 100, I except a value of 123.50 when I define a remote upc for the function in powerbuilder for SQLCA as this Function LONG FC_bto_agreement_calculated_amt(long claimagreement_id) RPCFUNC ALIAS FOR "dbo.fc_bto_agreement_calculated_amt" and test it, I get as expected, just the values to the left of t...

Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle
Hello, I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something likereturn (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id); instead of using the way below (look at the C# code) Stored Procedure create or replace PROCEDURE "GET_ACCOUNT" ( p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur ) IS BEGIN OPEN retCursor FOR SELECT ID, NAME FROM ACCOUNT WHERE ID = p_ID ; ...

Oracle Function with return value
Hi, I'm looking for a way to execute a oracle function, with parameters and a return value. I've tried declaring a transaction user object and also an execute from a string, but either way I couldn't figure a way to get the return value. Thanks a lot! You're on the right track. Declare the Oracle function as an external function in the transaction user object using RPCFUNC, set your application to use the user transaction object as the transaction class, then call the function: s_foo = SQLCA.fu_bar (foo, bar) and check SQLCA.SQLCode to make sure there wasn't an...

Can we call user defined Javascript Function from an AJAX call in code behind
Hi all,     I having a problem regarding how to call my javascript functionfrom code behind in AJAX. Let me explain this nicely...I'm using ajax with databound control datalist in a page. The ajax part is working perfect. I'm loading this page in an iFrame. Now my problem, since the datalist control get the data from the database the no of records varies.So if in case the records are more the records are more my page won't be seen full and get cut from the bottom. I have a javascript function to resize the iFram but that is called on load property of the iFram...

Anyone have PB9 called Oracle SP that calls c that returns values successfully?
Asking this for a coworker who's going crazy trying to figure out the problem He has PB9 calling Oracle SP that's calling a c function that was compiled using Oracle Pro C/C++ Precompiler. He's wanting the out variables returning data but they don't. I'm not really familiar with the Oracle parameter types so I'm not sure what the problem is: -- the sp as defined in our oracle 9i db CREATE OR REPLACE function job_description(is_spw varchar2, is_suserid varchar2, li_study_seq binary_integer, ls_survey_type varchar2, ls_item varchar2, ls_case_grp varchar2,...

Passing boolean values to oracle function / procedure from C#.Net
Hi,  I dont know how to pass boolean values to oracle function / procedure arguments, could you please help me to know ?  Thank you, Sreedhar After spending lot of time on google, I understand C#.Net doest support PL/SQL boolean values. Thanks Sreedhar...

PB 10.5.1 Build 6021 , Oracle 10g server , Oracle 8i client. Making an RPC call to a Oracle Function and the last character is getting truncated
Making an RPC call to a Oracle Function and the last character is getting truncated. Similar Support Case : CR Number: 386424 Case Number: 11158124 I tried Disableunicode = 1 and not working .Is there any other workaround for oracle 8i client. That case was for Oracle 9i server and 8i client. There was a second workaround which was to use the Oracle 9i client. Not sure if that would work for you since you're on Oracle 10g server. > Making an RPC call to a Oracle Function and the last > character is getting truncated. > > Simi...

How To call Oracle function from ADO.NET?
How To call Oracle function from ADO.NET? The simple function takes 2 integer arguments and returns string. I want to call this function from my C# code. Thanks the sql would be select function(param1,param2) as result from dual; where function is your function, an param* are your two arguements. The result will come back as recordset("result"). Best not use result because it could quite easily be reserved word. Hi, Or you can use ExecuteNonQuery and access the return param directly. Example follows. Greg /* create or replace function myf...

How to user user-functions in Oracle
Hi, Using PB5.0 & PFC & Or7.1/7.2/7.3 I have a user defined function in Oracle in the format Function foo( in number) return varchar2 which i wish to use as follows, in my Datawindow syntax select var1, foo( var2 ) from tab1 This works perfectly in SQL*plus but when I try putting this in the datawindow SQL by converting to syntax or otherwise I get erroe ORA-00904, Invalid column name. What is to be done to use user defined functions & Stored Proc in this manner --- Sanjay Minni Software Consultant Minisoft Pvt. Ltd. 2...

How to user user-functions in Oracle
Hi, Using PB5.0 & PFC & Or7.1/7.2/7.3 I have a user defined function in Oracle in the format Function foo( in number) return varchar2 which i wish to use as follows, in my Datawindow syntax select var1, foo( var2 ) from tab1 This works perfectly in SQL*plus but when I try putting this in the datawindow SQL by converting to syntax or otherwise I get erroe ORA-00904, Invalid column name. What is to be done to use user defined functions & Stored Proc in this manner --- Sanjay Minni Software Consultant Minisoft Pvt. Ltd. 2...

Web resources about - How can i call a Oracle Function which return value is user defined record? SOS !!!!! - asp.net.oracle

Talk:Trigonometric functions - Wikipedia, the free encyclopedia
This article is within the scope of WikiProject Mathematics , a collaborative effort to improve the coverage of Mathematics on Wikipedia. If ...

TV binging, exercise skipping linked to poor cognitive function
Bad habits early in life may affect brain power later, researchers suggest.

iPhone best tricks and functions - Business Insider
Your iPhone can do a lot more than you think.

HPE Offers Catalog of Virtual Network Functions for Telcos
The vendor's OpenNFV Solution Portal gives carriers a single place to find a range of VNFs that are pre-tested to run on their networks.

Code Craft – Embedding C++: Timing Virtual Functions
... compiler just like the standard language types. A subtype inherits the characteristics of its parent type and can use, or not, the member functions ...

Political row over omitting Kerala CM from PM Modi’s function
Kerala chief minister Oommen Chandy on Saturday decided to keep away from Prime Minister Narendra Modi’s function in Kollam in south Kerala after ...

Colorado governor: This Planned Parenthood shooting may be a function of inflammatory rhetoric from bloggers ...
Via Grabien , skip to 4:45 of the clip below for the key bit. I naively thought we were (mostly) done with “climate of hate” stupidity after ...

Surprise: Binge-Watching TV May Cause a Decline in Brain Function
With the second season of Transparent only a few days away, some bad news awaits us: According to a recent study and common sense, probably, ...

1964 Archival Footage of British Soldiers Under the Effects of LSD Losing the Ability to Properly Function ...
1964 archival footage of British soldiers being given LSD-25 while training the the field. Over time they lost their ability to properly function, ...

Power Through Your Work With a “Forcing Function”
... to flow faster and more easily when I know I only have a couple of hours to turn it in. Entrepreneur Dan Martell calls this a “forcing function” ...

Resources last updated: 12/17/2015 6:11:44 AM