Results 1 to 9 of 9
Thread: ORA00911: invalid character

092903, 17:39 #1Registered User
 Join Date
 Mar 2002
 Posts
 162
Unanswered: ORA00911: invalid character
I get an error when I try to execute this procedure, which is part of a package :ERROR at line 1:
ORA00911: invalid character
ORA06512: at "SA.GET_PBS", line 41
ORA06512: at line 1
line 41 is denoted with two stars, could anyone please give me a clue as to what the invalid character could be. when I run the dynamic string on its own it seems fine, but its somehow not compiling when i try to run the proc
************************************************** *********
PROCEDURE calculate_inf_rates (p_pbs IN NUMBER, p_ver_id IN NUMBER, p_var_id IN NUMBER,
p_cycle_key IN NUMBER, p_year_limit NUMBER)
AS
v_year NUMBER(4);
v_cy NUMBER(4);
v_current_year NUMBER;
v_inf_rate NUMBER(6,3);
v_py_inf_rate NUMBER;
v_session_id NUMBER(5,0);
v_sql_stmt VARCHAR2(500);
BEGIN
inf_Rates table is a global temporary table created to hold the results of the inflation rate calculation
Determine what year to start calculating from by getting the CY value (FY1) from tx_version
SELECT FY  1
INTO v_cy
FROM tx_version
WHERE ver_id = p_ver_id
AND var_id =p_var_id
AND cycle_key = p_cycle_key;
Start with 1997
v_year := 1997;
BEGIN
 While the year being calculated for is less than the Year_limit execute the following code
WHILE v_year <= p_year_limit
LOOP
BEGIN
 get the inflation rate for the given pbs from opp_escalation
** v_sql_stmt:= 'SELECT NVL(FY'v_year',0) INTO v_inf_rate FROM opp_escalation WHERE pbs_key ='p_pbs' AND ver_id ='p_ver_id' AND var_id ='p_var_id';';
EXECUTE IMMEDIATE v_sql_stmt;
END;
 If the year is less than CY then the inflation rate is 1
IF v_year < v_cy THEN
BEGIN
v_current_year := 1;
END;  if v_year < v_cy
ELSE
BEGIN
/* if the year is greater than CY then calculate the inflation rate by:
getting the inflation rate for the given pbs from opp_escalation (v_inf_rate)
getting the calculated inflation rate from inf_Rates for the previous year (v_py_inf_rate)
multiplying the previous year's inflation rate * (1 + the inflation rate from opp_escalation)*/
SELECT NVL(inf_rate,1)
INTO v_py_inf_rate
FROM inf_rates
WHERE year =(v_year  1);
v_current_year := v_py_inf_rate * (1 + v_inf_rate);
END;
END IF;
 store the year and the results of the calculation in inf_Rates
INSERT INTO inf_Rates (year, inf_rate, year_inf_rate)
VALUES (v_year, Round(v_current_year, 25), v_inf_rate);
 increment the year
v_year := v_year + 1;
END LOOP;
END;  while
END calculate_inf_rates;

092903, 17:54 #2Registered User
 Join Date
 Mar 2002
 Posts
 162
Re: ORA00911: invalid character
the problem is with this string
v_sql_stmt:= 'SELECT NVL(FY'v_year',0) INTO v_inf_rate FROM opp_escalation WHERE pbs_key ='p_pbs' AND ver_id ='p_ver_id' AND var_id ='p_var_id';';
oracle doesnt like the first semicolon ';' character at the end and is reading it as invalid. what can i do about it?

092903, 21:00 #3Registered User
 Join Date
 Aug 2002
 Posts
 3
Re: ORA00911: invalid character
I would recommend to change the construction of the SQL Statement to the following.
v_sql_stmt:= 'SELECT NVL(FY'v_year',0) FROM opp_escalation WHERE pbs_key = :m1 AND ver_id = :m2 AND var_id = :m3 ';
EXECUTE IMMEDIATE v_sql_string
INTO v_inf_rate
USING p_pbs, p_ver_id, p_var_id ;

092903, 21:05 #4Registered User
 Join Date
 Mar 2002
 Posts
 162
Re: ORA00911: invalid character
Originally posted by sreekon
I would recommend to change the construction of the SQL Statement to the following.
v_sql_stmt:= 'SELECT NVL(FY'v_year',0) FROM opp_escalation WHERE pbs_key = :m1 AND ver_id = :m2 AND var_id = :m3 ';
EXECUTE IMMEDIATE v_sql_string
INTO v_inf_rate
USING p_pbs, p_ver_id, p_var_id ;
It worked. I didnt use the bind variable stuff I just did this:
v_sql_stmt:= 'SELECT NVL(FY'v_year',0) FROM opp_escalation WHERE pbs_key ='p_pbs' AND ver_id ='p_ver_id' AND var_id ='p_var_id;
EXECUTE IMMEDIATE v_sql_stmt INTO v_inf_rate;
Thanks again for your reply .

