1 2 3 Previous Next 36 Replies Latest reply on Dec 2, 2013 9:17 AM by Billy~Verreynne

    How to execute a proc and spool files in a database job

    imation3m

      Database is oracle 11g

       

      I have a plsql procedure that takes one date parameter and generates some data in two different tables.

      Then I need to query these two tables based on the same date parameter and spool in two different files and send these files to specified email address.

       

      I want to schedule this whole process as a database job which will run daily except holidays.

       

      Can I get some guidance on how best to do this?

       

      How can i call a database procedure as well as spool file in a database job?

        • 1. Re: How to execute a proc and spool files in a database job
          Mike Kutz

          well, start by creating a package to hold everything in one spot.

          One of the procedures will be a 'run them all' process which:

          1. call the procedure you mentioned
          2. call a procedure that creates a file from table 1
          3. call a procedure that creates a file from table 2
          4. call a procedure that sends the two files via e-mail
          5. calculate 'next date/time' to run

           

          For 2 and 3, search this form for "how do I create a CSV file?"

          For 4, many people use UTL_MAIL or UTL_SMTP.  Personally, APEX_MAIL may be easier to use since it has functionality to add attachments already.

          For 5, you may need to have a table in the database that describes which days are holidays...

          2,3,4, will be other procedures in the package while 5 will be a function

           

          The 'run them all' procedure will have a spec like this:

          procedure run_them_all( p_next_date in out date );

           

          Then you just run that through DBMS_JOB like that...

           

          note : the HOWTO for each one of the above steps can be googled...

          • 2. Re: How to execute a proc and spool files in a database job
            imation3m

            Can I do it like this:

             

            1. Make one script file 1.sql and call my procedure in it.
            2. Make another script file 2.sql can put my select queries and spool into files.

             

            Then in the database job, I just call 1.sql and then 2.sql

             

            Is it possible?

             

            Note: Ill put all the date logic and holiday logic in my basic procedure.

            • 3. Re: How to execute a proc and spool files in a database job
              Billy~Verreynne

              Creating external filesystem files from database data, from inside the database?

               

              Wrong. Most of the time.

               

              Creating an external file as a SQL script, and then execute that from inside the database?

               

              Wrong. All of the time. (with the exception of Apex app exports/imports)

               

              I would use a package to encapsulate code for creating CSV "files" (as database CLOBs) from a SQL cursor (pass a ref cursor, code changes this to a DBMS_SQL cursor, describes the cursor, and transforms cursor output to CSV).

               

              I would use a package that provides the ability to send e-mail with CLOB attachments, from inside the database (using UTL_SMTP).

               

              Now that the building blocks exist for a robust, maintainable, reusable, and flexible solution, I would create a business solution package that creates the requires SQL report query, transforms the output to a CSV CLOB, and then mail it to the required recipients. And this package would be well instrumented.

               

              And I would maintain that if this is not your approach to building a solution for business, you are not a programmer and fail to understand the basic concepts of how to engineer software.

              • 4. Re: How to execute a proc and spool files in a database job
                imation3m

                Ok thanks, ill work on creating a package.

                If any thing I get stuck with ill get back in here.

                 

                Thanks again.

                • 5. Re: How to execute a proc and spool files in a database job
                  imation3m

                  Ok I created the following two procedure and called them in a package:

                   

                  1 - Main procedure

                  2 - Generate file as CLOB object and return this object

                   

                  But when I am calling this 2 procedure in my package, it is throwing an error:

                   

                  "Error(14,36): PLS-00103: Encountered the symbol "," when expecting one of the following:     in out <an identifier> <a double-quoted delimited-identifier>    ... long double ref char time timestamp interval date binary    national character nchar "

                   

                  Package is as follows:

                   

                  CREATE OR REPLACE PACKAGE BCSB_CONSUMER_PKG AS

                   

                   

                    V_CNCF_CLOB CLOB;

                    V_CNCS_CLOB CLOB;

                   

                    --=============================================================================================

                    -- CALL P_BCSB_CONSUMER TO GENERATE DATA

                    --=============================================================================================

                    PROCEDURE P_BCSB_CONSUMER ;

                   

                    --=============================================================================================

                    -- CALL P_GEN_FILES TO GENERATE CSV FILES AS CLOB OBJECTS

                    --=============================================================================================

                    PROCEDURE P_GEN_FILES(V_CNCF_CLOB, V_CNCS_CLOB);

                   

                  END BCSB_CONSUMER_PKG;

                  • 6. Re: How to execute a proc and spool files in a database job
                    Billy~Verreynne

                    I think the approach I am proposing, is best illustrated with sample code. Thus I did a very basic Cursor-to-CSV package below. A similar package is needed to create an e-mail with a CLOB as plain text attachment, and then sending this to a SMTP server. And then a business package that will use these 2 interface packages to create CSV files and e-mail these as attachments.

                     

                    SQL> create or replace package CsvLib authid current_user as
                      2 
                      3          ENCLOSED_BY             varchar2(1) := '"';
                      4          COLUMN_SEPARATOR        varchar2(1) := ',';
                      5 
                      6          procedure CursorToCSV( c in out sys_refcursor, csv in out clob );
                      7 
                      8  end;
                      9  /

                    Package created.

                    SQL>
                    SQL> create or replace package body CsvLib as
                      2 
                      3  -- ----------------------------------------------------------------------------
                      4  -- writes a line to csv "file" with a linefeed
                      5  procedure WriteLn( csv in out clob, line varchar2) is
                      6          LF      constant varchar2(2) := chr(10);        -- linux/unix lf
                      7  begin
                      8          dbms_lob.WriteAppend( csv, length(line||LF), line||LF );
                      9  end;
                    10 
                    11 
                    12  -- ----------------------------------------------------------------------------
                    13  -- adds tokens/columns to a csv line of data
                    14  function MakeLine( line varchar2, token varchar2, lastToken boolean default false ) return varchar2 is
                    15          newLine varchar2(32767);
                    16  begin
                    17          newLine := line||ENCLOSED_BY||token||ENCLOSED_BY;
                    18 
                    19          if not lastToken then
                    20                  newLine := newLine || COLUMN_SEPARATOR;
                    21          end if;
                    22 
                    23          return( newLine );
                    24  end;
                    25 
                    26 
                    27 
                    28  -- ----------------------------------------------------------------------------
                    29  -- converts ref cursor output into a csv "file"
                    30  procedure CursorToCSV( c in out sys_refcursor, csv in out clob ) is
                    31          colList dbms_sql.Desc_Tab;
                    32          colCnt  number;
                    33          cur     number;
                    34          rc      number;
                    35          line    varchar2(32767);
                    36          colVal  varchar2(4000);
                    37  begin
                    38          -- convert ref cursor handle to a cursor number
                    39          cur := dbms_sql.To_Cursor_Number( c );
                    40 
                    41          -- do a describe of the SQL projection
                    42          dbms_sql.describe_columns( cur, colCnt, colList );
                    43 
                    44          -- we are treating all columns in the SQL projection
                    45          -- of the cursor as varchar2(4000) strings
                    46          for i in 1..colCnt loop
                    47                  dbms_sql.Define_Column( cur, i, colVal, 4000 );
                    48          end loop;
                    49 
                    50          -- write column names to csv as header
                    51          for i in 1..colCnt loop
                    52                  line := MakeLine( line, colList(i).col_name, i=colCnt );
                    53          end loop;
                    54          WriteLn( csv, line );
                    55 
                    56          -- process cursor output
                    57          while dbms_sql.fetch_rows( cur ) > 0
                    58          loop
                    59                  line := null;
                    60                  for i in 1..colCnt loop
                    61                          dbms_sql.column_value( cur, i, colVal );
                    62                          line := MakeLine( line, colVal, i=colCnt );
                    63                  end loop;
                    64                  WriteLn( csv, line );
                    65          end loop;
                    66 
                    67          dbms_sql.Close_Cursor( cur );
                    68  end;
                    69 
                    70 
                    71  end;
                    72  /

                    Package body created.

                    SQL>
                    SQL> -- client using PL/SQL API (e.g a business process package)
                    SQL> --
                    SQL> set long 99999
                    SQL> -- define client (host) variables
                    SQL> var refcur refcursor
                    SQL> var csv clob
                    SQL>
                    SQL> -- opens the cursor
                    SQL> exec open :refcur for select * from emp order by 1;

                    PL/SQL procedure successfully completed.

                    SQL> -- create the clob for use as CSV file
                    SQL> exec DBMS_LOB.CreateTemporary( :csv, true );

                    PL/SQL procedure successfully completed.

                    SQL>
                    SQL> -- calls the API
                    SQL> exec CsvLib.CursorToCSV( c => :refcur, csv => :csv );

                    PL/SQL procedure successfully completed.

                    SQL>
                    SQL> -- processes the CSV clob returned (e.g. call e-mail package to mail it)
                    SQL> print :csv

                    CSV
                    --------------------------------------------------------------------------------
                    "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
                    "7369","SMITH","CLERK","7902","1980/12/17 00:00:00","800","","20"
                    "7499","ALLEN","SALESMAN","7698","1981/02/20 00:00:00","1600","300","30"
                    "7521","WARD","SALESMAN","7698","1981/02/22 00:00:00","1250","500","30"
                    "7566","JONES","MANAGER","7839","1981/04/02 00:00:00","2975","","20"
                    "7654","MARTIN","SALESMAN","7698","1981/09/28 00:00:00","1250","1400","30"
                    "7698","BLAKE","MANAGER","7839","1981/05/01 00:00:00","2850","","30"
                    "7782","CLARK","MANAGER","7839","1981/06/09 00:00:00","2450","","10"
                    "7788","SCOTT","ANALYST","7566","1987/04/19 00:00:00","3000","","20"
                    "7839","KING","PRESIDENT","","1981/11/17 00:00:00","5000","","10"
                    "7844","TURNER","SALESMAN","7698","1981/09/08 00:00:00","1500","0","30"
                    "7876","ADAMS","CLERK","7788","1987/05/23 00:00:00","1100","","20"
                    "7900","JAMES","CLERK","7698","1981/12/03 00:00:00","950","","30"
                    "7902","FORD","ANALYST","7566","1981/12/03 00:00:00","3000","","20"
                    "7934","MILLER","CLERK","7782","1982/01/23 00:00:00","1300","","10"

                    SQL>

                     

                    Finally, the business package is scheduled to run in the background (as a DBMS_JOB or DBMS_SCHEDULER process) as required.

                     

                    No external moving parts. PL/SQL only. Which means this solution will work on Oracle-on-Windows, Oracle-on-Linux, Oracle-on-Solaris, Oracle-on-AIX, and so on.

                    • 7. Re: How to execute a proc and spool files in a database job
                      Mike Kutz

                      Billy

                      Nice package.

                      FYI -- The AskTom site has one that handles various NLS settings....but it saves it to a BFILE instead of a CLOB.

                      http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteflat.html

                       

                      imation3m

                      you forgot to declare what type of data the paramaters are for the procedure.

                      --=============================================================================================

                        -- CALL P_GEN_FILES TO GENERATE CSV FILES AS CLOB OBJECTS

                        --=============================================================================================

                        PROCEDURE P_GEN_FILES(V_CNCF_CLOB in out clob, V_CNCS_CLOB in out clob);

                      • 8. Re: How to execute a proc and spool files in a database job
                        Billy~Verreynne

                        Not much of an effort doing that package - was a quick copy-and-paste from existing sample code while waiting for a WAN connectivity problem to be solved (lost comms with our main data centre).

                         

                        There's a lot that should be added and customised - especially on the formatting side (and protecting against ref cursor leakage as 9 times out of 10 the caller will not have resource protection and close the ref cursor when the call to the lib raises an exception)

                         

                        But it does illustrate the basic I think is important - supporting bind variables (caller does binding in ref cursor, removing the need to support dynamic binding), and using CLOBs as one would use normal files (no need to step outside of Oracle and into the o/s).

                         

                        And of course - the importance of lowercase coding...

                        • 9. Re: How to execute a proc and spool files in a database job
                          imation3m

                          Thanks, the package is compiled.

                          Now only one thing left. to send email with attachments.

                           

                          Never did send any email directly from database

                           

                          Can you guys help me with that? And also I need to make the filenames of csv files like CNCF-04OCT2013 and CNCS-04OCT2013

                          • 10. Re: How to execute a proc and spool files in a database job
                            Billy~Verreynne

                            There are numerous e-mail with attachment code examples in this forum space. You will find the search button on the top right.

                            • 11. Re: How to execute a proc and spool files in a database job
                              imation3m

                              I tried the following in my procedure to send a simple plain text email for testing purpose:

                              --------------------------------------------

                              declare

                                   l_body   CLOB;

                              begin

                                  l_body := 'APEX_MAIL'||utl_tcp.crlf||utl_tcp.crlf;

                                  l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;

                                  l_body := l_body ||'  The IT Team'||utl_tcp.crlf;

                                  APEX_MAIL.SEND(

                                      p_to       => 'myemail@mycompany.com',

                                      p_from     => 'myemail@mycompany.com',

                                      P_BODY     => L_BODY,

                                      p_subj     => 'APEX_MAIL Package - Plain Text message');

                              end;

                              ----------------------------------------------

                               

                              But when I am executing this procedure, it is showing an error:

                              Error report:

                              ORA-20001: This procedure must be invoked from within an application session.

                              ORA-06512: at "APEX_030200.WWV_FLOW_MAIL", line 285

                              ORA-06512: at "APEX_030200.WWV_FLOW_MAIL", line 320

                              ORA-06512: at "APEX_030200.WWV_FLOW_MAIL", line 362

                              ORA-06512: at "MIBS.P_BCSB_PROCESS", line 147

                              ORA-06512: at line 2

                              • 12. Re: How to execute a proc and spool files in a database job
                                imation3m

                                I checked with my DBA and APEX_MAIL is not configured so I have to use UTL_MAIL instead.

                                • 13. Re: How to execute a proc and spool files in a database job
                                  Billy~Verreynne

                                  UTL_MAIL does not support CLOBs/BLOBs - it only supports 32KB string. So if your CLOB converts to a string size larger than 32KB, UTL_MAIL will fail.

                                  • 14. Re: How to execute a proc and spool files in a database job
                                    imation3m

                                    What can I do then? Any other alternative?

                                    1 2 3 Previous Next