Getting error on executing a procedure having dyinamic sql

Question

This the procedure I have written in Toad (Oracle 19c):

CREATE OR REPLACE PROCEDURE ITMS.SP_ADD_FOREIGN_KEY_CONSTRAINT_AUTO (
    P_TABLE_NAME    VARCHAR2,
    P_COLUMN_NAME   VARCHAR2)
IS
BEGIN
    EXECUTE IMMEDIATE   'ALTER TABLE '
                     || P_TABLE_NAME
                     || ' ADD CONSTRAINT '
                     || P_TABLE_NAME
                     || '_'
                     || SUBSTR(P_COLUMN_NAME,1, INSTR(P_COLUMN_NAME,'_ID')-1)
                     || '_FK FOREIGN KEY ('
                     || P_COLUMN_NAME
                     || ') REFERENCES '
                     || SUBSTR(P_COLUMN_NAME,1, INSTR(P_COLUMN_NAME,'_ID')-1)
                     || '(ID)';
END;

I have created the above procedure which is executing fine in toad for Oracle with query:

EXEC SP_ADD_FOREIGN_KEY_CONSTRAINT_AUTO('DTMS_BA_VOLUME_DISCOUNT_DETAILS','REF_BUSINESS_ASSOCIATE_ID')

where I have written all query in single line.

but if I execute like this :

EXEC SP_ADD_FOREIGN_KEY_CONSTRAINT_AUTO('DTMS_BA_VOLUME_DISCOUNT_DETAILS',
   'REF_BUSINESS_ASSOCIATE_ID') `  

Where I have written some part of query in next line. I am getting error -

ORA-06550: line 1, column 76:  
PLS-00103: Encountered the symbol ";" when expecting one of the following:  

Can you please tell why I am getting this?

Answer 1

This is not an SQL or Oracle issue or anything to do with the procedure or dynamic SQL, it is an issue that the client application is behaving exactly as documented when it sends a command to the database but it does not meet your expectation of how it should behave.

From the SQL*Plus documentation on the EXECUTE command:

Syntax

EXEC[UTE] statement

where statement represents a PL/SQL statement.

Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure. For more information on PL/SQL, see your Oracle Database PL/SQL Language Reference.

Usage

If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen).

The length of the command and the PL/SQL statement cannot exceed the length defined by SET LINESIZE.

As the documentation implies; the EXECUTE command expects your statement to fit onto a single line so when it encounters a line break it expects statement to be a complete PL/SQL statement and will send that statement to the database for it to parse. The database is returning the appropriate error because it is being sent the command:

EXEC SP_ADD_FOREIGN_KEY_CONSTRAINT_AUTO('DTMS_BA_VOLUME_DISCOUNT_DETAILS',

and the command is syntactically invalid as it has an open bracket but no closing bracket.

Everything is behaving exactly as the documentation states it should.

As the documentation states, if you do want to have a command spanning multiple lines then you need to use the SQL*Plus continuation character (a hyphen).