Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: ORA-00911: invalid character

    I get an error when I try to execute this procedure, which is part of a package :ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "SA.GET_PBS", line 41
    ORA-06512: 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 (FY-1) 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;

  2. #2
    Join Date
    Mar 2002
    Posts
    162

    Re: ORA-00911: 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?

  3. #3
    Join Date
    Aug 2002
    Posts
    3

    Re: ORA-00911: 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 ;

  4. #4
    Join Date
    Mar 2002
    Posts
    162

    Re: ORA-00911: 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 ;
    Thanks I actually did that already. I forgot to post it.
    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 .

  5. #5
    Join Date
    Nov 2003
    Location
    Minnesota
    Posts
    3

    Re: ORA-00911: 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:
    ORA-00911: invalid character
    ORA-06512: at "SA.GET_PBS", line 41
    ORA-06512: 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 (FY-1) 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;
    I get the same ORA-0091 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?

  6. #6
    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

  7. #7
    Join Date
    Oct 2003
    Posts
    5

    Re: ORA-00911: invalid character

    Originally posted by lara
    I get the same ORA-0091 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?
    Hi,
    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; 11-06-03 at 06:21.

  8. #8
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: ORA-00911: invalid character

    Hi,

    L_stmt := 'ALTER USER '||L_cur_user||' IDENTIFIED BY '''||I_new_pass||''';'";
    SATHISH .

  9. #9
    Join Date
    Nov 2003
    Location
    Minnesota
    Posts
    3
    Thanks, actually the problem was with something else.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •