# Unable to configure oracle stored procedure in logstash jdbc pipeline

**URL:** <https://discuss.elastic.co/t/unable-to-configure-oracle-stored-procedure-in-logstash-jdbc-pipeline/340369>\
**Category:** Logstash\
**Tags:** jdbc\
**Created:** [August 8, 2023, 12:30pm UTC](https://discuss.elastic.co/t/unable-to-configure-oracle-stored-procedure-in-logstash-jdbc-pipeline/340369 "2023-08-08T12:30:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sreenivas1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sreenivas1/32/98464_2.png) [@Sreenivas1](https://discuss.elastic.co/u/Sreenivas1)\
**Post date:** [August 8, 2023, 12:30pm UTC](https://discuss.elastic.co/t/unable-to-configure-oracle-stored-procedure-in-logstash-jdbc-pipeline/340369/1 "2023-08-08T12:30:45Z")

</div>

Hi all,

I'm trying to call stored procedure created in oracle database using logstash jdbc pipeline but even I tried with many ways to pass stored procedure in statement it's getting failed with sql error exceptions . please help me with correct way to configure this.  
Note: I'm able to execute stored procedure in sql developer client and it's working fine.

**tried all the commented statements one after other but no one is working**

```auto
input {
  jdbc {
jdbc_validate_connection => true 
jdbc_driver_library => "<path>\ojdbc8.jar"
jdbc_connection_string => "jdbc:oracle:thin:@<host>:1521/<db>"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_user => "<user>"
jdbc_password => "<pwd>"
sql_log_level => "debug"
schedule => "*/1 * * * *"

# statement => ["EXEC <package>.<storedprocedure>()"]
# statement => ["EXEC <package>.<storedprocedure>();"]
# statement => ["exec <package>.<storedprocedure>()"]
# statement => "CALL <package>.<storedprocedure>"
# statement => "{call <package>.sp_elk_sync_ptnr_cntct}"
#statement => "{call <storedprocedure>}"
# {call PKGNAME.STOREDPROCNAME(?)}
# e.g.
# statement => "{call <package>.<storedprocedure>()}"
  }
}

```

---

<div class="post-metadata">

**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\
**Post date:** [August 9, 2023, 5:31am UTC](https://discuss.elastic.co/t/unable-to-configure-oracle-stored-procedure-in-logstash-jdbc-pipeline/340369/2 "2023-08-09T05:31:31Z")

</div>

Hi,

What are you intending to do? A jdbc input only makes sense when the procedure really is a function and returns something that can be processed lateron. In this case, you could use something like `statement => "Select <package>.<storedprocedure>() FROM DUAL"`

Best regards  
Wolfram

---

<div class="post-metadata">

**Author:** ![Sreenivas1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sreenivas1/32/98464_2.png) [@Sreenivas1](https://discuss.elastic.co/u/Sreenivas1)\
**Post date:** [August 9, 2023, 7:11am UTC](https://discuss.elastic.co/t/unable-to-configure-oracle-stored-procedure-in-logstash-jdbc-pipeline/340369/3 "2023-08-09T07:11:37Z")

</div>

Hi Wolfram,

Thanks for your reply.

In my case Stored procedure will filter records from one table and keep it in a temporary table, which I'm fetching in subsequent Jdbc inputs.

And I tried with statement you have provided but it's giving  
below error

```auto

 [2023-08-09T12:39:03,689][WARN][logstash.inputs.jdbc][main][6d730e488867f8c827ad66de6f57cf4778a2b3702141cd39337059e0c734f861] Exception when executing JDBC query {:exception=>"Java::JavaSql::SQLSyntaxErrorException: ORA-00904: \"<package>\".\"<procedure>\": invalid identifier\n"}

```

Thanks,  
Sreenivas A

---

<div class="post-metadata">

**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\
**Post date:** [August 9, 2023, 7:30am UTC](https://discuss.elastic.co/t/unable-to-configure-oracle-stored-procedure-in-logstash-jdbc-pipeline/340369/4 "2023-08-09T07:30:43Z")

</div>

Hello Sreenivas,

Okay, I understand. In my opinion, there are 3 options:

1. Convert your procedure into a function which retuns a dummy value so that you can use `select procedure() from dual` to call it from within logstash.
2. Remove the stored procedure and use the SQL used in it directly in the jdbc input. Depending on the complexity, this might not be possible.
3. Do not trigger the procedure in Logstash and run it as a [database scheduler](https://oracle-base.com/articles/10g/scheduler-10g#jobs) instead.

I would prefer (3) and create a scheduler that calls the procedure regularly and thus updates your temporary table. Logstash can then rely on the table always being up-to-date without worrying about refreshing the table.

Best regards  
Wolfram

---

<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:** [September 6, 2023, 7:31am UTC](https://discuss.elastic.co/t/unable-to-configure-oracle-stored-procedure-in-logstash-jdbc-pipeline/340369/5 "2023-09-06T07:31:03Z")

</div>

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