Stored proc called from dstag doesn't generate rows of data

[agathaeleanor]

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.

[chulett]

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

[agathaeleanor]

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

[ray.wurlod]

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

[agathaeleanor]

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