Error in calling user defind PL/SQL

nkln@you · Post by **nkln@you** » Mon Jan 21, 2008 2:08 pm

Hi,

I am trying to call the below statement from the Procedure stage. I have not given any procedure name and generate procedure call option is cleared

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SVCS',TABNAME=>'STG_COST',estimate_percent => dbms_stats.auto_sample_size,CASCADE=>TRUE);
END;

This statement is used just to collect the statistics of the table. But its giving the error ""STDPROC property required for stage "Test1.Stored_Procedure_29"""

here Test1 is the job name. I have not defined the input and output parameters and the procedure type is set to 'Transform'. In the input stage, i have checked the 'Forward Row Data' option.

Please let me know where i am going wrong.

chulett · Post by **chulett** » Mon Jan 21, 2008 2:26 pm

Input links but no output links means you should set the Procedure Type to Target it seems to me. That and I'm not sure that you can get away without defining any Input parameters, like column names for example. You may find that you'll need to pass in some of those data elements like owner and table name as columns, then bind them into the anonymous block.

Actually, upon reading more closely, why have any output link and forward the data? Does something happen downstream of this stage in your job?

nkln@you · Post by **nkln@you** » Mon Jan 21, 2008 2:41 pm

It has both input and output links. But i dont have any parameter defined as the PL/SQL doesnt need any.

The job does a simple insert/update but i need to collect the statistics of the index so that update query is run faster. For this reason, i need to run this statement in between the job.

chulett · Post by **chulett** » Mon Jan 21, 2008 3:36 pm

Of course the stats package needs parameters, you've got four defined in the anonymous block. What I'm saying is that while you feel it may not "need" them because they are not dynamic, DataStage will feel the need for them to exist in some fashion. You'll need at least one Input and Output column to satisfy the requirements of the stage - even if it is a 'dummy' column.

And then you're going to find it will want them bound into the call. Simple enough if you 'hard code' them to something simple like the owner name or table name as I mentioned earlier.

nkln@you · Post by **nkln@you** » Mon Jan 21, 2008 3:52 pm

Can you re post the answer... I am not a premium member, so am not able to see the full reply.

But from whatever i have gathered, the four parameters i have hardcoded. So do i still need to define in procedure stage?

ray.wurlod · Post by **ray.wurlod** » Mon Jan 21, 2008 7:46 pm

Even if Craig re-posts the answer it will still be a premium post. Premium memberships are one of the ways that the hosting and bandwidth costs incurred by DSXchange are met. Corporate discounts for multiple memberships are available (see DSXchange home page).

nkln@you · Post by **nkln@you** » Mon Jan 21, 2008 9:00 pm

Can you Please answer the query raised. How to make that statement run in the procedure stage.

chulett · Post by **chulett** » Mon Jan 21, 2008 11:53 pm

Curious how you are listed as a Premium Member and yet you state you aren't... how did that happen?

chulett · Post by **chulett** » Mon Jan 21, 2008 11:58 pm

Hard to understand what you have or don't have defined in the stage at this point. Do you have any columns in your input and output links?

nkln@you · Post by **nkln@you** » Tue Jan 22, 2008 12:40 am

chulett wrote:Hard to understand what you have or don't have defined in the stage at this point. Do you have any columns in your input and output links? ...

Yeah. I have input and output links. But the error is not something related to the procedure statement i guess. I tried running the job by giving the wrong user name in the procedure stage and it still gave the same error. that means before connecting to database itself, its finding something wrong. Will post again what option i have set.
Generate Procedure call: cleared
Procedure Type: Transform
Forward row data in the input stage: checked
output stage: both the options cleared
parameters: not entered anything
error codes: nothing entered

Error code I am getting: STDPROC property required for stage "Test1.Stored_Procedure_29

chulett · Post by **chulett** » Tue Jan 22, 2008 7:46 am

I did not ask if you have input and output links, but rather what columns you have in them. And make sure something is in the Syntax / Procedure Name field, even if it isn't "used" it needs something there.

kcbland · Post by **kcbland** » Tue Jan 22, 2008 8:03 am

Sorry to bug in on this post, but if you're needing to run a SP prior to the DML in an Oracle OCI stage, why not just put this anonymous block into the before-SQL tab and be done with it?

chulett · Post by **chulett** » Tue Jan 22, 2008 8:04 am

Oh, come on Ken - why take all the fun out of it?

kcbland · Post by **kcbland** » Tue Jan 22, 2008 8:30 am

Well, you know, it probably is much more fun to bang the head into the wall over and over trying to pound the square peg into the round hole.

In one line posted earlier it was mentioned that this SP is needed in order to prepare an index for a more optimized update experience. Without a doubt my preference is to run command-line SP via an elegant generic script for connecting thru sqlplus. I had that standard oft-repeated answer all ready to go and then it clicked that this is part of an OCI update. (Sigh, double sigh) Oh well, no chance convincing the OP to write that generic and reusable sqlplus script when low hanging fruit like a before-SQL tab exists.

Of course, there's always the "what's taking so long" question bound to come up once the before-SQL command hangs the job, but I can wait for that.

nkln@you · Post by **nkln@you** » Tue Jan 22, 2008 9:38 am

well.. i tried running the procedure in the before sql and t worked. but the standards in our organization says that if we have to run a procedure then we need to use the procedure stage provided by ds. so this problem.

regarding input/output columns, Yes i have them but am not using them in the procedure.Its just one to one mapping I am doing. In the output oci stage, select query and in the input oci stage, update or insert.