OraFAQ Forum: SQL & PL/SQL » PL/SQL Anonymous block

Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Anonymous block (Oracle 10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

PL/SQL Anonymous block [message #575406]

Wed, 23 January 2013 00:28

vnithya
Messages: 20
Registered: December 2012
Location: Chennai

Junior 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#');

Report message to a moderator

Re: PL/SQL Anonymous block [message #575411 is a reply to message #575406]

Wed, 23 January 2013 01:17

sss111ind
Messages: 272
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;

Report message to a moderator

Re: PL/SQL Anonymous block [message #575412 is a reply to message #575406]

Wed, 23 January 2013 01:19

rishwinger
Messages: 101
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)

Report message to a moderator

Re: PL/SQL Anonymous block [message #575416 is a reply to message #575406]

Wed, 23 January 2013 01:26

Michel Cadot
Messages: 54239
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

vnithya
Messages: 20
Registered: December 2012
Location: Chennai

Junior 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

Report message to a moderator

Re: PL/SQL Anonymous block [message #575427 is a reply to message #575424]

Wed, 23 January 2013 03:35

Michel Cadot
Messages: 54239
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 04:41:14 CDT 2013

Total time taken to generate the page: 0.08955 seconds