Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Unanswered: Oracle Stored Procedure and Datetime Parameter

    Hello again experts,

    I need help passing a datatime parameter from an oracle stored procedure to a crystal report. I seem to be able to get the date portion correct but it is not returning data based on the time portion. Here is my SP.
    Code:
    CREATE OR REPLACE PACKAGE BODY test 
    AS
      PROCEDURE p_test(StartDate IN DATE,
                                EndDate IN DATE,
                                results_cursor IN OUT CURSOR_TYPE) 
      IS
    BEGIN
        OPEN results_cursor FOR   
    SELECT * from tablex where Date1 >= to_date(StartDate, 'yyyy-mm-dd hh:mi:ss AM' ) and Date1 <= to_date(EndDate, 'yyyy-mi-dd hh:mm:ss AM');
     END;
    END;
    Now I connect this procedure in crystal reports XI and I get prompted for a StartDate and it says Please Enter DateTime in format "yyyy-mm-dd hh:mi:ss". If I choose 2007-07-09 00:00:00 and EndDate = 2007-07-09 00:00:00 I get all data from 2007-07-09. Why? Also, how does crystal know whether it is am or p.m.? My data is not being filtered by the time. Better yet does this look right to everyone or have I missed something? Thanks again.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You're already using DATE parameters, why are you type casting the parameters again inside your procedure ? Leave that out.

    Perhaps hh:mi:ss for crystal is militarry time (which is 24h based) reason it doesn't need AM or PM..

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    Thanks JMartinez,

    So your saying to just use
    Code:
    SELECT * from tablex where Date1 >= StartDate and Date1 <=EndDate;
    I tried this also with the same result.

    Any other ideas?
    Last edited by MrhelpMe; 07-13-07 at 17:16.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Can you confirm the procedure is working as expected outside of Crystal Reports, i.e. test it on the command line? Otherwise it seems like there are too many unknowns to deal with.

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    William R

    Thanks for the reply and sorry for the delay. I was in meetings where now the requirements have changed. Using the same select
    Code:
    select * from tablex....
    How would I allow the report to first run, then store the run date and use that run date when it runs again? So basically when the report runs the next time, it processes all records from the “last time ran” timestamp until present. Is there anyway to do this with oracle?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could tore results of report runs into a table and add additional WHERE condition to the report query which will evaluate the "last time ran", something like
    Code:
    SELECT * FROM tablex
    WHERE some_condition
      AND date_column > (SELECT MAX(last_time_run) FROM report_runs
                          WHERE report_name = 'this_report_name'
                         );

  7. #7
    Join Date
    Apr 2006
    Posts
    140
    Hi little foot,

    Sorry what is Last_time_run and report_runs. I don't have a table that stores the report name nor the last_time_run. Could you explain this a bit more? Are you telling me that I should create an additional table or something? Is this not possible with current tables?

    So for instance
    Code:
    select a.name,a.fname, a.lname
    from table1 a
    Join table 2 b on
    a.fname = b.fname
    I want to run this and store the last time I ran this query and then run the results from the last time I ran the query. Thanks again.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, my idea was to create additional table. If you don't store date (and time) when you last ran the report, well ... you can't know when it was!

    Unless it is a 24/7 application which might store this value into a GLOBAL VARIABLE and use it all the time. However, as soon as you close the application, this value will be lost and you'll have to start from the beginning (i.e. set the initial value once again and it'll be valid for this session).

  9. #9
    Join Date
    Apr 2006
    Posts
    140
    Hmmm so everytime I run the report I will need to update the table field DateTime. Ahh that stinks Is there anyway to automate this as much as possible? For instance, when it runs could I automatically insert a date time into this additional table somehow so that I will never have to do anything except create the additional table? Is there anyway to do this with oracle 9.2? Sorry for all the questions littlefoot.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If the report is written using the Oracle Reports Builder (ah, now I checked - it is not; you use Crystal Reports) ... but nevertheless: Reports Builder offers several triggers. I believe that Crystal Reports also *must* have something like that - for example, you could use AFTER PARAMETER FORM trigger (in Reports Builder, of course) which would update, or even insert a new record into the newly created "reports audit" table.

    Could you check whether there's something similar in Crystal Reports?

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    You could set up another function in Oracle, that you always call in your WHERE clause. It has to be set up as an autonomous_transaction, since it's running DML. And, if the report fails for some reason, after it's successfully run the SQL, then this would be a faulty design (maybe use a trigger which deletes that last record).

    In any case, here's an example
    Code:
    create table report_log (report_name varchar2(10), date_run date);
    
    create or replace function log_report_run (p_report_name varchar2, p_date_run date) return number
    as
      pragma autonomous_transaction;
    begin
      insert into report_log values (p_report_name, p_date_run);
      commit;
      return 1;
    exception when others then
      return 0;
      raise;
    end log_report_run;
    
    select *
    from dual
    where log_report_run('test',sysdate)=1;
    --=Chuck

  12. #12
    Join Date
    Apr 2006
    Posts
    140
    Chuck_forbes thank you so much for this. I tried to compile the function and I am getting a warning
    Code:
    plw-06002: Unreachable Code
    Should I be concerned? I am using oracle 9.2. Thanks again for your help.

  13. #13
    Join Date
    Apr 2006
    Posts
    140
    Also, what exactly is this code doing. How will it dump p_repory_name and p_date_run into this table.....Sorry this is a great piece of code but I am new to pragma and autonomous.

  14. #14
    Join Date
    Dec 2003
    Posts
    1,074
    I changed my mind. First, "pragma autonomous_transaction" will allow a FUNCTION or PROCEDURE to execute as if it were in a different user session. In my example, it's necessary to do this, as Oracle doesn't allow you to call a FUNC/PROC from a SQL statement when DML is performed in that FUNC/PROC.

    The more I think about it, though, I think that's probably a bad idea, for 2 reasons:

    1) If your Crystal report successfully executes the SQL, the record for that SQL's execution would be written & committed to the table report_log. What happens if your report hits another coding error before it successfully ends? You would have to write some code which went in and deleted that record.

    2) The FUNC I wrote is writing to a table, where that table is referenced in the same WHERE clause. In 10.1.0.3, it looks like the record being written to the table isn't visible to the where clause asking for the MAX(DATE_RUN). The effect is perhaps better displayed by seeing a query like:
    Code:
    select *
    from report_log
    where log_report_run('test',sysdate) = 1
    I don't know if different versions of Oracle would mask the recently added value or not. You'd think that they would, but you never know.

    I'd go back to Littlefoot's suggestion of picking the last possible step in the Crystal report where you can call an Oracle FUNC, and write a FUNC like the one I suggested, but without the "pragma autonomous_transaction". It wouldn't be necessary then (unless you're still wanting to call it via SQL - which it's sometimes easier to execute SQL in code in a tool than it is to call a FUNCTION).

    --=cf

  15. #15
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Chuck that's for the legthy explaination. So just so I understand I have created a function like below minus the pragma
    Code:
    CREATE OR REPLACE FUNCTION log_report_run (p_report_name varchar2, 
                                               p_date_run date) return number
    is
    begin
      insert into report_log values (p_report_name, p_date_run);
      commit;
      return 1;
    exception when others then
      return 0;
      raise;
    end;
    Now I still don't see how this table will get updated everytime I run the query/report. I'm assuming I should be saying something like littlefoot suggested but with your function as follow's but have no idea on how to implement this. Am I misunderstanding. Your help is appreciated.
    Code:
    AND date_column > (SELECT MAX(last_time_run) FROM report_runs
                          WHERE report_name = 'this_report_name');
    Also, I am still getting the warning PLW-06002: Unreachabale code which I have no idea what that means.

Posting Permissions

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