error when executing a varchar2 in a procedure

Question

I have a varchar2 with an INSERT and I want to execute it in a procedure I try to do it with an execute but this happens:

EXECUTE IMMEDIATE sql_str;

Error:

ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "SYS.INSERT_MOVIMIENTOS", line 47
ORA-06512: at line 1

Varchar2 carries an insert that is this and works if I paste it but when executing it in the procedure something of the procedure fails.

INSERT INTO MOVIMIENTOS (COD_BANCO, COD_SUCUR, NUM_CTA, FECHA_MOV, TIPO_MOV, IMPORTE) VALUES (2000, 2000, 0, '11/11/08', 'I', 500);

My procedure

CREATE OR REPLACE PROCEDURE INSERT_MOVIMIENTOS (
    INSERTMOV_COD_BANCO IN NUMBER,
    INSERTMOV_COD_SUCUR IN NUMBER,
    INSERTMOV_NUM_CTA   IN NUMBER,
    INSERTMOV_FECHA_MOV IN DATE,
    INSERTMOV_TIPO_MOV  IN CHAR,
    INSERTMOV_IMPORTE   IN NUMBER
)
IS
    sql_str VARCHAR2(500) := 'INSERT INTO MOVIMIENTOS (';
    movimiento movimientos_typ;
BEGIN
    movimiento := movimientos_typ(
        INSERTMOV_COD_BANCO,
        INSERTMOV_COD_SUCUR,
        INSERTMOV_NUM_CTA,
        INSERTMOV_FECHA_MOV,
        INSERTMOV_TIPO_MOV,
        INSERTMOV_IMPORTE
    );

IF movimiento.getCOD_BANCO() != 0 THEN
    sql_str := sql_str || 'COD_BANCO, COD_SUCUR, NUM_CTA, FECHA_MOV, TIPO_MOV, IMPORTE) VALUES (' ||
    movimiento.getCOD_BANCO() || ', ' ||
    movimiento.getCOD_SUCUR() || ', ' ||
    movimiento.getNUM_CTA() || ', ''' ||
    movimiento.getFECHA_MOV() || ''', ''' ||
    movimiento.getTIPO_MOV() || ''', ' ||
    movimiento.getIMPORTE() || ');';
ELSE
    sql_str := sql_str || 'COD_SUCUR, NUM_CTA, FECHA_MOV, TIPO_MOV, IMPORTE) VALUES (' ||
    movimiento.getCOD_SUCUR() || ', ' ||
    movimiento.getNUM_CTA() || ', ''' ||
    movimiento.getFECHA_MOV() || ''', ''' ||
    movimiento.getTIPO_MOV() || ''', ' ||
    movimiento.getIMPORTE() || ');';
END IF;

    DBMS_OUTPUT.PUT_LINE('////////////////////////////////////////');
    DBMS_OUTPUT.PUT_LINE('CONSULTA: ' || sql_str);

    DBMS_OUTPUT.PUT_LINE('////////////////////////////////////////');
     DBMS_OUTPUT.PUT_LINE('DATOS INTRODUCIDOS: ');
    movimiento.display;

    DBMS_OUTPUT.PUT_LINE('////////////////////////////////////////');
    EXECUTE IMMEDIATE sql_str; 
    DBMS_OUTPUT.PUT_LINE('FUNCION REALIZADA CON EXITO');
END;
/

What do you mean by "the varchar2 is fine"? Are you able to execute the INSERT by executing EXACTLY that command, as you have in your last code listing? Copy and paste, to make sure? — , Mar 09 '19 at 16:57
Please edit the question and show the table definition and a [mcve] — OldProgrammer, Mar 09 '19 at 16:57
Don't create objects in the SYS schema. Instead, create a user, assign the proper privileges to it, and use that user/schema when creating your tables, etc. Best of luck. — Bob Jarvis - Слава Україні, Mar 09 '19 at 18:17
It would be helpful to see the generated INSERT string, as we can't reproduce it without your object type definition. By the way it might make more sense to add this logic to the object itself. Also I will repeat my advice to avoid `char` and think carefully about why you are coding in uppercase. — William Robertson, Mar 10 '19 at 11:47

score 3 · Accepted Answer · answered Mar 09 '19 at 16:59

3

Donot end with the semicolon ; in your query string.

   movimiento.getIMPORTE() || ')';

The error is just because of it.

by the way, you should be using bind variables. Dynamically constructing the values this way is vulnerable to SQL Injection.

answered Mar 09 '19 at 16:59

Maheswaran Ravisankar

17,652
6
47
69

thanks I had a very long time trying to solve that nonsense – Victor Mar 09 '19 at 17:03
Alas, that (obvious) mistake is not the cause of ORA-00911, the error reported by the OP. The errant semicolon causes error ORA-00933, "SQL command not properly ended". The OP will eventually run into this error too, after he fixes the original error. (Or he won't, if he fixes it now, based on your answer; but that should not fix his original error.) – Mar 09 '19 at 17:07
@mathguy, errant semicolon cause 911 as well – Maheswaran Ravisankar Mar 09 '19 at 17:14

error when executing a varchar2 in a procedure

1 Answers1