Call procedure using anonymous block in pl/sql?

4.1k views Asked by At

I am fairly new to PL/SQL but have been reading up on it and have used some templates, including some which I found from here.

What I want to do is to write an anonymous block to call some procedures which wrere written earlier in a sql developer project. I have attempted it but it isn't running properly. It returns an error of "Error starting at line : 2 in command " and then reports a "closed connection."

This is my attempt:

DECLARE
    P_USER_NAME VARCHAR;
    P_DEBUG_FLAG  VARCHAR;
    P_DEBUG_FIELD VARCHAR;
    P_DEBUG_VALUE VARCHAR;

BEGIN

    schema.package.procedure(
        OutParam1, OutParam2, OutParam3, OutParam4);

    dbms_output.put_line('OutParam1: ' || P_USER_NAME);
    dbms_output.put_line('OutParam2: ' || P_DEBUG_FLAG);
    dbms_output.put_line('OutParam3: ' || P_DEBUG_FIELD);
    dbms_output.put_line('OutParam4: ' || P_DEBUG_VALUE);
END;
/

And these are the procedure I want to call:

   PROCEDURE CLEAR_DEBUG (P_USER_NAME IN VARCHAR2);

   PROCEDURE WRITE_DEBUG (P_USER_NAME     IN VARCHAR2,
                          P_DEBUG_FLAG    IN VARCHAR2,
                          P_DEBUG_FIELD   IN VARCHAR2,
                          P_DEBUG_VALUE   IN VARCHAR2);

   PROCEDURE READ_DEBUG (P_USER_NAME    IN     VARCHAR2,
                         P_REF_CURSOR      OUT SYS_REFCURSOR);
END P_DEBUG;
1

There are 1 answers

0
Pavel Gatnar On BEST ANSWER

There are more errors in your code:

  1. declare the variables properly - VARCHAR requires length constraint
  2. OutParam1, OutParam2, OutParam3, OutParam4 are not declared - use the declared variables as arguments instead, beware of the variables passed as arguments must have the same data type
  3. I expect you wanted to call P_DEBUG.WRITE_DEBUG(P_USER_NAME, P_DEBUG_FLAG, P_DEBUG_FIELD, P_DEBUG_VALUE);