Home » SQL & PL/SQL » SQL & PL/SQL » dbms_scheduler and program_argument_type
dbms_scheduler and program_argument_type [message #286868] Mon, 10 December 2007 05:19 Go to next message
haucki
Messages: 3
Registered: December 2007
Junior Member
Hi all,

if have this procedure:
procedure t_calc(
p_f_rec in out nocopy f_record,
p_calc in char,
p_gl_tv in tv_table,
p_n_tv in tv_record,
p_date in number,
p_schl_art in varchar2,
p_dschl_ober in varchar2,
p_schl_gruppe in varchar2,
p_zeit in zt,
p_table in varchar2
)

I want this procedure to schedule, but when i set the Programargumenttype or job_argument type i get an error false type.

the f_record is a recordset.
How is it possible, to start a job with recordset or own recordtype.


thx
Re: dbms_scheduler and program_argument_type [message #286897 is a reply to message #286868] Mon, 10 December 2007 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OUT parameter in a schedule job is meaningless.
How can you retrieve this value?

Regards
Michel
Re: dbms_scheduler and program_argument_type [message #286902 is a reply to message #286868] Mon, 10 December 2007 06:56 Go to previous messageGo to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Something like this should do the trick:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'orafaq_example'
  , job_type        => 'PLSQL_BLOCK'
  , job_action      => 'DECLARE
                          ..
                        BEGIN 
                          t_calc( p_f_rec, p_calc, p_gl_tv, p_n_tv, p_date
                                , p_schl_art, p_dschl_ober, p_schl_gruppe
                                , p_zeit, p_table ); 
                        END;'
  , start_date      => TO_DATE('11-DEC-2007 06','DD-MON-YYYY HH24')
  , repeat_interval => NULL
  , end_date        => NULL
  , enabled         => TRUE );
END;
/
;

But as Michel said you won't be able to retrieve the OUT parameter.

Regards
Andy
Re: dbms_scheduler and program_argument_type [message #286907 is a reply to message #286897] Mon, 10 December 2007 07:04 Go to previous messageGo to next message
haucki
Messages: 3
Registered: December 2007
Junior Member
i want to do this:
dbms_scheduler.create_job( job_name => 'testcalc', job_type => 'stored_procedure', job_action => 't_calc', number_of_arguments => 8, enabled => false);

dbms_scheduler.set_job_argument( job_name => 'testcalc', argument_position => 1, argument_value => frecord);

frecord is following type
type f_record is record(
f_id fall.f_id %type,
f_to_zustpi fall.f_to_zustpi %type,
f_endsach_dst fall.f_endsach_dst %type,
f_to_gmd fall.f_to_gmd %type,
f_to_distrikt fall.f_to_distrikt %type,
f_strtschl fall.f_strtschl %type,
f_stamp fall.f_stamp %type,
f_berichtsdat fall.f_berichtsdat %type,
);

but when i would set the job_argument, i get an error, false type. My Procedure needs the f_record type.
Re: dbms_scheduler and program_argument_type [message #286918 is a reply to message #286902] Mon, 10 December 2007 07:36 Go to previous messageGo to next message
haucki
Messages: 3
Registered: December 2007
Junior Member
hi amcghie

i tried this
procedure agg_tv(
    p_fall_rec        in out nocopy update_stat.fall_record,
    p_calc            in char, 
    p_zeitraeume      in update_stat.zeitraeume
  ) as
            
    
    
    v_n_tv          tv_table;
    delikt_obergruppen              obergruppen_table;
    dst_obergruppen                 obergruppen_table;
    endsb_obergruppen               obergruppen_table;
    gmd_obergruppen                 obergruppen_table;
    
    
    v_jobname varchar2(30);
    v_jobaction varchar2(32767);
    
  begin
    
    
    ermittle_neue_tv(p_fall_rec.f_id, v_n_tv);
    
    if v_n_tv.count > 0 then
      
      
      
      delikt_obergruppen    := funktionen.get_delikt_obergruppen(p_fall_rec.f_strtschl,    p_fall_rec.f_berichtsdat);
      
      gmd_obergruppen       := funktionen.get_gmd_obergruppen   (p_fall_rec.f_to_gmd,      p_fall_rec.f_berichtsdat);
      
      dst_obergruppen       := funktionen.get_dst_obergruppen   (p_fall_rec.f_to_zustpi,   p_fall_rec.f_berichtsdat);
      
      endsb_obergruppen     := funktionen.get_dst_obergruppen   (p_fall_rec.f_endsach_dst, p_fall_rec.f_berichtsdat);
      
              
      for neuer_tv in v_n_tv.first .. v_n_tv.last loop
        
     dbms_scheduler.create_job( job_name => 'testcalc'  ,
     					job_type => 'plsql_block',
     					job_action => 'begin
     									agg_taeter('||p_fall_rec||');
     									end;',
     					enabled => true
     									); 
    
  
      end loop;
    
    end if;

  end agg_tv;
  
  
  procedure agg_taeter(
    p_fall_rec                in out nocopy update_stat.fall_record,
    
  );
  
  
  create or replace type obergruppen_table as
table of varchar2(32767);

 type tatverdaechtiger_record is record(
    tv_id                 tvvd.tv_id                %type, 
    tv_t_id               tvvd.tv_t_id              %type, 
    tv_pers_einstelldat   tvvd.tv_pers_einstelldat  %type, 
    f_to_zustpi           fall.f_to_zustpi          %type, 
    f_endsach_dst         fall.f_endsach_dst        %type, 
    f_to_gmd              fall.f_to_gmd             %type, 
    f_to_distrikt         fall.f_to_distrikt        %type,
    tv_tgeschlecht        tvvd.tv_tgeschlecht       %type,
    t_bsi                 tv.t_bsi                  %type,
    f_tz_ende_yyyy        fall.f_tz_ende_yyyy       %type,
    f_tz_ende_mm          fall.f_tz_ende_mm         %type,
    f_tz_ende_dd          fall.f_tz_ende_dd         %type,
    f_tz_ende_hh          fall.f_tz_ende_hh         %type,

  );
  
  type tv_table is table of tatverdaechtiger_record index by binary_integer;
  
  type fall_record is record(
    f_id                  fall.f_id                 %type,
    f_to_zustpi           fall.f_to_zustpi          %type, 
    f_endsach_dst         fall.f_endsach_dst        %type, 
    f_to_gmd              fall.f_to_gmd             %type, 
    f_to_distrikt         fall.f_to_distrikt        %type
  );

When i compile i get an PLS-00306 Error False type ;
when if type p_fall_rec.f_id i get no Errors, but i need the whole recordset.

i tried this with an schedule.programm. the same problem.
the procedure only make inserts/updates in a table

[Updated on: Mon, 10 December 2007 08:50]

Report message to a moderator

Re: dbms_scheduler and program_argument_type [message #286925 is a reply to message #286918] Mon, 10 December 2007 08:20 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Previous Topic: Oracle Analytic Function Issue
Next Topic: Can we find date/dates based on ..... ?
Goto Forum:
  


Current Time: Sun Dec 04 19:04:36 CST 2016

Total time taken to generate the page: 0.06378 seconds