Error while calling java stored procedure in oracle database

Question

I have created a RunnableJar (a jar containing other third party jars like iText.jar, java classes, etc...) and I have upload that RunnableJar.jar in Oracle database using loadjava. In the jar i have main class GetOffer in that i have one public String getOffer(String param1) { } method and the use of this method is to create multiple pdf according to user selection and concat them.

Now I have created Stored procedure:

create or replace function getOffer(param1 in varchar2) return varchar2
    is language java name 'GetOffer.getOffer(java.lang.String) return java.lang.String';

And I have called this stored procedure using following call statement:

SQL> VARIABLE  myString  VARCHAR2(20);
SQL> CALL getOffer("XYZ") INTO :myString

When calling the procedure I am getting the below error:

SQL> VARIABLE  myString  VARCHAR2(20);
SQL> CALL getOffer("XYZ") INTO :myString
 2  ;
CALL getOffer("Any String") INTO :myString
 *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

How can I solve this problem?

Justin Cave · Accepted Answer · 2013-06-14 15:41:16Z

1

The code you posted is creating a stored function, not a stored procedure. If you want to call a function

SELECT getOffer('xyz')
  INTO :myString
  FROM dual;

Remember as well that strings in SQL and PL/SQL are delimited by single quotes not double quotes.

answered Jun 14, 2013 at 15:41

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

SURJA Over a year ago

Thanks Justin cave, i have one more question, in the java code i have use PdfWriter.getInstance(document, new FileOutputStream("/Standard.pdf")); when i use this jar in my local machine that time that pdf stored on local e: drive bcz my jar is there but if i put this RunnableJar.jar in oracle database on server so which location that pdf will stored ?

Justin Cave Over a year ago

@SURJA - Assuming it works, it would appear to want to write to the root directory on the database server. That's probably not what you want and unlikely to be something the DBA would allow.

SURJA Over a year ago

Thanks Justin , i call the function using this but now he is giving following error SELECT getOffer('xyz') INTO :myString FROM dual * ERROR at line 1: ORA-29540: class GetOffer does not exist i uploaded jar using loadjava-force -thin -user username/password@Servername:PORT:SID -resolve RunnableFirstTest.jar is there any thing that i need to change .

Justin Cave Over a year ago

@SURJA - That sounds like a separate question. We'd need to know how you built your JAR file and how you know that there is a GetOffer class in that JAR file.

SURJA Over a year ago

I m using eclipse. please see the structure mail-attachment.googleusercontent.com/attachment/…

|

Collectives™ on Stack Overflow

Error while calling java stored procedure in oracle database

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related