ORACLE: DUMP RESULT IN A TABLE

• nischalinn

  SSC Veteran

  Points: 217

  More actions

  October 8, 2013 at 1:58 am

  #299021

  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?

• LutzM

  SSC Guru

  Points: 107049

  More actions

  October 8, 2013 at 5:59 pm

  #1656967

  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[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]