Solved

Oracle Create Package body syntax error

Posted on 2011-03-19
3
499 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

747 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

11 Experts available now in Live!

Get 1:1 Help Now