subject:"\[R\] get data from pl sql block"

[R] get data from pl sql block

2015-09-07 Thread 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@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@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.d...@tecnocom.es
http://www.tecnocom.es

__
R-help@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.

Re: [R] get data from pl sql block

2015-09-07 Thread Jeff Newmiller

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 NewmillerThe .   .  Go Live...
DCN:Basics: ##.#.   ##.#.  Live Go...
  Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/BatteriesO.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" 
 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@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@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.d...@tecnocom.es
>http://www.tecnocom.es
>
>__
>R-help@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@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.

Re: [R] get data from pl sql block

2015-09-07 Thread 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.d...@tecnocom.es
http://www.tecnocom.es


De: Jeff Newmiller [jdnew...@dcn.davis.ca.us]
Enviado: lunes, 7 de septiembre de 2015 16:53
Para: Diaz Garcia, Luis Carlos; R-help@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 NewmillerThe .   .  Go Live...
DCN:<jdnew...@dcn.davis.ca.us>Basics: ##.#.   ##.#.  Live Go...
  Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/BatteriesO.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.d...@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@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@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.d...@tecnocom.es
>http://www.tecnocom.es
>
>__
>R-help@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@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] get data from pl sql block

Re: [R] get data from pl sql block

Re: [R] get data from pl sql block

3 matches

Site Navigation

Mail list logo

Footer information