Solved

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

Posted on 2004-10-15
5
3,010 Views
Last Modified: 2011-09-20
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
Comment
Question by:wkhan80
  • 3
5 Comments
 
LVL 7

Expert Comment

by:jacobhoover
ID: 12323366
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
 
LVL 7

Accepted Solution

by:
jacobhoover earned 250 total points
ID: 12323597
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
 
LVL 7

Expert Comment

by:jacobhoover
ID: 12550176
@wkhan80,
  any feedback?  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Advice in Xamarin 21 97
Math Equation 23 105
Why is initMap returning "not a function" error. 3 30
troubleshoot a python script 8 24
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question