Bind variables

How do i change the following to use bind variables? I need sql to change the following to use bind variables.

Here are the two most prominent statements showing in Shared Pool with No Bind Variables:  

--Stmt                                                           COUNT(*)        Mem       Open       EXEC
-------------------------------------------------------------- ---------- ---------- ---------- ----------
--insert into ABC_data ( price, message,        6504   66900419          0       6504
--insert into ABC_iypattern ( type, message, sequ       4911   51056835          0       4911

many thanks.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How are you executing the SQL on the database?

if PL/SQL:

INSERT INTO abc_data( price, message ) VALUES( :price, :message )
USING my_price, my_message;

If Java:

PreparedStatement p = connection.prepareStatement( "INSERT INTO abc_data( price, message ) VALUES( ?, ? )" );
p.setLong( 1, myPrice );
p.setLong( 2, myMessage );

There is a feature of Oracle 9i+ that allows Oracle to automatically create compiled SQL with system generated bind variables (I believe its called "CURSOR SHARING"... This is definitely worth a look...)
crishna1Author Commented:

Dumb itthe same for Java J2SE application?
yup, my Java code above is not J2EE specific, standard J2SE java.sql package usage.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

have a look at the JavaDoc for java.sql.Connection.prepareStatement and java.sql.PreparedStatement - to execute the statement after the "set" methods use preparedStatement.executeUpdate() since this is updating the database (if you were using a SELECT with bind variables you'd use executeQuery().)

crishna1Author Commented:
Hi thank you very much for your immediate responce.

Here is the deal , we are using the following in the code and the values for the insert statement are taken from XML.

The request is to change the following to use bind variables as it is taking too much shared pool right now.

many thanks.


            insertstr =
                "insert into aci_importenergypattern ( daytype, message, sequence, ";
            insertstr = insertstr +
                "tarifftype, startdate, enddate, batch ) values ( '";
            insertstr = insertstr + daytype + "', " + message + ", " + sequence +
                ", '" + tarifftype + "', ";
            insertstr = insertstr + "to_date('" + df.format(tempstartdate) +
                "', 'MM/dd/yyyy HH24:MI:SS'), ";
            insertstr = insertstr + "to_date('" + df.format(tempenddate) +
                "', 'MM/dd/yyyy HH24:MI:SS'), " + batch + ")";
            cat.debug("(Job-" + jobId + ") " + insertstr);
            try {
            catch (Exception e) {
                cat.error("(Job-" + jobId + ") " + e.toString());
                throw e;
crishna1Author Commented:
what do i need to do to replace the above to use bind variables?
change your insertstr to:

insertstr = "insert into aci_importenergypattern( daytype, message, sequence, tarifftype, startdate, enddate, batch ) " +
                "values( ?, ?, ?, ?, ?, ?, ? )";
PreparedStatement pstmt = connection.prepareStatement( insertstr );
// I've made some assumptions on the datatypes of the various values...
pstmt.setLong( 1, daytype );
pstmt.setString( 2, message );
pstmt.setLong( 3, seqence );
pstmt.setLong( 4, tarifftype );
pstmt.setTimestamp( 5, new java.sql.Timestamp( startdate.getTime() ) );
pstmt.setTimestamp( 6, new java.sql.Timestamp( enddate.getTime() ) );
pstmt.setString( 7, batch );

pstmt.executeUpdate( insertstr );

Note, I've removed error checking, etc...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
again, it might be worth your time having a look at SHARED_CURSOR feature (assuming Oracle 9i+), but since you're in a position to change the application, change the application....
crishna1Author Commented:
looks like in the above insert statement i will have to supply the values, if you look at the code supplied there are no values?

sorry i am not very familiar how this stuff works.

crishna1Author Commented:
also i am looking at the following example, doest it make any sense ty you,
" if you look at the code supplied there are no values" - yes there are values, the insertstr is build from the values in question

e.g. the following line:

 insertstr = insertstr + daytype + "', " + message + ", " + sequence +

has variables "daytype", "message" and "sequence", these are what are used in the set..() methods of the Prepared Statement (see my code above).

The code you posted has the following variables (or at least it is using the following variables to build the string, so if the code compiles, these variables exist!)


the url you posted is an example of what would happen if the set() methods were not called...

the only thing I couldn't figure out from your code snippet was the types of these variables (some are obviously dates, some are obviously strings (actually, it looks like tarifftype is a String), others are probably numeric...

earth man2Commented:
Yes it makes sense to me.
crishna1Author Commented:
Thank you very much for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.