• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3110
  • Last Modified:

ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ";"

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
0
wkhan80
Asked:
wkhan80
  • 3
1 Solution
 
jacobhooverCommented:
There is something wrong with:

   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;

On 9i it won't cleanly compile.. I belive it's in the case statment... Doing more checking.
0
 
jacobhooverCommented:
This will compile...:

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;
colprops sys.all_tab_columns%rowtype;
begin

if p_fk_col_list is null then
   select *
   into colprops
   from sys.all_tab_columns atc
   where table_name = p_pk_table and column_name = p_pk_col_list;
   
   v_datatype := colprops.data_type;
   if colprops.data_type like '%CHAR%' then
   v_datatype := colprops.data_type || '(' || colprops.data_length || ')';
   end if;
   if colprops.data_type = 'NUMBER' then
     select colprops.data_type ||decode(colprops.data_precision, null, '', '(' ||
     colprops.data_precision || decode(colprops.data_scale, null, '', ',' ||
     colprops.data_scale) || ')') INTO v_datatype from dual;
   end if;
         
   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;
0
 
jacobhooverCommented:
@wkhan80,
  any feedback?  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now