0

Error(7,1): PLS-00103: Encountered the symbol "CREATE". I tried to put / before create but then error was Error(6,1): PLS-00103: Encountered the symbol "/" .

I am new to PL/SQL programming, could you please help on this. 

CREATE OR REPLACE PACKAGE EMP_BULK_INSERT AS 

     PROCEDURE Bulk_Insert;

    END EMP_BULK_INSERT;
    /* package body */
    CREATE OR REPLACE PACKAGE BODY EMP_BULK_INSERT AS

      PROCEDURE Bulk_Insert
      AS
         /* select all records from source table */
          CURSOR kt_test_cur IS
            SELECT empid
            ,      empband
            ,      empname
            ,      workexp
            ,      salary
            from   kt_test;

            /* create nested table type and variable that will hold BIG_TABLE's records */
            TYPE kt_test_ntt IS TABLE OF kt_test_cur%ROWTYPE;
            l_kt_test kt_test_ntt;
       BEGIN
            /* open pointer to SELECT statement */
            OPEN kt_test_cur;
            /* collect data in the collection */
            FETCH kt_test_cur BULK COLLECT INTO l_kt_test;
            /* close the pointer */
            CLOSE kt_test_cur;

           /* print size of the collection */
           DBMS_OUTPUT.PUT_LINE('Nested table holds: ' || TO_CHAR(l_kt_test.COUNT) || ' records.');

            /* write data down to target table */
            FORALL indx IN l_kt_test.FIRST..l_kt_test.LAST
                INSERT INTO kt1_test(empid,empband,empname,workexp,salary)
                VALUES (l_kt_test(indx).empid,l_kt_test(indx).empband,l_kt_test(indx).empname,l_kt_test(indx).workexp,l_kt_test(indx).salary);

             DBMS_OUTPUT.PUT_LINE('Number of rows inserted ' || SQL%ROWCOUNT || ' rows');

             COMMIT;

        END Bulk_Insert;

    END EMP_BULK_INSERT;
MAhipal Singh
  • 4,745
  • 1
  • 42
  • 57
Kaushal Talniya
  • 182
  • 2
  • 5
  • 12

2 Answers2

1

The "/" needs to be on a blank line, all by itself. Like this:

CREATE OR REPLACE PACKAGE abc AS
...
END;
/
CREATE OR REPLACE PACKAGE BODY abc AS
...
END;
/

The meaning of the "/" is to execute the command buffer. In this case it executes the previous PL/SQL block.

For a more in depth discussion on this topic, see here on stack overflow

Hauke
  • 242
  • 2
  • 10
  • I added / , in both part ,specification and body.But now error is "Error(6,1): PLS-00103: Encountered the symbol "/" " – Kaushal Talniya Jul 03 '18 at 10:06
  • When I ran this code as s script in SQL developer , It worked and compiled successfully but as a package code it is having the same error.But It should work here as well. Any solution for this? – Kaushal Talniya Jul 03 '18 at 10:24
  • When I am trying to compile the whole package then I am getting the above error.But when I am copying and pasting all the content of this package in a separate worksheet, it got compiled. – Kaushal Talniya Jul 03 '18 at 13:36
  • How do you try to compile the package? Please note: The "/" is NOT part of the package code. It just tells frontends like SQL*Plus or a SQL*Developer worksheet where a PL/SQL block ends. – Hauke Jul 03 '18 at 14:06
  • If you go through the actual question, I was trying to compile a package without '/', then I got error as 'Error(7,1): PLS-00103: Encountered the symbol "CREATE".'.Then as per suggestion I put '/', before create then I got error as 'Error(6,1): PLS-00103: Encountered the symbol "/" '. Hence I paste code in the separate worksheet with '/'. then it worked.But I want to compile package without '/' . – Kaushal Talniya Jul 04 '18 at 05:08
0

If you want to manipulate the package vie the dedicated object viewa of SQL Developer you have to separate package def and body and use the "/" in neither:

First compile the package spec (without "/"). Then open the body in a separate tab page with the small package icon (left from the compile icon). Edit your body code and compile there (again, without the "/").

Hauke
  • 242
  • 2
  • 10