How to organize SQL script files and folders

Question

We have an Oracle 10g database (huge) in our company, and I provide employees with data on their requests. My problem is that I save almost every SQL query that I wrote, and now my list is too long. I want to organize and rename these .sql files so that I can easily find the one I need. At the moment I am using several folders called Sales Dept, Field Team, Planning Dept, Special , etc., And there are .sql files in these folders, for example

 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 contents, but this makes the file names longer and does not fully meet my needs. Sometimes someone comes in and requires a special report, and I give the file its name, but that is also not so good. I know that duplicates or very similar files grow in time, but I do not control them.

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

Answer 1

Folders are a disgusting way to catalog a large number of files, which you can find later. I know colleagues who compulsively create hundreds of folders and subfolders in Outlook to classify every pile of mail that comes in; they then spend several minutes opening the folder after the folder, trying to remember where they put things. I just keep everything in my inbox and then use Google Desktop Search to find them - much faster! Similarly, I try to store all my special SQL scripts in the same folder c: \ sql, and then use Google Desktop Search to find them.

Alternatively, perhaps you could create a simple database to save them in a table:

 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 can 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' ); 

Then you can search for it in different ways, for example.

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