Getting an error -> found 'begin' expecting external language

Question

I am creating a procedure inside a package but getting below error while executing below script

CREATE PACKAGE doc_interface AS

code_value                     VARCHAR2(30 CHAR)            ;

 PROCEDURE list_doc (
   lg_code    IN     fpl_ref.lang%TYPE,
   pl_no   IN     prt_req.pl_no%TYPE,
   cl_no    IN     prt_req.cl_no%TYPE,
   ct_no           IN      prt_req.ct_no%TYPE,
   potab_doc_list       OUT  custDocTab)
AS
BEGIN
   potab_doc_list := NEW custDocTab();

   IF pl_no IS NOT NULL AND cl_no IS NULL
   THEN
      select *
        BULK COLLECT INTO potab_doc_list
        from cs_record where req_id = 19736543;                          
   
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      z_error ('Error while fetching records =' || SQLERRM);
END list_doc;

END doc_interface;

Error:

[Error] Syntax check (12: 0): ERROR line 13, col 1, ending_line 13, ending_col 5, Found 'BEGIN', Expecting: EXTERNAL LANGUAGE

Not understanding what it is expecting after instead of BEGIN.

Answer 1

The code you have shown, with `CREATE PACKAGE, is creating a package specification - but you have included the procedure body, which belong in the package body. You need to create the specification and body separately:

CREATE PACKAGE doc_interface AS

code_value                     VARCHAR2(30 CHAR)            ;

 PROCEDURE list_doc (
   lg_code    IN     fpl_ref.lang%TYPE,
   pl_no   IN     prt_req.pl_no%TYPE,
   cl_no    IN     prt_req.cl_no%TYPE,
   ct_no           IN      prt_req.ct_no%TYPE,
   potab_doc_list       OUT  custDocTab);

END doc_interface;
/

then

CREATE PACKAGE BODY doc_interface AS

 PROCEDURE list_doc (
   lg_code    IN     fpl_ref.lang%TYPE,
   pl_no   IN     prt_req.pl_no%TYPE,
   cl_no    IN     prt_req.cl_no%TYPE,
   ct_no           IN      prt_req.ct_no%TYPE,
   potab_doc_list       OUT  custDocTab)
AS
BEGIN
   potab_doc_list := NEW custDocTab();

   IF pl_no IS NOT NULL AND cl_no IS NULL
   THEN
      select *
        BULK COLLECT INTO potab_doc_list
        from cs_record where req_id = 19736543;                          
   
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      z_error ('Error while fetching records =' || SQLERRM);
END list_doc;

END doc_interface;
/

it isn't clear if you want the code_value declared in the package specification - where it will be public - or in the package body - where it will be private.

Read more about package structure:

A package always has a specification, which declares the public items that can be referenced from outside the package.

If the public items include cursors or subprograms, then the package must also have a body. The body must define queries for public cursors and code for public subprograms. The body can also declare and define private items that cannot be referenced from outside the package, but are necessary for the internal workings of the package.

Also read about package state if you do have/need spec-level variables.