Skip to Main Content
Forums

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cannot perform fetch on a PLSQL statement: next

ram7Oct 18 2013 — edited Oct 15 2014

Storedprocedure:

create or replace PROCEDURE ADDITION

(

  A IN NUMBER 

, B IN NUMBER 

, C OUT NUMBER 

) AS

BEGIN

  C := A+B;

  dbms_output.put_line(c);

END ADDITION;

JDBC program:

stmt = conn.prepareCall("BEGIN addition(?, ?,?); END;");

      stmt.setInt(1, 10);

      stmt.setInt(2, 85);

      stmt.registerOutParameter(3, java.sql.Types.INTEGER);

      ResultSet rs = stmt.executeQuery();

      while(rs.next()){

      int c =rs.getInt("c");

    System.out.println(c);

      }

 

      stmt.close();

      conn.close();

   }

Error:

java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next

  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)

  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)

  at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:192)

  at stored_procedure.main(stored_procedure.java:32)
This post has been answered by unknown-7404 on Oct 18 2013
Jump to Answer

Comments

566902
Daniel
In your package you should put three Variable steps, drag each of the variables on to the package and make it a "Refresh" step. This will force a value into the variable, which should then make it available for substitution later in the package.
C
1 person found this helpful
Cezar Santos
Hi Daniel,

How are you?

You have some problems at you logic, let me try to help...

Problem 1)
Isn't possible to get "POP_NAME" outside from an interface, I mean, as this keyword returns a interface name only is possible to use in a interface.

One way to "put" this information out of an interface is altering the IKM.

Add a step (as first once you want to use this for log) with the following code:

at Target tab:
Technology: JavaScript
<@String vIntName="<=%odiRef.getPOP("POP_NAME")%>";@>

now you can use a Java variable at any point of the code, just use the syntax: <@=vIntName@>

Problem 2)
The "PrevStepLog" API only works well if it is the imediate step after the step that you want to get the "Last Log".
I mean, if you need to get the final status from interface and want to get "OK" or "KO" create the variable as you already did and make the flow "OK" and "KO" to this variable letting it as refresh.



Problem 3)
You told that "create a package", that is OK, but "not persistent" variable exists just in its original session and each scenario call create a new session.
It means that to send those values to a new scenario you need to declare those variables inside the new scenario.


Beside these small issues your process logic are very well.

You don't need to create a variable to get the session at the first package, at any time at the package the Session API is available, use it directly when call the second scenario.


Does it make any sense to you?


Cezar Santos
1 person found this helpful
Daniel Hein
CTs,

Thanks for help me but i already made this and the error persist.

Do i have to link the interface to the variables or just put them into the diagram?

Edited by: user10470648 on Nov 10, 2008 8:30 AM
Daniel Hein
Hi Cezar, i'm fine, thanks, and you?

First of all, thank you again for the fast response.

1) i really wrote my example wrongly. The correct ones referent to the "interface's name" that i'm using is:

select '<%=odiRef.getPrevStepLog("STEP_NAME")%>' from dual

anyway, your hint is now saved and will be used :-)

2) In this case, how can i make the flow "OK" and "KO", after interface, to all variables? because if i just make the flow to one variable, the "previous step" about the others variables won't be the interface.


3) Can you explain these please. i don't know the steps to workaround this.

I already have created the variables at my project. Do i have to create a scenario with these variables and then create other declare variables that receives the values of "my odi variables" at the principal package? If this is the right way, how can a set the declare variables to receive the value of "my odi variables" listed above?


Thanks for your hopefully help.

best regards.

Daniel Hein
Daniel Hein
Good morning Cezar,

Can you help me with the workaround that i wrote in the previous post?

i really apprecciate your help.

Thanks in advance,

Daniel
Daniel Hein
The error "no previous step" was happening because the "transaction isolation" was set to the default option, "no definition", instead of "serializable".


Anyway, thanks for all the help.

Regards,

Daniel Hein
Cezar Santos
Answer
Hi Daniel,

Sorry by my delay to reply you...

About the problems....

1) OK

2) You need to repeat the "status refresh variable" after execute each interface or procedure. It is possible to make paralallel execution but that is another architecture.

3) About the variables, just drag and drop it to the package and let it as declare type. That wll allow you, after create a scenario, use those variable as parmeters.

I will send you some ODI document about how to use it (in replay to the email you sent me)


Does it help you?

Edited by: Cezar Santos on 12/11/2008 11:34
Marked as Answer by Daniel Hein · Sep 27 2020
Daniel Hein
Help a lot Cezar.

I really apprecciate your concern about my issue.

Best regards,

Daniel hein
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 15 2013
Added on Oct 18 2013
#database-connectivity-discussions, #jdbc
2 comments
2,418 views