• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Problem with Pl/Sql function when calling in java

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic