是否可以在存储过程中运行 sqlplus 文件? [英] Is it possible to run a sqlplus file inside a stored procedure?

问题描述

是否可以从存储过程中运行和执行 sqlplus 文件?

Hi is it possible to run and execute a sqlplus file from inside a stored procedure ?

到目前为止，我已经尝试了以下方法:

CREATE OR REPLACE PROCEDURE SCRIPTRUN
(

    p_orgid     IN VARCHAR2,
    p_axtype    IN VARCHAR2,
    P_option    IN VARCHAR2

) AS 

 runn VARCHAR2(200) := '@C:\Scripts\delete_ax\delete-ORG.sql '||  p_orgid ||' '||  p_axtype||' '|| P_option ; 


BEGIN

 execute IMMEDIATE runn;


END SCRIPTRUN;

我收到的错误消息:

ORA-00900:无效的 SQL 语句ORA-06512:在DWOLFE.SCRIPTRUN"，第 17 行ORA-06512: 在第 10 行

ORA-00900: invalid SQL statement ORA-06512: at "DWOLFE.SCRIPTRUN", line 17 ORA-06512: at line 10

推荐答案

1.创建目录EXT_TAB_DATA/opt/oracle/DEV/SAMPLE

1.Create the directory EXT_TAB_DATA /opt/oracle/DEV/SAMPLE

 select * from dba_directories
    where directory_name='EXT_TAB_DATA'
    ==>
    OWNER          DIRECTORY_NAME          DIRECTORY_PATH                                                                                       
    --------- ------------------------ ----------------------------------- 
    SYS           EXT_TAB_DATA                /opt/oracle/DEV/SAMPLE              

2.使用sqlplus预处理器创建外部表sqlplus2.

2.Create an external table sqlplus2 with sqlplus preprocessor.

DROP TABLE ext_sqlplus
/
CREATE TABLE ext_sqlplus
    (stdout                         VARCHAR2(256 CHAR))
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION EXTERNAL (
   DEFAULT DIRECTORY  EXT_TAB_DATA
    ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
   PREPROCESSOR EXT_TAB_DATA:'sqlplus2'
   BADFILE 'EXT_SQLPLUS_%a_%p.bad'
   NOLOGFILE
   SKIP 1
   FIELDS TERMINATED BY '\n' 
   MISSING FIELD VALUES ARE NULL
 )
   LOCATION (
    EXT_TAB_DATA:'..'
   )
  )
   REJECT LIMIT UNLIMITED
  NOPARALLEL
/

3.使用 sqlplus 调用和 sql 文件 /opt/oracle/DEV/SAMPLE/select_sysdate_from_dual.sql.

3. Create the shell sqlplus2 with a sqlplus call and sql file /opt/oracle/DEV/SAMPLE/select_sysdate_from_dual.sql.

 oracle@esmd:~/DEV/SAMPLE> more sqlplus2 
/oracle/product/11.2.0.3/db/bin/sqlplus /nolog  @/opt/oracle/DEV/SAMPLE/select_sysdate_from_dual.sql

4.创建一个过程write_sqlfile，将查询写入sql文件select_sysdate_from_dual.sql.

4.Сreate a procedure write_sqlfile that writes queries to the sql file select_sysdate_from_dual.sql.

CREATE OR REPLACE 
PROCEDURE write_sqlfile (SQL_STRING IN VARCHAR2) IS
 OutFile  utl_file.file_type;


connect_string VARCHAR2(256):='connect system/manageresmd';
exit_string VARCHAR2(25):='exit';
file_name  VARCHAR2(256):='select_sysdate_from_dual.sql';
file_dir   VARCHAR2(25):='EXT_TAB_DATA'; 

BEGIN

  OutFile := utl_file.fopen(file_dir, file_name, 'w');
  utl_file.put_line(OutFile, connect_string, FALSE);
  utl_file.put_line(OutFile, SQL_STRING, FALSE);
  utl_file.put_line(OutFile, exit_string, FALSE);
  utl_file.fflush(OutFile);
  utl_file.fclose(OutFile);

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END write_sqlfile;
/

示例 1运行过程 write_sqlfile 将 sql 写入脚本.

Example 1 Run the procedure write_sqlfile to write sql to the script.

begin
write_sqlfile(
'
select ''1-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''2-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''3-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''4-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
select ''5-''||to_char(sysdate,''DD-MON-YYYY HH24:MI'') from dual;
');

end;

检查文件系统中文件的内容.

Check the contents of the file in the file system.

oracle@esmd:~/DEV/SAMPLE> more select_sysdate_from_dual.sql
connect system/manageresmd

select '1-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '2-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '3-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '4-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;
select '5-'||to_char(sysdate,'DD-MON-YYYY HH24:MI') from dual;

exit
oracle@esmd:~/DEV/SAMPLE>

对外部表执行查询，查看sqlplus输出

Execute a query to the external table and see the sqlplus output

SELECT *  FROM ext_sqlplus

输出

    SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 30 12:49:41 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.

'1-'||TO_CHAR(SYSDA
-------------------
1-30-AUG-2019 12:49


'2-'||TO_CHAR(SYSDA
-------------------
2-30-AUG-2019 12:49


'3-'||TO_CHAR(SYSDA
-------------------
3-30-AUG-2019 12:49


'4-'||TO_CHAR(SYSDA
-------------------
4-30-AUG-2019 12:49


'5-'||TO_CHAR(SYSDA
-------------------
5-30-AUG-2019 12:49

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

示例 2运行过程 write_sqlfile 将主机命令操作系统写入脚本.

Example 2 Run the procedure write_sqlfile to write host command OS to the script.

 begin
    write_sqlfile(
    '
    host  cd /opt ; /bin/ls -l
    ');


    end;

对外部表执行查询，查看sqlplus输出

Execute a query to the external table and see the sqlplus output

SELECT *  FROM ext_sqlplus;

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 30 14:21:27 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
total 20
drwxr-xr-x 3 root   root     4096 2012-12-12 10:13 app
drwxr-xr-x 3 root   root     4096 2014-11-10 11:04 IBM
drwxr-xr-x 3 root   root     4096 2012-04-24 09:58 kde3
drwxr-xr-x 8 oracle oinstall 4096 2019-08-13 09:47 oracle
drwxr-xr-x 3 root   root     4096 2012-04-25 11:41 ORCLfmap

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

这篇关于是否可以在存储过程中运行 sqlplus 文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！