thr3ads.net - R help - [R] get data from pl sql block [Sep 2015]

If this information is useful, please help other people find it:
Share via:

Diaz Garcia, Luis Carlos

2015-Sep-07 09:30 UTC

[R] get data from pl sql block

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

Jeff Newmiller

2015-Sep-07 14:53 UTC

head link

[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.

Diaz Garcia, Luis Carlos

2015-Sep-07 15:03 UTC

head link

[R] get data from pl sql block

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.

R help - Sep 2015 - get data from pl sql block

[R] get data from pl sql block

[R] get data from pl sql block

[R] get data from pl sql block