IPCC DB Read SQL error

Unanswered Question
Sep 12th, 2008
User Badges:

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

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
Loading.
BCOLE2007 Fri, 09/12/2008 - 11:08
User Badges:
  • Bronze, 100 points or more

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



Attachment: 
mserwon Fri, 09/12/2008 - 11:17
User Badges:

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.

BCOLE2007 Fri, 09/12/2008 - 11:46
User Badges:
  • Bronze, 100 points or more

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

mserwon Tue, 09/16/2008 - 06:40
User Badges:

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


BCOLE2007 Tue, 09/16/2008 - 07:32
User Badges:
  • Bronze, 100 points or more


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



Actions

This Discussion