wood burning stoves 2.0*
The moose likes JDBC and the fly likes Problem in executing Oracle Function from JSP Page Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem in executing Oracle Function from JSP Page" Watch "Problem in executing Oracle Function from JSP Page" New topic
Author

Problem in executing Oracle Function from JSP Page

Imran Mirza
Greenhorn

Joined: Oct 15, 2004
Posts: 8
Following is the code of oracle function:-

create or replace function add_constraint(p_pk_table varchar2, p_pk_col_list varchar2, p_fk_table varchar2, p_fk_col_list varchar2 := null)
return varchar2
is

v_add_statement varchar2(200) := null;
v_statement varchar2(4000) := null;
v_fk_col_list varchar2(1000) := null;
v_datatype varchar2(100) := null;


begin
if p_fk_col_list is null then
select case when atc.data_type like '%CHAR%' then atc.data_type || '(' || atc.data_length || ')'
when atc.data_type = 'NUMBER' then atc.data_type || decode(atc.data_precision, null, '', '(' || atc.data_precision || decode(atc.data_scale, null, '', ',' || atc.data_scale) || ')')
else atc.data_type end
into v_datatype
from all_tab_cols atc
where table_name = p_pk_table and column_name = p_pk_col_list;

v_add_statement := 'alter table ' || p_fk_table || chr(10) ||
'add ' || p_pk_col_list || ' ' || v_datatype || ' null';
execute immediate(v_add_statement);

v_fk_col_list := p_pk_col_list;

else
v_fk_col_list := p_fk_col_list;

end if;

v_statement := 'alter Table ' || p_fk_table || chr(10) ||
'add constraint udfk_' || substr(p_fk_table, 1, 12) || '_' || substr(p_pk_table, 1, 12) || ' foreign key (' || v_fk_col_list || ') references ' || p_pk_table || '(' || p_pk_col_list || ')' ;

execute immediate (v_statement);

return 'Success';

exception
when others then
return sqlerrm;

end;
////////////////////////////// END IF //////////////////////////////////////


Following is the code i am using to call above function

try{
cs = conn.prepareCall("{? = call add_constraint(?,?,?}");
cs.registerOutParameter(1,Types.VARCHAR);
cs.setString(2,"sampling."+table1);
cs.setString(3,Parent_Table_PK);
cs.setString(4,table2);
//cs.setString(5,null);
cs.execute();
} catch(Exception e) {System.out.println (e.getMessage());}



But when i am going to execute the function, It is giving me following error:-

ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod not range rem => .. <> or != or ~= >= <= <> and or like between || indicator The symbol ")" was substituted for ";" to continue.



Please help me out. I have already wasted alot of time. Thanks in advance.

Regards,
Imran Mirza
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Imran Mirza:

cs = conn.prepareCall("{? = call add_constraint(?,?,?}");




try to check quoted line one closing bracket is missing.

cs = conn.prepareCall("{? = call add_constraint(?,?,?)}");
[ October 20, 2004: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Imran Mirza
Greenhorn

Joined: Oct 15, 2004
Posts: 8
Thank you so much Shailesh Chandra.
Best Regards,
Imran mirza
mirza_imran@hotmail.com
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem in executing Oracle Function from JSP Page
 
Similar Threads
Help with line feed line break characters
ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ";"
Oracle mail
Oracle Stored Procedure Problem
Problem with Pl/Sql function when calling in java