Solved

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

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
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 …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

785 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