This content has been marked as final. Show 5 replies
It sounds like you are trying to use an imported function. If you want any kind of return value from the function, the stored procedure must have a ref cursor. Then you need to set up the app.config to define the metadata for the ref cursor. If the metadata is setup wrong, clicking "get column information" will do nothing.
let me tell you in detail,, while function import we have 3 option which tell us which return type we have to use scalar,complex or entity
here what if i want to map my sp output to one entity , then here i m suppose to select entity as a return of collection type the same senario easily work with SQL DB
over there i m able to get column information but for oracle can i do this...
by using sp with ref cursor i can do for that i m suppose to define information in app.config so same manner what other settings i hav to do for entity collection type......
The way the Oracle provider works right now requires that you use a cursor, even if you want to map it to an entity. To map it to an entity, that cursor needs to contain the necessary columns to form the entity (ie: it's returning data from the same table).
Here's an example from one I have mapped:
CREATE OR REPLACE PROCEDURE ENVMSTR.P_ORG_UNIT_R_BY_STAFF
STAFF_ID_P IN NUMBER
, RESULT_CURSOR_P OUT SYS_REFCURSOR
OPEN RESULT_CURSOR_P FOR
from dept_organizational_unit d
where deptorgunit_cd = (select deptorgunit_cd from staff where staff_id = STAFF_ID_P)
or deptorgunit_cd in ( select deptorgunitrel_parent_cd from dept_org_unit_relation
start with deptorgunitrel_child_cd = (select deptorgunit_cd from staff where staff_id = STAFF_ID_P)
connect by prior deptorgunitrel_parent_cd = deptorgunitrel_child_cd);
<add name="ENVMSTR.P_ORG_UNIT_R_BY_STAFF.RefCursor.RESULT_CURSOR_P" value="implicitRefCursor bindinfo='mode=Output'" />
With those things set up, I'm able to import the function and map it to the entity that represents the dept_organizational_unit table (that entity is called OrgUnit in the model, but it doesn't really matter what you call it). The interesting thing I found is that in this situation I didn't actually have to add config file entries for each column in the cursor, probably because it maps perfectly to the entity. If I was trying to use a complex type instead then I probably would (and the configuration required to do that gets horrific in a hurry).