Обсуждение: Stored procedures doubts
Hai everybody,
Hai,
Currently, I'm at the work of converting database from Oracle to Postgresql.
I have some doubts:
Currently, I'm at the work of converting database from Oracle to Postgresql.
I have some doubts:
1) What is the equivalent of Oracle ROWNUM in Postgresql? If there is no such equivalent, can U give me an idea about that?
2) How to replace stored procedures with many OUT parameters? I want to call the procedure from Java code.
Please tell me the way to return the values of OUT parameters returned previously by Oracle, from the plpgsql code.
It's very urgent. Please reply quickly with some code examples.
HOW to handle the values returned by the Oracle OUT parameters, in Postgresql?
i.e, how to return the Oracle OUT parameters' values to the calling Java code, in postgresql.
If anybody knows, please explain with suitable examples with plpgsql code and also the Calling Java code.
3) Can default values be assigned to postgresql function parameters? If yes, how?
Thanks in advance.
Senthil.
read docs 19.11. porting from oracle pl/sql coming with tarball or
it's section 23.11.
Jie Liang
-----Original Message-----
From: Senthil [mailto:senthil.kandaswamy@mobax.com]
Sent: Monday, December 30, 2002 9:11 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Stored procedures doubtsHai everybody,Hai,
Currently, I'm at the work of converting database from Oracle to Postgresql.
I have some doubts:
1) What is the equivalent of Oracle ROWNUM in Postgresql? If there is no such equivalent, can U give me an idea about that?
2) How to replace stored procedures with many OUT parameters? I want to call the procedure from Java code.
Please tell me the way to return the values of OUT parameters returned previously by Oracle, from the plpgsql code.
It's very urgent. Please reply quickly with some code examples.HOW to handle the values returned by the Oracle OUT parameters, in Postgresql?i.e, how to return the Oracle OUT parameters' values to the calling Java code, in postgresql.If anybody knows, please explain with suitable examples with plpgsql code and also the Calling Java code.
3) Can default values be assigned to postgresql function parameters? If yes, how?Thanks in advance.Senthil.
Hai everybody,
Thanks for Ur immediate reply, Jie Liang.
I had gone through the document plpgsql-porting.html, U mentioned already.
It was quite useful.
1) But, in that there is nothing about the Calling of plpgsql function(equivalent of Oracle stored procedure with many OUT parameters) from java code and returning of the plpgsql values corresponding to the Oracle OUT parameter, to the Java code, as this is my current issue to be solved urgently.
I want some answers related to this.
2) Moreover, somebody had said that OID in postgresql is the equivalent of Oracle's ROWNUM.
I cannot agree this, because Oracle's ROWNUM is table-specific, whereas postgresql OID is database specific.
Because of this difference, I'm unable to delete from tables based on ROWNUM, which I was asked to do.
Can anybody help me out to solve this issue, as quickly as possible?
3) What is the syntax for plpgsql GOTO label. When I use like this, I get some parse errors:
start:
.....
.....
....
GOTO start;
How to solve this issue? Can anybody point out, where I am going wrong?
4) My Oracle stored procedure which is to be converted to plpgsql function contains
EXIT WHEN SQL%ROWCOUNT = 0; TO COME OUT OF A LOOP.
How should it be converted in postgresql?
Since I'm in need of the answers very urgently, please send the answers as quickly as possible.
I'll be happy if I can get the answers by today or tomorrow.
Thanks everybody in advance.
Senthil
Hai David,
Thousands of thanks for Ur detailed explanation regarding my doubts.
It was quiet useful.
Thanks,
Senthil