October 8, 2013 at 1:58 am
CREATE TABLE TEST_SUM
(
USERID VARCHAR2(10),
DEBIT_AMT NUMBER(19,4),
CREDIT_AMT NUMBER(19,4),
BALANCE_AMT NUMBER(19,4)
)
note: the amount fields in the table is dynamic i.e. here there are only 3 amunt fields but there can be any number of "NUMBER" fields.
INSERT ALL
INTO TEST_SUM VALUES ('001',100,200,300)
INTO TEST_SUM VALUES ('005',500,200,700)
INTO TEST_SUM VALUES ('006',300,200,500)
INTO TEST_SUM VALUES ('003',400,300,700)
SELECT * FROM DUAL;
The below script gives the sum of the amount fields via cursor.
CREATE OR REPLACE PROCEDURE GET_SUM_AMT(VTBL VARCHAR2) AS
TBLCURSOR SYS_REFCURSOR;
TBLSQLSTR VARCHAR2(1000);
IMPORTEDROWS VARCHAR2(1000);
TOTAL_AMT VARCHAR2(1000);
BEGIN
TBLSQLSTR := 'SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME= :VAL_BND
AND
(COLUMN_NAME LIKE UPPER(''%AMT%'') OR COLUMN_NAME LIKE UPPER(''%AMOUNT%''))
AND
DATA_TYPE = ''NUMBER''';
OPEN TBLCURSOR FOR TBLSQLSTR USING VTBL;
LOOP
FETCH TBLCURSOR INTO IMPORTEDROWS;
EXIT WHEN TBLCURSOR%NOTFOUND;
EXECUTE IMMEDIATE 'SELECT SUM( '||IMPORTEDROWS||') FROM '||VTBL INTO TOTAL_AMT;
DBMS_OUTPUT.PUT_LINE(''||IMPORTEDROWS||' = '||TOTAL_AMT);
END LOOP;
CLOSE TBLCURSOR;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'AN ERROR WAS ENCOUNTERED - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
------------------------------------------------------------------------------------------
-- EXEC GET_SUM_AMT('TEST_SUM');
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
The result is:
PL/SQL block, executed in 0.266 sec.
DEBIT_AMT = 1300
CREDIT_AMT = 900
BALANCE_AMT = 2200
Total execution time 1.066 sec.
-------****************---------------
But I want the result in tabular form same as we get the result from this query:
SELECT Sum(DEBIT_AMT),Sum(CREDIT_AMT),Sum(BALANCE_AMT) FROM TEST_SUM;
. How can I do it?
October 8, 2013 at 5:59 pm
This is usually a SQL Server site.
If you get lucky, there'll be a person lurking around having the ORACLE knowledge required. But that would be random.
For Oracle, asktom.oracle.com is a good resource, if I remember correctly....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply