从Excel VBA调用Oracle PL SQL函数 [英] Calling Oracle PL SQL Function from Excel VBA

问题描述

我正在尝试从excel vba调用PL SQL函数.当我在ORACLE SQL DEVELOPER上执行它时，它可以完美运行.但是，当我尝试从excel vba中运行相同文件时，会出现以下错误.

I am trying to call PL SQL function from excel vba. When i execute it on ORACLE SQL DEVELOPER it runs perfectly. But when i try to run same from excel vba it gives me following error.

请查看以下代码段.

Private Sub CommandButton1_Click()

    Dim con As Object
    Dim rs As Object
    Dim cmd As Object
    Dim sQuery As String
    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set cmd = CreateObject("ADODB.Command")

    strcon = "Provider=OraOledb.Oracle;Data Source=10.1.2.238:1521/oracle.MultiActTrade.LOCAL; User ID=; Password=;Persist Security Info=True"
    con.Open strcon

    sQuery = "SELECT * FROM TABLE(MA_DWM_AVG(TO_CHAR('L48D88-S-IN'),TO_NUMBER(2),TO_NUMBER(11),TO_NUMBER(40)))"
    cmd.ActiveConnection = con
    cmd.CommandText = sQuery
    Set rs = cmd.Execute()

    Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rs

    rs.Close
End Sub

使用sQuery如下所示，错误仍然存​​在.变量数据类型已经非常好了.没有类型不匹配.

Even after using sQuery as follows error remains as it is. The variables data types are already taken care very well. No type mismatch is there.

SELECT * FROM TABLE(MA_DWM_AVG('L48D88-S-IN',2,11,40))

以下是我遇到错误的功能代码.功能代码很大，因此仅提供所需的代码段.

Following is the function code where i am facing an error. Function code is very big so giving the required snippet only.

create or replace function MA_DWM_AVG(FID IN VARCHAR, CHOICE INT, PERIOD1 INT,PERIOD2 INT)
return TEMP_NESTED as

 --to create a seperate transcion for the function we have created.
  PRAGMA AUTONOMOUS_TRANSACTION;
  V_RET TEMP_NESTED;

begin
--TRUNCATING GTT TABLE USED IN A FUNCTION
EXECUTE IMMEDIATE 'TRUNCATE TABLE GTT_DWM_STATS';
--IF DAILTY STATISTICS ARE NEEDED THEN CHOICE=1
--We calculate rownumber,fs_perm_sec_id, date ,closing value its avearages    for 2 periods, open price, high price for the day, low price for the day

--weekly statistics are allculated if choice=2
IF CHOICE =2 AND PERIOD1<>0 AND PERIOD2<>0 THEN
--First we calculate rownumber,fs_perm_sec_id, week end date, Closing price and its average for 2 periods


INSERT INTO GTT_DWM_STATS(ROWNUMBER,FID,CLOSINGDATE,PRICE_CLOSE,MOVINGAVERAGE_PERIOD1,MOVINGAVERAGE_PERIOD2) 
    SELECT ROWNUM,FS_PERM_SEC_ID,"DATE",PPRICE,
        CASE 
            WHEN COUNT(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD1-1 FOLLOWING) >= PERIOD1
        THEN AVG(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD1-1 FOLLOWING)
        ELSE NULL
        END AS "Moving Average Period 1",
        CASE 
            WHEN COUNT(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD2-1 FOLLOWING) >= PERIOD2
        THEN AVG(PPRICE) OVER (ORDER BY "DATE" DESC ROWS BETWEEN CURRENT ROW AND PERIOD2-1 FOLLOWING)
        ELSE NULL
        END AS "Moving Average Period 2"
        FROM(
                SELECT FS_PERM_SEC_ID,"DATE",P_PRICE AS PPRICE,P_VOLUME,
                    CASE 
                        WHEN (TO_CHAR("DATE",'D') >= AVG(TO_CHAR("DATE",'D')) OVER (order by "DATE" DESC rows between 1 preceding and current row) and ROWNUM>=1) 
                            or TO_CHAR("DATE",'D')=6
                    THEN 1
                    ELSE 0
                    END AS WEEKFLAG
                FROM FP_BASIC_BD WHERE FS_PERM_SEC_ID=FID AND P_VOLUME<>0 ORDER BY "DATE" DESC) WHERE WEEKFLAG=1;
--get week start date
--line 89 is here
UPDATE GTT_DWM_STATS 
    SET STARTDATE = 
        (SELECT "DATE" FROM FP_BASIC_BD  WHERE FP_BASIC_BD.FS_PERM_SEC_ID=FID AND FP_BASIC_BD."DATE">=TO_CHAR(TRUNC(TO_DATE(GTT_DWM_STATS.CLOSINGDATE,'DD-MON-YY'), 'IW'),'DD-MON-YY') AND P_VOLUME<>0 AND ROWNUM=1) 
        WHERE EXISTS (SELECT FP_BASIC_BD."DATE" FROM FP_BASIC_BD WHERE FP_BASIC_BD."DATE"=GTT_DWM_STATS.CLOSINGDATE );
--get opening price for the week
UPDATE GTT_DWM_STATS
    SET PRICE_OPEN = 
        (SELECT FP_BASIC_BD.P_PRICE_OPEN FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=GTT_DWM_STATS.FID AND FP_BASIC_BD."DATE"=GTT_DWM_STATS.STARTDATE);
