CODEWITHSUNDEEP
visual-studio-2010oracle-databaseentity-frameworkstored-proceduresplsqldeveloper
CodeCheshire
CodeCheshire

Reputation: 730

PLSQL stored procedure returns no values in Entity Framework

I have a working plsql oracle stored procedure (i have tested it in plsql and it return the correct value(s)):

  PROCEDURE FetchCode(code OUT NUMBER) IS
  BEGIN
      SELECT MYSEQ.NEXTVAL INTO code FROM DUAL;    
  END FetchCode;

and a visual studio solution using entity framework.

The csdl file contains 

      <FunctionImport Name="FETCHCODE" ReturnType="Collection(Decimal)">
      <Parameter Name="CODE" Mode="Out" Type="Decimal" />
      </FunctionImport>

I have been successful in pulling the above function in to entity but when i try the "Get Column Information" button, I receive the "The selected stored procedure returns no columns." message.

Under edit function import I can see that it:

Returns a Collection of Scalars: Decimal

The data type is set to "Number" and the direction is "Output".

Can anyone help explain why a sp like this would work on plsql but not in visual studio?

Upvotes: 0

Views: 1014

Answers (2)

codingbiz
codingbiz

Reputation: 26386

Try the following

  CREATE OR REPLACE PROCEDURE FetchCode
  (
     results_ OUT SYS_REFCURSOR
  ) IS
  BEGIN
      OPEN results_ FOR 
      SELECT MYSEQ.NEXTVAL As Code FROM DUAL;    
  END FetchCode;

Upvotes: 0

Anjan Biswas
Anjan Biswas

Reputation: 7912

Try

CREATE or REPLACE FUNCTION FetchCode IS
RETURN NUMBER
DECLARE
  code   NUMBER(10);
  BEGIN
      SELECT MYSEQ.NEXTVAL INTO code FROM DUAL;    
      RETURN code;
  END FetchCode;

Usually a function in Oracle is capable of returning values unlike Procedures where the output is accessed via the OUT or IN OUT variables.

Upvotes: 1

Related Questions