How to organize SQL script files & folders

Question

We have an Oracle 10g database (a huge one) in our company, and I provide employees with data upon their requests. My problem is, I save almost every SQL query I wrote, and now my list has grown too long. I want to organize and rename these .sql files so that I can find the one I want easily. At the moment, I'm using some folders named as Sales Dept, Field Team, Planning Dept, Special etc. and under those folders there are .sql files like

Delivery_sales_1, Delivery_sales_2, ...
Sent_sold_lostsales_endpoints, ...
Sales_provinces_period, Returnrates_regions_bymonths, ...
Jack_1, Steve_1, Steve_2, ...

I try to name the files regarding their content but this makes file names longer and does not completely meet my needs. Sometimes someone comes and demands a special report, and I give the file his name, but this is also not so good. I know duplicates or very similar files are growing in time but I don't have control over them.

Can you show me the right direction to rename all these files and folders and organize my queries for easy and better control? TIA.

Answer 1

Folders are a lousy way to catalog large numbers of files such that you can find things later. I have known colleagues to obsessively create hundreds of folders and subfolders in Outlook to categorise every piece of mail that comes in; they then spend several minutes opening folder after folder trying to remember where they put things. Me, I just keep everything in the Inbox and then use Google Desktop Search to find them - much quicker! Similarly, I tend to keep all my ad hoc SQL scripts in a single folder c:\sql and then use Google Desktop Search to find those.

Alternatively, perhaps you could build a simple database to keep them in, with a table like:

create table sql_scripts
  ( id integer primary key -- populated by a trigger
  , sql clob
  , date_created date default sysdate
  , who_for varchar2(30)
  , title varchar2(100)
  , keywords varchar2(100)
  );

Then you could insert, for example:

insert into sql_scripts
  ( sql
  , who_for varchar2(30)
  , title varchar2(100)
  , keywords varchar2(100)
  ) values
  ( 'select ename from emp where deptno=10'
  , 'Steve Jones'
  , 'List of employees in department 10'
  , 'hr,emp,dept10'
  );

You can then later search this in various ways e.g.

select * from sql_scripts
where upper(who_for) like 'STEVE%'
and upper(sql) like '%DEPTNO%'
and date_created > sysdate-365;