110503, 14:56 #5Registered User
 Join Date
 Nov 2003
 Location
 Minnesota
 Posts
 3
Re: ORA00911: invalid character
Originally posted by Wale
I get an error when I try to execute this procedure, which is part of a package :ERROR at line 1:
ORA00911: invalid character
ORA06512: at "SA.GET_PBS", line 41
ORA06512: at line 1
line 41 is denoted with two stars, could anyone please give me a clue as to what the invalid character could be. when I run the dynamic string on its own it seems fine, but its somehow not compiling when i try to run the proc
************************************************** *********
PROCEDURE calculate_inf_rates (p_pbs IN NUMBER, p_ver_id IN NUMBER, p_var_id IN NUMBER,
p_cycle_key IN NUMBER, p_year_limit NUMBER)
AS
v_year NUMBER(4);
v_cy NUMBER(4);
v_current_year NUMBER;
v_inf_rate NUMBER(6,3);
v_py_inf_rate NUMBER;
v_session_id NUMBER(5,0);
v_sql_stmt VARCHAR2(500);
BEGIN
inf_Rates table is a global temporary table created to hold the results of the inflation rate calculation
Determine what year to start calculating from by getting the CY value (FY1) from tx_version
SELECT FY  1
INTO v_cy
FROM tx_version
WHERE ver_id = p_ver_id
AND var_id =p_var_id
AND cycle_key = p_cycle_key;
Start with 1997
v_year := 1997;
BEGIN
 While the year being calculated for is less than the Year_limit execute the following code
WHILE v_year <= p_year_limit
LOOP
BEGIN
 get the inflation rate for the given pbs from opp_escalation
** v_sql_stmt:= 'SELECT NVL(FY'v_year',0) INTO v_inf_rate FROM opp_escalation WHERE pbs_key ='p_pbs' AND ver_id ='p_ver_id' AND var_id ='p_var_id';';
EXECUTE IMMEDIATE v_sql_stmt;
END;
 If the year is less than CY then the inflation rate is 1
IF v_year < v_cy THEN
BEGIN
v_current_year := 1;
END;  if v_year < v_cy
ELSE
BEGIN
/* if the year is greater than CY then calculate the inflation rate by:
getting the inflation rate for the given pbs from opp_escalation (v_inf_rate)
getting the calculated inflation rate from inf_Rates for the previous year (v_py_inf_rate)
multiplying the previous year's inflation rate * (1 + the inflation rate from opp_escalation)*/
SELECT NVL(inf_rate,1)
INTO v_py_inf_rate
FROM inf_rates
WHERE year =(v_year  1);
v_current_year := v_py_inf_rate * (1 + v_inf_rate);
END;
END IF;
 store the year and the results of the calculation in inf_Rates
INSERT INTO inf_Rates (year, inf_rate, year_inf_rate)
VALUES (v_year, Round(v_current_year, 25), v_inf_rate);
 increment the year
v_year := v_year + 1;
END LOOP;
END;  while
END calculate_inf_rates;
L_stmt := 'alter user “'L_cur_user'" identified by “'I_new_pass'" ';
Can anyone say how to fix it?

110503, 19:11 #6Registered User
 Join Date
 Mar 2002
 Posts
 162
try this:
L_stmt := 'ALTER USER 'L_cur_user' IDENTIFIED BY '''I_new_pass''';';
and then do a execute statement after that

110603, 00:39 #7Registered User
 Join Date
 Oct 2003
 Posts
 5
Re: ORA00911: invalid character
Originally posted by lara
I get the same ORA0091 error in a package I made. I am not sure how to fix it but because of this post I think it is related to this line:
L_stmt := 'alter user “'L_cur_user'" identified by “'I_new_pass'" ';
Can anyone say how to fix it?
try this :
L_stmt := 'alter user 'L_cur_user' identified by 'I_new_pass;
where L_cur_user & I_new_pass should be variable;Last edited by shiv_rasi; 110603 at 05:21.

110603, 04:13 #8Registered User
 Join Date
 Nov 2003
 Location
 Bangalore, INDIA
 Posts
 333
Re: ORA00911: invalid character
Hi,
L_stmt := 'ALTER USER 'L_cur_user' IDENTIFIED BY '''I_new_pass''';'";SATHISH .

111003, 16:41 #9Registered User
 Join Date
 Nov 2003
 Location
 Minnesota
 Posts
 3
Thanks, actually the problem was with something else.