[R] get data from pl sql block

Jeff Newmiller jdnewmil at dcn.davis.CA.us
Mon Sep 7 16:53:57 CEST 2015


You will get better answers on the R-sig-db mailing list.


You don't say which package you are using, but I don't think RODBC supports non-SELECT statements.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

On September 7, 2015 2:30:36 AM PDT, "Diaz Garcia, Luis Carlos" <Luis.Diaz at tecnocom.es> wrote:
>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
>
>______________________________________________
>R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.



More information about the R-help mailing list