Problem with Pl/Sql function when calling in java

[Krikor Krumlian]

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]

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

[Krikor Krumlian]

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]

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.