EDB Postgres Package

[angie]

Hi
I am getting   syntax error at or near ";" LINE 6:     ); in the package body when inserting package sql script into EDB postgres after converting from Oracle. I need help to solve this error.
In the sql script,
CREATE PACKAGE archive_apps
IS
  PROCEDURE capture_data(
        p_appl_id  IN  APPLICATIONS.appl_id%TYPE
    );
 PROCEDURE load_qstn_scores(
        p_appl_id  IN  APPLICATIONS.appl_id%TYPE
    );
END archive_apps;
CREATE PACKAGE BODY archive_apps
IS

PROCEDURE load_appl_vismin(
p_appl_id IN APPLICATIONS.appl_id%TYPE
);

PROCEDURE load_appl_vismin(
p_appl_id IN APPLICATIONS.appl_id%TYPE
)
IS
BEGIN
INSERT INTO APPL_VISIBLE_MINORITIES
( appl_id,
vismin_id )
SELECT apps.appl_id,
vismin.vismin_id
FROM GRADUATE_VISIBLE_MINORITIES vismin,
APPLICATIONS apps
WHERE vismin.grdt_id = apps.grdt_id
AND apps.appl_id = p_appl_id;
END load_appl_vismin;

END archive_apps;

[VibhorKumar]

You have provided less information on this thread. I was expecting if you could provide more detail, like sample code, EPAS version number etc. In future post, I would recommend you to include all possible information to look into the issue.

Coming to the posted code, I can see main issue is in declaration of following procedure in PACKAGE BODY.

PROCEDURE load_appl_vismin(

You would like to move the declaration of above procedure in PACKAGE definition instead of PACKAGE BODY. 

[angie]

Hi,

The example I provided above is a sample of code. I am using EDB Postgres 9.6

[VibhorKumar]

Thank you for the version information. Sample code is not a complete code which we can try and find the exact issue.

Please try the recommendation given in the previous post and let us know if that helps!

[jwatt]

Hi Angie,

Did Vibhor's prior recommendation help?  I've closed the duplicate thread to make sure we have this in a single stream.

Thanks,

Jamie

[angie]

Hi,
I trying to migrate from oracle to edb  version 9.6.
How do I keep  a function or a procedure like getSalary(...)  in create package body instead of having to put in create package header?
Example:
CREATE OR REPLACE PACKAGE empinfo
IS
emp_name VARCHAR2(10);
PROCEDURE get_name (
p_empno NUMBER
);
FUNCTION display_counter
RETURN INTEGER;
END;

CREATE OR REPLACE PACKAGE BODY empinfo
IS

Function getSalary (

p_empno NUMBER

 p_empno Number,

sal integer ;
);  

PROCEDURE get_name (
p_empno NUMBER
)
IS
BEGIN
SELECT ename INTO emp_name FROM emp WHERE empno = p_empno;
v_counter := v_counter + 1;
END;
FUNCTION display_counter
RETURN INTEGER
IS
BEGIN
RETURN v_counter;
END;
BEGIN
v_counter := 0;
DBMS_OUTPUT.PUT_LINE('Initialized counter');
END;

Function getSalary (

 p_empno Number,

sal integer ;
)IS BEGIN

.....

end;

END;

[VibhorKumar]

As mentioned in the thread, you cannot include procedure/function in Package Body without declaring in Package Header. If you think this is important for your use-case, please reach out to EDB support/account manager to include the request in EPAS roadmap.