Nov 20, 2012

SQLEXEC () in RowInit PeopleCode

We cannot perform INSERT,UPDATE statement's through SQLEXEC( ) in Rowinit PeopleCode event.

Below is the alternative to perform insertion and updation in rowinit .

Creating a stroed procedure, that perform's the insertion or updation.
Execute the procedure using SQLEXEC( ) in rowinit.

Example:

1.Creating the stored procedures in Oracle

CREATE OR REPLACE PROCEDURE CH_SAMPLE_PROCEDURE
AS 
BEGIN
    UPDATE PS_CH_TEST_TBL
            SET  COUNT=100;
    COMMIT;
END CH_SAMPLE_PROCEDURE;

2.Execute the procedure
 
EXECUTE CH_SAMPLE_PROCEDURE;

Sample code in ROWINIT:

1.Create a sql object,that contains the sql for creating stored procedure.
    SQLEXEC(SQL.CH_TESTPROCEDURE_SQL);
   Here CH_TESTPROCEDURE_SQL contains a sql for creating stored procedure.
2.Execute procedure.
    SQLEXEC("EXECUTE CH_SAMPLE_PROCEDURE");

Note:
1. To create a stored procedure , you need to have permissions in database level.
2. If you want to check whether the stored procedure is created or not use the below sql.
SELECT * FROM ALL_PROCEDURES WHERE OBJECT_NAME='CH_SAMPLE_PROCEDURE'