How to resolve the issue of PLSQL exporting SQL file without popping up a window?

2 years ago
Ava Mitchell
2 minutes

To resolve the issue of the SQL file not popping up when exporting in PL/SQL, you can use the following method:

  1. Use the SPOOL command: In a PL/SQL script, use the SPOOL command to output the results to a file instead of a popup window. Here is an example code:
SET SERVEROUTPUT ON
SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF

SPOOL output.sql

-- 在这里编写需要导出的SQL语句

SPOOL OFF

SET TERMOUT ON
SET VERIFY ON
  1. Utilizing the UTL_FILE package: directly writing the results into a file in PL/SQL using the UTL_FILE package. Example code is as follows:
DECLARE
  file_handle UTL_FILE.FILE_TYPE;
  output_text VARCHAR2(4000);
BEGIN
  file_handle := UTL_FILE.FOPEN('DIRECTORY_NAME', 'output.sql', 'W');

  -- 在这里编写需要导出的SQL语句
  output_text := 'SELECT * FROM table_name;';
  UTL_FILE.PUT_LINE(file_handle, output_text);
  
  UTL_FILE.FCLOSE(file_handle);
END;

Please note that the DIRECTORY_NAME mentioned in the example above is a directory object that has already been created in the database to specify the save path for output files. If a directory object has not been created yet, please first create one using the CREATE DIRECTORY statement.

Whether you choose to use the SPOOL command or the UTL_FILE package, you can write the SQL statements you want to export in a PL/SQL script and output the results to a specified file without any pop-up windows.

bannerAds