HI Friends,
I have written a proc for the below process.
SP_Control (table)
sno campgn_id campgn_typ campgn_no current_wave
1 ET07001 ONB ONB01 1
2 ET07001 ONB CNB01 1
3 ET03053 IAL IAL1A A
4 ET03053 IAL IAL2A A
5 ET03053 IAL IAL3A A
6 ET03053 IAL IAL4A A
After calling the procedures with bellow parameters
Get_next_campgn(‘ONB01’,’ONB’);
Get_next_campgn(‘CNB01’,’ONB’);
Get_next_campgn(‘IAL1A’,’IAL’);
Get_next_campgn(‘IAL2A’,’IAL’);
Get_next_campgn(‘IAL3A’,’IAL’);
Get_next_campgn(‘IAL4A’,’IAL’);
…………… it should update the table with below data.
sno campgn_id campgn_typ campgn_no current_wave
1 ET07001 ONB ONB02 2
2 ET07001 ONB CNB02 2
3 ET03053 IAL IAL1B B
4 ET03053 IAL IAL2B B
5 ET03053 IAL IAL3B B
6 ET03053 IAL IAL4B B
I have written a procedure like this and its compliled successfully.
But throws error while executing like
execute Get_next_campgn(‘ONB01’,’ONB’);
create or replace procedure Get_next_campgn(p_campgn varchar2,p_type varchar2)
as
begin
update SP_Control set campgn_no = substr(p_campgn,1,length(p_campgn)-1)||to_char(ascii(substr(p_campgn,-1,1))+1) ,
curr_wave = to_char(ascii(curr_wave)+1)
where campgn_type = p_type
and campgn_no = p_campgn ;
exception
when others then
dbms_output.put_line(sqlerrm);
end Get_next_campgn;
/
Error::::
Error starting at line 15 in command:
execute Get_next_campgn(‘ONB01’,’ONB’)
Error report:
ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "" when expecting one of the following:
( ) - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table continue avg count current exists max min prior sql
stddev sum variance execute multiset the both leading
trailing forall merge year month day hour minute second
timezone_hour timezone_minute timezone_region timezone_abbr
time timestamp interval date
<a string literal with character set specification>
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Please suggest....