Solved

Oracle procedu calling in java

Posted on 2009-04-13
3
1,465 Views
Last Modified: 2013-12-18
Hi Experts,
i'm calling store procedure in java i'm executing fine.

But when i chenge the value in store procedure my java will giving exception.

i'm changing value in store procedure like "open pCur for select * from xxx@cc3 where tstamp=pEndDate"
this code executing fine but when i change above code like "vSql long;begin
vSql:='select * from xxx@cc3 where tstamp=pEndDate';
then "open pCur for vSql" i just change.
then java will giving error

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01003: no statement parsed

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:209)
      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1159)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3284)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3328)
      at com.att.network.test.Dbtest.main(Dbtest.java:75)

please give me some suggestion.
thanks.

my store procedure
 
create or replace package body testPKG is
 
 
  procedure testtt (
 
  pRepKeys	IN		VARCHAR2,
	pMetKeys	IN		VARCHAR2,
	pPerType	IN		VARCHAR2,
	pEntity		IN		VARCHAR2,
	pBegDate	IN OUT	DATE,
	pEndDate	IN OUT	DATE,
	pGrpBy		IN		VARCHAR2 default null,
	pAggOrd		IN		VARCHAR2 DEFAULT 'T',
	pMonSummary IN		CHAR DEFAULT 'Y',
	pSkipDays	IN		VARCHAR2 DEFAULT NULL,
	pWeekBeg	IN		CHAR DEFAULT 'SUN',
	PSpecificHour	IN	CHAR DEFAULT 'AH',
	pUser_key	IN		VARCHAR2,
	pOSlogin	IN		VARCHAR2,
	pCur		OUT		cv_Type
  )
  is
 
dd  NUMBER;
vSql long;begin
 vSql:='select * from mnn.tf_sectorrtb_summary@cnnw3 where tstamp=pEndDate';
	open pCur for vSql;
 
 
 
  EXCEPTION
    WHEN OTHERS THEN
        ---  tt:= SUBSTR(SQLERRM,1,150);
       dbms_output.put_line('var_err_msg ' || SUBSTR(SQLERRM,1,150));
       --tt:=sysdate;
end testtt;
 
end testPKG;
 
 
my java code:
 
import oracle.jdbc.driver.OracleTypes;
 
import java.sql.*;
import java.text.SimpleDateFormat;
 
 
public class Dbtest {
	
 
	public static Date stringToDate(String strDT){
		java.sql.Date sqltDate= null; 
		String jdbcDate =null;
		
 
		try{
		
			SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
			java.util.Date dd = sdf.parse(strDT);
			jdbcDate = sdf.format(dd);
			sqltDate = new java.sql.Date(dd.getTime());
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println("Exception--->"+e.toString());
		}
		  return sqltDate ;
	}
 
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		String s1="12058,12059";
		String s2="16085,16070,16075";
		String s3="DAY";
		String s4="MARKET";
		String s5="17-MAR-2009";
		String s6="17-MAR-2009";
		String s7="N";
		String s8="T";
		String s9="N";
		String s10="";
		String s11="SUN";
		String s12="AH";
		int i=1100;
		String s14="CINGULARUS\\lf1484";
		Connection db_connection=null;
	    
	    
		try{		
			
			Class.forName("oracle.jdbc.OracleDriver");
			String url = "jdbc:oracle:thin:@10.180.190.63:1500:xxx";
			db_connection =DriverManager.getConnection (url, "yyy", "xxx");
		CallableStatement pstmt = db_connection.prepareCall("begin testPKG.testtt(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);end;");
		pstmt.setString(1,"12058");
		pstmt.setString(2,"16085");
		pstmt.setString(3,"DAY");
		pstmt.setString(4,"MARKET");
		pstmt.registerOutParameter(5, java.sql.Types.DATE);
		pstmt.setDate(5,stringToDate(s5));
		pstmt.registerOutParameter(6, java.sql.Types.DATE);
		pstmt.setDate(6,stringToDate(s6));
		pstmt.setString(7,"");
		pstmt.setString(8,"T");
		pstmt.setString(9,"N");
		pstmt.setString(10,"N");
		pstmt.setString(11,"SUN");
		pstmt.setString(12,"AH");
		pstmt.setString(13,"1100");
		pstmt.setString(14,"AUTOLOGIN");
		pstmt.registerOutParameter(15, OracleTypes.CURSOR);
		pstmt.executeQuery();
		ResultSet rs= (ResultSet)pstmt.getObject(15);
		while (rs.next()) {
			System.out.println("object value--->"+rs.getString(1));
			
		}
		ResultSetMetaData rm=rs.getMetaData();
		int columnCount = rm.getColumnCount();
		System.out.println("Number of Column:"+columnCount);
		for(int m=1;m<columnCount;m++){
			System.out.println("Column Name--->"+rm.getColumnName(m));
		}
	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				db_connection.close();
 
				}catch(Exception e){
			e.printStackTrace();
		}
		}
 
	}
 
}

Open in new window

0
Comment
Question by:krish12345
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 24132176
Your problem is that you put the variable name inside the string.  PL/SQL doesn't know what it is.

Change these 2 lines:

 vSql:='select * from mnn.tf_sectorrtb_summary@cnnw3 where tstamp=pEndDate';
        open pCur for vSql;


To:

 vSql:='select * from mnn.tf_sectorrtb_summary@cnnw3 where tstamp= :x';
        open pCur for vSql using pEndDate;
0
 

Author Closing Comment

by:krish12345
ID: 31569588
Thanks very much.
0
 

Author Comment

by:krish12345
ID: 24132435
Hi Expert,
sorry i change above fuction like this

vSql := FetchNWDataString(pRepKeys,pMetKeys,pPerType,pEntity,pBegDate,pEndDate,pGrpBy,pAggOrd,pMonSummary,pSkipDays,pWeekBeg,PSpecificHour);

could you tell me how I can call.
i'm getting same error.

please help me.
thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
glassfish admin console 1 53
import as existing maven project 3 78
Moving Oracle Database to other server 2 47
Oracle programming for starter 14 76
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question