🚀 Oracle to PostgreSQL SQL Converter

This project started as a real-world challenge in my company.
We had to migrate thousands of Oracle SQL queries (used across backend and frontend) to PostgreSQL.

At first, the plan was:

• Convert queries manually using online tools.
• Verify each query one by one.
• Repeat this for hundreds of folders and thousands of files.

👉 It was estimated to take 15+ days of full team effort, and it was messy, time-consuming, and frustrating.

---

💡 The Idea

Since I already had experience with automation & shell scripting, I thought:

"Why not automate the boring parts?"

I started exploring sed, awk, and regex replacements.
I also took help from AI tools to refine rules for replacing Oracle-specific syntax with PostgreSQL equivalents.

With this, I created a simple bash script that:

• Runs through all SQL files in a folder.
• Saves originals in Before_Conversion/.
• Generates PostgreSQL-compatible queries in After_Conversion/.
• Handles most common Oracle → PostgreSQL conversions automatically.

---

🏆 The Impact

What was initially a 15+ day job for the entire team turned into just 2–3 days:

• Day 1: Run the script across all files.
• Day 2–3: Team members split up to verify and run the converted queries.

This script was not 100% perfect (complex queries still needed tweaks), but it covered 80–90% of cases and saved us weeks of repetitive work.

---

⚡ Features

• Data Type Conversions

  • NUMBER → NUMERIC
  • VARCHAR2 → VARCHAR
  • DATE → TIMESTAMP(0)
  • CLOB/NCLOB → TEXT
  • BLOB → BYTEA

• Function Replacements

  • NVL() → COALESCE()
  • SYSDATE → NOW()
  • SYSTIMESTAMP → NOW()
  • SUBSTR() → SUBSTRING()
  • DECODE() → CASE WHEN ... END

• Join Syntax

  • Converts Oracle (+) outer joins into PostgreSQL LEFT JOIN.

• Safe Conversion

  • Keeps original queries in Before_Conversion/.
  • Writes updated queries in After_Conversion/.

---

🚀 Usage

1. Clone this repository:

   git clone https://github.com/ch-rahulmachra/Shell-Scripting-Project01.git
   

2. Make the script executable:

   chmod +x converter.sh
   

3. Run it:

   ./converter.sh

• Enter the path of the folder containing Oracle SQL files. • Converted queries will be saved in After_Conversion/.

---

📂 Example

Input (employees.sql):

SELECT NVL(SALARY, 0), SYSDATE FROM EMPLOYEES WHERE STATUS = 'A';

Output (employees.sql):

SELECT COALESCE(SALARY, 0), NOW() FROM EMPLOYEES WHERE STATUS = 'A';

---

⚠️ Limitations

•	Not all Oracle-specific features are covered.
•	Some complex queries may require manual fixes.
•	The script is intended as a helper tool, not a full Oracle → PostgreSQL converter.

---

🤝 Contributing

This project is based on real-world needs. If you have ideas for additional conversion rules or improvements, feel free to: • Open an issue • Submit a pull request • Share feedback

Together, we can make this tool more powerful for others facing the same challenge.

---

⭐ If this project saves you time, consider giving it a star on GitHub!