DOWNLOAD: SQLDIR Full Version

One stop sqlplus script management utility for Oracle:

    * Sql scripts reside on OS file system (UNIX, WINDOWS)
    * Scripts are organized into categories using special tags 
      embedded into each script (very simple like html)
    * SQLDIR parser scans them into searchable repository
    * Access scripts right from sqlplus using user friendly menu
      (character based for hardcore telnet users)
    * True tool for hardcore Oracle DBA that relies on sqlplus 
      rather then GUI tools because sqlplus is always there
    * Parser is written in C making it very fast
    * Organize just about any sqlplus script (adhoc reporting ...)
    * New scripts are easily added to repository and automatically
      parsed for description tag and category tag (set up a cron job
      and all new scripts will be at the tip of your finger momentarily)
    * Repository is saved as sqlplus script which makes it portable 
      take it anywhere and have it up and running in seconds perfect
      for all you road warriors out there ... I too was in consulting for years. 

1. Download and untar SQLDIR core files - sqldir_dreq.tar
   
   After you untar the archive you should have the 
   following directories:

	sqldir/bin
	sqldir/doc
	sqldir/sql

   The following SQL scripts will be in "sqldir/sql" directory:

	u_prompt.sql
	x_banner.sql
	x_db_name.sql
	x_dir.sql
	x_dir_share.sql
	x_dir_start.sql

   These scripts are the core of SQLDIR menu don't modify nor delete them.

   If you are on SOLARIS or Windows go to step 2 now.

   If you are on Linux rename sqldir_LINUX to sqldir:

      cd sqldir/bin
      mv sqldir_LINUX sqldir

   If you are on any other platform get the source code - FULL_sqldir.c
   and compile it using the following command:

      gcc -o sqldir FULL_sqldir.c 
      (ignore warning: return type of `main' is not `int')
      here's an example - compile SQLDIR on Red Hat Linux

2. Download and untar the latest SQL script archive - sql.tar
   (untar this file into the "sqldir/sql" directory)

3. Run sqldir/bin/dirlist.sh (dirlist.bat on windows) to parse 
   SQL scripts located in sqldir/sql directory.  This will create 
   three files in the "sqldir/bin" directory:

	sqldir_txt.sql
	sqldir_grp.sql
	sqldir_ddl.sql

4. Install SQLDIR menu by running "sqldir_ddl.sql" from 
   SQL*Plus.  You can use SYSTEM account for starters.

   -------------- END OF INSTALLATION ---------------

To access the SQLDIR menu start SQL*PLus in "sqldir/sql" directory
connect to the account you used in step 4 and run the following
script "x_dir.sql".  You should get a similar menu:

   $ pwd
   /u01/app/dbatoolz/sqldir/sql
   $ sqlplus system@DEMODB
   
   SQL*Plus: Release 8.1.7.0.0 - Production on Sat Jan 26 15:35:47 2002
   
   (c) Copyright 2000 Oracle Corporation.  All rights reserved.
   
   Enter password: 
   
   Connected to:
   Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
   With the Partitioning option
   JServer Release 8.1.7.2.0 - Production
   
   SQL> @x_dir
   
   Session altered.
   
   +=====================================================+
   |             SQLDIR SQL*Plus Menu v1.2               |
   +=====================================================+
   
    ID Group Name                                     Cnt
   --- --------------------------------------------- ----
     0 TOTAL Number Of Scripts: ....................  192
     1 APPS General ................................    2
     2 APPS Installation ...........................    3
     3 APPS Concurrent Programs ....................    8
     4 APPS Concurrent Managers ....................    8
     5 APPS Administration .........................   10
     6 Data Files ..................................   13
     7 Tablespace ..................................   25
     8 Utility .....................................   22
     9 Index .......................................    8
    10 Redo Log ....................................    8
    11 Rollback ....................................    5
    12 Maintenance .................................   20
    13 Reverse Engineering .........................    7
    14 SGA Maintenance .............................   16
    15 Table .......................................   22
    16 User Management .............................   29
    17 Statistics ..................................   13
    18 Storage Management ..........................   10
    19 Database Init Parameters ....................    2
    20 Latches .....................................    2
    21 Locks .......................................    5
    22 Segment Management ..........................   18
    23 Backup Management ...........................    2
    24 Parallel Query ..............................    1
    25 SQL Tracing Tuning ..........................   20
    26 Partitioning ................................    3
    27 Favorite Scripts ............................   32
   Enter Group Id or to search Enter [s SEARCH_STRING] :    


For further instructions please read:

   Generic SQLDIR Manual:	sqldir_generic.pdf
   Install demo on Sun OS:	sqldir_sun.htm
   Install demo on Win2K:	sqldir_win2000.htm
   Install SQLDIR on a shared management server

If you have any questions or issues post them here:

   SQLDIR Support

   If you have an older DEMO version of SQLDIR you no longer need to purchase 
   full version -- it's FREE:

     - full Compiled SQLDIR versions for Solaris, Linux and 
       Windows are available below.

     - SQLDIR source code is available below.

Well as promised here's the source code for SQLDIR:


/*
# +=============================================+
# |                  SQLDIR v3.1                |
# +---------------------------------------------+
# |       Copyright (c) 1998 DBAToolZ.com       |
# |              All rights reserved.           |
# +=============================================+
# AUTHOR:
#	Vitaliy Mogilevskiy VMOGILEV
#	DBAToolZ.com
#
# PURPOSE:
#	Parse SQL scripts for description and groups
#	create SQL*Plus ddl and load scripts to 
#	populate DBAToolZ SQL script library for
#	use with interactive menu
#
# USAGE:
#	sqldir [-d] <sql_directory_file> <cfg_groups_file>
#
# HISTORY:
#	01-OCT-1998	VMOGILEV	Created prototype using SQL*Loader
#					and windows batch scripts
#	27-OCT-2001	VMOGILEV	Re-written prototype using C which
#					allowed automatic parsing of script
#					descriptions and group assigments
#
*/

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <ctype.h>


#define TXT_STR 	"<SQLDIR_TXT>"	/* start of SQLDIR TEXT */
#define TXT_END 	"</SQLDIR_TXT>"	/* end of SQLDIR TEXT */
#define GRP_STR 	"<SQLDIR_GRP>"	/* start of SQLDIR GROUP */
#define GRP_END 	"</SQLDIR_GRP>"	/* end of SQLDIR GROUP */
#define TXT_MAX_LEN	256			/* maximum TXT line size */

/* parse string constants */
#define FIRST_LINE	1	/* print current and print next */
#define MIDDLE_LINE	2	/* print current and print next this is for the second pass ...*/
#define LAST_LINE	3	/* print current only */
#define NEXT_LINE	4	/* print next don't print current */
#define NO_LINE		0	/* don't print at all */

int debug = 0;

FILE *fdir;
FILE *fcfg;
FILE *fddl;
FILE *fgrp;
FILE *ftxt;
FILE *ftrc;

char txt_file[] = "sqldir_txt.sql\0";
char grp_file[] = "sqldir_grp.sql\0";
char ddl_file[] = "sqldir_ddl.sql\0";
char trc_file[] = "sqldir_ddl.trc\0";

char trace_stack[512];

void fileread(FILE *fin, char fin_name[], FILE *ftxt, FILE *fgrp);
void writeout(FILE *, char *);
void copytill(char to[], char *, int till);
void readdir(FILE *fdir, FILE *ftxt, FILE *fgrp);
void createddl(FILE *fddl);
void parsecfg(FILE *fcfg, FILE *fgrp);
void parsegrp(FILE *fgrp, char script_name[], char grp[]);
void replace(char txt[], int rwhat, char rwith[]);
void dump_trace(void);
int  getline(FILE *fdir, char fname[], int lim);
int  parse_str(char new_line[], char *line, char ss[], char es[], int printcurrent);


void main(int argc, char *argv[])
{
	while (--argc > 0 && (*++argv)[0] == '-')
		if ((strstr("-d", *argv) != NULL)) {
			debug = 1;
		}

	/* accept atleast two parameters after the debug switch*/
	if (argc != 2) {
		fprintf(stderr, "USAGE: sqldir [-d] <sql_directory_file> <cfg_groups_file>\n");
		exit(1);
	}

	/* open directory file for reading */
	if ((fdir = fopen(*argv, "r")) == NULL) {
		fprintf(stderr, "CAN'T OPEN DIRECTORY LIST FILE: %s for reading\n", *argv);
		exit(1);
	}

	/* open config file for reading */
	if ((fcfg = fopen(*++argv, "r")) == NULL) {
		fprintf(stderr, "CAN'T OPEN GROUP CFG FILE: %s for reading\n", *++argv);
		exit(1);
	}

	/* open sqldir files for writing */
	if ((fddl = fopen(ddl_file, "w")) == NULL) {
		fprintf(stderr, "CAN'T CREATE DDL FILE: %s for writing\n", ddl_file);
		exit(1);
	}
	if ((ftxt = fopen(txt_file, "w")) == NULL) {
		fprintf(stderr, "CAN'T CREATE TXT FILE: %s for writing\n", txt_file);
		exit(1);
	}
	if ((fgrp = fopen(grp_file, "w")) == NULL) {
		fprintf(stderr, "CAN'T CREATE GRP FILE: %s for writing\n", grp_file);
		exit(1);
	}
	if (debug && (ftrc = fopen(trc_file, "w")) == NULL) {
		fprintf(stderr, "CAN'T CREATE TRC FILE: %s for writing\n", trc_file);
		exit(1);
	} else if (debug)
		printf("Debug mode on trace file: %s \n", trc_file);
	
	printf("+=============================================+\n\0");
	printf("|                  SQLDIR v3.1                |\n\0");
	printf("+---------------------------------------------+\n\0");
	printf("|       Copyright (c) 1998 DBAToolZ.com       |\n\0");
	printf("|              All rights reserved.           |\n\0");
	printf("+=============================================+\n\0");
	
	createddl(fddl);
	parsecfg(fcfg, fgrp);
	readdir(fdir, ftxt, fgrp);
	fclose(fdir);
	fclose(fcfg);
	fclose(fddl);
	fclose(ftxt);
	fclose(fgrp);
	if (debug)
		fclose(ftrc);
	exit(0);
}

void fileread(FILE *fin, char fin_name[], FILE *ftxt, FILE *fgrp)
{
	char org_line[TXT_MAX_LEN];
	char new_line[TXT_MAX_LEN];

	char *file_line;

	int counter = 1;

	int printing_grp = NO_LINE;
	int printing_txt = NO_LINE;
	
	int single_quote = '\'';
	char single_quote_rep[] = "`\0";
	
	int semi_colon = ';';
	char semi_colon_rep[] = ",\0";

	file_line = org_line;
	
        writeout(ftxt, "INSERT INTO sqldir_scripts \n\0");
	writeout(ftxt, "VALUES ( sqldir_scripts_S.NEXTVAL,\n'");
        writeout(ftxt, fin_name);
        writeout(ftxt, "' ,\n'");

	/*
	|| Every script has to be assigned to ALL group
	|| to make sure the menu works with scripts
	|| that dont have any group assigments 
	*/
	writeout(fgrp, "-- START SCRIPT: \0");
	writeout(fgrp, fin_name);
	writeout(fgrp, "\n\0");
	writeout(fgrp, "INSERT INTO sqldir_mapping \n\0");
	writeout(fgrp, "SELECT script_id, -1 \n\0");
	writeout(fgrp, "FROM sqldir_scripts \n\0");
	writeout(fgrp, "WHERE script_name = '\0");
	writeout(fgrp, fin_name);
	writeout(fgrp, "'; \n\n\0");

	printf(" ... SCANNING %s", fin_name);
	
	while (fgets(file_line, TXT_MAX_LEN, fin) != NULL) {

		sprintf(trace_stack, "\n%s[%d]\t", fin_name, counter ++);
		dump_trace();

		printing_grp = parse_str(
		   new_line
		,  file_line
		,  GRP_STR
		,  GRP_END
		,  printing_grp);
		if (printing_grp == FIRST_LINE ||
		    printing_grp == LAST_LINE ||
		    printing_grp == MIDDLE_LINE)
			parsegrp(fgrp, fin_name, new_line);

		printing_txt = parse_str(
		   new_line
		,  file_line
		,  TXT_STR
		,  TXT_END
		,  printing_txt);
		if (printing_txt == FIRST_LINE ||
		    printing_txt == LAST_LINE ||
		    printing_txt == MIDDLE_LINE) {
		    	replace(new_line, single_quote, single_quote_rep);
		    	replace(new_line, semi_colon, semi_colon_rep);
			writeout(ftxt, new_line);
		}
	}

        writeout(ftxt, "'); \n\n\0");

	writeout(fgrp, "-- END SCRIPT: \0");
	writeout(fgrp, fin_name);
	writeout(fgrp, "\n\0");
	
	printf(" (%d) lines\n", counter);
}

/*
||	Function:
||		parse_str()
||
||	Purpose:
||		Parses *line using START|END pattern searching
||
||	Returns:
||		FIRST_LINE	1	- print current and print next
||		MIDDLE_LINE	2	- print current and print next this is for the second pass 
||		LAST_LINE	3	- print current only
||		NEXT_LINE	4	- print next don't print current
||		NO_LINE		0	- don't print at all
*/
int parse_str(char new_line[],
              char *line,
              char ss[],
              char es[],
              int printing)
{
	char *sp;				/* start position pointer */
	char *ep;				/* end position pointer */
	int i;

	sprintf(trace_stack, ".. %s%s .. ",ss,es);
	dump_trace();
	/*
	|| check in we are at the
	|| line that starts START STRING
	*/
	if (strstr(line, ss) != NULL &&
	    strstr(line, es) == NULL)
	{
		sprintf(trace_stack, ".. if#1 .. ");
		dump_trace();
	   	/* set pointer past the ss */
		sp = strstr(line, ss) + strlen(ss);
		/* print only if there's anything on the line */
		if (*sp != '\n') {
			/* write out parsed line */
			/* writeout(ofp, strcpy(new_line, sp)); */
			strcpy(new_line, sp);
			return FIRST_LINE;
		}
		return NEXT_LINE;
	}

	/*
	|| check if we are at the
	|| line that ends END STRING
	*/
	if (strstr(line, ss) == NULL &&
	    strstr(line, es) != NULL)
	{
		sprintf(trace_stack, ".. if#2 .. ");
		dump_trace();
		ep = strstr(line, es);
		/* print only if STRING END doesn't start the line */
		if ((strlen(line)-strlen(ep)) != 0) {
			/* copy parsed line, stop before STRING END */
			copytill(new_line, line, strlen(line)-strlen(ep));
			/*strncpy(new_line, line, strlen(line)-strlen(ep));*/
			/* return line with newline appended since I cut it before the /n*/
			/*writeout(ofp, strcat(new_line,newline));*/
			/*writeout(ofp, new_line);*/
			return LAST_LINE;
		}
		return NO_LINE;
	}

	/*
	|| check if we are at the
	|| line that starts and ends START/END STRING
	*/
	if (strstr(line, ss) != NULL &&
	    strstr(line, es) != NULL)
	{
		sprintf(trace_stack, ".. if#3 .. ");
		dump_trace();
	   	/* set start pointer past the ss */
		sp = strstr(line, ss) + strlen(ss);
		/* set end pointer */
		ep = strstr(line, es);
		/* print only if STRING END doesn't start the line */
		if ((strlen(sp)-strlen(ep)) != 0) {
			/* copy parsed line, stop before STRING END */
			copytill(new_line, sp, strlen(sp)-strlen(ep));
			/*strncpy(new_line, sp, strlen(sp)-strlen(ep));*/
			/* print line with newline appended */
			/*writeout(ofp, new_line);*/
			return LAST_LINE;
		}
		return NO_LINE;
	}

	/*
	|| if I got here check check if I am in
	|| the middle of the printing
	*/
	if (printing != NO_LINE && printing != LAST_LINE) {
		sprintf(trace_stack, ".. if .. ");
		dump_trace();
		/*writeout(ofp, line);*/
		strcpy(new_line, line);
		return MIDDLE_LINE;
	} else {
		sprintf(trace_stack, ".. else .. ");
		dump_trace();
		return NO_LINE;
	}
}


void writeout(FILE *ofp, char *line)
{
	static int counter = 1;

	sprintf(trace_stack, "(%d)\t", counter ++);
	dump_trace();
	sprintf(trace_stack, line);
	dump_trace();
	fputs(line, ofp);
}

void copytill(char to[], char *from, int till)
{
	int i, j;

	for (i=0; i < till; ++i) {
		sprintf(trace_stack,"LINE[%d]=(%c)\n",i,*(from + i));
		dump_trace();
		to[i] = *(from + i);
	}
	if (isspace(to[i-1])) {
		sprintf(trace_stack,"HI THERE");
		dump_trace();
		to[i-1] = '\0';
	} else
		to[i] = '\0';
}

void parsecfg(FILE *fcfg, FILE *fgrp)
{
	char grpline[TXT_MAX_LEN];
	char *grpcode;
	char *grpdesc;
	
	/* Insert "ALL" Group so that all scripts
	   are assigned to at least one group for the menu to work */
	writeout(fgrp, "INSERT INTO sqldir_groups VALUES ( -1, 'ALL', 'TOTAL Number Of Scripts:' );\n\0");
	
	while (getline(fcfg, grpline, TXT_MAX_LEN) > 0) {
		grpcode = strtok(grpline, ",");
		grpdesc = strtok(NULL, ",");
		if (grpcode && grpdesc) {
			writeout(fgrp, "INSERT INTO sqldir_groups VALUES ( sqldir_groups_S.NEXTVAL, UPPER('\0");
			writeout(fgrp, grpcode);
			writeout(fgrp, "'), '\0");
			writeout(fgrp, grpdesc);
			writeout(fgrp, "' );\n\0");
		}
	}
}

void parsegrp(FILE *fgrp, char script_name[], char grp[])
{
	char *grpitem;
	
	grpitem = strtok(grp, " ");
	if (grpitem) {
		writeout(fgrp, "INSERT INTO sqldir_mapping \n\0");
		writeout(fgrp, "SELECT s.script_id, g.grp_id \n\0");
		writeout(fgrp, "FROM sqldir_scripts s \n\0");
		writeout(fgrp, ",    sqldir_groups  g \n\0");
		writeout(fgrp, "WHERE s.script_name = '\0");
		writeout(fgrp, script_name);
		writeout(fgrp, "' \n\0");
		writeout(fgrp, "AND g.grp_name = UPPER('\0");
		writeout(fgrp, grpitem);
		writeout(fgrp, "'); \n\n\0");
		
		while ( (grpitem = strtok(NULL, " ")) != NULL ) {
			writeout(fgrp, "INSERT INTO sqldir_mapping \n\0");
			writeout(fgrp, "SELECT s.script_id, g.grp_id \n\0");
			writeout(fgrp, "FROM sqldir_scripts s \n\0");
			writeout(fgrp, ",    sqldir_groups  g \n\0");
			writeout(fgrp, "WHERE s.script_name = '\0");
			writeout(fgrp, script_name);
			writeout(fgrp, "' \n\0");
			writeout(fgrp, "AND g.grp_name = UPPER('\0");
			writeout(fgrp, grpitem);
			writeout(fgrp, "'); \n\n\0");
		}
	}
}

void replace(char txt[], int rwhat, char rwith[])
{
	int i = 0;
	int j;
	char new_txt[TXT_MAX_LEN];
	
	while (txt[i] != '\0') {
		if (txt[i] == rwhat) {
			j = 0;
			sprintf(trace_stack, "********* REPLACING: %c WITH %s", rwhat, rwith);
			dump_trace();
			while (rwith[j] != '\0') {
				new_txt[i+j] = rwith[j];
				++j;
			}
		} else
			new_txt[i] = txt[i];
		++i;
	}
	new_txt[i] = '\0';
	strcpy(txt, new_txt);
}

void readdir(FILE *fdir, FILE *ftxt, FILE *fgrp)
{
	FILE *fin;
	char file_name[TXT_MAX_LEN];
	char *script_name;
	int winfterm = '\\';
	int unixfterm = '/';
	int counter = 0;
	
	while (getline(fdir, file_name, TXT_MAX_LEN) > 0) {
		if ((fin = fopen(file_name, "r")) == NULL) {
			fprintf(stderr, "CAN'T OPEN FILE: %s for reading", file_name);
			exit(1);
		}
		script_name = strrchr(file_name, winfterm);
		if (!script_name)
			script_name = strrchr(file_name, unixfterm);
		if (script_name)
			script_name = script_name + sizeof(char);
		else
			script_name = file_name;
		fileread(fin, script_name, ftxt, fgrp);
		fclose(fin);
		counter++;
	}
	printf("\nTOTAL Files:\t%d\n", counter);
	printf("To load DBAToolZ script library run %s from SQL*Plus\n\0", ddl_file);
	printf("To access DBAToolZ script library run x_dir.sql from SQL*Plus\n\n\0");
	printf("For more information and software updates visit DBAToolZ.com\n\0");
}

int getline(FILE *fdir, char fname[], int lim)
{
	int c, i;
	
	for (i=0; i<lim-1 && (c=fgetc(fdir))!=EOF && c!='\n'; ++i)
		fname[i] = c;
	fname[i] = '\0';
	return i;
}

void dump_trace(void)
{
	if (debug)
		fputs(trace_stack, ftrc);
}

void createddl(FILE *fddl)
{
	writeout(fddl, "spool sqldir_ddl.log \n\0");
	writeout(fddl, "set echo on \n\0");
	writeout(fddl, "set term off \n\0");

	writeout(fddl, "drop sequence sqldir_groups_S;\n\0");
	writeout(fddl, "create sequence sqldir_groups_S;\n\0");
	writeout(fddl, "drop table sqldir_groups;\n\0");
	writeout(fddl, "create table sqldir_groups(\n\0");
	writeout(fddl, "   grp_id number not null,\n\0");
	writeout(fddl, "   grp_name varchar2(25) not null,\n\0");
	writeout(fddl, "   grp_desc varchar2(150));\n\0");
	
	writeout(fddl, "drop sequence sqldir_scripts_S;\n\0");
	writeout(fddl, "create sequence sqldir_scripts_S;\n\0");
	writeout(fddl, "drop table sqldir_scripts;\n\0");
	writeout(fddl, "create table sqldir_scripts(\n\0");
	writeout(fddl, "   script_id number not null,\n\0");
	writeout(fddl, "   script_name varchar2(150) not null,\n\0");
	writeout(fddl, "   script_desc varchar2(2000) );\n\0");

	writeout(fddl, "drop table sqldir_mapping;\n\0");
	writeout(fddl, "create table sqldir_mapping(\n\0");
	writeout(fddl, "   script_id number not null,\n\0");
	writeout(fddl, "   grp_id number not null);\n\0");
	writeout(fddl, "create unique index sqldir_mapping_u01\n\0");
	writeout(fddl, "on sqldir_mapping(script_id,grp_id);\n\0");  
	                                 
	writeout(fddl, "@sqldir_txt.sql \n\0");
	writeout(fddl, "@sqldir_grp.sql \n\0");
	writeout(fddl, "spool off \n\0");
	writeout(fddl, "exit; \n\0");
}

Attached here are compiled binaries for Solaris, Linux and Windows.

On any other platform just compile above source code using C compiler:

   gcc -o <output_file> <source_file>

EXAMPLE (on RH Linux):

stechkin.LMON-> gcc -o NEW_sqldir FULL_sqldir.c
FULL_sqldir.c: In function `main':
FULL_sqldir.c:80: warning: return type of `main' is not `int'

stechkin.LMON-> file FULL_sqldir.c
FULL_sqldir.c: ASCII C program text

stechkin.LMON-> file NEW_sqldir
NEW_sqldir: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), 
dynamically linked (uses shared libs), not stripped

stechkin.LMON-> NEW_sqldir
USAGE: sqldir [-d] <sql_directory_file> <cfg_groups_file>

.