Micro Focus Contributor
Micro Focus Contributor
303 views

Oracle JDBC - Stored Procedure doubts


Hello everyone!

I'm working with a client that wants to use a JDBC driver to call a
stored procedure for provisioning. Basically the driver is neither being
used in direct or indirect mode rather schema unaware mode and calling
the stored procedure "updateUser" to work all the background user
handling.

The stored procedure is inside a package so the full name of the sp is
in the schema.package.procedure-name format. I'm using the
jdbc:call-procedure node for the call. If i call it that way i get an
error stating "Unable to retrieve metadata for procedure/function
'SIRA_USER.MM_USER_MGR.UPDATEUSER'. Please check the procedure/function
name again and add a schema qualifier if necessary. The absence of this
metadata may affect this driver's ability to call this stored
procedure/function correctly."

After some reading i found out that if the sp is called with a full name
in the schema.procedure-name format, that is if the procedure is not
inside a package, the driver has no problems retrieving. As a workaround
i made an sp that calls the sp inside the package.

I wanted to know if there is a way to call the sp inside the package AND
get the required metadata to make the sp work without the workaround.

I have another doubt regarding how to pass the password of a user to the
SP and getting the driver to mask it. From what i could read if i copy
the password in a <password/> node inside the <jdbc:statement/> node i
should be able to pass it as {$$password} in the <values/> node of the
corresponding <param/> node, but for some reason in the corresponding
table i'm getting the value {$password} and not the actual password.

Here's the policy i'm using: http://pastebin.com/U4Sj6FH4

And this is the trace for the operation: http://pastebin.com/DPc88g8j

Any help will be appreaciated.

Thanks in advance! 😄


--
EPedros
------------------------------------------------------------------------
EPedros's Profile: https://forums.netiq.com/member.php?userid=5531
View this thread: https://forums.netiq.com/showthread.php?t=53698

Labels (1)
0 Likes
6 Replies
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: Oracle JDBC - Stored Procedure doubts

Wildly guessing:

The error states that it cannot process the 'add' while in schema-unaware
mode. Perhaps strip that out while letting your jdbc block go through. I
haven't tested this with anything like your setup before, but perhaps that
will help.

--
Good luck.

If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below...
0 Likes
Micro Focus Contributor
Micro Focus Contributor

Re: Oracle JDBC - Stored Procedure doubts


Thanks ab! I tried stripping the 'add'. It indeed removed the warning
regarding the add operation but I'm still getting {$password} on the
database end instead of the actual password.

Here's the new trace: http://pastebin.com/gTcAa5jJ


Regards!

ab;258075 Wrote:
> Wildly guessing:
>
> The error states that it cannot process the 'add' while in
> schema-unaware
> mode. Perhaps strip that out while letting your jdbc block go through.
> I
> haven't tested this with anything like your setup before, but perhaps
> that
> will help.
>
> --
> Good luck.
>
> If you find this post helpful and are logged into the web interface,
> show your appreciation and click on the star below...



--
EPedros
------------------------------------------------------------------------
EPedros's Profile: https://forums.netiq.com/member.php?userid=5531
View this thread: https://forums.netiq.com/showthread.php?t=53698

0 Likes
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: Oracle JDBC - Stored Procedure doubts

I take it that is not what you want? Why are you sending it?


<jdbc:param>
<jdbc:value>{$password}</jdbc:value>
</jdbc:param>


I cannot see what is adding it, but there are suppressed traces in there,
so perhaps those.


--
Good luck.

If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below...
0 Likes
Micro Focus Contributor
Micro Focus Contributor

Re: Oracle JDBC - Stored Procedure doubts


Basically i'm trying to do token substitution as described in
http://tinyurl.com/ndfwlsq, but i'm not sure if the way i'm doing it is
ok for stored procedures since i haven't found a section talking about
it explicitly. The idea is that the {$$password} token should be
substituted with the password of the user so in the database end i would
see the actual password.

The code that's adding the {$$password} token is:

Code:
--------------------
<do-append-xml-text expression="../jdbc:statement/jdbc:call-procedure/jdbc:param[last()]/jdbc:value[last()]">
<arg-string>
<token-text xml:space="preserve">{$$password}</token-text>
</arg-string>
</do-append-xml-text>
--------------------


--
EPedros
------------------------------------------------------------------------
EPedros's Profile: https://forums.netiq.com/member.php?userid=5531
View this thread: https://forums.netiq.com/showthread.php?t=53698

0 Likes
Micro Focus Contributor
Micro Focus Contributor

Re: Oracle JDBC - Stored Procedure doubts


