| Stored Procedure Problems [message #36849] |
Thu, 27 December 2001 06:21  |
Tina
Messages: 38
Registered: November 2001
|
Member |
|
|
Hello there,
I'm new to Oracle, but have good experience with MS SQL Server. I am currently in the process of converting our SQL stored procedures to Oracle. However, I have begun to read up on Oracle stored procedures and they would appear to be different from SQL Server.
For example, my SQL Server store procedures runs a number of small SQL statements, such as create table, updates, etc.
I want to run to following in one go, please can you advise me on the best way to run this in Oracle, package, function, etc?
CREATE TABLE ONE.OPENACC
AS
(SELECT a.CUSTOMER_ID,
a.ACCOUNT_NUMBER
FROM TWO.RELATIONSHIP a,
TWO.DATES b
WHERE a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER
AND TO_CHAR(b.DATE_OPEN, 'MON') = 'SEP'
AND TO_CHAR(b.DATE_OPEN, 'YY') = '01'
AND b.DATE_CLOSED IS NULL)
--
alter table ONE.OPENACC add(
TYPE varchar(10));
--
CREATE TABLE ONE.EXIST
AS
(SELECT distinct b.CUSTOMER_ID,
b.ACCOUNT_NUMBER
FROM ONE.OPENACC a,
TWO.RELATIONSHIP b
WHERE b.CUSTOMER_ID = a.CUSTOMER_ID
AND b.ACCOUNT_NUMBER <> a.ACCOUNT_NUMBER)
--
update ONE.OPENACC
set TYPE = 'Existing'
where CUSTOMER_ID in (select CUSTOMER_ID from ONE.EXIST)
--
update ONE.OPENACC
set TYPE = 'New'
where TYPE is null
Any help is much appreciated!! Thank-you!
----------------------------------------------------------------------
|
|
|
|
|
|
|
|
| Re: Stored Procedure Problems [message #37393 is a reply to message #36849] |
Tue, 05 February 2002 13:24  |
piyush
Messages: 4
Registered: February 2002
|
Junior Member |
|
|
|
I want to do exectly opposit. I have number of oracle store procedure and want to convert for SQL server. Do you know how I can do that? Any ready made utility?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
