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 Newmiller The ..... ..... Go Live... DCN:<jdnew...@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.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.
