Migrating from Oracle to Postgre
Hi ,
Is there any guidelines to migrate the oracle to Postgre ?.
Seems,Postgre doesn't support store procedure so we need to convert Store Proc's to functions.
For connect-sql instances(like RDB-Open ,RDB-Save) ,Do we need change the package name for connect-SQL rules ?
How to call the functions which are converted from connect-sql rules.?
Thanks,
Brahmesh.
***Edited by Moderator Marissa to update categories***
Pegasystems Inc.
IN
Hi,
I didn't find any document as such but there are similar discussion which should give you a fair bit of idea
https://collaborate.pega.com/question/migrate-code-oracle-postgre-database
https://collaborate.pega.com/discussion/how-upload-customers
Hope this helps.
Thank You.
Skandiabanken
SE
As per my knowledge,Postgre don't support the store procedure so we need to convert them to functions.
What is the method to call the functions from Connect-sql rules?
Pegasystems Inc.
IN
Hi,
I confirmed this with our migration team and they told postgre do support stored procedure, but the decision to postgres depends on the complexity of those procedures in oracle. Sometimes postgres might not be able to handle every procedure that is written in oracle.
So our migration team reviews each environment before the migration to identify custom procedures that can / cannot be migrated to postgres
I didn't understand you second question, can you provide little clarity regarding what you are looking for?
Thank You
Skandiabanken
SE
I don't find option/syntax to create store procedure in postgre environment. Please refer below link.
https://wiki.postgresql.org/wiki/FAQ#Does_PostgreSQL_have_stored_procedures.3F
PostgreSQL’s PL/pgSQL is similar to Oracle PL/SQL and can be used to write stored functions. PostgreSQL doesn’t have packages or procedures (only functions).
My second questions is ,Oracle store procedures are migrated to functions and it's working fine when it's ran in postgre query tool.what is the method to call these functions from Connect-SQL rules.
I think ..Call method doesn't work to call function.I tried with SELECT AppSchema.functionaname({pyWorkPage.pyID},{pyWorkPage.ErrorMsg out}) but end up with below error.
There was a problem getting a list: code: 0 SQLState: 2F003 Message: This statement does not declare an OUT parameter. Use { ?= call ... } to declare one.
Skandiabanken
SE
Adding to above .
I'm able to invoke function from connect-sql and able to capture the function return value as well. However this function returns the single value and I would like to understand how to return and capture the multiple return values.
SELECT functionname({pyWorkPage.pyID}) AS "pyWorkPage.ErrorMsg".
Pegasystems Inc.
IN
Hi,
Try it like this
SELECT concat_lower_or_upper(a := 'Hello', b := 'World', uppercase := true);
concat_lower_or_upper is my function and Hello and worls is my attributes.
Pegasystems Inc.
IN
Hi,
In this function all variables are input variables. Please find my function details
CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
SELECT CASE
WHEN $3 THEN UPPER($1 || ' ' || $2)
ELSE LOWER($1 || ' ' || $2)
END;
$$
It converts lower case to upper case.
So when I run the below query
SELECT concat_lower_or_upper('Hello', 'World', true);
it converts the lower case to upper case
the o/p will be
HELLO WORLD.
Regards,
Parthiban Mohan