# plsql.rb: Call PL/SQL procedures and functions
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

puts 'Get version information from Oracle:'
versions = conn.exec("BEGIN DBMS_UTILITY.db_version(:ver, :comp); END;", 
			' '*50, ' '*50)
puts "Oracle DB Version: " + versions[0]
puts "Oracle DB Compatibility: " + versions[1]
puts

puts 'Call TO_CHAR function:'
bind_vars = conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 
			'ABCD', 123)
puts "TO_CHAR input: " + bind_vars[1].to_s
puts "TO_CHAR output: " + bind_vars[0]
puts

puts 'Get DBMS_OUTPUT:'
conn.exec("BEGIN DBMS_OUTPUT.ENABLE(NULL); END;")
conn.exec("BEGIN DBMS_OUTPUT.put_line('Hello world!'); END;")
conn.exec("BEGIN DBMS_OUTPUT.put_line('Can you see me?'); END;")

cursor = conn.parse("BEGIN DBMS_OUTPUT.get_line(:line, :status); END;")
cursor.bind_param(':line', nil, String, 255)
cursor.bind_param(':status',Fixnum)

while true
	cursor.exec
	break if cursor[':status'] == 1
	puts cursor[':line']
end

puts '-'*80