With clause is not suported ?

[kontaa]

Server is oracle12. Client oci is also ver12 in windows 7. Sqlplus* can use ‘with clause’ in select statement. But ruby script with ruby-oci8 happen error. Error message is like a ‘first statement is not Select’.

I know with clause was supported from Oracle 9i. Ruby-oci8 supports until Oracle8 api and with clause is not supported ?

Please tell me.

[kubo]

Could you post sample code to reproduce the issue?

The following 'with clause' works for me.

require 'oci8'

conn = OCI8.new('scott/tiger')

stmt = <<EOS
with emp_with_comm as (select * from emp where comm is not null)
select * from emp_with_comm
EOS

conn.exec(stmt) do |row|
  puts row.join(', ')
end

[kontaa]

Thanks your quick response.

I’ll post my sample code from my work office next Monday.

[kontaa]

Sorry, this time, it was my stupid mistake. Like a below, last statement should originally be SELECT wa sql.

stmt = <<EOS
with emp_with_comm as
(select * from emp where comm is not null)
sql * from emp_with_comm
EOS

Now, it works fine !! thank you.