Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: Help required for calling an SP from a scheduled job

    I have the following code in a job scheduled to import data on to a table from a flat file, I am experiencing a problem ie, the SP called at the end of the code is not working at all. otherwise the job works fine, if the same code with calling SP is run at the SQL> prompt everything including the called SP is working OK, I have even tried the exec command with the SP.
    Is there is a way to run a second job as soon as the first job is over. ie ON success/failure of the first job the second has to fire.

    Thanks in advance

    begin
    create or replace directory data_dir as 'd:\db\';
    drop table op_macdetails;
    create table op_macdetails (
    md_ma number(10),
    md_im varchar2(8),
    md_mo number(10),
    md_sno varchar2(18),
    md_actdate date
    )
    organization external
    (
    type oracle_loader
    default directory data_dir
    access parameters
    (
    records delimited by newline skip 1
    nologfile
    fields terminated by '|'
    optionally enclosed by '"' LRTRIM
    (md_ma,md_im,md_mo,md_sno,md_actdate date "dd-mon-yyyy")
    )
    location('macdetails.csv')
    );
    mcheck;
    end;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you've said that this code works fine at the SQL*Plus prompt, I believe that you didn't quite understand what is going on.

    This code works but not as a PL/SQL procedure (encapsulated between BEGIN and END), but every command for itself:
    SQL> CREATE directory -> OK
    SQL> DROP TABLE -> OK
    SQL> CREATE TABLE -> OK
    SQL> mcheck -> no way - you'd need "EXEC(ute) mcheck" to run the procedure

    But, PL/SQL doesn't support running DDL's that way; if you insist on doing that, you'll need to use EXECUTE IMMEDIATE (search for its use).

    However, are you sure you want to do that? What's the point in creating a directory, dropping and recreating the table in a batch job? It doesn't make much sense (at least, not until you explain why are you trying to do that).

    Directory is to be created only once, external table as well. Also, directory is to be created by a privileged user (SYSTEM, for example), and it has to GRANT READ and WRITE privileges to use which uses external table. External table should be created by another user (the one which, actually, works with the 'macdetails.csv' table and data stored in it).

    Finally: do you, actually, know what you are doing and why?

  3. #3
    Join Date
    Feb 2006
    Posts
    5
    Littlefoot thanks for your reply.

    This point is correct.
    SQL> mcheck -> no way - you'd need "EXEC(ute) mcheck" to run the procedure

    What I am trying to do is, a flat file is expected to be kept at a location on a daily basis, I have to upload the details in the flat file onto a table (kind of temporary table) from where the details will go to another table via another stored procedure which is what I am trying to invoke from the job. the create and drop is meant only to use it as temporary table ie once the data reach the second table, it is no longer required on the first hence the drop. I am not sure is this the best way. I got the code for using the loader from the net only,


    I will explore the execute immediate and get back.

    Bye

    Sherry

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, dropping and recreating a table is not a good way. Create table only once, fill it with data and - when no longer needed - truncate the table. Or, you could perhaps use a Global Temporary Table (search for this term).

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    An external table occupies NO space in the database. If your data in the external table is going to be replace every day, simply over lay the existing flat file with the new data and it will be referenced by the existing external table definition (assuming that the flat file is laid out the same each time). Do not replace the external table definition every time.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Feb 2006
    Posts
    5
    beilstwh, Thanks a lot, now i got a better picture about external tables, now all I have to call is invoking the second procedure to take data from external table to main table.

    Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •