[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Executing an oracle stored procedure

Posted on 2001-07-09
7
Medium Priority
?
722 Views
Last Modified: 2007-12-19
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();
..................              
         
0
Comment
Question by:neimeier
7 Comments
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 6267783

0
 

Expert Comment

by:bvjagan
ID: 6268193
Hi,

  Can u send the stored procedure..

Rgds,
JAgan
0
 
LVL 3

Expert Comment

by:graf27
ID: 6268576

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:neimeier
ID: 6269237
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
 
LVL 3

Accepted Solution

by:
graf27 earned 150 total points
ID: 6272020
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
 

Author Comment

by:neimeier
ID: 6273028
graf27 ,

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

Thanks for all your help
0
 

Author Comment

by:neimeier
ID: 6275604
I switched drivers and now am using a thin native driver...thanks for your help
0

Featured Post

Independent Software Vendors: 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

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This video teaches viewers about errors in exception handling.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month18 days, 6 hours left to enroll

830 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