# Jdbc Oracle - only one row returned before closing

**URL:** <https://discuss.elastic.co/t/jdbc-oracle-only-one-row-returned-before-closing/92183>\
**Category:** Logstash\
**Created:** [July 7, 2017, 12:29am UTC](https://discuss.elastic.co/t/jdbc-oracle-only-one-row-returned-before-closing/92183 "2017-07-07T00:29:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![c95mbq](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@c95mbq](https://discuss.elastic.co/u/c95mbq)\
**Post date:** [July 7, 2017, 12:29am UTC](https://discuss.elastic.co/t/jdbc-oracle-only-one-row-returned-before-closing/92183/1 "2017-07-07T00:29:10Z")

</div>

Hi,

I'm an absolute noob so apologizing in advance.

I'm using version 5.4.0 of ELK and have a working Logstash configuration for indexing a csv file. This works ok but I wanted to try and query directly for data and ultimately setup a schedule using jdbc and Logstash. However, when I run the conf file, only one row/record seems to be returned before shutting down. A cut-down version of the conf file as per below:

```
input {

	jdbc {
		  jdbc_driver_library => "E:\Oracle\client64\product\12.1.0\client_1\jdbc\lib\ojdbc7.jar"
			   jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
			   jdbc_connection_string => "jdbc:oracle:thin:@fqn:port/servicename"
			   jdbc_user => "user"
			   jdbc_password => "password"
	   
			statement => "select c.id as ED_ID,to_char(c.updt_dt_tm, 'dd/mm/yyyy hh24:mi') as UPDT_DT_TM,regexp_substr(c.trigger2,'[^|]+', 1, 2) as RESUS_PATIENTS from cust_ed_esc c order by c.id"
	}
}
filter {
	date {
target => "@timestamp"
match => ["UPDT_DT_TM", 'dd/MM/yyyy HH:mm', "dd/mm/yyyy HH:mm"]
	}
	mutate {
		convert => ["ED_ID","integer"]
		convert => ["RESUS_PATIENTS","integer"]
	}
}

output {
	stdout { codec => rubydebug }
}

```

I can't see anything that stands out in the output either, looks like it fetches a single record:

`09:40:18.081 [[main]>worker10] DEBUG logstash.pipeline - output received`

```
 {
                        "ed_id" => 14.0,
                   "updt_dt_tm" => "05/05/2017 14:56",
               "resus_patients" => "2"
}

```

> 09:40:18.097 [Ruby-0-Thread-22: C:/logstash-5.4.0/logstash-core/lib/logstash/pipeline.rb:532] DEBUG logstash.pipeline - Pushing flush onto pipeline  
> 09:40:18.098 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x39d84788 sleep\>"}  
> 09:40:18.099 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x20ab08ea sleep\>"}  
> 09:40:18.099 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x5da8c9c2 sleep\>"}  
> 09:40:18.099 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x3392047e sleep\>"}  
> 09:40:18.100 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x37339df3 sleep\>"}  
> 09:40:18.101 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x6a94c55a sleep\>"}  
> 09:40:18.101 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x18cf4dd7 sleep\>"}  
> 09:40:18.102 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x290929d7 sleep\>"}  
> 09:40:18.103 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x1d472a0d sleep\>"}  
> 09:40:18.103 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x438978c9 sleep\>"}  
> 09:40:18.103 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x3fbacca9 sleep\>"}  
> 09:40:18.104 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0x312ac07c sleep\>"}  
> 09:40:18.104 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pushing shutdown {:thread=\>"#\<Thread:0xf16ee85 sleep\>"}  
> 09:40:18.241 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x20ab08ea  
> 09:40:18.241 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x5da8c9c2  
> 09:40:18.241 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3392047e  
> 09:40:18.242 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x37339df3  
> 09:40:18.242 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x6a94c55a  
> 09:40:18.242 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x18cf4dd7  
> 09:40:18.243 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x290929d7  
> 09:40:18.243 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3c976bfa  
> 09:40:18.243 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x6503161b  
> 09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x1d472a0d  
> 09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x438978c9  
> 09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3fbacca9  
> 09:40:18.244 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x312ac07c  
> 09:40:18.245 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0xf16ee85  
> 09:40:18.245 [[main]-pipeline-manager] DEBUG logstash.pipeline - Shutdown waiting for worker thread #Thread:0x3ca4bd16  
> 09:40:18.245 [[main]-pipeline-manager] DEBUG logstash.filters.date - closing {:plugin=\>"LogStash::Filters::Date"}  
> 09:40:18.246 [[main]-pipeline-manager] DEBUG logstash.filters.mutate - closing {:plugin=\>"LogStash::Filters::Mutate"}  
> 09:40:18.246 [[main]-pipeline-manager] DEBUG logstash.outputs.stdout - closing {:plugin=\>"LogStash::Outputs::Stdout"}  
> 09:40:18.247 [[main]-pipeline-manager] DEBUG logstash.pipeline - Pipeline main has been shutdown  
> 09:40:20.701 [LogStash::Runner] DEBUG logstash.instrument.periodicpoller.os - PeriodicPoller: Stopping  
> 09:40:20.702 [LogStash::Runner] DEBUG logstash.instrument.periodicpoller.jvm - PeriodicPoller: Stopping  
> 09:40:20.703 [LogStash::Runner] DEBUG logstash.instrument.periodicpoller.persistentqueue - PeriodicPoller: Stopping  
> 09:40:20.714 [LogStash::Runner] WARN logstash.agent - stopping pipeline {:id=\>"main"}  
> 09:40:20.715 [LogStash::Runner] DEBUG logstash.pipeline - Closing inputs  
> 09:40:20.716 [LogStash::Runner] DEBUG logstash.inputs.jdbc - stopping {:plugin=\>"LogStash::Inputs::Jdbc"}  
> 09:40:20.718 [LogStash::Runner] DEBUG logstash.pipeline - Closed inputs

Any help or pointers as to why only a single record is returned would be much appreciated - maybe that's not even the case and multiple records are returned but regardless if I send output to console or into Elastic, I only ever see one record.

Editing this post to add that it seems it is always the same record that is being returned. The query is on a table with a unique index on a field called ED\_ID and it's always the same record that's returned despite the query containing an order by clause on the ed\_id field and it's not the first record that's returned.

Thank you for your kind help  
/Magnus

---

<div class="post-metadata">

**Author:** ![c95mbq](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@c95mbq](https://discuss.elastic.co/u/c95mbq)\
**Post date:** [July 11, 2017, 10:40pm UTC](https://discuss.elastic.co/t/jdbc-oracle-only-one-row-returned-before-closing/92183/2 "2017-07-11T22:40:39Z")

</div>

Also posted this same question on SO with slightly altered config, still hoping someone will see something obviously wrong. [https://stackoverflow.com/questions/45002704/logstash-jdbc-connection-only-returns-one-row](https://stackoverflow.com/questions/45002704/logstash-jdbc-connection-only-returns-one-row)

I see the below output in the console, which probably explains why it's only returning one record but can't for the life of me understand why it would insert a where clause on rownum, see below:

> 11:26:30.665 [[main]\<jdbc] INFO logstash.inputs.jdbc - (0.084000s) SELECT \* FROM (SELECT count(_) "COUNT" FROM (select c.id as ED\_ID,to\_char(c.updt\_dt\_tm, 'dd/mm/yyyy hh24:mi') as UPDT\_DT\_TM,c.esc\_status as ED\_STATUS,c.ed\_score as ED\_SCORE  
> ,regexp\_substr(c.trigger2,'[^|]+', 1, 2) as RESUS\_PATIENTS,regexp\_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE\_PATIENTS,regexp\_substr(c.trigger4,'[^|]+', 1, 2) AS  
> RAFT\_PATIENTS,regexp\_substr(c.trigger5,'[^|]+', 1, 2) AS ISO\_PATIENTS,regexp\_substr(c.trigger6,'[^|]+', 1, 2) AS TOC\_PATIENTS,regexp\_substr(c.trigger7,'[^|]+',1, 2) AS ETA\_PATIENTS,regexp\_substr(c.trigger8,'[^|]+', 1, 2) AS PAED\_AM\_PATIENT  
> S,regexp\_substr(c.trigger9,'[^|]+', 1, 2) AS PAED\_PM\_PATIENTS,regexp\_substr(c.trigger10,'[^|]+', 1, 2) AS TO\_BE\_SEEN\_BY\_DR\_PATIENTS,regexp\_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT\_BED\_PATIENTS,regexp\_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL\_PATIENTS,regexp\_substr(c.trigger13,'[^|]+', 1, 2) AS EMU\_PATIENTS from cust\_ed\_esc c order by c.id) "T1") "T1" WHERE (ROWNUM \<= 1)  
> 11:26:30.670 [[main]\<jdbc] DEBUG logstash.inputs.jdbc - Executing JDBC query {:statement=\>"select c.id as ED\_ID,to\_char(c.updt\_dt\_tm, 'dd/mm/yyyy hh24:mi') as UPDT\_DT\_TM,c.esc\_status as ED\_STATUS,c.ed\_score as ED\_SCORE,regexp\_substr(c.trigger2,'[^|]+', 1, 2) as RESUS\_PATIENTS,regexp\_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE\_PATIENTS,regexp\_substr(c.trigger4,'[^|]+', 1, 2) AS RAFT\_PATIENTS,regexp\_substr(c.trigger5,'[^|]+', 1, 2) AS ISO\_PATIENTS,regexp\_substr(c.trigger6,'[^|]+', 1, 2) AS TOC\_PATIENTS,regexp\_substr(c.trigger7,'[^|]+', 1, 2) AS ETA\_PATIENTS,  
> regexp\_substr(c.trigger8,'[^|]+', 1, 2) AS PAED\_AM\_PATIENTS,regexp\_substr(c.trigger9,'[^|]+', 1, 2) AS PAED\_PM\_PATIENTS,regexp\_substr(c.trigger10,'[^|]+', 1, 2)  
> AS TO\_BE\_SEEN\_BY\_DR\_PATIENTS,regexp\_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT\_BED\_PATIENTS,regexp\_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL\_PATIENTS,regexp\_substr(c.trigger13,'[^|]+', 1, 2) AS EMU\_PATIENTS from cust\_ed\_esc c order by c.id", :parameters=\>{:sql\_last\_value=\>1970-01-01 00:00:00 UTC}, :count=\>1}  
> 11:26:30.674 [[main]\<jdbc] INFO logstash.inputs.jdbc - (0.001000s) SELECT \* FROM (SELECT count(_) "COUNT" FROM (select c.id as ED\_ID,to\_char(c.updt\_dt\_tm, 'dd/mm/yyyy hh24:mi') as UPDT\_DT\_TM,c.esc\_status as ED\_STATUS,c.ed\_score as ED\_SCORE  
> ,regexp\_substr(c.trigger2,'[^|]+', 1, 2) as RESUS\_PATIENTS,regexp\_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE\_PATIENTS,regexp\_substr(c.trigger4,'[^|]+', 1, 2) AS  
> RAFT\_PATIENTS,regexp\_substr(c.trigger5,'[^|]+', 1, 2) AS ISO\_PATIENTS,regexp\_substr(c.trigger6,'[^|]+', 1, 2) AS TOC\_PATIENTS,regexp\_substr(c.trigger7,'[^|]+',1, 2) AS ETA\_PATIENTS,regexp\_substr(c.trigger8,'[^|]+', 1, 2) AS PAED\_AM\_PATIENT  
> S,regexp\_substr(c.trigger9,'[^|]+', 1, 2) AS PAED\_PM\_PATIENTS,regexp\_substr(c.trigger10,'[^|]+', 1, 2) AS TO\_BE\_SEEN\_BY\_DR\_PATIENTS,regexp\_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT\_BED\_PATIENTS,regexp\_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL\_PATIENTS,regexp\_substr(c.trigger13,'[^|]+', 1, 2) AS EMU\_PATIENTS from cust\_ed\_esc c order by c.id) "T1") "T1" WHERE (ROWNUM \<= 1)  
> 11:26:30.682 [[main]\<jdbc] INFO logstash.inputs.jdbc - (0.005000s) SELECT \* FROM (SELECT \* FROM (select c.id as ED\_ID,to\_char(c.updt\_dt\_tm, 'dd/mm/yyyy hh24:mi') as UPDT\_DT\_TM,c.esc\_status as ED\_STATUS,c.ed\_score as ED\_SCORE,regexp\_substr(  
> c.trigger2,'[^|]+', 1, 2) as RESUS\_PATIENTS,regexp\_substr(c.trigger3,'[^|]+', 1, 2) AS ACUTE\_PATIENTS,regexp\_substr(c.trigger4,'[^|]+', 1, 2) AS RAFT\_PATIENTS,regexp\_substr(c.trigger5,'[^|]+', 1, 2) AS ISO\_PATIENTS,regexp\_substr(c.trigger6,  
> '[^|]+', 1, 2) AS TOC\_PATIENTS,regexp\_substr(c.trigger7,'[^|]+', 1, 2) AS ETA\_PATIENTS,regexp\_substr(c.trigger8,'[^|]+', 1, 2) AS PAED\_AM\_PATIENTS,regexp\_substr(c.trigger9,'[^|]+', 1, 2) AS PAED\_PM\_PATIENTS,regexp\_substr(c.trigger10,'[^|]+'  
> , 1, 2) AS TO\_BE\_SEEN\_BY\_DR\_PATIENTS,regexp\_substr(c.trigger11,'[^|]+',1,2) AS WITHOUT\_BED\_PATIENTS,regexp\_substr(c.trigger12,'[^|]+', 1, 2) AS TOTAL\_PATIENTS,regexp\_substr(c.trigger13,'[^|]+', 1, 2) AS EMU\_PATIENTS from cust\_ed\_esc c order  
> by c.id) "T1") "T1" WHERE (ROWNUM \<= 1)

editing post to add that if I manually run the queries output in the above log in something like SQL developer, they do return the right data, all of the data, and so the question remains why only one row seems to return when run from logstash.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 8, 2017, 10:40pm UTC](https://discuss.elastic.co/t/jdbc-oracle-only-one-row-returned-before-closing/92183/3 "2017-08-08T22:40:53Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.