[R] get data from pl sql block

[Diaz Garcia, Luis Carlos]

Hi Jeff
yes I use RODBC, and I think you're write... Only "select" statments... I have a solution I think.
When I'll be sure, I'll share the info into the list.

Thanks a lot !


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

________________________________________
De: Jeff Newmiller [jdnewmil at dcn.davis.CA.us]
Enviado: lunes, 7 de septiembre de 2015 16:53
Para: Diaz Garcia, Luis Carlos; R-help at r-project.org
Asunto: Re: [R] get data from pl sql block

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.