Error creating procedure: declare and end error

0
votes

I'm starting with the procedures and I'm lost with these problems.

I do not understand why I get an error when I add the procedure because I think it's ok and neither does it cause an error in the end.

I attach the code.

Errors:

LINE/COL ERROR

10/1 PLS-00103: Encountered the symbol "DECLARE"
16/4 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Code:

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
);

DECLARE
    sql_str VARCHAR2(500):='';

BEGIN
    sql_str:=sql_str||'INSERT INTO MOVIMIENTOS (';
    dbms_output.put_line(sql_str);
END;    
/
2
oraclestored-proceduresplsql
problem with ; before DECLARE. Check techonthenet.com/oracle/procedures.php – Jahirul Islam Bhuiyan
The ; is only one problem here. The other one is that DECLARE starts an anonymous block. An anonymous block cannot appear within the declaration section of another block. – Jeffrey Kemp

2 Answers

1
votes

Sample syntax:

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 varchar2
    , insertmov_importe   in number )
is
    sql_str varchar2(500) := 'INSERT INTO MOVIMIENTOS (';
begin
    dbms_output.put_line(sql_str);
end;
/

The trailing slash is for the client application so may not be required depending on what tool you are using.

I changed the datatype for insertmov_tipo_mov. char adds blank spaces which nobody needs, and is provided mainly for ANSI compatibility. It's best to use the standard type.

Even better, use table name.columnname%type to anchor it to the type of the corresponding table column.

A lot of examples online are in uppercase, for no clear reason. You don't as a rule write computer code in uppercase.

0
votes

Remove extra ;, or write IS

Syntax of create procedure

CREATE OR REPLACE PROCEDURE INSERT_MOVIMIENTOS (
    ...
)
IS
    sql_str VARCHAR2(500):='';
    ...
;