Executing stored procedures in Oracle using ZOracleDA
Hello I am trying to execute a PL/SQL stored procedure from a ZSQL method. I am using Zope 2.0.1, ZOracleDA 2.1.0, Oracle 8i, all on a Sun UltraSparc machine. The stored procedure is valid. It works. I have read the DCOracle release information and it states that it supports the invocation of stored procedures. I need this in order to use the UserDB product, because my user information is splitted over many tables, and I execute a stored procedures that querys/updates/deletes the user info from the database. I have the following code for example: Execute InsertUsuario ( <dtml-sqlvar username type="string">, <dtml-sqlvar password type="string">, <dtml-sqlvar AUTHENTICATED_USER type="string">, <dtml-sqlvar REMOTE_ADDR type="string"> ) inside a ZSQL Method. When I test this method I get the following traceback: <PRE> Traceback (innermost last): File /export/home/bmatt/iPersonal/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /export/home/bmatt/iPersonal/lib/python/ZPublisher/Publish.py, line 179, in publish File /export/home/bmatt/iPersonal/lib/python/Zope/__init__.py, line 201, in zpublisher_exception_hook (Object: ApplicationDefaultPermissions) File /export/home/bmatt/iPersonal/lib/python/ZPublisher/Publish.py, line 151, in publish File /export/home/bmatt/iPersonal/lib/python/ZPublisher/BaseRequest.py, line 304, in traverse File /export/home/bmatt/iPersonal/lib/python/OFS/Application.py, line 260, in __bobo_traverse__ (Object: ApplicationDefaultPermissions) File /export/home/bmatt/iPersonal/lib/python/ZPublisher/HTTPResponse.py, line 510, in notFoundError NotFound: (see above) </PRE> Can anyone tell me what does this means? TIA /B Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/>
At 23:08 21-11-99 , Bruno Mattarollo wrote:
Execute InsertUsuario ( <dtml-sqlvar username type="string">, <dtml-sqlvar password type="string">, <dtml-sqlvar AUTHENTICATED_USER type="string">, <dtml-sqlvar REMOTE_ADDR type="string"> )
Are you passing in AUTHENTICATED_USER and REMOTE_ADDR? They are not available otherwise. You can access REMOTE_ADDR on the REQUEST object as well: <dtml-sqlvar "REQUEST['REMOTE_ADDR']" type="string"> Maybe you can access AUTHENTICATED_USER this way as well, but I don't have the time right now to test this for you. -- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | T: +31 35 7502100 F: +31 35 7502111 | mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ---------------------------------------------
Hello. Yes, I am passing those vars. I even did a "test" of the ZSQL method where you have to write in the textboxes the values of all the parameters. And I got the error described in my previous email... This is very strange... Or at least it's very strange to me ;-) thanks Martijn /B Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/> ----- Original Message ----- From: "Martijn Pieters" <mj@antraciet.nl> To: "Bruno Mattarollo" <brunomadv@ciudad.com.ar>; <zope@zope.org> Sent: Monday, November 22, 1999 8:43 AM Subject: Re: [Zope] Executing stored procedures in Oracle using ZOracleDA
At 23:08 21-11-99 , Bruno Mattarollo wrote:
Execute InsertUsuario ( <dtml-sqlvar username type="string">, <dtml-sqlvar password type="string">, <dtml-sqlvar AUTHENTICATED_USER type="string">, <dtml-sqlvar REMOTE_ADDR type="string"> )
Are you passing in AUTHENTICATED_USER and REMOTE_ADDR? They are not available otherwise.
You can access REMOTE_ADDR on the REQUEST object as well:
<dtml-sqlvar "REQUEST['REMOTE_ADDR']" type="string">
Maybe you can access AUTHENTICATED_USER this way as well, but I don't have the time right now to test this for you.
-- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | T: +31 35 7502100 F: +31 35 7502111 | mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ---------------------------------------------
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope No cross posts or HTML encoding! (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
At 13:05 22-11-99 , Bruno Mattarollo wrote:
Hello.
Yes, I am passing those vars. I even did a "test" of the ZSQL method where you have to write in the textboxes the values of all the parameters. And I got the error described in my previous email... This is very strange... Or at least it's very strange to me ;-)
thanks Martijn
Have you tried replacing all vars with static values? So, instead of: <dtml-sqlvar "REQUEST['REMOTE_ADDR']" type="string"> do: <dtml-sqlvar "'101.102.103.104'" type="string"> etc. If the errors still occur, it's not your Z SQL code.. Disclaimer: I have no experience with the DCOracleDA nor Oracle stored procedures. -- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | T: +31 35 7502100 F: +31 35 7502111 | mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ---------------------------------------------
Hi Martijn, Yes, I tryied that also... And I am getting the same traceback... :( <PRE> Traceback (innermost last): File /export/home/bmatt/iPersonal/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /export/home/bmatt/iPersonal/lib/python/ZPublisher/Publish.py, line 179, in publish File /export/home/bmatt/iPersonal/lib/python/Zope/__init__.py, line 201, in zpublisher_exception_hook (Object: ApplicationDefaultPermissions) File /export/home/bmatt/iPersonal/lib/python/ZPublisher/Publish.py, line 151, in publish File /export/home/bmatt/iPersonal/lib/python/ZPublisher/BaseRequest.py, line 304, in traverse File /export/home/bmatt/iPersonal/lib/python/OFS/Application.py, line 260, in __bobo_traverse__ (Object: ApplicationDefaultPermissions) File /export/home/bmatt/iPersonal/lib/python/ZPublisher/HTTPResponse.py, line 510, in notFoundError NotFound: (see above) </PRE> Strange... The stored procedure works great when invoqued directly from the SQL*Plus utility. Cheers and thanks again Martijn. /B Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/> ----- Original Message ----- From: "Martijn Pieters" <mj@antraciet.nl> To: "Bruno Mattarollo" <brunomadv@ciudad.com.ar>; <zope@zope.org> Sent: Monday, November 22, 1999 10:31 AM Subject: Re: [Zope] Executing stored procedures in Oracle using ZOracleDA
At 13:05 22-11-99 , Bruno Mattarollo wrote:
Hello.
Yes, I am passing those vars. I even did a "test" of the ZSQL method where you have to write in the textboxes the values of all the parameters. And I got the error described in my previous email... This is very strange... Or at least it's very strange to me ;-)
thanks Martijn
Have you tried replacing all vars with static values? So, instead of:
<dtml-sqlvar "REQUEST['REMOTE_ADDR']" type="string">
do:
<dtml-sqlvar "'101.102.103.104'" type="string">
etc.
If the errors still occur, it's not your Z SQL code..
Disclaimer: I have no experience with the DCOracleDA nor Oracle stored procedures.
-- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | T: +31 35 7502100 F: +31 35 7502111 | mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ---------------------------------------------
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope No cross posts or HTML encoding! (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
At 14:48 22-11-99 , Bruno Mattarollo wrote:
Hi Martijn,
Yes, I tryied that also... And I am getting the same traceback... :(
And this is from the test tab? Then either the DCOracleDA doesn't support Stored Prcedures, or there is a bug. -- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | T: +31 35 7502100 F: +31 35 7502111 | mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ---------------------------------------------
Try "begin InsertUsuario(... ); end;" instead of Execute. Arpad Kiss ----- Original Message ----- From: Bruno Mattarollo <brunomadv@ciudad.com.ar> To: <zope@zope.org>; Martijn Pieters <mj@antraciet.nl> Sent: Monday, November 22, 1999 1:05 PM Subject: Re: [Zope] Executing stored procedures in Oracle using ZOracleDA
Hello.
Yes, I am passing those vars. I even did a "test" of the ZSQL method where you have to write in the textboxes the values of all the parameters. And I got the error described in my previous email... This is very strange... Or at least it's very strange to me ;-)
thanks Martijn
/B
Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/>
----- Original Message ----- From: "Martijn Pieters" <mj@antraciet.nl> To: "Bruno Mattarollo" <brunomadv@ciudad.com.ar>; <zope@zope.org> Sent: Monday, November 22, 1999 8:43 AM Subject: Re: [Zope] Executing stored procedures in Oracle using ZOracleDA
At 23:08 21-11-99 , Bruno Mattarollo wrote:
Execute InsertUsuario ( <dtml-sqlvar username type="string">, <dtml-sqlvar password type="string">, <dtml-sqlvar AUTHENTICATED_USER type="string">, <dtml-sqlvar REMOTE_ADDR type="string"> )
Are you passing in AUTHENTICATED_USER and REMOTE_ADDR? They are not available otherwise.
You can access REMOTE_ADDR on the REQUEST object as well:
<dtml-sqlvar "REQUEST['REMOTE_ADDR']" type="string">
Maybe you can access AUTHENTICATED_USER this way as well, but I don't have the time right now to test this for you.
-- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | T: +31 35 7502100 F: +31 35 7502111 | mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ---------------------------------------------
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope No cross posts or HTML encoding! (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope No cross posts or HTML encoding! (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Hello... Thanks Arpad, that solved it. Perhaps we should incorporate this as a Zope Tip... Because you cannot find it anywhere ... Or am I wrong? Is there a reason to use BEGIN StoredProc(parameters, ...); END; and not Execute? Thanks again to you and to Martijn.... Cheers, /B Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/> ----- Original Message ----- From: "Arpad Kiss" <sekter@mail.matav.hu> To: "Bruno Mattarollo" <brunomadv@ciudad.com.ar>; <zope@zope.org>; "Martijn Pieters" <mj@antraciet.nl> Sent: Monday, November 22, 1999 10:45 AM Subject: Re: [Zope] Executing stored procedures in Oracle using ZOracleDA
Try "begin InsertUsuario(... ); end;" instead of Execute. Arpad Kiss
----- Original Message ----- From: Bruno Mattarollo <brunomadv@ciudad.com.ar> To: <zope@zope.org>; Martijn Pieters <mj@antraciet.nl> Sent: Monday, November 22, 1999 1:05 PM Subject: Re: [Zope] Executing stored procedures in Oracle using ZOracleDA
Hello.
Yes, I am passing those vars. I even did a "test" of the ZSQL method where you have to write in the textboxes the values of all the parameters. And I got the error described in my previous email... This is very strange... Or at least it's very strange to me ;-)
thanks Martijn
/B
Bruno Mattarollo <brunomadv@ciudad.com.ar> --- Python Powered <http://www.python.org/psa/>
----- Original Message ----- From: "Martijn Pieters" <mj@antraciet.nl> To: "Bruno Mattarollo" <brunomadv@ciudad.com.ar>; <zope@zope.org> Sent: Monday, November 22, 1999 8:43 AM Subject: Re: [Zope] Executing stored procedures in Oracle using ZOracleDA
At 23:08 21-11-99 , Bruno Mattarollo wrote:
Execute InsertUsuario ( <dtml-sqlvar username type="string">, <dtml-sqlvar password type="string">, <dtml-sqlvar AUTHENTICATED_USER type="string">, <dtml-sqlvar REMOTE_ADDR type="string"> )
Are you passing in AUTHENTICATED_USER and REMOTE_ADDR? They are not available otherwise.
You can access REMOTE_ADDR on the REQUEST object as well:
<dtml-sqlvar "REQUEST['REMOTE_ADDR']" type="string">
Maybe you can access AUTHENTICATED_USER this way as well, but I don't have the time right now to test this for you.
-- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | T: +31 35 7502100 F: +31 35 7502111 | mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ---------------------------------------------
On Mon, 22 Nov 1999, Bruno Mattarollo wrote:
Thanks Arpad, that solved it. Perhaps we should incorporate this as a Zope Tip... Because you cannot find it anywhere ... Or am I wrong?
Is there a reason to use BEGIN StoredProc(parameters, ...); END; and not Execute?
I don't think execute is an Oracle keyword. As far as I am aware, the two syntaxes for calling stored procedures from something other than PL/SQL have always been: begin ... -- PL/SQL Block, which can call procedures end; or select myfunc(args) from dual which will only work if myfunc is a function and returns a valid SQL datatype. -- ___ // Zen (alias Stuart Bishop) Work: zen@cs.rmit.edu.au // E N Senior Systems Alchemist Play: zen@shangri-la.dropbear.id.au //__ Computer Science, RMIT WWW: http://www.cs.rmit.edu.au/~zen
participants (4)
-
Arpad Kiss -
Bruno Mattarollo -
Martijn Pieters -
Stuart 'Zen' Bishop