--get high value of p_price_high for week's duration
UPDATE GTT_DWM_STATS 
    SET PRICE_HIGH= 
        (SELECT MAX(P_PRICE_HIGH) FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=FID AND FP_BASIC_BD."DATE" BETWEEN GTT_DWM_STATS.STARTDATE AND GTT_DWM_STATS.CLOSINGDATE)
        WHERE EXISTS(SELECT P_PRICE_HIGH FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=GTT_DWM_STATS.FID);
--get low value of p_price_low for week's duration
UPDATE GTT_DWM_STATS 
    SET PRICE_LOW= 
        (SELECT MIN(P_PRICE_LOW) FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=FID AND FP_BASIC_BD."DATE" BETWEEN GTT_DWM_STATS.STARTDATE AND GTT_DWM_STATS.CLOSINGDATE) WHERE EXISTS(SELECT P_PRICE_LOW FROM FP_BASIC_BD WHERE FP_BASIC_BD.FS_PERM_SEC_ID=GTT_DWM_STATS.FID);


END IF;


--get the GTT values into table derived from object type and return it.
select 
    cast(
    multiset(

                select * from GTT_DWM_STATS WHERE GTT_DWM_STATS.FID=FID ORDER BY CLOSINGDATE DESC

             )as TEMP_NESTED) into v_ret from dual;

   COMMIT;
   return V_RET;

end MA_DWM_AVG;

您还可以将DDL用于全局临时表，如下所示.

You can also refer the DDL for global temporary table which is as follows.

CREATE GLOBAL TEMPORARY TABLE "MA_FACTSET"."GTT_DWM_STATS" 
(   "ROWNUMBER" NUMBER(*,0), 
    "FID" VARCHAR2(20 BYTE), 
    "CLOSINGDATE" DATE, 
    "PRICE_CLOSE" FLOAT(126), 
    "MOVINGAVERAGE_PERIOD1" FLOAT(126), 
    "MOVINGAVERAGE_PERIOD2" FLOAT(126), 
    "STARTDATE" DATE, 
    "PRICE_OPEN" FLOAT(126), 
    "PRICE_HIGH" FLOAT(126), 
    "PRICE_LOW" FLOAT(126)
   ) ON COMMIT PRESERVE ROWS ;      

请不要建议我将函数用作过程.由于函数使用的是全局临时表，最后它返回该完整表.因此，必须将其用作强制功能.

推荐答案

您对日期的处理完全不正确.很难指出确切的位置，因为有一些潜在的位置.我将解释这段代码有什么问题(摘自您的"89行"语句):

Your work with dates is totally incorrect. It's hard to point exact place because there are some potential places. I'll explain what's wrong with this piece of code (it is taken from your "line 89" statement):

... FP_BASIC_BD."DATE">=TO_CHAR(TRUNC(TO_DATE(GTT_DWM_STATS.CLOSINGDATE,'DD-MON-YY'), 'IW'),'DD-MON-YY') ...

GTT_DWM_STATS.CLOSINGDATE具有日期格式(根据您的DDL语句).但是to_date函数将字符串作为第一个参数.在这个地方，Oracle执行以下操作:

GTT_DWM_STATS.CLOSINGDATE has date format (according to your DDL statement). But to_date function takes a string as a first parameter. In this place, Oracle does the following:

• 将日期隐式转换为字符串(使用会话的格式)
• 然后to_date函数尝试将其转换回日期，将字符串视为以'DD-MON-YY'格式写入的日期
• 然后trunc截断日期
• 然后to_char再次将其转换为字符串，然后将字符串与日期进行比较(我想是)FP_BASIC_BD."DATE"
• 如果FP_BASIC_BD."DATE"是日期，则Oracle再次将等号右边的表达式结果隐式转换为日期
• 或者如果FP_BASIC_BD."DATE"是字符串，则Oracle根据字符串比较规则比较字符串.

• implicitly converts date to string (using session's format)
• then to_date function tries to convert it back into a date, treating the string as a date written in 'DD-MON-YY' format
• then trunc truncates the date
• then to_char converts it again into a string, and than you compare your string with the date (I guess) FP_BASIC_BD."DATE"
• if FP_BASIC_BD."DATE" is a date, Oracle converts implicitly the result of expression to the right of equal sign again into a date
• or if FP_BASIC_BD."DATE" is a string, Oracle compares strings acording to rules of a string comparison.

您需要的是摆脱所有不必要的转换:

What you need here is to get rid of all unnecessary transformations:

FP_BASIC_BD."DATE" >= TRUNC(GTT_DWM_STATS.CLOSINGDATE, 'IW')

然后，您必须仔细检查使用日期的所有其他语句.如果函数使用日期作为输入参数，则必须传递日期，如果函数使用字符串-请传递字符串.比较也一样:将字符串与字符串进行比较，将日期与日期进行比较.

And after that you have to check carefully all other statements where you are working with dates. If a function takes a date as input parameter, you have to pass a date, if a function takes a string - pass a string. The same with comparisons: compare a string with a string and a date with a date.

这篇关于从Excel VBA调用Oracle PL SQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！