Problem solve Get help with specific problems with your technologies, process and projects.

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;
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;
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.

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
    CURSOR  emp_cursor IS
        SELECT    *
        FROM      scott.emp
        ORDER BY  empno;
    FOR  emp_record  IN  emp_cursor
        show_emp (emp_record);
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
    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 last published in August 2003

Dig Deeper on Using Oracle PL-SQL

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.