0

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?

CodeCheshire
  • 710
  • 1
  • 8
  • 27

2 Answers2

1

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.

Anjan Biswas
  • 7,746
  • 5
  • 47
  • 77
0

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;
codingbiz
  • 26,179
  • 8
  • 59
  • 96
  • I tried this solution and the csdl file changed to the following: I attempted to get column information and on execute it exits out of the 'edit function import' window entirely. It still does not seem to be returning anything on the edmx/vs side unfortunately. – CodeCheshire Jul 22 '12 at 16:49