aspose file tools*
The moose likes JDBC and the fly likes Problem with Pl/Sql function when calling in java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with Pl/Sql function when calling in java" Watch "Problem with Pl/Sql function when calling in java" New topic
Author

Problem with Pl/Sql function when calling in java

Krikor Krumlian
Greenhorn

Joined: Sep 24, 2004
Posts: 9
Hello all ,

I have the following pl/sql function

FUNCTION NEW RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
PARENT_ID NUMBER(38) IN DEFAULT
ITEM_ID NUMBER(38) IN DEFAULT
LOCALE VARCHAR2 IN DEFAULT
CREATION_DATE DATE IN DEFAULT
CREATION_USER NUMBER(38) IN DEFAULT
CONTEXT_ID NUMBER(38) IN DEFAULT
CREATION_IP VARCHAR2 IN DEFAULT
ITEM_SUBTYPE VARCHAR2 IN DEFAULT
CONTENT_TYPE VARCHAR2 IN DEFAULT
TITLE VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
NLS_LANGUAGE VARCHAR2 IN DEFAULT
TEXT VARCHAR2 IN DEFAULT
DATA BLOB IN DEFAULT
RELATION_TAG VARCHAR2 IN DEFAULT
IS_LIVE CHAR IN DEFAULT


Now when i want to trigger this function i use the following code in
sqlplus

SQL> variable answer number;
SQL> execute :answer := content_item.new (name => 'k',title => 'k is here', parent_id => 982, description => 'yahoo', content_type => 'wg_tv_program', is_live => 'Y', creation_user => 44, creation_ip => '222' );

As you can see i don't use all argument names . And this works fine

But to do this through jdbc i have the following code

CallableStatement cs = con.prepareCall("{ ? = call content_item.new(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?) } ");
cs.registerOutParameter(1, java.sql.Types.INTEGER);
cs.setString(2,"k"); //name
cs.setInt(3, 982); //parent_id
cs.setInt(4,0); //item_id ??
cs.setString(5,"mm"); //locale
cs.setDate(6,null); // Creation_date
cs.setInt(7,44); //Creation User
cs.setInt(8,0); // Context_ID
cs.setString(9,"222"); //creation_ip
cs.setString(10,"nn"); // Item_subtype
cs.setString(11,"wg_tv_program"); //content_Type
cs.setString(12,"Krikor"); //title
cs.setString(13,"hehe"); //description
cs.setString(14,"bb"); // mime_Type
cs.setString(15,"oo"); // nls_type
cs.setString(16,"tt"); // text
cs.setString(17,"ff");
cs.setString(18,"dd");
cs.setString(19,"Y"); //IS_LIVE

cs.execute();
logger.debug("WHAT DO WE HAVE HERE" + cs.getInt(1));

So when the following code is called , i get the following

<Couldn't create Program : ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'NEW'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
>

And i cannot figure out what i am doing wrong ?
Also is there a way to use the argument names so i won't have to use all the arguments.? how can i use the default values ? tried to set some of the values to null but that threw an error saying i can't input errors ?

Sorry for the Mass questions
Thank you all
Kash Mhai
Greenhorn

Joined: Apr 01, 2005
Posts: 29
1. Check if the word new is a keyword?
2. Change the name of the function to something else, and try
3. See if you have mentioned the SCHEMA name in the query
SCHEMA.PACKAGE.PROCEDUE(...)

Cheers


-------------<br />There are wheels within wheels...<br />P.G Wodehouse
Krikor Krumlian
Greenhorn

Joined: Sep 24, 2004
Posts: 9
Well i don't thin the name is the problem cause i was able to do the following ,

CallableStatement cs = con.prepareCall("{ call ? := content_item.new(?,?) }");
cs.registerOutParameter(1, java.sql.Types.INTEGER);
cs.setString(2,"kb"); //name
cs.setInt(3, 982); //parent_id
This worked Fine but if i do this

CallableStatement cs = con.prepareCall("{ call ? := content_item.new(?,?,?) }");
cs.registerOutParameter(1, java.sql.Types.INTEGER);
cs.setString(2,"kb"); //name
cs.setInt(3, 982); //parent_id
cs.setNull(4,java.sql.Types.INTEGER); // item_id

Then it complains saying that i cannot input a null , and i don't really want to
input any number myself cause that is being generated by the function i think
cause the return value is exaclty the same .

So my question what should i input into this parameter , where it's declared as an IN
it's autopopulated by the function ? How can i view the code of the funtion which is in a
package by the way .

thank you all again
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Krikor,
Like I said in my reply to this very same question you posted in the OTN forums, you should use "java.sql.Types.NUMERIC", and not "java.sql.Types.INTEGER".

Good Luck,
Avi.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem with Pl/Sql function when calling in java