Solved

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

Posted on 2004-10-15
5
2,995 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
Comment Utility
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
Comment Utility
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
Comment Utility
@wkhan80,
  any feedback?  
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
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 …
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…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

762 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

11 Experts available now in Live!

Get 1:1 Help Now