[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

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();
..................              
         
0
neimeier
Asked:
neimeier
1 Solution
 
sudhakar_koundinyaCommented:

0
 
bvjaganCommented:
Hi,

  Can u send the stored procedure..

Rgds,
JAgan
0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
graf27Commented:
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now