Search code examples
oracle-databaseplsqloracle-xe

Procedure call inside a PL/SQL block

I have some SQL code to populate a database, which works just fine:

INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (1, 1, 1);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (2, 1, 1);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (3, 1, 2);

EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);

To enhance maintainability, I'm trying to replace some magic numbers with variables. I've never used variables before but I've read I need to use a PL/SQL block so:

DECLARE
    ELEMENT_TEXT SMALLINT := 1;
    ELEMENT_IMAGE SMALLINT := 2;
BEGIN

INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (1, 1, ELEMENT_TEXT);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (2, 1, ELEMENT_TEXT);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (3, 1, ELEMENT_IMAGE);

EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);

END;
/

This works just fine, except the procedure call:

EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);
           *
ERROR en lÝnea 45:
ORA-06550: lÝnea 45, columna 9:
PLS-00103: Se ha encontrado el sÝmbolo "RESET_SEQUENCE" cuando se esperaba uno
de los siguientes:
:= . ( @ % ; immediate
El sÝmbolo ":=" ha sido sustituido por "RESET_SEQUENCE" para continuar.

The PLS-00103 error translates as:

PLS-00103: found 'string' but expected one of the following: 'string'

If I change the call to:

EXECUTE IMMEDIATE RESET_SEQUENCE('DOCUMENTO_ELEMENTO_ID_SEQ', 4);

... I get this:

PLS-00222: en este ßmbito no existe ninguna funci¾n cuyo nombre sea
'RESET_SEQUENCE'
ORA-06550: lÝnea 45, columna 1:
PL/SQL: Statement ignored

... that translates as:

PLS-00222: no function with name 'string' exists in this scope

My questions are:

  1. Am I using variables correctly or there's a better solution for my initial goal?
  2. What do I need to fix in my procedure call syntax and why?
Solution

    1. IMO, it would be better if you had a procedure with 3 parameters, where you would have 1 insert...and then use that procedure to make the inserts. That way you can add exception handling, and if something happens you would know.

    2. Try deleting the EXECUTE when using the block version of your code. I don't think you need it :)