Porting from Oracle PL/SQL to PL/pgSQL
Posted 14 February 2007 - 05:15 PM
What is the problem you are experiencing (what doesn't work)?
Can you tell us why the function retrieves the high sequence number this way?
What does it use the result for?
Are you aware ProIV technically does not support output into host variables (eg.SELECT INTO)?
Posted 15 February 2007 - 06:10 AM
SELECT MAX(V_SEQ) INTO :#SEQ
1. First of all SELECT INTO a $ or # scratch variables do work within ORACLE's PL/SQL.Basically,I need same functionally for Postgres(i.e called Porting). The scratch variable is then available globally for usage in the PROIV function.I use this quiet a lot.This eliminates PROIV to read every record and the cycle.
2. In Postgres,I can use CREATE OR REPLACE FUNCTION in rather than PACKAGE method which it is deprecated in Postgres.I thought there would be some differrent ideas of using the PACKAGE method.
3. This can be done at any logic, one does not need to physically map the file into the cycle(optimisation).
4.Instead of packages, I would like to use schemas to organize my Postgres database functions into groups.
5.Example 36.5. Porting a Simple Function from PL/SQL to PL/pgSQL in Posgress 8.1 manual explains it.
Posted 15 February 2007 - 03:52 PM
"Host variables that pass data from the RDBMS back to ProIV are not supported"
I mentioned it because people do seem to use them and not realise it is unsupported behaviour and because it is very likely the reason for your problem.
Anyway, good luck with finding a workaround.
Posted 15 February 2007 - 04:49 PM
Create a pro-iv file for a db table that contains a 1-character key and a numeric,
and call a global function that executes the SQL on the before read:
SELECT ' ', MAX(V_SEQ) FROM TABNAME
and then set and return the value on the read no error.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users