DSXchange

Hi All,

I have a stored proc running fine in sqlplus but not when called from datastage. Stored proc defined as 'Transform'.

I ran the stored proc in sqlplus which inserted approximately 700 rows. However, when the stored proc calls from datastage, it generates zero rows.

One thing i suspected is the definition of the SP comes with ampersand(&), part of the SP goes like this

....WHERE edu IN ('Pre-sch', 'Sec & Post Sec')

Need your advise on this.

Thanks a bunch.

If it inserts rows, I suspect it should be setup as a "Target" rather than as "Transform". What is your job design?

Thanks Craig for your response. The job design with a single STP stage. While the stored procedure defined insert into <table>.

... then there's no way the SP could SELECT rows, is there?

Just found that the commit command in sp is placing at the wrong line. Should have commit before 'END' of procedure.

A useful note to take