OraQA

10G solution to calculate 400!

WITH tmp AS
        (SELECT *
           FROM (SELECT rn,col,prod
                  FROM (  SELECT ROWNUM rn
                            FROM DUAL
                          CONNECT BY ROWNUM<=400
                        )
                      ,(  SELECT ROWNUM col
                            FROM DUAL
                          CONNECT BY ROWNUM<=30
                        )
                MODEL IGNORE NAV RETURN UPDATED ROWS
                DIMENSION BY (rn,col)
                MEASURES (0 prod )
                   RULES (
                   prod[any,any] order by rn,col=(CASE WHEN cv(rn)=1 AND cv(col)=1 THEN 1
                                                       ELSE MOD(prod[cv()-1,cv()],1E30)*cv(rn) + TRUNC(prod[cv(),cv()-1]/1E30)
                                                  END )
                   )
                )
         WHERE rn=400
        )
SELECT LTRIM(REPLACE(MAX(SYS_CONNECT_BY_PATH(LPAD(MOD(prod,1E30),30,'0'),'*')),'*'),'0') AS result
  FROM tmp
START WITH col=(SELECT MAX(col) FROM tmp WHERE prod>0)
CONNECT BY col = PRIOR col-1
/

RESULT
-----------------------------------------------
640345228466238952623479703195030058507025830260029594586844459428023971691868314362784786474632646762943505750358568108482981628835174352289619886468
029979373416541508381624264619423523070462443250151144486708906627739149181173319559964407095496713452904770203224349112107975932807951015453726672516
278778900093497637657103263503315339653498683868313393520243737881577867915063118587026182701698197400629830253085912983461622723045583395207596115053
022360868104332972551948526744322324386699484224042325998055516106359423769613992319171340638589965379701478272066063202173794720103213566246138090779
423045973606995675958360961587151299138222865785795493616176544804532220078258184008484364155912294542753848035583745180226759000613995601455952061272
11192918105032491008000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

11GR2 solution using recursive subquery factoring:
WITH t (n,str,result,roundup) AS (
SELECT 1,CAST('' AS VARCHAR2(4000)),CAST('1' AS VARCHAR2(4000)),0 FROM DUAL
UNION ALL
SELECT CASE WHEN str IS NULL THEN n+1 ELSE n END
      ,CASE WHEN str IS NULL THEN (CASE WHEN roundup=0 THEN '' ELSE TO_CHAR(roundup) END)||result
            WHEN LENGTH(str)>30 THEN SUBSTR(str,1,LENGTH(str)-30)
            ELSE ''
       END
      ,CASE WHEN str IS NULL THEN ''
            ELSE SUBSTR(TO_CHAR(TO_NUMBER(CASE WHEN LENGTH(str)>=30 THEN SUBSTR(str,-30)
                                                 ELSE str
                                          END)*n+roundup+1E30
                               )
                        ,-30)||result
       END
      ,CASE WHEN str IS NULL THEN 0
            ELSE TRUNC((TO_NUMBER(CASE WHEN LENGTH(str)>=30 THEN SUBSTR(str,-30)
                                       ELSE str
                                  END)*n+roundup
                        )/1E30)
       END
  FROM t
 WHERE n<=400
) CYCLE n,result SET cycle_flag TO 'Y' DEFAULT 'N'
SELECT LTRIM(result,'0') FROM t
WHERE n=400 AND str IS NULL;

LTRIM(RESULT,'0')
------------------------------------------------------------------------------------------------------------------------------------------------------
640345228466238952623479703195030058507025830260029594586844459428023971691868314362784786474632646762943505750358568108482981628835174352289619886468
029979373416541508381624264619423523070462443250151144486708906627739149181173319559964407095496713452904770203224349112107975932807951015453726672516
278778900093497637657103263503315339653498683868313393520243737881577867915063118587026182701698197400629830253085912983461622723045583395207596115053
022360868104332972551948526744322324386699484224042325998055516106359423769613992319171340638589965379701478272066063202173794720103213566246138090779
423045973606995675958360961587151299138222865785795493616176544804532220078258184008484364155912294542753848035583745180226759000613995601455952061272
11192918105032491008000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.