CODEWITHSUNDEEP
oracle-databaseentity-framework
Guy E
Guy E

Reputation: 1927

The selected stored procedure or function returns no columns when using Oracle SP

Visual Studio 2013, Entity Framework 5: Doing "add function import" for an Oracle stored procedure that has output type of SYS_REFCURSOR. In the "Returns a collection of" - choosing Complex and then clicking on the "Get Column Information" - I keep getting:

"The selected stored procedure or function returns no columns".

I'm aware that the config file (app.config in that case) should include entry for the stored procedure. I generated it:

<oracle.manageddataaccess.client>
    <version number="*">
      <implicitRefCursor>
        <storedProcedure schema="<Scheme name>" name="<SP Name>">
          <refCursor name="<ref cursor name>">
           <bindInfo mode="Output" />
          </refCursor>
        </storedProcedure>
      </implicitRefCursor>
    </version>
  </oracle.manageddataaccess.client>

What am I missing here ? (read lots of posts - none of them helped). What should be the value of the DBName in the settings entry ? the name of the DB itself ? The name of the connection string entry in the config file ? the name of the entity Framework that was created ?

Upvotes: 0

Views: 355

Answers (2)

Guy E
Guy E

Reputation: 1927

I found out what the problem was: 1. In the Server Explorer, run the procedure. 2. There's a screen which let you sign the option to include the needed configuration in the config file (see below). In order for the entity framework to generate the model - it should know the fields. In order to achieve this, need to check the "select for config" option, and then click "add config" This is the relevant screen: https://i.sstatic.net/FFoTv.jpg

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

I don't know Visual Studio. But, as of DBName you asked about: in Oracle, a connection string looks like connect username/password@database where database represents its alias in the TNSNAMES.ORA file. If you don't have it, you can still connect. Have a look at this example, maybe you'll see something familiar and will be able to use it.

I've removed confidential data because Big Brother is watching :), as well as some irrelevant stuff.

I should know database name (db11g) so that I could ping it and find out host, port and SID:

M:\>tnsping db11g

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=db_11g) (PORT=1521)) (CONNECT_DATA= (SID=db11g)))
OK (50 msec)

OK, now ping the host to find its IP address:

M:\>ping db_11g

Pinging db_11g [xxx.xxx.xxx.xxx] with 32 bytes of data:
Reply from xxx.xxx.xxx.xxx: bytes=32 time=4ms TTL=59

Finally, connect:

M:\>sqlplus scott/[email protected]:1521/db11g
                        --------------- 
                        IP address      ----
                                        Port -----
                                             SID

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL>

Hopefully, you'll make it work. If not, sorry, I can't help you any better.

Upvotes: 0

Related Questions