Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Create Package body syntax error

Posted on 2011-03-19
3
Medium Priority
?
513 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 952 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 948 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

916 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