[R] get data from pl sql block

[Diaz Garcia, Luis Carlos]

Hello all

last week I create a script with R
This script connect to Oracle database and retreave some data.

This is a sample of the code

dbName <- sqlQuery(con, "SELECT instance_name, host_name from v$instance",errors=FALSE)
title (main = paste0("Mapa de los dblinks del entorno: ", dbName$INSTANCE_NAME, "_", 
       dbName$HOST_NAME),sub="Luis Diaz - Emergencies & improvments")

This code works fine, but now I need to get data from a pl sql block, such like this:

	DECLARE
	v_result number;

	BEGIN
	EXECUTE IMMEDIATE 'select count(1) from dual at db_link'';
	v_result:=0; 
	DBMS_OUTPUT.PUT_LINE(v_result);
	
	EXCEPTION 
	WHEN OTHERS THEN 
	v_result:=1; 
	DBMS_OUTPUT.PUT_LINE(v_result);

	END; 
/

This code return 0 if the db link works and 1 if not...
I try tis way:

isDead <- sqlQuery(con,"
	set serveroutput on
	DECLARE
	v_result number;

	BEGIN
	EXECUTE IMMEDIATE 'select count(1) from dual at DB_LINK';
	v_result:=0; 
	EXCEPTION 
	WHEN OTHERS THEN 
	v_result:=1; 
END; 
/
", errors=FALSE)
print(isDead)

The result of this isDead variable is always : -1
I expect a value 0 or 1 depending of the db link result.
Do you have any idea ?

Thanks a los


Tecnocom
Luis Diaz
Arquitecto Bases de Datos Oracle
Email: luis.diaz at tecnocom.es
http://www.tecnocom.es