How to generate a CSV file from SQL*Plus – Part 2, by Zahar Hilkevich

This is a logical continuation of the topic we touched in a previous post.

This time we will see how to generate a CSV file from a cursor returned by a stored procedure. Our procedure should simply return a ref cursor based on the same query we used before:

SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno, dept.dname
ORDER BY 1

Here is the code for such stored procedure:

CREATE OR REPLACE PROCEDURE sp_get_dept(p_result OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN p_result FOR
    SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
    FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
    GROUP BY dept.deptno, dept.dname
    ORDER BY 1;
END;

The following script will help you accomplish the task:

accept fname prompt "Please enter full report file name: "

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET TIMING OFF
SET ECHO OFF
SET LINESIZE 100
SET PAGESIZE 0
SET VERIFY OFF

spool &fname 

DECLARE
   c SYS_REFCURSOR;
   --The following cursor is needed only to quickly define the type of the variable v;
   CURSOR c_type IS
   SELECT deptno, dname, 100 AS emp_count
   FROM dept
   WHERE 1=2;

   v c_type%ROWTYPE;
BEGIN
   DBMS_OUTPUT.ENABLE(NULL);

   --Dump out the header line:
   DBMS_OUTPUT.PUT('Dept No,');
   DBMS_OUTPUT.PUT('Dept Name,');
   DBMS_OUTPUT.PUT_LINE('Emp Count');
  
   --Call stored procedure
   sp_get_dept(c);   

   --Dump out the records:
   LOOP
        FETCH c INTO v;
        EXIT WHEN c%NOTFOUND;
	DBMS_OUTPUT.PUT('"' || v.deptno || '",');
        DBMS_OUTPUT.PUT('"' || v.dname || '",');
        DBMS_OUTPUT.PUT_LINE('"' || v.emp_count || '"');
   END LOOP;
   CLOSE c;
END;
/

spool off

The content of the generated file should look like this:

Dept No,Dept Name,Emp Count                                                                         
"10","ACCOUNTING","3"                                                                               
"20","RESEARCH","5"                                                                                 
"30","SALES","6"                                                                                    
"40","OPERATIONS","0"       

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to find and recompile all invalid Oracle objects?

Finding invalid Oracle object is a fairly simple DBA task (run as system or a user with select privilege on dba_objects view):

col object_type for a20
col object_name for a50

SELECT owner, object_type, object_name
FROM dba_objects
WHERE status='INVALID'
ORDER BY 1,2
/

Recompiling the invalid objects involves a few tricks. Normally, an Oracle object can be recompiled with the following SQL:

ALTER  [object_type] [owner].[object_name] COMPILE

However, PACKAGE BODY and SYNONYM objects require some special treatment. For the PACKAGE BODY objects the syntax changes just slightly:

ALTER  PACKAGE [owner].[object_name] COMPILE BODY

For the SYNONYMS (which don’t necessarily need to be recompiled) the most reliable way to obtain the VALID status is to recreate them using dbms_metadata.get_ddl function.

Below is a complete script that puts everything together:

--******************Recompiling Invalid Objects (if any exist)***************
--Author: Zahar Hilkevich (https://sqlpatterns.wordpress.com)
--***************************************************************************

DECLARE
CURSOR c IS
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status='INVALID';
v_sql VARCHAR2(500);
BEGIN
  FOR v IN c LOOP
    IF v.object_type='PACKAGE BODY' THEN
        v_sql:='ALTER PACKAGE ' || v.owner || '.' || v.object_name || ' COMPILE BODY'; 
    ELSIF v.object_type='SYNONYM' THEN
	v_sql:=dbms_metadata.get_ddl(object_type=>'SYNONYM',
                                     name=>v.object_name,
                                     schema=>v.owner);
    ELSE
        v_sql:='ALTER ' || v.object_type || ' ' || v.owner || '.' || v.object_name || ' COMPILE'; 
    END IF;
    --DBMS_OUTPUT.PUT_LINE(v_sql);
    BEGIN
        EXECUTE IMMEDIATE v_sql;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(v_sql);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
  END LOOP;
END;
/

--****************** End of Recompiling Invalid Objects *********************

--****************** Invalid Objects Report *********************************

col object_type for a20
col object_name for a50

SELECT owner, object_type, object_name
FROM dba_objects
WHERE status='INVALID'
ORDER BY 1,2
/

--******************End of Invalid Objects Report ***************************

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to generate a CSV file from SQL*Plus – Part 1, by Zahar Hilkevich

SQL Plus was widely used for reporting purposes for years but not any more. However, it can still be very handy if we want the report to be opened in another software product, such as Microsoft Excel.
In this post, I will show you how you can generate an Excel file directly from SQL*Plus.

Let’s say, for simplicity sake, we need to dump the result for the following query in Excel (csv) file:

SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno, dept.dname
ORDER BY 1

The following script will help you accomplish the task:

accept fname prompt "Please enter full report file name: "

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET TIMING OFF
SET ECHO OFF
SET LINESIZE 100
SET PAGESIZE 0
SET VERIFY OFF

spool &fname 

DECLARE
   CURSOR c IS
   SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
   FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
   GROUP BY dept.deptno, dept.dname
   ORDER BY 1;

BEGIN
   DBMS_OUTPUT.ENABLE(NULL);

   --Dump out the header line:
   DBMS_OUTPUT.PUT('Dept No,');
   DBMS_OUTPUT.PUT('Dept Name,');
   DBMS_OUTPUT.PUT_LINE('Emp Count');

   --Dump out the records:
   FOR v IN c LOOP
	DBMS_OUTPUT.PUT('"' || v.deptno || '",');
        DBMS_OUTPUT.PUT('"' || v.dname || '",');
        DBMS_OUTPUT.PUT_LINE('"' || v.emp_count || '"');
   END LOOP;
END;
/

spool off

The content of the generated file should look like this:

Dept No,Dept Name,Emp Count                                                                         
"10","ACCOUNTING","3"                                                                               
"20","RESEARCH","5"                                                                                 
"30","SALES","6"                                                                                    
"40","OPERATIONS","0"       

Here is how you run it from SQL*Plus (after establishing the connection):

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Part 2 is here