oracle 从命令提示符编译 PLSQL（不在 sqlplus 中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3447162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compiling PLSQL from command prompt (not inside sqlplus)
提问by mehmet6parmak
is there any way compiling plsql from command prompt not by opening sqlplus and writing the command or @filename?
有没有办法从命令提示符编译plsql，而不是通过打开sqlplus并编写命令或@filename？
We want to import the output to a file and parse it for a code review tool we are working on
我们想将输出导入到一个文件中，并为我们正在开发的代码工具解析它
Thanks...
谢谢...
回答by Alex Poole
Not sure I quite understand what you mean, it sounds like you just want to capture the output of the execution, but can't tell if you actually mean that you want to avoid SQL*Plus completely. From my first reading this is as simple as:
不确定我是否完全理解您的意思，听起来您只是想捕获执行的输出，但无法判断您是否真的意味着要完全避免 SQL*Plus。从我第一次阅读开始，这很简单：
sqlplus -s user/password @filename > outputfile
... but that makes me think I've missed something important.
……但这让我觉得我错过了一些重要的事情。
回答by oluies
Dont think so, see this presentationby Pete Finnigan for some compiler internals. It lists an ANTLRgrammar filethat you could use.
不要这么认为，请参阅Pete Finnigan 的此演示文稿以了解一些编译器内部结构。它列出了您可以使用的ANTLR语法文件。
I have also seen mentions about Scalacombinatorparser implementations for PL/SQL.
回答by JulesLt
If you just need to output the source - USER_SOURCE.
如果您只需要输出源 - USER_SOURCE。
I do a lot of dynamic generation of packages, and I've written code that extracts, adds additional code, and recompiles the package.
我做了很多包的动态生成，我编写了提取、添加额外代码和重新编译包的代码。
At it's simplest you just need to wrap up your CREATE PACKAGE command as dynamic SQL.
最简单的方法是将 CREATE PACKAGE 命令包装为动态 SQL。
EXECUTE IMMEDIATE
'CREATE OR REPLACE PACKAGE myPackageName AS '||pPackageSource;
However, that means your source is just one string. I use the older DBMS_SQL.PARSE method that can accept an array of VARCHAR2 lines (dbms_sql.varchar2s).
但是，这意味着您的来源只是一个字符串。我使用旧的 DBMS_SQL.PARSE 方法，该方法可以接受一组 VARCHAR2 行 (dbms_sql.varchar2s)。
Example - code to pull the source from user_source into a varchar 2s, then recompile the same package via dbms_sql.
示例 - 将源从 user_source 拉入 varchar 2s 的代码，然后通过 dbms_sql 重新编译相同的包。
DECLARE
lCid INTEGER;
lError INTEGER;
lSource dbms_sql.varchar2s;
FUNCTION fSource(
pName IN VARCHAR2,
pType IN VARCHAR2)
RETURN dbms_sql.varchar2s
IS
CURSOR cSource IS
SELECT RTRIM(text,CHR(10))
FROM user_source
WHERE name = pName
AND type = pType
ORDER BY line;
lSource pp_type.gtyp_ArrayOfSource;
BEGIN
OPEN cSource;
FETCH cSourcee BULK COLLECT INTO lSource;
CLOSE cSource;
RETURN lSource;
END fSource;
BEGIN
lSource := fSource(pName => 'myPackageName',pType => 'PACKAGE');
/* Add CREATE or REPLACE to the start of the source */
lSource(1) := 'CREATE OR REPLACE '||lSource(1);
-- Cannot use EXECUTE IMMEDIATE as this is an ARRAY
lCid := dbms_sql.OPEN_CURSOR;
--
dbms_sql.parse(
c => lCid ,
statement => lSource,
lb => 1,
ub => p_source.count,
lfflg => true,
language_flag => dbms_sql.v7
);
dbms_sql.close_cursor (lCid);
END;
This could easily be changed to extract the source from a file, HTTP service, etc - anything that the pl/sql code / database can reach.
这可以很容易地更改为从文件、HTTP 服务等中提取源 - pl/sql 代码/数据库可以访问的任何内容。
The actual code includes validation, checks the result is valid, emails any errors (from user_errors) matched against the lines in user_source, etc.
实际代码包括验证、检查结果是否有效、将与 user_source 中的行匹配的任何错误（来自 user_errors）通过电子邮件发送等。
Security
安全
The code needs to be running with privileges to install a package - this may mean installing the package against a user with higher privileges than the executing user.
代码需要以安装包的权限运行 - 这可能意味着针对比执行用户具有更高权限的用户安装包。
If you are doing this, you need to be very careful about what interfaces are exposed to 'normal' DB users - i.e. you do not want to create anything that lets a normal user extract or modify the package source or privileges.
如果你这样做，你需要非常小心什么接口暴露给“普通”数据库用户——即你不想创建任何让普通用户提取或修改包源或权限的东西。
My approach was creating a low-level package (hidden from normal users) for all the service functions (get source, install, dynamic execute, etc) and then a higher level package that exposed a limited set of specific actions to regular users - in your case this would be something like exposing a 'reviewCode' procedure.
我的方法是为所有服务功能（获取源、安装、动态执行等）创建一个低级包（对普通用户隐藏），然后创建一个更高级别的包，向普通用户公开一组有限的特定操作 - 在您的情况这类似于公开“reviewCode”程序。
