oracle Sqlplus可以将文件内容读入变量吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19721274/
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
Can Sqlplus read the contents of a file into a variable?
提问by John O
I've been tinkering with sqlplus for awhile now, and I've managed to get sqlplus to read the contents of a file into a substitution variable as such:
我一直在修改 sqlplus 一段时间，我已经设法让 sqlplus 将文件的内容读入替换变量，如下所示：
exit | sqlplus -s login/pass@db @script.sql "`cat file.txt`"
This mostly works as my script requires... even newlines in the file are preserved. However, I was only using a sample file which was 50 or 60 bytes in size. The actual files I'll end up using will be at least a few kilobytes. So it was at this point I decided to check the max size of a substitution variable: 240 characters.
这主要是因为我的脚本需要......甚至文件中的换行符也会被保留。但是，我只使用了一个大小为 50 或 60 字节的示例文件。我最终将使用的实际文件至少有几千字节。因此，此时我决定检查替换变量的最大大小：240 个字符。
Is there a way within my sqlplus script to read a file's contents into a bind variable? The Oracle documentation seems to hint at this with the GET command, saying that typicallyyou'll use this just to load a sql/sqlplus script.
在我的 sqlplus 脚本中有没有办法将文件内容读入绑定变量？Oracle 文档似乎用 GET 命令暗示了这一点，说通常您将使用它来加载 sql/sqlplus 脚本。
http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm#1009882
http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm#1009882
file_name[.ext] Represents the file you wish to load (typically a script).
file_name[.ext] 表示您要加载的文件（通常是脚本）。
回答by diziaq
Yes, there's a tricky way to do it. Put something into props.txtand run the script:
是的，有一个棘手的方法来做到这一点。将一些东西放入props.txt并运行脚本：
DECLARE
-- the @@ expression must be in separate line as follows
file_contents VARCHAR2(32767) := '
@@props.txt
';
BEGIN
dbms_output.put_line('===');
dbms_output.put_line(file_contents);
dbms_output.put_line('===');
END;
/
Note that the file props.txtcan not contain an "@" or you'll get nested
SQL*PLUS calls
请注意，该文件props.txt不能包含“@”，否则您将获得嵌套的 SQL*PLUS 调用
回答by HAL 9000
No. Loadwould only store the file contents in Sql*Plus's own sql buffer. You can then run, editand listthe buffer.
号Load将只存储在SQL * Plus自己的SQL缓冲区中的文件内容。然后你可以run，edit和list缓冲区。
A substitution variable is not the right place to load a file into. Use a bind variableof type clob for that and load the file contents using utl_file. But of course the file has to be located on the server in this case.
替换变量不是加载文件的正确位置。variable为此使用类型为 clob的绑定并使用utl_file. 但当然，在这种情况下，文件必须位于服务器上。
edit: if the data has to be located on the client, your option would be to load the clob using a pl/sql block and several calls to dbms_lob.writeappend
编辑：如果数据必须位于客户端上，您的选择是使用 pl/sql 块和多次调用dbms_lob.writeappend加载 clob
Your file would have to look like this (cannot test it ATM):
您的文件必须如下所示（无法在 ATM 上对其进行测试）：
var l clob;
var l clob;
begin
dbms_lob.createtemporary(l);
dbms_lob.writeappend(l, 'abcdef...');
dbms_lob.writeappend(l, 'ijkl...');
end;
/
回答by Michal Bělík
Please consider using literal quoted string - this allows you to have quotes in the linked file:
请考虑使用文字引号字符串 - 这允许您在链接文件中使用引号：
DECLARE
-- the @@ expression must be in separate line as follows
file_contents VARCHAR2(32767) := q'[
@@props.txt
]';
BEGIN
dbms_output.put_line('===');
dbms_output.put_line(file_contents);
dbms_output.put_line('===');
END;
/
