[Solved] Calling a PL/SQL Procedure from a Jobstream

[vetteheadracer]

Is it possible to call a pl/sql procedure from a Jobstream in DecisionStream?

If so what is the syntax? Should I use a Procedure Node or a SQL Node?

[chad.ongstad]

use a procedure node.... you will need to add a sql function

  Sql(connection,sqlstatement);


SQL('SOURCEA','BEGIN <PROC NAME (PARAMETERS)>; END;');

If you need to pass in parameters from DS you will need to use the concat function to create your sql statement on the fly.

Chad

[vetteheadracer]

Thanks Chad it worked great :)

[bmilespal]

Is it possible to get Event Studio to execute a Decision Stream job?

If so, how?

[newmans_99]

Yes. From Data Manager 8.2+ you can publish Data Movement tasks into Cognos Connection. These tasks can then be added to a Job, these Jobs can be called from Even Studio.

Steve

[wendoze]

Hey Chad,

Any chance you can elaborate on "If you need to pass in parameters from DS you will need to use the concat function to create your sql statement on the fly."

I'm having probs passing a parameter, even when I copy the syntax straight from Toad. Not sure what you mean by using the concat function....?

thanks heaps.

[wendoze]

Nevermind, worked it out (eventually!).  ;)

Thanks for the tip.

If anyone is intersested, instead of the following from Toad (off Oracle):

======================
DECLARE
  V_DATASOURCE VARCHAR2(200);

BEGIN
  V_DATASOURCE := 'CFMS';

  DB.DATA_CONTROL_UPDATE_CURR_FLAG ( V_DATASOURCE );
  COMMIT;
END;

========================
I called it with this in Stored Procedure Node:

Sql('DB',
'BEGIN DB.DATA_CONTROL_UPDATE_CURR_FLAG (''CFMS'');
  COMMIT;
END;');

========================

[chad.ongstad]

If you don't want to hardcode your input parameters to your function you would need to do the following in your procedure node. This would be particulary usefull if the procedure is going to be called multiple times with different parameters. Allowing you to pass the variables through to the procedure each time you call it.

$v_Param1 := '''CFMS''';
$v_DB := 'DB';
$v_SQL := concat('BEGIN DB.DATA_CONTROL_UPDATE_CURR_FLAG (' ,$v_Param1, '); COMMIT; END;');
Sql($v_DB,$v_SQL);