英文:
Getting error on executing a procedure having dyinamic sql
问题
以下是我在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;
我创建了上述存储过程,可以在Toad for Oracle中使用以下查询正常执行:
EXEC SP_ADD_FOREIGN_KEY_CONSTRAINT_AUTO('DTMS_BA_VOLUME_DISCOUNT_DETAILS','REF_BUSINESS_ASSOCIATE_ID')
其中,我将所有查询都写在了一行中。
但如果我像这样执行:
EXEC SP_ADD_FOREIGN_KEY_CONSTRAINT_AUTO('DTMS_BA_VOLUME_DISCOUNT_DETAILS',
'REF_BUSINESS_ASSOCIATE_ID')
在这里,我将查询的某部分写在了下一行。我收到错误消息:
ORA-06550: line 1, column 76:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
请问为什么会出现这个错误?
英文:
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?
答案1
得分: 0
以下是翻译好的内容:
这不是SQL或Oracle问题,与存储过程或动态SQL无关,而是客户端应用程序在向数据库发送命令时正如文档所述地表现出来,但它不符合您对其行为的预期。
语法
EXEC[UTE] statement
其中
statement
代表一个PL/SQL语句。执行单个PL/SQL语句。当您要执行引用存储过程的PL/SQL语句时,
EXECUTE
命令通常很有用。有关PL/SQL的更多信息,请参阅您的Oracle Database PL/SQL语言参考。用法
如果由于PL/SQL语句的原因,您的
EXECUTE
命令不能放在一行上,可以使用SQL*Plus的继续字符(连字符)。命令和PL/SQL语句的长度不能超过由
SET LINESIZE
定义的长度。
正如文档所示,EXECUTE
命令期望您的statement
能够适应单行,因此当它遇到换行时,它期望statement
是一个完整的PL/SQL语句,并将该语句发送到数据库进行解析。数据库返回了适当的错误,因为它收到了以下命令:
EXEC SP_ADD_FOREIGN_KEY_CONSTRAINT_AUTO('DTMS_BA_VOLUME_DISCOUNT_DETAILS',
该命令在语法上无效,因为它有一个开放括号但没有闭合括号。
一切都正如文档所述地表现出来。
正如文档所述,如果确实希望有跨多行的命令,那么需要使用SQL*Plus的继续字符(连字符)。
英文:
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
>
> lang-sql
> 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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论