Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure Problems
Stored Procedure Problems [message #36849] Thu, 27 December 2001 06:21 Go to next message
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 #36851 is a reply to message #36849] Thu, 27 December 2001 08:03 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
for that you dont need stored procedure. save it in text file and execute at sql prompt

SQL> @filename

----------------------------------------------------------------------
Re: Stored Procedure Problems [message #36855 is a reply to message #36849] Thu, 27 December 2001 20:34 Go to previous messageGo to next message
RajuKVG
Messages: 13
Registered: December 2001
Junior Member
Ya that is the only method to do it.

----------------------------------------------------------------------
Re: Stored Procedure Problems [message #37393 is a reply to message #36849] Tue, 05 February 2002 13:24 Go to previous message
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?
Previous Topic: Unique ID for a server
Next Topic: Re: How to insert table with default values?
Goto Forum:
  


Current Time: Sun Sep 19 11:43:24 CDT 2021