从Excel调用Oracle PL SQL函数

Question

我试图从excel vba调用PL SQL函数。当我在ORACLE SQL DEVELOPER上执行它时，它运行得非常完美。但是，当我试图从excel vba运行相同的时候，它会给出以下错误。

​

​

请看下面的代码片段。

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之后，错误仍然保持原样。变量数据类型已经得到了很好的处理。没有类型错配。

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

下面是我面临错误的函数代码。函数代码非常大，因此只提供所需的代码段。

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，如下所示。

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 ;      

请不要建议我使用函数作为一个过程。因为函数使用的是全局临时表，所以它在最后返回完整的表。因此，将其作为一个函数强制使用.

Answer 1

你的约会工作是完全不正确的。这是很难指出确切的地方，因为有一些潜在的地方。我将解释这段代码的错误之处(摘自您的“第89行”语句)：

... 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执行以下操作：

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

这里您需要的是消除所有不必要的转换：

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

在此之后，您必须仔细检查所有处理日期的其他语句。如果函数以日期作为输入参数，则必须传递日期，如果函数接受字符串--传递字符串。比较:将字符串与字符串进行比较，日期与日期进行比较。