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;
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.
Hi,
The example I provided above is a sample of code. I am using EDB Postgres 9.6
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!
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
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;
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.