CODEWITHSUNDEEP
c#sqloracle-databasestored-procedures
smilu
smilu

Reputation: 899

Ref cursor not returning column names from StoredProcedure?

I have a Oracle stored procedure which returns a SYS_REFCURSOR. I want to return custom Parameters which is declared inside the stored Procedure. Now, when i call it inside my application(C#), it doesn't show me the column names, instead it gives me names as :B1,:B2.. etc. How can I achieve this.

ORACLE EG:

CREATE PROCEDURE usp_MyStoredProcedure
(
    OUTPUT_DATA  OUT SYS_REFCURSOR
)
IS
P_NAME VARCHAR2;
P_AGE  VARCHAR2;
P_DOB  VARCHAR2;
BEGIN
     SELECT NAME INTO P_NAME FROM TABLE1;
     SELECT AGE INTO P_AGE FROM TABLE2;
     SELECT DOB INTO P_DOB FROM TABLE3;
OPEN OUTPUT_DATA FOR
    SELECT P_NAME,P_AGE,P_DOB FROM DUAL;
END;

Now, when I execute the above code I get the column names as :B1,:B2,:B3. How can i get the column names as P_NAME,P_AGE,P_DOB or some other alias names? Is it possilbe? (The same SELECT QUERY if you create in MSSQL you will get the Parameter names as Column names)

I already tried 

SELECT P_NAME as Name,P_AGE as Age,P_DOB as DOB FROM DUAL;

But then also i'm getting the same old result

THE QUERY WITH ALIAS NAME WAS NOT SAVED WHEN I TRIED.

Upvotes: 1

Views: 674

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

OPEN OUTPUT_DATA FOR
    SELECT P_NAME as name ,P_AGE as age,P_DOB as dob FROM DUAL;

You can test it via sqlplus like this.

VARIABLE MYCUR refcursor;
EXEC URPROC (:MYCUR);

print MYCUR;

You miss the alias if you choose values in query instead of column names.

Upvotes: 1

Related Questions