Solved

Executing an oracle stored procedure

Posted on 2001-07-09
7
693 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 50 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now