Executing an oracle stored procedure

I am connecting to the Oracle DB fine.  Once I encounter the "execute" line of the following code I get a "Not all variables are bound" exception.  Al data types are char's of different lengths.

.................
CallableStatement cstmt = con.prepareCall("{call ehcivr.hrp_matchempid(?, ?, ?, ?, ?, ?, ?)} ");
               
cstmt.setString(1, "1114567890");
cstmt.setString(2, "456789");
cstmt.registerOutParameter(3, Types.CHAR);
cstmt.registerOutParameter(4, Types.CHAR);
cstmt.registerOutParameter(5, Types.CHAR);
cstmt.registerOutParameter(6, Types.CHAR);
cstmt.registerOutParameter(7, Types.CHAR);
     
cstmt.execute();
..................              
         
neimeierAsked:
Who is Participating?
 
graf27Connect With a Mentor Commented:
Ok, sorry: you must not.
I read the "Oracle 8i JDBC Developers Guide and Reference Release 3 (8.1.7) from July 2000,
Part No:A 83724-01" again.

In the sample is used a "function" instead a "procedure". In my education papers, its described different from this guide.

So, if a registry is missing, the error message is:

java.sql.SQLException: Missing IN or OUT paramters at index: <:x>

I can't get your error message "Not all variables are bound", so it seems we are working with different JDBC-drivers.

You can try out first to registry all OUT parameters and than setup the IN parameters.
Can you compile and run the code from previous comment :


                        CallableStatement cstmt = conn.prepareCall("{ call dbms_job.submit(?,?) }");

                        cstmt.registerOutParameter(1,Types.INTEGER);   // register JOB
                        // cstmt.registerOutParameter(2,Types.CHAR);         //  Ok, don't need, but works also
                        cstmt.setString(2,"DBMS_OUTPUT.NEWLINE;");              //   setup WHAT with real job
                        cstmt.execute();
                     
                       System.out.println("Job started: " + cstmt.getInt(1));  // get JOB

you can verify the job (if you are very fast or send more jobs, otherwise the job is done):

"select job, next_date, what from user_jobs"

This is my reference test when I use a new JDBC-driver or an new DB-instance.
0
 
sudhakar_koundinyaCommented:

0
 
bvjaganCommented:
Hi,

  Can u send the stored procedure..

Rgds,
JAgan
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
graf27Commented:

Try out the standard package:

PROCEDURE SUBMIT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          OUT
 WHAT                         VARCHAR2                        IN
 NEXT_DATE             DATE                                   IN     DEFAULT
 INTERVAL                 VARCHAR2                        IN     DEFAULT
 NO_PARSE               BOOLEAN                          IN     DEFAULT
 INSTANCE                 BINARY_INTEGER          IN     DEFAULT
 FORCE                       BOOLEAN                         IN     DEFAULT

Java-Code:
     CallableStatement cstmt = conn.prepareCall("{ call dbms_job.submit(?,?) }");

     cstmt.registerOutParameter(1,Types.INTEGER);   // register JOB
     cstmt.registerOutParameter(2,Types.CHAR);         //  register WHAT
     cstmt.setString(2,"MYJOBS.ANYTHING;");              //   setup WHAT
     cstmt.execute();

   
    System.out.println("Job started: " + cstmt.getInt(1));  // get JOB


So it seems, you haven't registered your OUT parameters right:
I don't know your package so I try:


                    cstmt.registerOutParameter(1,Types.INTEGER);    // you must! register
                    cstmt.registerOutParameter(1,Types.INTEGER);   //  you must! register
                    cstmt.registerOutParameter(3, Types.CHAR);
                    cstmt.registerOutParameter(4, Types.CHAR);
                    cstmt.registerOutParameter(5, Types.CHAR);
                    cstmt.registerOutParameter(6, Types.CHAR);
                    cstmt.registerOutParameter(7, Types.CHAR);

                    cstmt.setInt(1,1114567890);  // it is an int
                    cstmt.setInt(2,456789);

                   cstmt.execute();


0
 
neimeierAuthor Commented:
graf27,

Do I have to register a parameter that is only of type "IN".
Do I really need to cstmt.registerOutParameter("WHAT"), when it's an "IN" parameter?
0
 
neimeierAuthor Commented:
graf27 ,

I am currently using an ODBC bridge driver.  Which driver are you using?  Thin?

Thanks for all your help
0
 
neimeierAuthor Commented:
I switched drivers and now am using a thin native driver...thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.