return single record
Hi Gurus! i want to return a SQL which will call a function which returns one and only record. like select function(arguments list) from dual; (or something similar to that); I am not supoosed to use ref cursor or object (collection type) or pl-sql table . This funtion will certainly return use a user defined data type ( i am guessing ) but i want it to be used in SQL statement. thanx in advance.. Shishir Kumar MishraAgni Software (P) Ltd.,Bangalore-560055, India Email :[EMAIL PROTECTED]
RE: return single record
Hmmm... let me have it clearly ... 1. You want to execute a function using SELECT 2. This function will return only one record. 3. This function will return a *user-defined-record* Well, Oracle doesn't have a problem with 1 maybe 2 but it certainly does have a problem with 3. SELECT can't handle data structures it doesn't know about. Call me old timer, but what stops you from using the simple way ... why SQL? my_rec := my_users_function(arglist); You could do this dynamically too ... Okay ... what am I missing in this picture? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, December 18, 2001 7:25 AM To: Multiple recipients of list ORACLE-L Hi Gurus! i want to return a SQL which will call a function which returns one and only record. like select function(arguments list) from dual; (or something similar to that); I am not supoosed to use ref cursor or object (collection type) or pl-sql table . This funtion will certainly return use a user defined data type ( i am guessing ) but i want it to be used in SQL statement. thanx in advance.. Shishir Kumar Mishra Agni Software (P) Ltd., Bangalore-560055, India Email :[EMAIL PROTECTED] *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1
Re: return single record
Hi Raj ! Actually I had similar problems some days back where i was supposed to treturn recordset(more than one) . At that time i created object and collection of object and then using cast operator and the operator i was able to return recodset from function . I could have used that function in sql statement also; Actually i am Delphi programmar and our client does not want to use like that. Moreover they don't want to use ref cursor or any PL sql table i hope u will sugeest some thing on it. thanx in advance Shishir Kumar Mishra Software Engineer Agni Software (P) Ltd., 25/1, 11th Main,18th Cross, Malleswaram, Bangalore-560055, India Phone : +91-80-344 4576, 346 1126,346 1127 Fax : +91-80-334 2049 Email :[EMAIL PROTECTED] -- - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 6:45 PM Hmmm... let me have it clearly ... 1. You want to execute a function using SELECT 2. This function will return only one record. 3. This function will return a *user-defined-record* Well, Oracle doesn't have a problem with 1 maybe 2 but it certainly does have a problem with 3. SELECT can't handle data structures it doesn't know about. Call me old timer, but what stops you from using the simple way ... why SQL? my_rec := my_users_function(arglist); You could do this dynamically too ... Okay ... what am I missing in this picture? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, December 18, 2001 7:25 AM To: Multiple recipients of list ORACLE-L Hi Gurus! i want to return a SQL which will call a function which returns one and only record. like select function(arguments list) from dual; (or something similar to that); I am not supoosed to use ref cursor or object (collection type) or pl-sql table . This funtion will certainly return use a user defined data type ( i am guessing ) but i want it to be used in SQL statement. thanx in advance.. Shishir Kumar Mishra Agni Software (P) Ltd., Bangalore-560055, India Email :[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shishir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: return single record
I think using 'the' on the collection would be my second choice after ref cursor. Why does your client doesn't want to use ref cursor? I can't think of anything else, as returning arbitrary data structure would be a problem in select. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, December 18, 2001 11:15 AM To: Multiple recipients of list ORACLE-L Hi Raj ! Actually I had similar problems some days back where i was supposed to treturn recordset(more than one) . At that time i created object and collection of object and then using cast operator and the operator i was able to return recodset from function . I could have used that function in sql statement also; Actually i am Delphi programmar and our client does not want to use like that. Moreover they don't want to use ref cursor or any PL sql table i hope u will sugeest some thing on it. thanx in advance *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1