Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Create Package body syntax error

Posted on 2011-03-19
3
Medium Priority
?
508 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 77

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

715 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