Re: ACCELERATING STORE PROCEDURE
Date: Thu, 19 Feb 2009 18:11:26 -0800 (PST)
Message-ID: <44a93011-5f61-4907-9ea9-93b613276d5b_at_w1g2000prk.googlegroups.com>
On Feb 19, 5:57 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> Oh hello thank you so much for your help, i do this but i have a
> problem with the DECODE function, i'm receiving this error:
>
> 16:47:37.329 DBMS nmsnoc-db -- Error: PL/SQL: SQL Statement ignored,
> Batch 1 Line 80 Col 17
> 16:47:37.407 DBMS nmsnoc-db -- Error: PL/SQL: ORA-00934: group
> function is not allowed here, Batch 1 Line 82 Col 19
>
> i probe with this (into the sp):
>
> SELECT
> SUM(1) INTO vMIN_VALID_COFETEL,
> SUM(decode(B.MODALIDAD,'CPP',decode(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',1,0),0)) INTO vMOVILES_VALIDOS
> FROM om_db.CDRS A, om_db.cofetel B;
>
> Decode function, could be used inside of an Store Procedure?
>
> Regards
It looks like I made a mistake, let's see if we are able to determine
what is wrong by using a test setup:
First, a table for experimentation:
CREATE TABLE T1 AS
SELECT
MOD(ROWNUM,5) C1
FROM
DUAL
CONNECT BY
LEVEL<=1000;
COMMIT;
Let's try an anonymous PL/SQL block with SUM and DECODE:
DECLARE
vC0 NUMBER;
BEGIN
SELECT
SUM(DECODE(C1,0,1,0)) INTO vC0
FROM
T1;
END;
/
PL/SQL procedure successfully completed.
No errors, so SUM and DECODE are OK to use.
Try #2:
DECLARE
vC0 NUMBER;
vC1 NUMBER;
BEGIN
SELECT
SUM(DECODE(C1,0,1,0)) INTO vC0,
SUM(DECODE(C1,1,0,0)) INTO vC1
FROM
T1;
END;
/
ERROR at line 8:
ORA-06550: line 8, column 5:
PL/SQL: ORA-00934: group function is not allowed here
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored
OK, it looks like we were able to reproduce the problem, (after thinking about it for a couple minutes) I wonder if it is a problem with INTO appearing more than once? Try again, this time defining a type which is a record:
DECLARE
TYPE vTYPE IS RECORD (
vC0 NUMBER,
vC1 NUMBER);
MyValues vTYPE;
BEGIN
SELECT
SUM(DECODE(C1,0,1,0)),
SUM(DECODE(C1,1,1,0)) INTO MyValues
FROM
T1
WHERE
ROWNUM=1;
END;
/
PL/SQL procedure successfully completed.
Looks like the above worked, let's try another experiment:
DECLARE
TYPE vTYPE IS RECORD (
vC0 NUMBER, vC1 NUMBER, vC2 NUMBER, vC3 NUMBER, vC4 NUMBER,
vC5 NUMBER);
MyValues vTYPE;
BEGIN
SELECT
SUM(DECODE(C1,0,1,0)),
SUM(DECODE(C1,1,1,0)),
SUM(DECODE(C1,2,1,0)),
SUM(DECODE(C1,3,1,0)),
SUM(DECODE(C1,4,1,0)),
SUM(DECODE(C1,0,1,0))+SUM(DECODE(C1,1,1,0)) INTO MyValues
FROM
T1;
END;
/
PL/SQL procedure successfully completed.
That worked also, let's extend it again:
CREATE TABLE T2 AS
SELECT
0 C1, 0 C2, 0 C3, 0 C4,
0 C5
FROM
DUAL; DECLARE
TYPE vTYPE IS RECORD (
vC0 NUMBER, vC1 NUMBER, vC2 NUMBER, vC3 NUMBER, vC4 NUMBER,
vC5 NUMBER);
MyValues vTYPE;
BEGIN
SELECT
SUM(DECODE(C1,0,1,0)),
SUM(DECODE(C1,1,1,0)),
SUM(DECODE(C1,2,1,0)),
SUM(DECODE(C1,3,1,0)),
SUM(DECODE(C1,4,1,0)),
SUM(DECODE(C1,0,1,0))+SUM(DECODE(C1,1,1,0)) INTO MyValues
FROM
T1;
UPDATE
T2
SET
C1=C1 + MyValues.vC1,
C2=C2 + MyValues.vC2,
C3=C3 + MyValues.vC3,
C4=C4 + MyValues.vC4,
C5=C5 + MyValues.vC5;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
The above worked (or at least did not throw an error).
SELECT
*
FROM
T2;
C1 C2 C3 C4 C5 ---------- ---------- ---------- ---------- ----------
200 200 200 200 400
Let's try a second time:
DECLARE
TYPE vTYPE IS RECORD (
vC0 NUMBER, vC1 NUMBER, vC2 NUMBER, vC3 NUMBER, vC4 NUMBER,
vC5 NUMBER);
MyValues vTYPE;
BEGIN
SELECT
SUM(DECODE(C1,0,1,0)),
SUM(DECODE(C1,1,1,0)),
SUM(DECODE(C1,2,1,0)),
SUM(DECODE(C1,3,1,0)),
SUM(DECODE(C1,4,1,0)),
SUM(DECODE(C1,0,1,0))+SUM(DECODE(C1,1,1,0)) INTO MyValues
FROM
T1;
UPDATE
T2
SET
C1=C1 + MyValues.vC1,
C2=C2 + MyValues.vC2,
C3=C3 + MyValues.vC3,
C4=C4 + MyValues.vC4,
C5=C5 + MyValues.vC5;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT
*
FROM
T2;
C1 C2 C3 C4 C5 ---------- ---------- ---------- ---------- ----------
400 400 400 400 800
Now, to do the same without PL/SQL...
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu Feb 19 2009 - 20:11:26 CST