Solved

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

Posted on 2004-10-15
5
3,000 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now