Solved

Oracle Create Package body syntax error

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

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about DB Schema 27 61
SYbase 4 37
Oracle 12c Localhost trying to create a Migration Repo, need help with User creation. 13 30
Oracle Nested table uses ? 2 35
This article describes some very basic things about SQL Server filegroups.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

830 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