Dot Net - Dataset - retrieving from sys_refcursor Oracle Package

First, I am not sure if this is really a problem or just an issue with my understanding of some issues. Essentially, I was trying to access an ORACLE STORED PROCEDURE in a PACKAGE. The procedure returns a sys_Refcursor parameter - which simply means, it is weakly typed.

Creating a Table Adapter in VS2010 and making the appropriate references did not go on as I had anticipated. Normally, if you bind a procedure to a TableAdapter, it should generate the column list automatically as well as the parameter list - if any. However, I noticed that this was not the case for the sys_Refcursor type parameter procedure. I actually think - it's got nothing to do with the sys_refcurson type but a problem with the way the procedure name is generated with some funny escape characters. Either way, you can get around this issue following the steps described in this article.

Once you have this situation, the column list is not automatically generated for the Data table, neither is the parameter list generated correctly. You will need to take corrective measures to fix this. Allow the wizard to complete the setup - you are most likely going to get an alert of one form or the other indicating - the system could not access the procedure specified.

Once the wizard is done, click on the PROPERTY window for the table adapter - most likely, the name will be the default which will be tbe procedure name - with the sepecial charaters as well as a postfix - TableAdapter. You should change the default name for the TableAdapter as well as the DataTable - specifically, get rid of the special characters and ensure that the name is not too lengthy. Also, modifiy the command text to reflect the correct procedure name.

The last thing you will need to do is to manually generate the parameter list for the Generate Method type that you are using. Pay special attention to the output parameter which is of type sys_refcursor in Oracle. Set the properties as shown in the image below and you should be good to go.

Comments

Post a Comment

Popular posts from this blog

Decompiling Delphi - 3

I came across this on delphi.about.com web site and thought it useful to add it a a reference here. continued from part two The art of reverse engineering has traditionally been the land of technical wizards, familiar with assembly language and debuggers. Several Delphi decompilers have appeared that allow anybody, even with limited technical knowledge, to reverse engineer most Delphi executable files. If you are interested in reverse engineering Delphi programs I suggest you to take a look at the following few "decompilers": IDR (Interactive Delphi Reconstructor) A decompiler of executable files (EXE) and dynamic libraries (DLL), written in Delphi and executed in Windows32 environment. Final project goal is development of the program capable to restore the most part of initial Delphi source codes from the compiled file but IDR, as well as others Delphi decompilers, cannot do it yet. Nevertheless, IDR is in a status considerably to facilitate such process. In co...
Read more

Decompiling Delphi - 2

I came across this on delphi.about.com web site and thought it useful to add it a a reference here. continued from part one For the moment, Borland does not offer any product capable of decompiling an executable (.exe) file or the "Delphi compiled unit" (.dcu) back to the original source code (.pas). Delphi compiled unit: DCU When a Delphi project is compiled or run a compiled unit (.pas) file is created. By default the compiled version of each unit is stored in a separate binary-format file with the same name as the unit file, but with the extension .DCU. For example unit1.dcu contains the code and data declared in the unit1.pas file. This means that if you have someones, for example, component compiled source all you have to do is to reverse it and get the code. Wrong. The DCU file format is undocumented (proprietary format) and may change from version to version. After the compiler: Delphi Reverse Engineering If you would like to try to decompile a Delphi e...
Read more

Artificial Intelligence, Oxymoron and Natural Intelligence

Image
I posted this question to a couple of online AI engines and to my surprise, none came up with the correct anwser. This is the question - "Two Americans were crossing a bridge on a bright summer morning, one was the father of the other and the other wasn't the son. So, what was their relationship?" This is a classic IQ question - supposedly, employed by Microsoft in the easrly 90s in the recruitment of top talent for its work pool. Essentially, this is a mis-direction fixation challenge. On the surface - it is a fairly straight forward question with the requisite hint at solving it. For whatever reason, most people asked this question are always fixated on the "father and son" narative, which in reality - is the hint to solving the question, but always end up getting it wrong. My point really is - the so called AI engines got the answer to the question wrong as well. The real poser is - should we still be referring to the encompassing ecosystem...
Read more