Alright, i wasn't able to use the token substitution with {$$password}
so i decided to try another approach. By adding the is-sensitive="true"
attribute to the jdbc:statement node i was able to mask the whole
operation so the password is not showing there. The password itself is
being passed as cleartext since that's what the sp is expecting.


Code:
--------------------
<nds dtdversion="4.0" ndsversion="8.x">
<source>
<product edition="Advanced" version="4.0.2.0">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<jdbc:statement event-id="plnx0108#20150622155744#1#1:bda95dfb-8fcc-4f6d-6d87-fb5da9bdcc8f" is-sensitive="true" src-dn="\TELARG\Telecom\Usuarios\Personas\SIRA_TEST003" xmlns:jdbc="urn:dirxml:jdbc"><!-- content suppressed --><!-- content suppressed --><!-- content suppressed -->
</jdbc:statement>
</input>
</nds>
--------------------


Now i need to find a way to mask the output from the call to the sp
itself since the password is showing (third line):


Code:
--------------------
[06/22/15 12:57:46.152]:TECO_SIRA_PR ST:{call SIRA_USER.CALL_UPDATEUSER(?,?,?,?,?,?,?,?,?,?,?,?)}
[06/22/15 12:57:46.153]:TECO_SIRA_PR ST: IN @ index 1, param 'CALL_NAME', field '$1', length: 12, value = 'SIRA_TEST003'
[06/22/15 12:57:46.154]:TECO_SIRA_PR ST: IN @ index 2, param 'CALL_PASSWORD', field '$2', length: 18, value = 'PasswordCompleja02'
[06/22/15 12:57:46.154]:TECO_SIRA_PR ST: IN @ index 3, param 'CALL_ADMINISTRATOR', field '$3', value = 1
[06/22/15 12:57:46.154]:TECO_SIRA_PR ST: IN @ index 4, param 'CALL_EMAIL', field '$4', length: 31, value = 'adalessandro1@local.testing.org'
[06/22/15 12:57:46.155]:TECO_SIRA_PR ST: IN @ index 5, param 'CALL_ROLEID', field '$5', length: 5, value = 'role1'
[06/22/15 12:57:46.155]:TECO_SIRA_PR ST: IN @ index 6, param 'CALL_BUSY', field '$6', value = 0
[06/22/15 12:57:46.170]:TECO_SIRA_PR ST: IN @ index 7, param 'CALL_MOBILEPHONENUMBER', field '$7', length: 8, value = '555-8765'
[06/22/15 12:57:46.170]:TECO_SIRA_PR ST: IN @ index 8, param 'CALL_FIRSTNAME', field '$8', length: 7, value = 'Adrian1'
[06/22/15 12:57:46.171]:TECO_SIRA_PR ST: IN @ index 9, param 'CALL_SURNAME', field '$9', length: 13, value = 'D'Alessandro1'
[06/22/15 12:57:46.171]:TECO_SIRA_PR ST: IN @ index 10, param 'CALL_MANAGERNAME', field '$10', length: 2, value = 'NA'
[06/22/15 12:57:46.171]:TECO_SIRA_PR ST: OUT @ index 11, param 'CALL_OUT_STATUS', field '$11'
[06/22/15 12:57:46.172]:TECO_SIRA_PR ST: OUT @ index 12, param 'CALL_OUT_ERR_MSG', field '$12'
[06/22/15 12:57:46.176]:TECO_SIRA_PR ST: OUT @ index 11, param 'CALL_OUT_STATUS', field '$11', value = 1
[06/22/15 12:57:46.176]:TECO_SIRA_PR ST: OUT @ index 12, param 'CALL_OUT_ERR_MSG', field '$12', value = null
--------------------


Is this output comming from the shim or from the jdbc jar? Any ideas on
how to mask it?

Thanks!


--
EPedros
------------------------------------------------------------------------
EPedros's Profile: https://forums.netiq.com/member.php?userid=5531
View this thread: https://forums.netiq.com/showthread.php?t=53698

0 Likes
Micro Focus Contributor
Micro Focus Contributor

Re: Oracle JDBC - Stored Procedure doubts


After fiddling around a little bit with the Trace level i got the output
to disappear. It was simply a matter of lowering the Trace level to 2
(from 3 where it was) and that was it, the connection trace was gone.

I'll close the thread since that resolves my issue.


--
EPedros
------------------------------------------------------------------------
EPedros's Profile: https://forums.netiq.com/member.php?userid=5531
View this thread: https://forums.netiq.com/showthread.php?t=53698

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.