Error in calling user defind PL/SQL
Moderators: chulett, rschirm, roy
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
Aim high
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.chulett wrote:It's certainly possible, we call that stats package quite a bit... but typically as a target at the end of a job stream...
And am not able to view the Premium contents. so if possible post it as a normal content else its fine.
Aim high
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?
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?
Aim high
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?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.kcbland wrote: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? ...
Aim high
Hold on a minute. Your looks like this:
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 --> SP --> OCI
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)
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Ok. I changed the job like this:kcbland wrote:Hold on a minute. Your looks like this:
That won't work. Not a chance. Your rows have to stream from one passive stage thru an active stage and to anoth ...Code: Select all
OCI --> SP --> OCI
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.
Aim high
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
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.
Aim high