Solved

Oracle Create Package body syntax error

Posted on 2011-03-19
3
500 Views
Last Modified: 2012-05-11
I want to create a scheduled Job in Oracle 11g that will copy data from a source table to a destination identical table so that both remains in sync.

But before copying I need to make sure that the source table is non-empty.

The merge command gives problem as there is no promary key on the table and I do not know how to merge without the primary key (Because cannot specify the field value in the merge "on" clause.

However I try to create this package body but getting error. Cannot find where the syntax is wrong.
CREATE OR REPLACE PACKAGE BODY MYPKG IS
PROCEDURE LOAD_AA_DATA AS
 DECLARE TOT_ROWS NUMBER;

 BEGIN
    
DECLARE TOT_ROWS NUMBER;

SELECT COUNT(*) INTO TOT_ROWS FROM MYTAB;
IF 
  TOT_ROWS > 399000
  THEN 
    (
    DELETE FROM MYTAB;
    INSERT INTO MYTAB SELECT * FROM other_schema.MYTAB;
    COMMIT;
    )
END IF;

END;
END;

Open in new window

0
Comment
Question by:toooki
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 238 total points
ID: 35173036
you have this line 2 times:
DECLARE TOT_ROWS NUMBER;

remove the second line,

otherwise, please specify the error you get.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 237 total points
ID: 35173040
There is no 'declare' inside a procedure.

You also try to create a variable gain after the begin.

Also there are no parans in pl/sql that you have in the if statement.
0
 

Author Comment

by:toooki
ID: 35173764
Yes it worked. Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now