Sqlplus可以将文件的内容读入变量吗? [英] Can Sqlplus read the contents of a file into a variable?

问题描述

我已经尝试修改sqlplus一段时间了，我设法使sqlplus将文件内容读入替换变量，如下所示:

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:

exit | sqlplus -s login/pass@db @script.sql "`cat file.txt`"

这主要是按照我的脚本要求工作的，即使文件中的换行符也被保留.但是，我只使用了一个大小为50或60字节的示例文件.我最终将使用的实际文件至少为几千字节.因此，我决定检查替换变量的最大大小:240个字符.

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.

我的sqlplus脚本中是否有一种方法可以将文件的内容读取到绑定变量中? Oracle文档似乎使用GET命令对此进行了暗示，说通常您将仅使用此命令来加载sql/sqlplus脚本.

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 typically you'll use this just to load a sql/sqlplus script.

http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm#1009882

file_name [.ext]表示要加载的文件(通常是脚本).

file_name[.ext] Represents the file you wish to load (typically a script).

推荐答案

是的，有一种很棘手的方法. 将一些内容放入 props.txt 中并运行脚本:

Yes, there's a tricky way to do it. Put something into props.txt and run the script:

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;
/

请注意，文件props.txt不能包含"@"，否则您将被嵌套 SQL * PLUS调用

Note that the file props.txt can not contain an "@" or you'll get nested SQL*PLUS calls

这篇关于Sqlplus可以将文件的内容读入变量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！