Home » SQL & PL/SQL » SQL & PL/SQL » DON"T CHANGE DATE
DON"T CHANGE DATE [message #188016] Wed, 16 August 2006 14:40 Go to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
Hey everybody. I want to amend the script below so that is the document is reprinted, the printed_Date remains the same.

DECLARE
  Cursor c_p IS
  SELECT printed_date from document where
  Doc_type_no = 8
  AND assess_no = :assess_no
  AND doc_State_no = 2
  and tax_payeR_no = :assess.tax_payeR_no;
 
BEGIN

  OPEN c_p;
  FETCH c_p INTO :date_printed;
  CLOSE C_p;
END;

    next_record;
  END LOOP;
  go_record(1);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  WHEN OTHERS THEN
    CGTE$OTHER_EXCEPTIONS;
END;
Re: DON"T CHANGE DATE [message #188018 is a reply to message #188016] Wed, 16 August 2006 14:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How does the code determine if this is a "reprint" or not?
Re: DON"T CHANGE DATE [message #188019 is a reply to message #188018] Wed, 16 August 2006 14:46 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
Not sure. Maybe is the date_printed field is not null?
Re: DON"T CHANGE DATE [message #188020 is a reply to message #188019] Wed, 16 August 2006 15:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is your database, your tables and your data. If you don't know it, how are we supposed to know the answer?

Yet another question: it is possible to keep track of reports that you've ran (for example, in a table that contains report name and date it was executed). But it is impossible to know whether the report was actually sent and successfully printed on the printer. This should be done by a person who prints reports - "After you have a hard copy in your hands, check this checkbox in order to show everyone that this report was printed".

Once you have that information stored in a table, it shouldn't be a problem to do whatever you want with report fields.
Re: DON"T CHANGE DATE [message #188203 is a reply to message #188020] Thu, 17 August 2006 07:38 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
First of all it is a database that we use at work and I am fairly new to Oracle, that why I ask questions in the PL/SQL for Newbies forum.

Now, I was thinking of adding piece of code so that if the report is reprinted, the printed_date field won't change. So it should go something like if the printed_date field is not null, do not change it, else put the current date. I just don't know how to add it in the already created script above.

Thanks.
Re: DON"T CHANGE DATE [message #188205 is a reply to message #188203] Thu, 17 August 2006 07:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm slightly confused (common occurrence)
The code you posted won't change the data in the database at all.

I'll guess that this a misplaced Forms question, in which case you could try replacing
  OPEN c_p;
  FETCH c_p INTO :date_printed;
  CLOSE C_p;
with
  IF :date_printed IS NOT NULL THEN
    OPEN c_p;
    FETCH c_p INTO :date_printed;
    CLOSE C_p;
  END IF;

which should solve the problem
Re: DON"T CHANGE DATE [message #188209 is a reply to message #188203] Thu, 17 August 2006 07:53 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Code you provided is incomplete and doesn't make much sense; why would you want to use a cursor when you only open - fetch - close it? It *should* be used for collection of data, not a single record.

Besides that, there's record navigation (which record? Is this code taken from a form?) which does ... what?

Perhaps such a function might help ... see whether you can use it somewhere (and how) or not.
CREATE OR REPLACE FUNCTION fun_print_date (par_report_id IN NUMBER)
RETURN DATE
IS
  l_printed_date DATE;
BEGIN
  SELECT printed_date
    INTO l_printed_date
    FROM document
    WHERE report_identifier = par_report_id;
    
  RETURN (l_printed_date);
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN (sysdate);
END; 
Previous Topic: pcl commands
Next Topic: Parent/Child Request trying to incombaitibly lock object
Goto Forum:
  


Current Time: Fri Dec 09 17:33:49 CST 2016

Total time taken to generate the page: 0.26907 seconds