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.
Please have a look at following code snippet.
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
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;
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 ;
Please don't suggest me to use function as a procedure. Because function is using a Global Temporary Table and at the end it returns that complete table. So using it as a function compulsory.
