Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

IPCC DB Read SQL error

I am using a DB Read statement to call a stored procedure in an oracle database from a simple test script, but getting a sql error everytime. Is there any way to find out what the sql error is?

The select statement looks like

SELECT ROP_USER.SHE_VALIDATE_WORK_ORDER( $StoreID , $WorkOrderID ) WORKORDERID , CURRENTSTATUS , CALLEXISTSFLAG FROM DUAL;

I have a dummy table defined that has WORKORDERID , CURRENTSTATUS , CALLEXISTSFLAG defined as VARCHAR2(50).

Thx

5 REPLIES
Bronze

Re: IPCC DB Read SQL error

Hi,

To see the Oracle error, turn up the tracing to level 7 (expand the normal view to expanded) on the SS_DB step.

Execute the script and check the log file

and search for the Oracle error codes ORA-

curious how the PL/SQL Proc turns out, please let me know

Blair

*please rate all posts

New Member

Re: IPCC DB Read SQL error

I looked in the MIVR log files after turning on the debug you suggested and found the SQL 904 error. Now I have to figure out what that means.

Thanks again.

Bronze

Re: IPCC DB Read SQL error

If its an ORA-904 error

(invalid column name)...

try this

SELECT ROP_USER.SHE_VALIDATE_WORK_ORDER( $StoreID , $WorkOrderID ) AS WORKORDERID , CURRENTSTATUS , CALLEXISTSFLAG FROM DUAL;

Try naming the Pl/sql column with an AS

PL/SQL procedures and CRS scripting Database steps have issues in my experience. I think technically Procedures don't return a value on a select, hence maybe this is where the 904 error comes in

I have been able to retrieve a PL/SQL Function, as opposed to a Procedure from a

select statement from dual on a DB Read CRS step. There are limitations in Oracle with PL/SQL functions versus PL/SQL procedures, but Functions do return a value and can be called be selected from.

If you must use a Procedure are comfortable with Java create a custom class to retrieve the Oracle PL/SQL Procedure may be the way to go.

let me know how it goes

Blair

*Please rate all useful posts

New Member

Re: IPCC DB Read SQL error

Thanks Blair,

I changed the syntax to use an as, but now I get a 920 error.

Any idea on that error.

I am not comfortable with a custom class, but can a sql function return multiple return values?

Thanks

Bronze

Re: IPCC DB Read SQL error

Not sure of the 920 error, I can speculate this is related to the Procedure Call in a select statement

A PL/SQL function can only return 1 Value, however you could write a seperate function for each value needed and treat them each as a column, not the most Database efficient but workable.

Keep me posted, I want to hear how it turns out

Blair

*Please rate all useful posts

221
Views
5
Helpful
5
Replies