英文:
SPOOL compiler out when executing function creation script in SQL*PLUS
问题
我在执行创建函数的脚本时，在preProd服务器上无法获取编译器错误信息。
脚本是使用SQL*Plus执行的，但我们没有看到有关编译器输出的任何详细信息。
我们使用SPOOL命令，就像使用select、update等查询一样，但现在在spool文件中没有写入任何错误详细信息。
每次我们都会得到Function created with compilation errors，但我们不知道错误是什么，无法解决它们。
相反，在DEV中使用Oracle SQL Developer时，我们可以获取详细信息。
我们需要配置什么以在输出文件中获取编译器输出。
英文:
I'm having an issue to obtain compiler error from preProd server when executing and script to create a function.
Script is executed using SQL*Plus but we don't see any detail about compiler output.
We use SPOOL like with a select, update, etc query but now no details about error are written in spool file.
We obtain Function created with compilation errors everytime but we don't know what are the errors to solve them.
Instead in DEV using Oracle SQL Developer we obtain details.
What do we need to configure to obtain compilator out in an output file.
答案1
得分: 1
以下是翻译好的部分：
可能会有帮助，如果您发布从SQL*Plus执行的脚本。
与您的输出类似的输出：首先，我创建一个过程，该过程因表不存在而失败：
SQL> create or replace procedure p_test is
2 l_cnt number;
3 begin
4 select count(*)
5 into l_cnt
6 from table_that_does_not_exist;
7 end;
8 /
警告：过程创建时存在编译错误。
SQL> show err
过程 P_TEST 的错误：
行/列 错误
-------- -----------------------------------------------------------------
4/3 PL/SQL：忽略 SQL 语句
6/10 PL/SQL：ORA-00942：表或视图不存在
SQL>；
然后，我执行 `test.sql` 脚本，该脚本创建一个函数，显示错误并终止 spool。这是脚本：
set feedback on
create or replace function f_test return number is
begin
return 1;
end;
/
show err procedure p_test
spool off
当我从 SQL*Plus 调用它时：
SQL> @test
已创建函数。
过程 P_TEST 的错误：
行/列 错误
-------- -----------------------------------------------------------------
4/3 PL/SQL：忽略 SQL 语句
6/10 PL/SQL：ORA-00942：表或视图不存在
当前未 spool
SQL>；
所以：
- 函数已创建
- 显示错误堆栈（与函数无关，而与选择不存在的表的过程有关）
- `spool off` 失败，因为当前没有 spooling
- 如果您正在 spooling，也许您会在输出文件中看到一些内容。但是，由于您没有进行 spooling，因此没有文件（或者 - 也许有一个旧文件，两天前创建的），没有您正在寻找的信息
如我所说：这与您的情况*类似*。如果可能的话，请发布您所做的一切（隐藏不相关或敏感部分），以便我们能够看到更多信息。
英文:
It would probably help us help you if you posted script you're executing from SQL*Plus.
Output which is similar to yours: first, I'm creating a procedure which fails as table doesn't exist:
SQL> create or replace procedure p_test is
2 l_cnt number;
3 begin
4 select count(*)
5 into l_cnt
6 from table_that_does_not_exist;
7 end;
8 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE P_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
6/10 PL/SQL: ORA-00942: table or view does not exist
SQL>
Then, I'm executing test.sql script which creates a function, shows errors and terminates spool. This is the script:
set feedback on
create or replace function f_test return number is
begin
return 1;
end;
/
show err procedure p_test
spool off
When I call it from SQL*Plus:
SQL> @test
Function created.
Errors for PROCEDURE P_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
6/10 PL/SQL: ORA-00942: table or view does not exist
not spooling currently
SQL>
So:
- function is created
- error stack is displayed (not related to function, but procedure that selects from table that doesn't exist)
spool offfails because I'm NOT spooling currently- if you were spooling, maybe you'd see something in output file. But, as you aren't spooling, there's no file (or - maybe there's an old file, created two days ago) and no info you're looking for
As I said: this is similar to what you have. If possible, post everything you did (hide irrelevant or sensitive parts) so that we'd see some more info.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论