Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ORACLE: DUMP RESULT IN A TABLE Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 9:34 PM
Points: 3, Visits: 29
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?
Post #1502493
Posted Tuesday, October 8, 2013 5:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 6,845, Visits: 13,381
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....




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1502887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse