Home » SQL & PL/SQL » SQL & PL/SQL » [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns
icon4.gif  [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns [message #188140] Thu, 17 August 2006 04:26 Go to next message
sQbanieTZ
Messages: 3
Registered: August 2006
Junior Member
Hello,
I have to write stored procedure in Oracle 8.0.6.0 which will work as fast as possible (cause the table has about 15000k records and GBs of data).

At the beginning - in the attached file there are: table definition and sample 100 records to insert.

Here's the decription of the problem:
Table HT_REJNZ works as a archive log for another table HT_ZAM. After every update in HT_ZAM there are two new records inserted into HT_REJNZ (column 'nowe' is a tag that informs which records are before and which records are after update):

Recno | <column values before update> | data_syste | nowe
n | ... | <update date> | " "

Recno | <column values after update> | data_syste | nowe
n+1 | ... | <update date> | "X"

Requirements for procedure:
1. Procedure with three parameters:
- from DATE (format DD/MM/YY)
- to DATE (format DD/MM/YY); default - current date (DD/MM/YY)
- columns_list STRING (names of column form HT_REJNZ delimited by space)
2. At the beginning values of the parameters should be checked - whether dates are right, whether to is a later date than from and the most important - whether column names in columns_list exist in HT_REJNZ. Of course procedure should exit when this requirements fail.
3. And at last general idea of this procedure: it should delete couples of records (with 'nowy' equals " " and "X") from HT_REJNZ which:
- have data_syste value between from and to
AND
- where there is no change in values of all columns EXCEPT 'nowy', 'recno' and columns passed by as parameters to this procedure.

Examples:
Records in HT_REJNZ table:

recno | columnA | columnB | columnC | columnD | data_syste | nowy
1 | a | b | c | d | 06/07/06 | " "
2 | a | b | c | e | 06/07/06 | "X"
3 | a | f | g | h | 07/07/06 | " "
4 | a | j | g | i | 07/07/06 | "X"
5 | a | b | g | c | 08/07/06 | " "
6 | a | j | g | i | 08/07/06 | "X"
7 | a | f | g | h | 07/07/06 | " "
8 | j | b | g | d | 07/07/06 | "X"


And execute of the procedure:
exec zestawienie('06/07/06', '07/07/06', 'columnB columnD');
After this records with following recno should be deleted:
1, 2, 3, 4 (5, 6 no because of date; 7,8 no because of the additional change on columnA)

Any idea how to do this? I've thought about using two cursors:
1. select * from HT_REJNZ where (data_syste between from and to) and nowy=' ' order by recno
2. select * from HT_REJNZ where (data_syste between from and to) and nowy='X' order by recno
and in the loop compare records from first and second cursor.
But I've no idea how to parse parametere columns_list, check whether it's correct and how to check equality of columns EXCEPT the ones from parameter. Could You help me, please?
  • Attachment: script.sql
    (Size: 329.94KB, Downloaded 200 times)
Re: [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns [message #189637 is a reply to message #188140] Fri, 25 August 2006 06:03 Go to previous messageGo to next message
sQbanieTZ
Messages: 3
Registered: August 2006
Junior Member
Nobody can help?
Re: [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns [message #190753 is a reply to message #188140] Fri, 01 September 2006 02:14 Go to previous messageGo to next message
sQbanieTZ
Messages: 3
Registered: August 2006
Junior Member
Really nobody knows the solution?
Re: [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns [message #190783 is a reply to message #190753] Fri, 01 September 2006 03:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect part of the problem is asking for a procedure.
Personally speaking, SQL solutions to complex problems are a challenge and fun.
Writing procedures is what I do to earn a living, and I don't get paid to do your job.

Re: [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns [message #190834 is a reply to message #190783] Fri, 01 September 2006 08:40 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Additionally, the formatting of the posting makes it very difficult to read. When a poster can't make the effort to make the posting legible, I sually ignore the question.
Re: [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns [message #190836 is a reply to message #190834] Fri, 01 September 2006 08:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Same here. To many requirements, too little 'own stuff'
If you would provide your solution and just ask for the missing part, you would get an answer. Don't feel like writing a complete procedure.
Re: [stored procedure Oracle 8.0.6.0] deleting records with changes on specific columns [message #190982 is a reply to message #188140] Sun, 03 September 2006 19:56 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Also, 8.0 is no longer supported. You need to upgrade. Anything that we might write might not work on 8.0. You should upgrade to at least 9i or perhaps 10g.
Previous Topic: Encountered the symbol "FETCH" when expecting one of the following: begin function pac
Next Topic: What is the best way to do this, stuck!! please help
Goto Forum:
  


Current Time: Sun Dec 11 00:18:29 CST 2016

Total time taken to generate the page: 0.21639 seconds