Title: Using unnamed procedural code in JdbcTemplate
Tags: oracle;spring;stored-procedures;jdbc;jdbctemplate
Question: I have a table in an Oracle database where three columns are automatically populated by a trigger when a row in inserted.  To retrieve those values when the insert happens, I am using some procedural code but it doesn't exist as a named stored procedure.  The code looks something like this but with more outputs.
DECLARE widgetId NUMBER;
BEGIN INSERT INTO widget(foo) VALUES('bar')
RETURNING widget_id INTO widgetId;
Does Spring JdbcTemplate provide any clean, convenient way of calling that code?  I see where JdbcTemplate supports calling a stored procedure but that seems to require that the code live in the database and can be called by name.  JdbcTemplate's KeyHolder seems promising but I'm not entirely sure if it can be used to retrieve an arbitrary number of values generated by a trigger.  I also haven't had much luck finding anything but simple examples.  Does anyone have any insight into a clean way of calling that code using JdbcTemplate?
Comment: On a side note for anyone scratching their head at the example, that procedural code was based on an example I found elsewhere in the basebase and I assume it was a holdover from before JDBC had as much support for returning generated values.  Nowadays, a more elegant solution is to provide JDBC Template a KeyHolder and retrieve the generated values using keyHolder.getKeys().get("generate_value_column_name").  I hope that helps someone.

Here is the accepted answer: To be called the code will have to be a stand-alone procedure or function, or will have to be an externally visible procedure or function in a package.  As far as I'm aware there's no way to execute anonymous code.
Share and enjoy.
Comment for this answer: That's what I was afraid of.  Thanks for confirming that for me.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2011:00:12/22/11', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<oracle><spring><stored-procedures><jdbc><jdbctemplate>', 'Title': 'Using unnamed procedural code in JdbcTemplate', 'ViewCount': 273, 'id': 8598284, 'user_AboutMe': '<p>I\'ve been programming since I was in kid and doing it for a living for over a decade. I mostly work with Android now, play with machine learning in my free time and did a ton of Enterprise Java in the past.</p>\n<p>Some publicly available projects I\'ve developed include a library for implementing <a href="https://github.com/CoalMineSoftware/jStately" rel="nofollow noreferrer">OO state machines</a>, <a href="https://github.com/CoalMineSoftware/AndroidDataAccessTemplate" rel="nofollow noreferrer">streamlining the use of Android databases and ContentProviders</a> and <a href="https://github.com/CoalMineSoftware/EnumUriMatcher" rel="nofollow noreferrer">type-safe Android URI matching</a>.</p>\n', 'user_AccountId': 243192.0, 'user_DisplayName': 'spaaarky21', 'user_Reputation': 6232.0, 'user_Views': 781.0, 'user_id': 515205.0}}