I have a requirement such that when a project is created, for that project id , project number to be sent as output and printed.
I created an after insert trigger on pa_projects_all and in that I am utilisng a pragma autonomous procedure which just takes project id (:new.project_id) as input and corresponding project_number, an error code(if applicable), error message(if applicable) as OUT parameter and printed as well.
Now when I am creating a project, the trigger is running(which I could see from some dummy table inserts) but this project id when used in the procedure is returning NO data found.
Now this procedure is correct which I confirmed by running as standalone even the SQL within it returns data as standalone.
But somehow within the trigger although the :new.project_id generated but it seems the project id actually not created/committed in base table due to which fetching the details shows no data found error.
Till this I am confirm but unable to resolve it. Trying all possible ways but not working out.