How can we do this?
Response
Niels Gron over 10 years ago
You can accomplish this functionality in Aqua Data Studio with the same or similar commands. Here are some clarifications …
The SPOOL command is a SQLPlus command and not an Oracle command. It runs on the client side :
http://ss64.com/ora/syntax-sqlplus.html
SPOOL file : Store query results in file
UTL_FILE is a PL/SQL package on the Oracle server which can be executed on the server side.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm
With the UTL_FILE package, PL/SQL programs can read and write operating system text files.
Aqua Data Studio does not implement the SPOOL command of SQLPlus, but you can do the same with FluidShell in ADS 12.0. You can also do the same in the Query Window of ADS 11.0 with an Aqua Command although there is a memory limit. I’ll illustrate both below. Aqua Data Studio also allows you to execute PL/SQL packages on Oracle, either by executing stored procedures, or by executing anonymous code blocks.
http://www.orafaq.com/wiki/Anonymous_block
Here is a simple anonymous block :
declare
i number;
begin
i := 10;
DBMS_OUTPUT.put_line(‘Hello world! ‘);
DBMS_OUTPUT.put_line(i);
end;
Here is an example with UTL_FILE :
CREATE OR REPLACE DIRECTORY MYDIR AS ‘c:\test’
go
GRANT WRITE ON DIRECTORY MYDIR TO DBA
go
DECLARE
FILEID UTL_FILE.FILE_TYPE;
LINE_BUFF VARCHAR2(1000);
BEGIN
FILEID:= UTL_FILE.FOPEN (‘MYDIR’, ’emp.dat’, ‘W’);
FOR emprec IN (SELECT * FROM EMPLOYEES) LOOP
LINE_BUFF :=TO_CHAR (emprec.FIRST_NAME);
UTL_FILE.PUT_LINE ( FILEID ,LINE_BUFF);
END LOOP;
END;
See my attached screenshot examples anonymous-block.png & utl_file.png. Do note that UTL_FILE will create the file on the server filesystem.
In ADS 11 and 12, when you query for a resultset you can Save Results to a file. In the Save Results dialog there is a Preview Script tab which will give you an Aqua Command example to save the results of a query. I can then execute this command in conjunction with a SELECT statement. See attached screenshot save-results.png and execute-save-results.png. You can use the “saveResults” or “saveLastResult” command. Here here …
https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation12/page/196/Aqua-Commands
Do keep in mind that the saveResults commands will hold the full resultset in memory before saving to disk, so there is a memory limitation if you are working with large resultsets.
In ADS 12 we have a new feature called FluidShell, which is similar to SQLPlus is was built to have more functionality to spool data to different sources and formats, including conversion of the data. See the attached screenshot fluid-shell-example.png for a simple example of what you can do. If you are looking for a very flexible and powerful way of extracting and spooling data, then FluidShell would be the best option.
-Niels
Attachments(11).zip