| Informix -> Oracle migration [message #4394] |
Wed, 04 December 2002 18:56  |
Paul Sun
Messages: 1
Registered: December 2002
|
Junior Member |
|
|
I got a program of Informix application.
I have to migrate the Informix to Oracle.
There is a Informix statement:
"SELECT dbinfo('sqlca.sqlerrd1') as refnox FROM systables WHERE tabid = 1"
... Then use refnox to do something.
I don't know how to translate it to Oracle.
systables is a system table of Informix. dbinfo is Informix's function.
My questions is how to realize the same function in Oracle. I create all user tables in Oracle. I don't know how to create a "systables" - like system table in Oracle. Is there a similar systables in Oracle?
What should I do?
|
|
|
|
| Re: Informix -> Oracle migration [message #4398 is a reply to message #4394] |
Thu, 05 December 2002 00:18  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Oracle doesn't know any datatype like autonumber or SERIALx. To assign a unique value to a NUMBER column, you need to do some programming. One possible way is the following:
1) create a SEQUENCE to generate unique values. But be aware, this sequence cannot guarantee that there are no gaps in the unique numbers. In fact this is not a big issue, but it's useful to know.
2) create a stored procedure/function that fetches the sequence.nextval.
3) call this stored procedure/function in a BEFORE INSERT ROW trigger to assign the value to the NUMBER column.
Now you have a similar functionality for a SERIALx column in Oracle.
To accomplish the functionality you want, you could do like this:
INSERT INTO your_table(....)
VALUES(....)
RETURNING unique_number INTO some_variable;
Afterwards, you can use some_variable in the rest of your processing.
That's an extremely brief and incomplete answer, but it should get you on the right track. Oracle documentation can be found at Oracle Technology Network (OTN) or at Todd Barry's favourite: tahiti.Oracle.com.
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
