Can a oracle Stored procedure return a cursor as a return value?

[Kalyana Chakravathy]

Hi,
I have a doubt on Oracle Stored Procedures and Functions. What is difference between the two and can a stored procedure return a value back. Some one at my place wrote a stored procedure that he says returns a cursor.
I wrote code in java using Stored Procedure in spring. But it always gives me a DB Exception.

[2010-05-20 12:18:15,373] - [main ] - [ERROR] [com.yahoo.sm.ads.ame.common.service.AccountMappingServiceImpl] Cannot access data.CallableStatementCallback; bad SQL grammar [{call GET_YCRM_STG_CONTACT_DEFAULT(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_YCRM_STG_CONTACT_DEFAULT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

com.test.data.YCRMDataException: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GET_YCRM_STG_CUSTOMER_DEFAULT(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_YCRM_STG_CUSTOMER_DEFAULT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
        at com.test.CustomerDataServiceImpl.getCustomerDetails(CustomerDataServiceImpl.java:39)
        at com.test.GetDataTest.testGetCustomerDetails(GetDataTest.java:42)
Caused by: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GET_YCRM_STG_CUSTOMER_DEFAULT(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_YCRM_STG_CUSTOMER_DEFAULT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:952)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:985)
        at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:117)
        at com.test.storedprocedure.CustomerDetailsStoredProcedure.execute(CustomerDetailsStoredProcedure.java:38)
        at com.test.CustomerDataServiceImpl.getCustomerDetails(CustomerDataServiceImpl.java:31)
        ... 28 more

It seems it thinks the procedure takes two parameters when I don't say its a function. I see in debug it takes the SqlOutParameter also as a stored procedure parameter. When I declare it to be a function its not, its seeing only SqlParameter declarations and changing.

PL/SQL: Statement ignored
        at com.yahoo.sm.ads.ame.common.ycrm.CustomerDataServiceImpl.getCustomerDetails(CustomerDataServiceImpl.java:39)
        at com.yahoo.sm.ads.ame.common.ycrm.GetDataTest.testGetCustomerDetails(GetDataTest.java:42)
Caused by: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GET_YCRM_STG_CUSTOMER_DEFAULT(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7

It takes only one parameter.

If i change it to function in my java code, it says there is no function in scope, and there is none.

Thats when I got the doubt, can stored procedures return values.

Please answer anyone.

I had another thread with question on how to access stored procedure, but no one is answering it.

Thanks,
Kalyana CJ.

[Fatih Keles]

Hi Kalyana,

Can you post pl/sql code?

Regards,

Fatih.

[Kalyana Chakravathy]

The procedure.

create or replace
PROCEDURE GET_YCRM_STG_CUSTOMER_DEFAULT(customerId IN NUMBER)
AS
	  TYPE REC_CURSOR IS REF CURSOR;
	  CONTACT_REC 	REC_CURSOR;
    TYPE CONTACT_DETAIL IS RECORD
  (
        opFirstName 		  VARCHAR2(150 CHAR),   --1
        opLastName 		    VARCHAR2(150 CHAR),   --2
        opMiddleInitial 	VARCHAR2(50 CHAR),    --3
        opUserRole 		    varchar2(150 char),   --4
        opJobTitle 		    VARCHAR2(150 CHAR),   --5
        opEmail 		      VARCHAR2(100 CHAR),   --6
        opEmailFormat 	  varchar2(150 char),   --7
        opPhone1 		      VARCHAR2(120 CHAR),   --8
        opPhone2 		      VARCHAR2(120 CHAR),   --9
        opFax 		        VARCHAR2(120 CHAR),   --10
  );
  REF_CONTACT_DETAIL  CONTACT_DETAIL;
BEGIN
	OPEN CONTACT_REC FOR SELECT  
        A.CON_FST_NAME,                         --1
        A.CON_LAST_NAME,                        --2
        A.CON_MID_NAME,                         --3
        'Super Admin',                          --4
        A.JOB_TITLE,                            --5
        A.PR_EMAIL_ADDR,                        --6
        'TEXT',                                 --7
        A.PRI_PHONE,                            --8
        A.SEC_PHONE,                            --9
        A.CON_FAX_NUM,                          --10
	FROM 
        CONTACT A,
        CON_ADDR B
	WHERE 	
        B.CON_ID(+)=A.CON_ID
        AND A.CUST_ID=customerId;
  LOOP
	FETCH CONTACT_REC INTO REF_CONTACT_DETAIL;
	EXIT WHEN CONTACT_REC%NOTFOUND;
     
	  END LOOP;
  close CONTACT_REC;
END;

The java code,

public class CustomerDetailsStoredProcedure extends StoredProcedure {

	public CustomerDetailsStoredProcedure(DataSource ds) {
		super(ds, StoredProcedures.CUST_CONTACT_DETAILS);

		declareParameter(new SqlOutParameter(Constants.Cursor.getValue(), OracleTypes.CURSOR, new ContactDetailsMapper()));
		declareParameter(new SqlParameter("customerID", Types.BIGINT));
		compile();
	}

	public Map execute(long customerID) {
		Map inputs = new HashMap();
		inputs.put("customerId", customerID);
		return super.execute(inputs);
	}
}

This fails with the DB exception,

Cannot access data.CallableStatementCallback; bad SQL grammar [{call GET_YCRM_STG_CONTACT_DEFAULT(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_YCRM_STG_CONTACT_DEFAULT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
......................

If I say

		super(ds, StoredProcedures.CUST_CONTACT_DETAILS);
		setFunction(true);

		declareParameter(new SqlOutParameter(Constants.Cursor.getValue(), OracleTypes.CURSOR, new ContactDetailsMapper()));
		declareParameter(new SqlParameter("ipYSMCustomerId", Types.BIGINT));
		compile();

then it fails saying,

Cannot access data.CallableStatementCallback; bad SQL grammar [{? = call GET_YCRM_STG_CONTACT_DEFAULT(?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00222: no function with name 'GET_YCRM_STG_CONTACT_DEFAULT' exists in this scope
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

and it should fail as there is no such function. But the number of arguments that the stored procedure expects and the function expects have changed.

Thats why I got the doubt may be stored procedure doesn't return any value.

[Fatih Keles]

Kalyana,

This procedure only takes one parameter and does not have any out parameter, so it does not return anything and parameter count is also wrong.

First correct your pl/sql code, this sample code from oracle-base will be helpful, and this is also.

Regards,

Fatih.

[Kalyana Chakravathy]

Thank you very much, it works now. By the way if I convert it into a function will there be any problem. I read that stored procedure is compiled only once and stored, where as a function is compiled every time. Is that true, if so how much does it affect the performance. I am very new to JDBC, hibernate, Stored Procedures and stuff. All I know is just some basic knowledge of SQL queries. Please correct me if I am wrong.