[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Executing an oracle stored procedure

Posted on 2001-07-09
7
Medium Priority
?
717 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
[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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

650 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