Search code examples
sqloracle-databasenamingfile-organization

How to organize SQL script files & folders


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.


Solution

  • 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;