[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@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
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] Rodbc retrieve data
Hi every one first I would like to introduce myself, as I'm new here. I'm Luis from Barcelona, I'm Oracle dba and I need to create some nice graphs. So, I was looking for a solution and I saw R... I think it's a good tool to make the task I need. So here is the task: I need to get all the dblink from one database and draw the result of the query. The dblink will have the origin database name, the destination, the name of the link the type too. So I look into the R doc and I saw the way to get the data from my database: library(RODBC) con - odbcConnect(DPL03, uid=myuser, pwd=mypass,believeNRows=FALSE ) dbName - sqlQuery(con, SELECT instance_name from v$instance,errors=FALSE) Now I have dbName with one value, the name of my instance, but I don't know how to insert this data into this: plot.new() title (main =Map of the dbLinks of the database,sub=Luis Diaz - Emergencies improvments) As you see, I create a screen to plot where I'll draw shapes and lines but the first issue is to insert here the name of my instance, but I can't. I try to concatenate like this: title (main =Mapa de los dblinks del entorno: + dbName ,sub=Luis Diaz - Emergencies improvments) But I have an error, and if I use print(dbName) the value is printed but outside of the plot.new() screen. Some one can help ? Thanks in advance ! Cheers __ 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.
