SPOOL ofilename.lst; SELECT * FROM tablex; SPOOL OFFit 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?
Requires Free Membership to View
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 100000allows 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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation