Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Anonymous block (Oracle 10g)
PL/SQL Anonymous block [message #575406] Wed, 23 January 2013 00:28 Go to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Hi all

Please help and Thanks in Advance,i need to create a plsql anonymous block which should be handle the below points.

1) As a first step i need to delete all the records in this table
2) Then as a second step, My record will come one by one but it should not insert the duplicates.

For eg
This is two different record and this should be inserted

'U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 106 ALR 11','CODE#ALR#YEAR#1992#PAGE#11#VOL#106#'

'U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 66 ALJR 271','CODE#ALJR#YEAR#1992#PAGE#271#VOL#66#'

and if the same records comes as a duplicate that record should be deleted..

'U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 106 ALR 11','CODE#ALR#YEAR#1992#PAGE#11#VOL#106#'

--Only This record should be deleted --'U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 106 ALR 11','CODE#ALR#YEAR#1992#PAGE#11#VOL#106#'

'U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 66 ALJR 271','CODE#ALJR#YEAR#1992#PAGE#271#VOL#66#');


This is my table structure ..
-----------------------------
create table Test (Transtype varchar2(50),
LBU varchar2(50),
Unique_case_no number(20),
Case_name varchar2(200),
Cit varchar2(200),
NVP varchar2(200) );

Insert into test values('U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 106 ALR 11','CODE#ALR#YEAR#1992#PAGE#11#VOL#106#');

Insert into test values('U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 66 ALJR 271','CODE#ALJR#YEAR#1992#PAGE#271#VOL#66#');

Insert into test values('U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 175 CLR 564','CODE#CLR#YEAR#1992#PAGE#564#VOL#175#');

Insert into test values('U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 59 A Crim R 255','CODE#A Crim R#YEAR#1992#PAGE#255#VOL#59#');

Insert into test values('U','AU',33245,'Ainsworth v Criminal Justice Commission','(1992) 106 ALR 11','CODE#ALR#YEAR#1992#PAGE#11#VOL#106#');
Re: PL/SQL Anonymous block [message #575411 is a reply to message #575406] Wed, 23 January 2013 01:17 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

try it

CREATE OR REPLACE
PROCEDURE Test_proc(
    Transtype      IN VARCHAR2,
    LBU            IN VARCHAR2,
    Unique_case_no IN NUMBER,
    Case_name      IN VARCHAR2,
    Cit            IN VARCHAR2,
    NVP            IN VARCHAR2)
IS
BEGIN
  INSERT INTO TEST
  SELECT transtype,
    lbu,
    unique_case_no,
    case_name,
    city,
    nvp
  FROM dual d
  WHERE NOT EXISTS
    (SELECT NULL
    FROM TEST e
    WHERE e.transtype=transtype
    AND e.lbu=lbu
    AND e.unique_case_no=unique_case_no
    AND e.case_name=case_name
    AND city=city
    AND e.nvp=nvp
    );
  COMMIT;
END;
Re: PL/SQL Anonymous block [message #575412 is a reply to message #575406] Wed, 23 January 2013 01:19 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
doubt!!

Quote:
Then as a second step, My record will come one by one but it should not insert the duplicates.


From where exactly these records are coming

i. from other table : u can try restrict the duplicate data while fetching using distinct
ii. user i/p
iii. others(cant think of anything)
Re: PL/SQL Anonymous block [message #575416 is a reply to message #575406] Wed, 23 January 2013 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As soon as 1) is executed you cannot do 2). Wink

In the end, you just want to delete duplicates, doesn't it?
This is a FAQ, search for this.

From your previous topic:

Michel Cadot wrote on Thu, 17 January 2013 10:52
I merge this topic with your previous one which is on the same subject.
The same answer also applies.

In addition I remind you the rules:
Quote:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
...


And your answer:

vnithya wrote on Fri, 18 January 2013 05:56
I am Sorry Michel ...


Was you really sorry?

Regards
Michel

[Updated on: Wed, 23 January 2013 01:27]

Report message to a moderator

Re: PL/SQL Anonymous block [message #575424 is a reply to message #575406] Wed, 23 January 2013 03:13 Go to previous messageGo to next message
userora
Messages: 55
Registered: December 2012
Location: Chennai
Member
Thanks a lot sss111ind. But it doesn't helped me Sad ..I am also trying Will keep you posted for sure.

Hi rishwinger ,
My data comes as the file format and the records will get inserted into this table and if another file data comes my test table should get deleted and in this i need to check for duplication from the file while inserting like as i mentioned earlier..But the Table which i am selecting and the file format data will contain the same structure as test table.

Hi Michel,
It's not only duplication delete but also a parallel input..
Yes, really i meant for sorry because you mentioned you MUST feedback, if not thank ..That i failed to do.So i make a practise of thanking Smile
Re: PL/SQL Anonymous block [message #575427 is a reply to message #575424] Wed, 23 January 2013 03:35 Go to previous message
Michel Cadot
Messages: 57617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It's not only duplication delete but also a parallel input..


So I failed to understand what you want and your test case is not complete as there is nothing to "input".
Anyway, have a look at MERGE statement.
Also always post your version with 4 decimals.

Regards
Michel

Previous Topic: Is it possible to identify/Count records in ref cursor without actually fetching
Next Topic: Materialized view Refresh
Goto Forum:
  


Current Time: Mon Apr 21 10:59:38 CDT 2014

Total time taken to generate the page: 0.13640 seconds