sending sql output to a csv file

>> Thursday, 15 October 2015

So I would previously have been doing something like this

spool myfile.csv;
set heading off
set pages off
select column1_name||','||column2_name||','||column3_name||','||column4_name
from mytable order by 1;
spool off

which is fine but I had to build up the statement each time.

Step in Tom, I found this procedure tucked away in an asktom page from 2000

sqlplus / as sysdba
grant execute on utl_file to system;
create or replace directory DPUMP_TMP as '/tmp';
grant read,write on directory DPUMP_TMP to system;

CREATE OR REPLACE procedure SYSTEM.dump_table_to_csv( p_tname in varchar2,
                                               p_dir   in varchar2,
                                                p_filename in varchar2 )
     l_output        utl_file.file_type;
     l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(4000);
    l_status        integer;
    l_query         varchar2(1000)
                      default 'select * from ' || p_tname;
    l_colCnt        number := 0;
     l_separator     varchar2(1);
    l_descTbl       dbms_sql.desc_tab;
      l_output := utl_file.fopen( p_dir, p_filename, 'w' );
     execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

     dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
          utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
         dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
       l_separator := ',';
      end loop;
     utl_file.new_line( l_output );

     l_status := dbms_sql.execute(l_theCursor);

      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        l_separator := '';
          for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
              utl_file.put( l_output, l_separator || l_columnValue );
              l_separator := ',';
         end loop;
          utl_file.new_line( l_output );
     end loop;
       utl_file.fclose( l_output );

     execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
     when others then
         execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

exec SYSTEM.DUMP_TABLE_TO_CSV ('schema.tablename','DPUMP_TMP','myfilename.csv');

If you want to read more about Tom talking about unloading data to a flat file go here


Oracle Midlands - ClubOracle

>> Thursday, 21 May 2015

It as taken me a while to get to an Oracle Midlands event - this was the 9th and I've been saying I'd go since number 1.. The venue in Birmingham was easy to get to from the train station, there is easy parking at the venue (once you are through city centre traffic!)

I was surprised at the number of attendees.  There were 30 there and I think there would have been more if it weren't for the dreadful weather on the day combined with the unusually heavy traffic.

The group was really friendly and I was pleased to see people I immediately recognised both from twitter feeds and from previous jobs.

The first speaker at #om9 was Joel Goodman.  I think that was a bit of a scoop. Joel is amazing.  He is a walking oracle encyclopaedia combined with some of the best training (and presentation) skills I have ever known.  He trained me on Oracle 8i Backup and Recovery more years back than I am prepared to admit to.

His session was on Oracle Distributed Transactions. Clear and concise fundamentals including2 phase commits and the killer "this is how it can bite you" information.

I knew by 7:05pm my train ticket had paid for itself when the fog started to clear on some of the reasons for in-doubt transactions and the chain of table locks.  Another one of those 'I'm sure I knew this once but I really ought to know it all the time" moments.

There was a break for a snack and a chat, a which point the penny dropped for a few of the people I'd been talking to like old friends who I actually was and that they talk to me on-line fairly regularly.  And the same for me as people introduced themselves.  I love how friendly DBAs are.

The second session was Datapump.  I'll kick back on this I thought, I know this one. Wrong!  Rich Harrison pulled the "oh, I didn't know that" out of the bag a number of times.  He is also a really smooth presenter and one to watch out for in the future.  The next day at work a colleague was having a slow running export nightmare and I was all "did you know you can do this?" !  Felt good.

So If you are in the Midlands region (or passing through), I recommend this user group. It is free to attend (sponsored by Redstack Techologies) and in the evening.  What's not to like.

Have a look at the up and coming events here.


Security Presumptive

>> Friday, 15 May 2015

We have the auditors currently visiting the office.  They don't mix with anyone at all, from what I can see, apart from the one man that is the 'go between'.  They work out of a meeting room not very far away from my desk.

I feel it necessary to say before I tell you why I'm telling you about this that I work in a very secure building.  It is surrounded by high wire fences,  cameras and sound detectors.  A secure site within an already secure site.  It's safe to say it is secure!  There are very few people that can get in and generally we all know each other fairly well,  if not intimately then at least by sight or on "you left your card in the coffee machine" terms.

To get to my internal office door you have to go through 3 sets of secure doors requiring a personal level of access and also past the glass window of the 24 hours manned security office.  It's safe to say by the time you get to my office door you have passed security.  There is nothing in my office of merit or value to be secured!  There used to be something behind the door to secure.  That thing is now many, many miles away.  Any security left on my office door is there because a jobsworth didn't take it away with 'the thing'.

So back to my auditor non-interactive-'friend'.  Everyday, many times a day she opens my office door with her secure access.  My office doors are double doors.  The right hand door is next to the security point.  The left hand door is not (this is relevant!).

Some office incumbents noticed many moons ago that no one uses the left hand door.  People push the right hand door, it is locked, they then try their secure access method and if it says 'come on in' they then open the door.  An office incumbent took the bolt off the left hand door.  So now the office incumbents walk through the left hand door.  It can be done backwards whilst balancing a laptop and a hot cup of tea without the need to swing the body part attached to a pass at a reader or punch anything into a keypad with your nose.  Others use their pass and come through the locked right hand door.

 So back to my auditor non-interactive-'friend':  everyday, many times a day, she opens the right hand door with her secure access. Today I watched her use her secure access and then open the left hand door.  She has been there at least 2 weeks so this is progress but she still hasn't thought to just push the door first.

There is another door that leads to a meeting room with a key card reader on it.  (Again, there is nothing of merit or value behind this door, even the furniture behind it wouldn't sell at a car boot!) The door lock does not work, men with hammers are aware of this and on a regular basis they come to it, bang their hammers, scratch their heads and leave again.  The key card reader beeps when you do stuff to it with your access and its light turns green if it likes you.

For 2 weeks I have watched my non-interactive-auditor-'friend' use her secure access to go through the door, never once doubting that it is locked.  Although bizarrely there is no handle on the door the other side and she must just push the door to get out!!!

So my point is:  just because something asks you for a password you shouldn't assume that it is secure.  Does it care what that password is?  What happens if you try to get in without the password?  The appearance of secure does not mean it is.  You might have userids, roles, profiles, passwords, key readers, whatever,  but what if, as I found recently, one table has clear text passwords in that could (note the past tense here - sorted) be read by a multitude of people?! Or what if people that are fed up with dealing with security on a daily basis can undo a bolt and no one is alerted?!

I am desperate to tell my non-interactive-auditor-'friend' to just walk through the door but I'm also now totally wrapped up in watching to see how many weeks it takes her to twig.

I am also torn about what this tells me about her approach to looking at security.  Life for many would be jolly easy if our non-interactive-auditor-'friend' just puts a lovely large green tick on a box to say that she tried the security and it all worked fine and moves onto her next job. But whilst I think/hope/know the baskets of eggs I am responsible for are safe from the foxes, I would really like her to find any security holes in anything because there are lots of people out there that are constantly pushing at the left hand door to see if it opens all on its own and I want to bolt it solid before they even try.


  © Blogger template Simple n' Sweet by 2009

Back to TOP