Home » SQL & PL/SQL » SQL & PL/SQL » convert sql server stored procedure to oracle
| convert sql server stored procedure to oracle [message #142819] |
Mon, 17 October 2005 19:13  |
AndrewG1976
Messages: 7
Registered: June 2005 |
Junior Member |
|
|
Hi,
I'm trying to convert the following sql server stored procedure to oracle:
CREATE PROCEDURE TransferSummary
@startDate datetime,
@endDate datetime
AS
SELECT 'Payment Range' =
CASE
When cast(BlFundsTransfer.amount as int) <= 2500 Then '1/ $0 -> $25.00'
When cast(BlFundsTransfer.amount as int) <= 5000 Then '2/ $25.01 -> $50.00'
When cast(BlFundsTransfer.amount as int) <= 10000 Then '3/ $50.01 -> $100.00'
when cast(BlFundsTransfer.amount as int) <= 50000 Then '4/ $100.01 -> $500.00'
When cast(BlFundsTransfer.amount as int) >= 50001 Then '5/ > $500.00'
Else 'Gap in range'
End,
BlFundsTransfer.channel, YEAR(BlFundsTransfer.postedDate) AS TranYear, MONTH(BlFundsTransfer.postedDate) AS TranMonth, pstatus, SUM(amount * 1) AS SumAmount, COUNT(BlFundsTransfer.id) AS CountOfid
FROM BlFundsTransfer
WHERE (BlFundsTransfer.postedDate >= @startDate) AND (BlFundsTransfer.postedDate <= @endDate)
AND (BlFundsTransfer.pstatus <> 'x')
GROUP BY BlFundsTransfer.channel, YEAR(BlFundsTransfer.postedDate), MONTH(BlFundsTransfer.postedDate), BlFundsTransfer.pstatus,
CASE
When cast(BlFundsTransfer.amount as int) <= 2500 Then '1/ $0 -> $25.00'
When cast(BlFundsTransfer.amount as int) <= 5000 Then '2/ $25.01 -> $50.00'
When cast(BlFundsTransfer.amount as int) <= 10000 Then '3/ $50.01 -> $100.00'
when cast(BlFundsTransfer.amount as int) <= 50000 Then '4/ $100.01 -> $500.00'
When cast(BlFundsTransfer.amount as int) >= 50001 Then '5/ > $500.00'
Else 'Gap in range'
End
ORDER BY BlFundsTransfer.channel, YEAR(BlFundsTransfer.postedDate), MONTH(BlFundsTransfer.postedDate), BlFundsTransfer.pstatus
GO
I've done the following but get a compile error in sql*plus:
SQL> CREATE OR REPLACE PROCEDURE TransferSummary AS
2 startDate date;
3 endDate date;
4 BEGIN
5 SELECT
6 (CASE
7 When to_number(BlFundsTransfer.amount) <= 2500 Then '1/ $0 -> $25.00'
8 When to_number(BlFundsTransfer.amount) <= 5000 Then '2/ $25.01 -> $50.00'
9 When to_number(BlFundsTransfer.amount) <= 10000 Then '3/ $50.01 -> $100.00'
10 when to_number(BlFundsTransfer.amount) <= 50000 Then '4/ $100.01 -> $500.00'
11 When to_number(BlFundsTransfer.amount) >= 50001 Then '5/ > $500.00'
12 Else 'Gap in range'
13 END) as "Payment Range",
14 BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY') AS TranYear, to_char(BlFu
ndsTransfer.postedDate, 'mm') AS TranMonth, pstatus, SUM(amount * 1) AS SumAmount, COUNT(BlFundsTran
sfer.id) AS CountOfid
15 FROM BlFundsTransfer
16 WHERE (BlFundsTransfer.postedDate >= @startDate) AND (BlFundsTransfer.postedDate <= @endDat
e)
17 AND (BlFundsTransfer.pstatus <> 'x')
18 GROUP BY BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY'), to_char(BlFundsT
ransfer.postedDate, 'mm'), BlFundsTransfer.pstatus,
19 CASE
20 When to_number(BlFundsTransfer.amount) <= 2500 Then '1/ $0 -> $25.00'
21 When to_number(BlFundsTransfer.amount) <= 5000 Then '2/ $25.01 -> $50.00'
22 When to_number(BlFundsTransfer.amount) <= 10000 Then '3/ $50.01 -> $100.00'
23 when to_number(BlFundsTransfer.amount) <= 50000 Then '4/ $100.01 -> $500.00'
24 When to_number(BlFundsTransfer.amount) >= 50001 Then '5/ > $500.00'
25 Else 'Gap in range'
26 END
27 ORDER BY BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY'), to_char(BlFundsT
ransfer.postedDate, 'mm'), BlFundsTransfer.pstatus;
28 END;
29 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE TRANSFERSUMMARY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4 PLS-00103: Encountered the symbol "CASE" when expecting one of
the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Can't I use "CASE" in a stored procedure?
|
|
|
| Re: convert sql server stored procedure to oracle [message #142841 is a reply to message #142819 ] |
Tue, 18 October 2005 00:01  |
 |
Barbara Boehmer
Messages: 4672
Registered: November 2002
Location: California, USA |
Senior Member |
|
|
Yes, you can use CASE in PL/SQL since Oracle 9i. In 8i, you would have to use dynamic SQL. You still have some @ in your code that needs to be removed. Also, in PL/SQL, you must select into something or open a cursor using the select statement. Please see the suggested code below, which is untested, since you did not provide a create table statement.
CREATE OR REPLACE PROCEDURE TransferSummary
(p_startDate IN DATE,
p_endDate IN DATE,
p_ref OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_ref FOR
SELECT CASE When cast(BlFundsTransfer.amount as int) <= 2500
Then '1/ $0 -> $25.00'
When cast(BlFundsTransfer.amount as int) <= 5000
Then '2/ $25.01 -> $50.00'
When cast(BlFundsTransfer.amount as int) <= 10000
Then '3/ $50.01 -> $100.00'
when cast(BlFundsTransfer.amount as int) <= 50000
Then '4/ $100.01 -> $500.00'
When cast(BlFundsTransfer.amount as int) >= 50001
Then '5/ > $500.00'
Else 'Gap in range'
End AS "Payment Range",
BlFundsTransfer.channel,
to_char(BlFundsTransfer.postedDate, 'YYYY') AS TranYear,
to_char(BlFundsTransfer.postedDate, 'mm') AS TranMonth,
pstatus,
SUM(amount * 1) AS SumAmount,
COUNT(BlFundsTransfer.id) AS CountOfid
FROM BlFundsTransfer
WHERE (BlFundsTransfer.postedDate >= p_startDate)
AND (BlFundsTransfer.postedDate <= p_endDate)
AND (BlFundsTransfer.pstatus <> 'x')
GROUP BY CASE When cast(BlFundsTransfer.amount as int) <= 2500
Then '1/ $0 -> $25.00'
When cast(BlFundsTransfer.amount as int) <= 5000
Then '2/ $25.01 -> $50.00'
When cast(BlFundsTransfer.amount as int) <= 10000
Then '3/ $50.01 -> $100.00'
when cast(BlFundsTransfer.amount as int) <= 50000
Then '4/ $100.01 -> $500.00'
When cast(BlFundsTransfer.amount as int) >= 50001
Then '5/ > $500.00'
Else 'Gap in range'
End AS "Payment Range",
BlFundsTransfer.channel,
to_char(BlFundsTransfer.postedDate, 'YYYY') AS TranYear,
to_char(BlFundsTransfer.postedDate, 'mm') AS TranMonth,
pstatus
ORDER BY channel, TranYear, TranMonth, pstatus;
END TransferSummary;
/
SHOW ERRORS
VARIABLE g_ref REFCURSOR
EXECUTE TransferSummary (SYSDATE, SYSDATE, :g_ref)
PRINT g_ref
|
|
|
| Re: convert sql server stored procedure to oracle [message #142851 is a reply to message #142819 ] |
Tue, 18 October 2005 00:51 |
AndrewG1976
Messages: 7
Registered: June 2005 |
Junior Member |
|
|
Thanks for responding. Here is the script to create the table:
CREATE TABLE BLFUNDSTRANSFER
(
ID VARCHAR2(8) NOT NULL,
OBJECTVERSION INTEGER NOT NULL,
CUSTOMERNUMBER VARCHAR2(15),
CREATIONDATE DATE,
CREATIONTIME VARCHAR2(5),
CUSTOMERREFNUM VARCHAR2(20),
ACCOUNT VARCHAR2(19),
AMOUNT CHAR(12),
PSTATUS CHAR(1),
POSTINGDATE DATE,
POSTEDDATE DATE,
POSTEDTIME VARCHAR2(5),
ERRORMESSAGE LONG,
HOSTREFERENCENUMBER VARCHAR2(100),
BUSINESSUSERCUSTOMERNUMBER VARCHAR2(15),
BUSINESSUSERSIGNON VARCHAR2(15),
CHANNEL VARCHAR2(4),
TOACCOUNT VARCHAR2(19)
)
TABLESPACE DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 135360K
NEXT 20M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
I'm running Oracle 8.1.7.4. How can I do this using dynamic sql?
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> CREATE OR REPLACE PROCEDURE TransferSummary
2 (p_startDate IN DATE,
3 p_endDate IN DATE,
4 p_ref OUT SYS_REFCURSOR)
5 AS
6 BEGIN
7 OPEN p_ref FOR
8 SELECT CASE When cast(BlFundsTransfer.amount as int) <= 2500
9 Then '1/ $0 -> $25.00'
10 When cast(BlFundsTransfer.amount as int) <= 5000
11 Then '2/ $25.01 -> $50.00'
12 When cast(BlFundsTransfer.amount as int) <= 10000
13 Then '3/ $50.01 -> $100.00'
14 when cast(BlFundsTransfer.amount as int) <= 50000
15 Then '4/ $100.01 -> $500.00'
16 When cast(BlFundsTransfer.amount as int) >= 50001
17 Then '5/ > $500.00'
18 Else 'Gap in range'
19 End AS "Payment Range",
20 BlFundsTransfer.channel,
21 to_char(BlFundsTransfer.postedDate, 'YYYY') AS TranYear,
22 to_char(BlFundsTransfer.postedDate, 'mm') AS TranMonth,
23 pstatus,
24 SUM(amount * 1) AS SumAmount,
25 COUNT(BlFundsTransfer.id) AS CountOfid
26 FROM BlFundsTransfer
27 WHERE (BlFundsTransfer.postedDate >= p_startDate)
28 AND (BlFundsTransfer.postedDate <= p_endDate)
29 AND (BlFundsTransfer.pstatus <> 'x')
30 GROUP BY CASE When cast(BlFundsTransfer.amount as int) <= 2500
31 Then '1/ $0 -> $25.00'
32 When cast(BlFundsTransfer.amount as int) <= 5000
33 Then '2/ $25.01 -> $50.00'
34 When cast(BlFundsTransfer.amount as int) <= 10000
35 Then '3/ $50.01 -> $100.00'
36 when cast(BlFundsTransfer.amount as int) <= 50000
37 Then '4/ $100.01 -> $500.00'
38 When cast(BlFundsTransfer.amount as int) >= 50001
39 Then '5/ > $500.00'
40 Else 'Gap in range'
41 End AS "Payment Range",
42 BlFundsTransfer.channel,
43 to_char(BlFundsTransfer.postedDate, 'YYYY') AS TranYear,
44 to_char(BlFundsTransfer.postedDate, 'mm') AS TranMonth,
45 pstatus
46 ORDER BY channel, TranYear, TranMonth, pstatus;
47 END TransferSummary;
48 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE TRANSFERSUMMARY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/10 PLS-00103: Encountered the symbol "CASE" when expecting one of
the following:
( * - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string>
SQL> VARIABLE g_ref REFCURSOR
SQL> EXECUTE TransferSummary (SYSDATE, SYSDATE, :g_ref)
BEGIN TransferSummary (SYSDATE, SYSDATE, :g_ref); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object MEWEBIVR.TRANSFERSUMMARY is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> PRINT g_ref
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "g_ref"
|
|
|
| Re: convert sql server stored procedure to oracle [message #142984 is a reply to message #142851 ] |
Tue, 18 October 2005 11:11 |
|
Barbara Boehmer
Messages: 4672
Registered: November 2002
Location: California, USA |
Senior Member |
|
|
To open a ref cursor dynamically, you need to enclose the string within single quotes: open refcur for '...'; However, you then need to replace each set of single quotes within that string with two single quotes. You also need to use bind variables for your parameters and supply the values in a using clause. I should mention that this is not the only way to do things, but is probably the best way. You could use a global temporary table instead of a ref cursor and you could use decode and sign instead of case with dynamic sql and you could concatenate your values instead of using bind variables, but those are all inefficient methods. In the demonstration below, I have also made a few other improvements, such as replacing your cast as int with to_number and using an inline view, so that the case statement does not have to be repeated in the group by clause.
scott@ORA92> CREATE TABLE BLFUNDSTRANSFER
2 (
3 ID VARCHAR2(8) NOT NULL,
4 OBJECTVERSION INTEGER NOT NULL,
5 CUSTOMERNUMBER VARCHAR2(15),
6 CREATIONDATE DATE,
7 CREATIONTIME VARCHAR2(5),
8 CUSTOMERREFNUM VARCHAR2(20),
9 ACCOUNT VARCHAR2(19),
10 AMOUNT CHAR(12),
11 PSTATUS CHAR(1),
12 POSTINGDATE DATE,
13 POSTEDDATE DATE,
14 POSTEDTIME VARCHAR2(5),
15 ERRORMESSAGE LONG,
16 HOSTREFERENCENUMBER VARCHAR2(100),
17 BUSINESSUSERCUSTOMERNUMBER VARCHAR2(15),
18 BUSINESSUSERSIGNON VARCHAR2(15),
19 CHANNEL VARCHAR2(4),
20 TOACCOUNT VARCHAR2(19)
21 )
22 /
Table created.
scott@ORA92> INSERT ALL
2 INTO blfundstransfer (id, objectversion, amount, pstatus, posteddate, channel)
3 VALUES (1, 1, 100, 'a', SYSDATE, 'cha1')
4 INTO blfundstransfer (id, objectversion,amount, pstatus, posteddate, channel)
5 VALUES (2, 1, 6000, 'b', SYSDATE-30, 'cha2')
6 INTO blfundstransfer (id, objectversion, amount, pstatus, posteddate, channel)
7 VALUES (3, 1, 200, 'a', SYSDATE, 'cha1')
8 SELECT * FROM DUAL
9 /
3 rows created.
scott@ORA92> CREATE OR REPLACE PROCEDURE TransferSummary
2 (p_start_date IN DATE,
3 p_end_date IN DATE,
4 p_ref OUT SYS_REFCURSOR)
5 AS
6 BEGIN
7 OPEN p_ref FOR
8 'SELECT "Payment Range", channel, TranYear, TranMonth, pstatus,
9 SUM (amount) AS SumAmount, COUNT (id) AS CountOfid
10 FROM (SELECT CASE WHEN TO_NUMBER (BlFundsTransfer.amount) <= 2500
11 THEN ''1/ $0 -> $25.00''
12 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 5000
13 THEN ''2/ $25.01 -> $50.00''
14 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 10000
15 THEN ''3/ $50.01 -> $100.00''
16 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 50000
17 THEN ''4/ $100.01 -> $500.00''
18 WHEN TO_NUMBER (BlFundsTransfer.amount) >= 50001
19 THEN ''5/ > $500.00''
20 ELSE ''Gap in Range''
21 END AS "Payment Range",
22 BlFundsTransfer.channel,
23 TO_CHAR (BlFundsTransfer.postedDate, ''YYYY'') AS TranYear,
24 TO_CHAR (BlFundsTransfer.postedDate, ''mm'') AS TranMonth,
25 pstatus, amount, id
26 FROM BlFundsTransfer
27 WHERE postedDate >= :b_start_date
28 AND postedDate <= :b_end_date
29 AND pstatus <> ''x'')
30 GROUP BY "Payment Range", channel, TranYear, TranMonth, pstatus
31 ORDER BY channel, TranYear, TranMonth, pstatus'
32 USING p_start_date, p_end_date;
33 END TransferSummary;
34 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> EXECUTE TransferSummary (SYSDATE-100, SYSDATE+1, :g_ref)
PL/SQL procedure successfully completed.
scott@ORA92> COLUMN channel FORMAT A7
scott@ORA92> COLUMN tranyear FORMAT A8
scott@ORA92> COLUMN tranmonth FORMAT A9
scott@ORA92> COLUMN pstatus FORMAT A7
scott@ORA92> PRINT g_ref
Payment Range CHANNEL TRANYEAR TRANMONTH PSTATUS SUMAMOUNT COUNTOFID
--------------------- ------- -------- --------- ------- ---------- ----------
1/ $0 -> $25.00 cha1 2005 10 a 300 2
3/ $50.01 -> $100.00 cha2 2005 09 b 6000 1
scott@ORA92>
|
|
|
| Re: convert sql server stored procedure to oracle [message #142985 is a reply to message #142851 ] |
Tue, 18 October 2005 11:18 |
|
Barbara Boehmer
Messages: 4672
Registered: November 2002
Location: California, USA |
Senior Member |
|
|
Here is a separate copy of just the code without the line numbers, so that you can copy and paste it more easily.
CREATE OR REPLACE PROCEDURE TransferSummary
(p_start_date IN DATE,
p_end_date IN DATE,
p_ref OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_ref FOR
'SELECT "Payment Range", channel, TranYear, TranMonth, pstatus,
SUM (amount) AS SumAmount, COUNT (id) AS CountOfid
FROM (SELECT CASE WHEN TO_NUMBER (BlFundsTransfer.amount) <= 2500
THEN ''1/ $0 -> $25.00''
WHEN TO_NUMBER (BlFundsTransfer.amount) <= 5000
THEN ''2/ $25.01 -> $50.00''
WHEN TO_NUMBER (BlFundsTransfer.amount) <= 10000
THEN ''3/ $50.01 -> $100.00''
WHEN TO_NUMBER (BlFundsTransfer.amount) <= 50000
THEN ''4/ $100.01 -> $500.00''
WHEN TO_NUMBER (BlFundsTransfer.amount) >= 50001
THEN ''5/ > $500.00''
ELSE ''Gap in Range''
END AS "Payment Range",
BlFundsTransfer.channel,
TO_CHAR (BlFundsTransfer.postedDate, ''YYYY'') AS TranYear,
TO_CHAR (BlFundsTransfer.postedDate, ''mm'') AS TranMonth,
pstatus, amount, id
FROM BlFundsTransfer
WHERE postedDate >= :b_start_date
AND postedDate <= :b_end_date
AND pstatus <> ''x'')
GROUP BY "Payment Range", channel, TranYear, TranMonth, pstatus
ORDER BY channel, TranYear, TranMonth, pstatus'
USING p_start_date, p_end_date;
END TransferSummary;
/
SHOW ERRORS
VARIABLE g_ref REFCURSOR
EXECUTE TransferSummary (SYSDATE-100, SYSDATE+1, :g_ref)
COLUMN channel FORMAT A7
COLUMN tranyear FORMAT A8
COLUMN tranmonth FORMAT A9
COLUMN pstatus FORMAT A7
PRINT g_ref
|
|
|
| Re: convert sql server stored procedure to oracle [message #143038 is a reply to message #142985 ] |
Tue, 18 October 2005 20:50 |
AndrewG1976
Messages: 7
Registered: June 2005 |
Junior Member |
|
|
Wow, thanks so much Barbara! This is great!!
Do I have to create the ref cursor separately? I got an error when trying to create the proc.
Thanks
SQL> CREATE OR REPLACE PROCEDURE TransferSummary
2 (p_start_date IN DATE,
3 p_end_date IN DATE,
4 p_ref OUT SYS_REFCURSOR)
5 AS
6 BEGIN
7 OPEN p_ref FOR
8 'SELECT "Payment Range", channel, TranYear, TranMonth, pstatus,
9 SUM (amount) AS SumAmount, COUNT (id) AS CountOfid
10 FROM (SELECT CASE WHEN TO_NUMBER (BlFundsTransfer.amount) <= 2500
11 THEN ''1/ $0 -> $25.00''
12 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 5000
13 THEN ''2/ $25.01 -> $50.00''
14 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 10000
15 THEN ''3/ $50.01 -> $100.00''
16 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 50000
17 THEN ''4/ $100.01 -> $500.00''
18 WHEN TO_NUMBER (BlFundsTransfer.amount) >= 50001
19 THEN ''5/ > $500.00''
20 ELSE ''Gap in Range''
21 END AS "Payment Range",
22 BlFundsTransfer.channel,
23 TO_CHAR (BlFundsTransfer.postedDate, ''YYYY'') AS TranYear,
24 TO_CHAR (BlFundsTransfer.postedDate, ''mm'') AS TranMonth,
25 pstatus, amount, id
26 FROM BlFundsTransfer
27 WHERE postedDate >= :b_start_date
28 AND postedDate <= :b_end_date
29 AND pstatus <> ''x'')
30 GROUP BY "Payment Range", channel, TranYear, TranMonth, pstatus
31 ORDER BY channel, TranYear, TranMonth, pstatus'
32 USING p_start_date, p_end_date;
33 END TransferSummary;
34 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE TRANSFERSUMMARY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
4/21 PLS-00201: identifier 'SYS_REFCURSOR' must be declared
SQL>
|
|
|
| Re: convert sql server stored procedure to oracle [message #143045 is a reply to message #143038 ] |
Tue, 18 October 2005 22:00 |
|
Barbara Boehmer
Messages: 4672
Registered: November 2002
Location: California, USA |
Senior Member |
|
|
I forgot that SYS_REFCURSOR did not exist until 9i. In 8i, you will need to declare a ref cursor type in a package, as shown below. You really should upgrade, since 8i is no longer supported.
scott@ORA92> CREATE OR REPLACE PACKAGE types
2 AS
3 TYPE ref_cursor IS REF CURSOR;
4 END types;
5 /
Package created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PROCEDURE TransferSummary
2 (p_start_date IN DATE,
3 p_end_date IN DATE,
4 p_ref OUT types.ref_cursor)
5 AS
6 BEGIN
7 OPEN p_ref FOR
8 'SELECT "Payment Range", channel, TranYear, TranMonth, pstatus,
9 SUM (amount) AS SumAmount, COUNT (id) AS CountOfid
10 FROM (SELECT CASE WHEN TO_NUMBER (BlFundsTransfer.amount) <= 2500
11 THEN ''1/ $0 -> $25.00''
12 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 5000
13 THEN ''2/ $25.01 -> $50.00''
14 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 10000
15 THEN ''3/ $50.01 -> $100.00''
16 WHEN TO_NUMBER (BlFundsTransfer.amount) <= 50000
17 THEN ''4/ $100.01 -> $500.00''
18 WHEN TO_NUMBER (BlFundsTransfer.amount) >= 50001
19 THEN ''5/ > $500.00''
20 ELSE ''Gap in Range''
21 END AS "Payment Range",
22 BlFundsTransfer.channel,
23 TO_CHAR (BlFundsTransfer.postedDate, ''YYYY'') AS TranYear,
24 TO_CHAR (BlFundsTransfer.postedDate, ''mm'') AS TranMonth,
25 pstatus, amount, id
26 FROM BlFundsTransfer
27 WHERE postedDate >= :b_start_date
28 AND postedDate <= :b_end_date
29 AND pstatus <> ''x'')
30 GROUP BY "Payment Range", channel, TranYear, TranMonth, pstatus
31 ORDER BY channel, TranYear, TranMonth, pstatus'
32 USING p_start_date, p_end_date;
33 END TransferSummary;
34 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> EXECUTE TransferSummary (SYSDATE-100, SYSDATE+1, :g_ref)
PL/SQL procedure successfully completed.
scott@ORA92> COLUMN channel FORMAT A7
scott@ORA92> COLUMN tranyear FORMAT A8
scott@ORA92> COLUMN tranmonth FORMAT A9
scott@ORA92> COLUMN pstatus FORMAT A7
scott@ORA92> PRINT g_ref
Payment Range CHANNEL TRANYEAR TRANMONTH PSTATUS SUMAMOUNT COUNTOFID
--------------------- ------- -------- --------- ------- ---------- ----------
1/ $0 -> $25.00 cha1 2005 10 a 300 2
3/ $50.01 -> $100.00 cha2 2005 09 b 6000 1
scott@ORA92>
|
|
|
| Re: convert sql server stored procedure to oracle [message #143048 is a reply to message #143045 ] |
Tue, 18 October 2005 22:21 |
AndrewG1976
Messages: 7
Registered: June 2005 |
Junior Member |
|
|
Thanks again. I'm not sure why we're still using 8i, but I will mention it to the DBA's.
This stored procedure will be called by an application, the sql server version used the following syntax to execute it:
EXEC TransferSummary(startDate =>'2003/11/01',endDate =>'2004/10/31')
And the Oracle syntax to execute the procedure is:
EXECUTE TransferSummary (SYSDATE-100, SYSDATE+1, :g_ref)
Would the application need to be modified to declare the BIND variable first?
|
|
| |
| Re: convert sql server stored procedure to oracle [message #143101 is a reply to message #143055 ] |
Wed, 19 October 2005 01:42 |
AndrewG1976
Messages: 7
Registered: June 2005 |
Junior Member |
|
|
|
OK thanks very much for all your help.
|
|
|
Goto Forum:
Current Time: Sat Jul 4 19:36:17 CDT 2009
Total time taken to generate the page: 0.01292 seconds
|
]
Members
Search
Help
Register
Login
Home
