Q

Outputting rows to flat file, granting EXECUTE to user

I want to output all rows from a table to a flat file. If I use something like:

SPOOL  ofilename.lst;
SELECT * FROM tablex;
SPOOL  OFF
it works just fine. The problem is that the user that will execute this script does not have access to the table. I want to create a stored procedure that will grant EXECUTE to the user. But if I do this:
SPOOL  ofilename.lst;
EXECUTE schemax.select_data;
SPOOL  OFF;
I get no data. Is there a way to do this?

The easiest solution is

GRANT SELECT ON tablex TO usery;
If usery can see the data through the flat file, what's wrong with letting usery see the data directly?

PL/SQL is a very powerful and flexible tool, but it's not the best tool for every job. For quick and easy output, SQL*Plus is a great tool. Think carefully about why it's inadequate before spending any effort on some other method. For outputting data, report writers (such as SQR) are often the best tools.

If you decide you really have to do this in a stored procedure, the utl_file package provides some procedures (such as fopen, put_line and fclose) that you can use to write to a file, and the dbms_output package has procedures (like put_line) that you can use to write to SQL*Plus interface. utl_file assumes you have used CREATE DIRECTORY to make an output area available. If you use dbms_output in SQL*Plus, make sure the script has a SET SERVEROUTPUT command before it executes the procedure.

SET  SERVEROUTPUT ON SIZE 100000
allows SQL*Plus to display up to 100,000 bytes of output. The output will be buffered: you probably won't see anything at all until the execution is ended.

When you execute a SELECT statement in SQL*Plus, the result set is generated and automatically displayed, subject to settings such as COLUMN, SPOOL and TERMOUT that may be in force. When you execute a SELECT statement in a PL/SQL routine, the result set is generated, but nothing is automatically displayed: you have to provide explicit instructions on how to format and display the data, which probably explains why you didn't see any results when you ran the select_data procedure.

If you write a stored procedure, it might look something like this:

--      *******************************
--      **  s h o w _ a l l _ e m p  **
--      *******************************

--  show_all_emp displays every row in emp, sorted by empno

PROCEDURE  show_all_emp
IS
    CURSOR  emp_cursor IS
        SELECT    *
        FROM      scott.emp
        ORDER BY  empno;
BEGIN
    FOR  emp_record  IN  emp_cursor
    LOOP
        show_emp (emp_record);
    END LOOP;
END   show_all_emp;


--      ***********************
--      **  s h o w _ e m p  **
--      ***********************

--  show_emp displays a single emp record

PROCEDURE  show_emp
(
    in_record    IN    scott.emp%ROWTYPE
)
IS
BEGIN
    dbms_output.put (TO_CHAR (in_record.empno, '00000000'));
    dbms_output.put ('  ');
    dbms_output.put (RPAD (in_record.ename, 15));
    dbms_output.put ('  ');
    dbms_output.put_line (TO_CHAR (in_record.hiredate, 'YYYY-MM-DD'));
END   show_emp;

This was first published in August 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close