Open jamesydb opened 1 year ago
@rahulb-yb , @amit-yb , @kneeraj
Tried the issue with the following schema
CREATE PACKAGE banking AS
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;
/
CREATE PACKAGE BODY banking AS
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
NULL;
END;
END banking;
/
CREATE TABLE anniversaries AS
SELECT DISTINCT TRUNC(hire_date) anniversary FROM HR.employees;
ALTER TABLE anniversaries ADD PRIMARY KEY (anniversary);
CREATE TRIGGER anniversary_trigger
BEFORE INSERT ON HR.employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO anniversaries VALUES(TRUNC(:new.hire_date));
-- Only commits the preceding INSERT, not the INSERT that fired
-- the trigger.
COMMIT;
EXCEPTION
-- If someone else was hired on the same day, we get an exception
-- because of duplicate values. That's OK, no action needed.
WHEN OTHERS THEN NULL;
END;
/
Issue encountered in import schema
cat /home/ubuntu/export-dir/schema/failed.sql
CREATE OR REPLACE FUNCTION trigger_fct_anniversary_trigger() RETURNS trigger AS $BODY$
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
INSERT INTO anniversaries VALUES (TRUNC(NEW.hire_date));
-- Only commits the preceding INSERT, not the INSERT that fired
-- the trigger.
COMMIT;
EXCEPTION
-- If someone else was hired on the same day, we get an exception
-- because of duplicate values. That's OK, no action needed.
WHEN OTHERS THEN NULL;
END;
RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;
CREATE TRIGGER anniversary_trigger
BEFORE INSERT ON employees FOR EACH ROW
EXECUTE PROCEDURE trigger_fct_anniversary_trigger();
Hi Team,
Pragma autonomous transaction support does not supported for PG/PLSQL code in YSQL but it is not reported in the assessment report. Can you please help.
Thanks Jaimin