Few days back I posted couple of articles related to Oracle APEX & realized that my test database doesn’t have sample schemas other than HR. So, I decided to download and setup them. Once after extracting the archives from github for 19c, the installation thrown many errors about SQL files not being found in the path “__SUB__CWD__“

So I opened couple of the files and found entries like this

CONNECT system/&&password_system@&&connect_string

SET SHOWMODE OFF

@__SUB__CWD__/human_resources/hr_main.sql &&password_hr &&default_ts &&temp_ts &&password_sys &&logfile_dir &&connect_string

CONNECT system/&&password_system@&&connect_string
SET SHOWMODE OFF

@__SUB__CWD__/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts &&password_hr &&password_sys __SUB__CWD__/order_entry/ &&logfile_dir &vrs &&connect_string

CONNECT system/&&password_system@&&connect_string

SET SHOWMODE OFF

@__SUB__CWD__/product_media/pm_main.sql &&password_pm &&default_ts &&temp_ts &&password_oe &&password_sys __SUB__CWD__/product_media/ &&logfile_dir __SUB__CWD__/product_media/ &&connect_string

A quick search landed me on oracle-base article about installing samples schemas. This article explains how to replace “__SUB__CHD__” string with the present working directory (pwd) value. Unfortunately, Windows doesn’t have a built-in tool for such & I decided to make one for myself. “winreplace” is a Windows executable developed using C# (C Sharp). I’ve used Visual Studio 2019 community edition for developing this application. You may use later versions and higher .Net framework versions as it suits your requirements. This utility can iterate through files and sub-folders within a directory and replace strings. I avoided command line arguments as maintaining the position and spaces could be challenging at times. All arguments expected by the application are accepted through individual prompts and they must be entered without quotes. Below code was last modified on 6th January 2024. The latest build insures that ONLY the files modified.

using System;

using System.Collections.Generic;

using System.IO;

using System.Text.RegularExpressions;



namespace winreplace

{

    class Program

    {

        static void Main(string[] args)

        {

            //Console.WriteLine("Hello World!");

            //https://learn.microsoft.com/en-us/dotnet/api/system.io.directory.enumeratefiles?view=net-8.0&redirectedfrom=MSDN#overloads

            //https://stackoverflow.com/questions/59734757/save-with-different-name-in-c-sharp-form-application



            string sourceDirectory = string.Empty;

            string fileType = string.Empty;

            string searchString = string.Empty;

            string replaceString = string.Empty;

            string backupFiles = string.Empty;



            //Accept user inputs

            //We'll not do extensive validations for this release. User have to insure the validity of information passed as inputs

            Console.WriteLine(@"winreplace by simpleoracle.com, Version 1.0, December 31, 2023");

            Console.WriteLine(@"This utility could be used for replacing specific strings recursively within a given file path. This utility creates a "".bak"" file for each file it iterates through regardless whether a match found or not. Fix expected.");

            Console.WriteLine(@"This utility is provided as it is and you are adviced to use the same with caution. Although the software creates a backup for every file it accesses, making additional backups for important files is totally user's responsibility. Simpleoracle.com will not able to fix/repair or recover your files under any circumstances once after they are modified.");

            Console.WriteLine("\n");



            Console.Write(@"Files Path(eg: D:\My Text Files are here): ");

            sourceDirectory = Console.ReadLine();



            Console.Write(@"File type(eg: *.txt): ");

            fileType = Console.ReadLine();



            Console.Write(@"String to search for(eg: domain name): ");

            searchString = Console.ReadLine();



            Console.Write(@"String to replace with(eg: simpleoracle.com): ");

            replaceString = Console.ReadLine();



            try

            {

                var txtFiles = Directory.EnumerateFiles(sourceDirectory, fileType, SearchOption.AllDirectories);

                foreach (string currentFile in txtFiles)

                {

                    string fileName = currentFile.Substring(sourceDirectory.Length + 1);

                    //Directory.Move(currentFile, Path.Combine(archiveDirectory, fileName));

                    //Comment the below line if you don't want to create backup files.

                    ReplaceInFile(currentFile, searchString, replaceString);

                    //Console.WriteLine(fileName);

                }

            }

            catch (Exception e)

            {

                Console.WriteLine(e.Message);

            }

        }



        static public void ReplaceInFile(string filePath, string searchText, string replaceText)

        {

            //We will get the content from the plain text type file.

            StreamReader reader = new StreamReader(filePath);

            string content = reader.ReadToEnd();

            reader.Close();

            //Now we will check whether the content read from the file

            //has matches for the string that we are searching for.

            MatchCollection matches = Regex.Matches(content, searchText);

            int count = matches.Count;

            //We will attempt to replace the strings only if matches exist

            //Each file that is modified will be backed up with an extension ".bak"

            if (count > 0)

            {

                try

                {

                    Console.WriteLine("{0} has {1} Matches. File will be backed up", filePath, count);

                    File.Copy(filePath, filePath + ".bak", true);

                    content = Regex.Replace(content, searchText, replaceText);

                    StreamWriter writer = new StreamWriter(filePath);

                    writer.Write(content);

                    writer.Close();

                }

                catch (Exception e)

                {

                    Console.WriteLine("There was an error. Please check whether the file is read only.");

                }

            }

        }

    }

}

You can download a published version from this link. Extract the contents to a single folder, eg: D:\winreplace. This application can work ONLY with plain text files like .txt, .sql, .log and files of same nature. This software cannot be used for Microsoft documents/PDF or documents of such complex architecture. Read more about Microsoft documents/PDF here

Now add this path to PATH environment variable, that will help you to access the executable from any command prompt.

You can call the executable as demonstrated in the image below.

I was pretty happy, being able to fix the sql, dat files as mentioned in the oracle-base article using “my own” utility.

Please note, there are hardly any error handling included with the application. Please ensure to make backups for your important files prior using this utility. I’ve added a no liability clause with the software (not visible with the images above as it was added later)

You must run the executable each time for different types of files. If the application fails, default error messages will be displayed, giving you a hint about what went wrong.

That’s all folks.