Export Query Results in PL/SQL

1 year ago
Liam
2 minutes

In PL/SQL, you can use the DBMS_OUTPUT package to export query results.

Firstly, the results need to be output to the output buffer in a PL/SQL program using the DBMS_OUTPUT.PUT_LINE function.

For example, here is a simple PL/SQL program that outputs the query results to the output buffer:

DECLARE
  v_column1 table_name.column_name%TYPE;
  v_column2 table_name.column_name%TYPE;
BEGIN
  -- 查询数据
  SELECT column1, column2 INTO v_column1, v_column2 FROM table_name WHERE condition;

  -- 输出数据
  DBMS_OUTPUT.PUT_LINE('Column1: ' || v_column1);
  DBMS_OUTPUT.PUT_LINE('Column2: ' || v_column2);
END;

After running the PL/SQL program, the output buffer needs to be enabled in SQL*Plus to export the results. You can enable the output buffer by using the command SET SERVEROUTPUT ON.

After executing the above PL/SQL program and enabling the output buffer, you can use the SPOOL command to export the output results to a file. For example, here is an example of exporting the output results to a file:

SET SERVEROUTPUT ON
-- 启用输出缓冲区

SPOOL output.txt
-- 导出结果到文件output.txt

-- 执行PL/SQL程序

SPOOL OFF
-- 停止导出结果到文件

Once the above SQL script is executed, the query results will be exported to the file output.txt.

bannerAds