I have to use SQL+ on one of my databases. No way around it. So I will write my queries, run them, check the output, tweak them and run again as necessary. So I might start with
SELECT
ename,
job
FROM
emp;
Once I know it is working correctly, I wrap it in a procedure so I can get the data in a text file:
SET TRIM ON
SET PAGESIZE 2000
SET SERVEROUTPUT ON SIZE unlimited
EXEC DBMS_OUTPUT.ENABLE(null)
--
SPOOL c:\mySQL\out.txt
--
BEGIN
--
DECLARE
--
CURSOR c_cur IS
--
SELECT
ename,
job
FROM
emp;
--
BEGIN
--
DBMS_OUTPUT.PUT_LINE(
'NAME|JOB|'
);
FOR r_cur IN c_cur LOOP
DBMS_OUTPUT.PUT_LINE(
r_cur.ename||'|'||
r_cur.job
);
END LOOP;
END;
END;
/
Then I import this into Excel as delimited data on the pipe |
which works great until I decide that I actually do need one more column. Then I have make changes to the SQL, and two more changes in the output portion (one for the heading, and one for the LOOP)
I was wondering if anyone had written a cool procedure that I could run ANY SQL through, and it would automatically know my column names write the headings and then loop through the data automatically.
I'm not tied to using the exact procedure I described above. The key is, I am looking for a general procedure that I can run any script through, and it will handle the output for me, without additional modification.