Error in calling user defind PL/SQL

[kcbland]

I suggest you find an example working job by the "genius" who came up with the standard because they've obviously tried it and have a working example.

[nkln@you]

Its not that i have to go for procedure stage. If there is no way we can run that statement in the procedure stage, then i will have to go back and say that DS procedure stage doesnt have the capability to run that statement and we have to go for after/before sql approach. But before that i need to know if this is possible or not.

[chulett]

It's certainly possible, we call that stats package quite a bit... but typically as a target at the end of a job stream. For 'inline' stats we stick with before/after in the ORA stage but I'd wager it could be made to work your way as well.

[nkln@you]

It's certainly possible, we call that stats package quite a bit... but typically as a target at the end of a job stream...

Can you please let me know how you call the dbms_stats even as a target. I mean the options that you set in the procedure stage. And here i am assuming that whatever statements i posted(exec dbms_stats...) is correct.

And am not able to view the Premium contents. so if possible post it as a normal content else its fine.

[chulett]

Give it a try, it's not that hard to figure out. Sounds like your first problem was because you left the Procedure Name blank, make sure you put something there.

[nkln@you]

I tried giving the name "test" in the procedure name and it actually worked!!!

Now I am able to collect the statistics of the table but a new problem came up. In the output link, just one row is getting transferred. I read the manual and came to know that for STP to transfer multiple rows, i need to check the option "Procedure returns multiple rows" and for this to work, i need to provide the"Procedure EOD return code". But I am not running any procedure. So what should i do now?

[nkln@you]

Can anyone please help me in this.

[kcbland]

What rows were you expecting from this SP? I thought it updates your indexes, why does the returned rows matter? How do you know there are more than 1 row returned?

[nkln@you]

What rows were you expecting from this SP? I thought it updates your indexes, why does the returned rows matter? How do you know there are more than 1 row returned? ...

The procedure stage is between two oci stages. One oci stage acting as an output selects records from a table and the other stage acting as an input stage updates a table. In between these two is the procedure stage where i am collecting stats for the indexes. So from the source stage, 1000 rows are coming but after the pl/sql statement(dbms_stats) is run, just one record is updated in the target table. I am expecting all rows to be just passed through this procedure stage as it is.

[chulett]

What? Who's indexes are you expecting to update stats on smack in the middle of your process?

[kcbland]

Hold on a minute. Your looks like this:

Code: Select all

OCI --> SP -->  OCI

That won't work. Not a chance. Your rows have to stream from one passive stage thru an active stage and to another passive stage. The SP stage is not a passive stage. It is a special case stage, more suited to a Sequence as it's a "do something" stage, like a Command stage. Without a doubt your situation calls for the OCI stage before-SQL.

If you want to use the SP stage, consider breaking up your job to three jobs and use a Sequence or job control run them:

Code: Select all

OCI --> XFM --> SEQ1 (to capture source data)
SP --> XFM --> SEQ2 (to capture SP result messages)
SEQ1 --> XFM --> OCI (load load source data)

[nkln@you]

Hold on a minute. Your looks like this:

Code: Select all

OCI --> SP -->  OCI

That won't work. Not a chance. Your rows have to stream from one passive stage thru an active stage and to anoth ...

Ok. I changed the job like this:

OCI_1--> TX1-->SP-->TX2-->OCI_2

OCI_1 is used to select data which is getting updated in a table in OCI_2. Now to make sure the update query uses the indexes, i wanted to collect the statistics of the table which I am trying to do in SP. Now 1000 rows are coming from OCI_1 till SP but after the SP gets executed(dbms_stats), only 1 row is transferred to TX2 & OCI_2.

So Please let me know why just one row is coming out of SP and how to make it work.

[kcbland]

Look at my example again and you will see that you did not follow what I posted. You have to load the file you read from OCI1. The act of running the SP is irrelevant to that design.

[kcbland]

In the section of your job design, where TXT_2-->OCI_2 lives, make sure that the file being READ here is the SAME filename written to in TXT_1.

My suggestion to break your job into 3 discrete jobs makes PROFOUND sense to show you what is happening.

YOU DON'T NEED AN INPUT TO THE SP STAGE AT ALL. The only purpose it serves in your current job design (which I personally think is terrible and would never do) is to coordinate activities. Coordinating activities (dump table to file, run stored procedure, load file to database) is a job control purpose, best suited to a Sequence or Batch job.

[nkln@you]

So is there no way i can make this job run i.e. collect the statistics in between two porcess. If splitting the job is the only option then i would rather split tjis job into 2 jobs and in the AfterJobSubRoutine of the first job, i would execute the same statement using ExecSH. No need to go for SP stage at all. Regarding the input to SP stage, i agree its not required. Infact the output is also not dependent on the SP stage. Its just that i wanted to collect the stats between the two oci stages.

But since i wanted to do it between two process, i thought may be SP stage might work.

Everything is wrking as of now. Its just that the no. of rows transferred to the output link is just 1.