# Oracle HR on Rails

**URL:** <https://www.ruby-forum.com/t/oracle-hr-on-rails/97328>\
**Category:** Rails\
**Created:** [July 6, 2006, 11:57am UTC](https://www.ruby-forum.com/t/oracle-hr-on-rails/97328 "2006-07-06T11:57:11Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ghulette](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/51bf81/32.png) [@ghulette](https://www.ruby-forum.com/u/ghulette)\
**Post date:** [July 6, 2006, 11:57am UTC](https://www.ruby-forum.com/t/oracle-hr-on-rails/97328/1 "2006-07-06T11:57:11Z")

</div>

Interesting read…apologies if it has been posted already.

[http://www.oracle.com/technology/pub/articles/saternos-rails.html](http://www.oracle.com/technology/pub/articles/saternos-rails.html)

---

<div class="post-metadata">

**Author:** ![ghulette](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/51bf81/32.png) [@ghulette](https://www.ruby-forum.com/u/ghulette)\
**Post date:** [July 6, 2006, 12:09pm UTC](https://www.ruby-forum.com/t/oracle-hr-on-rails/97328/2 "2006-07-06T12:09:53Z")

</div>

> Interesting read…apologies if it has been posted already.

Yes

Oracle staff seem to ignore some of the jewels of Ror like act\_as\_tree :

## Quote

The query to construct the corporate hierarchy is as follows:

SELECT tree._,  
LEAD (hierarchy\_level) OVER (ORDER BY seq) next\_level  
FROM (  
SELECT CONNECT\_BY\_ROOT last\_name top\_node\_name,  
(last\_name || ‘, ’ || first\_name ||’ (’||job\_title||’)’)  
employee\_name,  
emp.id employee\_id,  
SYS\_CONNECT\_BY\_PATH (last\_name, ‘-\>’) node\_path,  
LEVEL hierarchy\_level,  
ROWNUM seq  
FROM(  
SELECT e._, j.job\_title  
FROM employees e, jobs j  
WHERE e.job\_id = j.id  
) emp  
START WITH emp.job\_title= ‘President’  
CONNECT BY PRIOR emp.id = manager\_id  
ORDER SIBLINGS BY emp.id  
) tree  
ORDER BY seq

---

<div class="post-metadata">

**Author:** ![ghulette](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/51bf81/32.png) [@ghulette](https://www.ruby-forum.com/u/ghulette)\
**Post date:** [July 6, 2006, 1:52pm UTC](https://www.ruby-forum.com/t/oracle-hr-on-rails/97328/3 "2006-07-06T13:52:15Z")

</div>

I expect nothing less from Oracle… lots of proprietary SQL, poor  
documentation, and lots of product plugs.

🙂

---

<div class="post-metadata">

**Author:** ![ghulette](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/51bf81/32.png) [@ghulette](https://www.ruby-forum.com/u/ghulette)\
**Post date:** [July 6, 2006, 1:55pm UTC](https://www.ruby-forum.com/t/oracle-hr-on-rails/97328/4 "2006-07-06T13:55:25Z")

</div>

On 7/6/06, Mathieu C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> ```
> (last_name || ', ' || first_name ||' ('||job_title||')') employee_name,
> CONNECT BY PRIOR emp.id = manager_id
> ORDER SIBLINGS BY emp.id
> ) tree
> 
> ```
> 
> ORDER BY seq

Oracle has special syntax (CONNECT BY) for selecting tree-like data  
efficiently a single query, something I don’t think acts\_as\_tree  
doesn’t make use of (and I’m too lazy to check). An oracle only  
version of acts\_as\_tree could be made much more efficient than the  
generic database version.

Tom

---

<div class="post-metadata">

**Author:** ![ghulette](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/51bf81/32.png) [@ghulette](https://www.ruby-forum.com/u/ghulette)\
**Post date:** [July 6, 2006, 10:42pm UTC](https://www.ruby-forum.com/t/oracle-hr-on-rails/97328/5 "2006-07-06T22:42:18Z")

</div>

Tom W. wrote:

> Oracle has special syntax (CONNECT BY) for selecting tree-like data  
> efficiently a single query, something I don’t think acts\_as\_tree  
> doesn’t make use of (and I’m too lazy to check). An oracle only  
> version of acts\_as\_tree could be made much more efficient than the  
> generic database version.

Yep. In some cases the proprietary approach actually does make sense.  
Might as well get some value out of all that $$$.