Running Oracle Stored prodecdure with paramaters returns error - VisualCron - Forum

Community forum

dsaucier
2010-04-30T19:39:18Z
I have a similar problem as another post but the resolution for them is not working for me. I cannot execute a stored procedure in an Oracle DB. The error I am getting is: The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.

I am using the connection wizzard and selecting database\oracle\oracle. I have done this with both standard and using ODP.NET without tnsnames.ora. I can connect with both versions and run a select statement succesfully.

I have also tried the job on a x32 2003 server with Oracle Client x32 and a x64 2008 server with Oracle Client x64. Both return the error above.

I had our DBA create a stored job with no paramaters and it succesfully ran the job.


Any ideas would be apreciated.
Support
2010-04-30T20:58:38Z
Which connection type do you use Oracle, ODBC or OLEDB?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Support
2010-04-30T21:26:20Z
Please let us know if this version works better for you:

http://neteject.com/down...on/VisualCron5.5.8-3.exe 
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
dsaucier
2010-05-03T17:41:25Z
We use the Oracle connection.
I will try the new version on our test server.
dsaucier
2010-05-03T19:00:48Z
The new version seems to do the trick. My DBA reports that it ran the job correctly. He wants to test a bit more and analyize the output and return of information. I will try to push this out to production today or tomorrow. I will update again then.

dsaucier
2010-05-03T20:41:45Z
The DBA has finished his testing. However, when a stored procedure fails; The job returns "success" anyway. Is there a way to caputre when the stored procedure fails? Is this something I should put on the wish list?

We passed an argument with a table name to the stored procedure that then runs an update on a column in that table. When passed the correct name the stored procedure did as expected. When passed an invalid table name, the procedure failed but the VC jobs returned success.

Support
2010-05-03T23:32:26Z
How does it fail? Do you get any error in standard output or error columns?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
dsaucier
2010-05-04T18:04:10Z
Nothing gets passed back. There is no output returned. There is no stderr erturned.
dsaucier attached the following image(s):
Support
2010-05-05T16:44:43Z
1. And this is a normal stored procedure you call?
2. How exactly does your stored procedure fail?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
dsaucier
2010-05-05T17:33:46Z
Here is the normal stored procedure we used to test.
CREATE OR REPLACE

PACKAGE vern_pkg IS

PROCEDURE pop (p_table_name IN VARCHAR2,

p_text_value IN vern_test.text_value%TYPE,

p_num_value IN vern_test.num_value%TYPE DEFAULT null,

p_dt IN vern_test.dt%TYPE DEFAULT SYSDATE);



PROCEDURE get_sysdate(P_DT IN OUT VERN_TEST.DT%TYPE);

END; -- Package spec

/





CREATE OR REPLACE

PACKAGE BODY vern_pkg IS

PROCEDURE pop (p_table_name IN VARCHAR2,

p_text_value IN vern_test.text_value%TYPE,

p_num_value IN vern_test.num_value%TYPE DEFAULT NULL,

p_dt IN vern_test.dt%TYPE DEFAULT SYSDATE) IS

v_sql VARCHAR2 (4000);

BEGIN

v_sql :=

'INSERT INTO '

|| p_table_name

|| ' VALUES ('''

|| p_text_value

|| ''', '

|| p_num_value

|| ', TO_DATE('''

|| p_dt

|| ''',''DD-MON-YYYY''))';



EXECUTE IMMEDIATE v_sql;



COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

DBMS_OUTPUT.put_line (SQLERRM);

END;



PROCEDURE get_sysdate (p_dt IN OUT vern_test.dt%TYPE) IS

BEGIN

p_dt := SYSDATE;

END;

END;

/



Here we pass correct info and get a success using sqlplus:

SQL> EXEC VERN_PKG.pop ('VERN_TEST', 'BOGUS', 12345, '01-JAN-2010');


PL/SQL procedure successfully completed.

Here we pass the incorrect table name 'XVERN_TEST' via sqlplus:


SQL> EXEC VERN_PKG.pop ('XVERN_TEST', 'BOGUS', 12345, '01-JAN-2010');

ORA-00942: table or view does not exist

PL/SQL procedure successfully completed.



When running this stored procedure from VC, is there any way to capture the ORA error generated?


BTW, this is mostly greek to me and I rely heavily on my DBA to provide this info so I might be using incorrect terminology.






dsaucier
2010-05-10T20:45:02Z
Just to update this thread. My DBA discovered that he had error handling routines active inside the procedure that were intercepting the errors. This was not a fault of VC at all. Once the routines were removed, the error was captured by VC properly.

Thanks for the help and support.
Support
2010-05-10T20:48:38Z
Thanks for the feedback. We had this testing in queue but we suspected this since we are capturing all unhandled errors.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Scroll to Top