From SQL to RESTful API — in Minutes with Oracle ORDS

Published on 05 Aug 2025 by CHATURVEDINIDHI26@GMAIL.COM

🚀 Hotel Search API with Oracle ORDS

Here’s how I built a Hotel Search API using just SQL and ORDS:

  
BEGIN  
   ORDS.DEFINE\_MODULE(  
       p\_module\_name    = hotel\_apis,  
       p\_base\_path      = /hotel/,  
       p\_status         = PUBLISHED  
   );

   ORDS.DEFINE\_TEMPLATE(  
       p\_module\_name = hotel\_apis,  
       p\_pattern     = search/  
   );

   ORDS.DEFINE\_HANDLER(  
       p\_module\_name = hotel\_apis,  
       p\_pattern     = search/,  
       p\_method      = GET,  
       p\_source\_type = ORDS.source\_type\_query,  
       p\_source      = q'\[  
           SELECT h.CODE, h.NAME, h.CITY\_CODE, h.STAR,  
                  f.DESCRIPTION AS FACILITY\_DESCRIPTION,  
                  o.DISCOUNT\_CODE, o.DISCOUNT\_VALUE  
           FROM HOTEL h  
           LEFT JOIN HOTEL\_FACILITY f   
                  ON f.PARENT\_FACILITY\_CODE = h.CODE  
           LEFT JOIN VENDOR\_CONTRACT\_OFFERS o  
                  ON o.CONTRACT\_ID = h.CODE  
           WHERE (:p\_city\_code IS NULL OR h.CITY\_CODE = :p\_city\_code)  
             AND h.ACTIVE\_FLG = 'Y'  
       \]'  
   );  
   COMMIT;  
END;  
/  

💡 What this does:

• 📦 Creates a /hotel/search endpoint
• 🔍 Filters results with query params like p\_city\_code, p\_star
• 📊 Joins hotel, facility, and offer tables in one go
• 🌐 Directly testable in Postman

✅ The best part?

• No separate backend layer — just SQL → API
• Fully secure with ORDS authentication options
• Returns JSON out-of-the-box

🔜 Next step: returning nested JSON so each hotel includes an array of its facilities & offers, just like industry-standard booking APIs.

#Oracle #ORDS #APEX #API #HotelBooking #SQL #PLSQL #Postman #RestAPI