Stored Function with return type pl/sql record.
Posted: Thu 09 Jun 2005 15:46
I'm trying to get the return value of a stored function that returns a record. While doing that i keep getting two types of errors depending on whether i'm using a direct connection type or not.
With connection type set to direct i get an empty error message window when i click execute in the command text editing window of the OracleCommand. When using the indirect connection method i get an error "Illegal variable name/number." And that's with automatically generated parameters.
Below is a detailed description of what i do:
I use the designer interface to drag and drop OracleConnection and OracleCommand objects. I set up the connection and connects successfully. I set the command to use the connection i created. I open the command text editing window, set the type to Stored Procedure and type in the function name in the object dropdown. If i try to open the dropdown i get an error message about wrong charset and the dropdown comes up empty. I then click on parameters tab and answer yes to the little popup asking me whether i want it to autogenerate parameters for me. After that when i click execute i get one of the error messages i described above depending on whether i'm using a direct connection or not.
Also i noticed the the auto-generated parameters are different depending on whether i'm using a direct connection or not. In case of a direct connection the parameter names come up as $ otherwise they are RESULT$.
Sorry for the long post but i'm getting pretty desperate already considering how no other dataprovider lets me get the return value from an oracle function if the type of the value is pl/sql record.
Taavi Kuusik
With connection type set to direct i get an empty error message window when i click execute in the command text editing window of the OracleCommand. When using the indirect connection method i get an error "Illegal variable name/number." And that's with automatically generated parameters.
Below is a detailed description of what i do:
I use the designer interface to drag and drop OracleConnection and OracleCommand objects. I set up the connection and connects successfully. I set the command to use the connection i created. I open the command text editing window, set the type to Stored Procedure and type in the function name in the object dropdown. If i try to open the dropdown i get an error message about wrong charset and the dropdown comes up empty. I then click on parameters tab and answer yes to the little popup asking me whether i want it to autogenerate parameters for me. After that when i click execute i get one of the error messages i described above depending on whether i'm using a direct connection or not.
Also i noticed the the auto-generated parameters are different depending on whether i'm using a direct connection or not. In case of a direct connection the parameter names come up as $ otherwise they are RESULT$.
Sorry for the long post but i'm getting pretty desperate already considering how no other dataprovider lets me get the return value from an oracle function if the type of the value is pl/sql record.
Taavi Kuusik