Results 1 to 2 of 2

Thread: invalid cursor

  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Unanswered: invalid cursor

    I promoted the following procedure to production. In UAT the report can be executed without any problem from Crystal Reports. But in Production I am getting Invalid Cursor Ora-01001 message. The volumn of data is the same in both environments and the privilidges are the same also. Is there any other reason why this is happening in Production

    many thanks

    Kerrie

    CREATE OR REPLACE PROCEDURE TA_USER.PROC_BANK_OF_ITALY_SUBS_REDS (
    SPprmEnv IN varchar2
    ,SPprmFund IN varchar2
    ,SPprmStartDate IN date
    ,SPprmEndDate IN date
    ,SPprmReturn_Cursor IN OUT Cursor_Package.Crystal_Cur_Type
    )

    AS

    v_selectStmt Varchar2(32767);

    BEGIN

    delete from TMP_IFAST_SUM_TRANS;
    delete from TMP_IFAST_FUNDCLASS_BAL_BYDAY;

    PROC_GET_PRICES (SPprmEnv, SPprmFund, SPprmEndDate,null); --populate start price temp tables with the month end price

    --Get the Closing Balance
    insert into TMP_IFAST_SUM_TRANS
    select env
    ,1 --dummy
    ,fundcode
    ,fundclass
    ,sum(units)
    from IFASTTRANSACTION
    where tradedate > SPprmEndDate
    and env = SPprmEnv
    group by env
    ,fundcode
    ,fundclass;

    insert into TMP_IFAST_FUNDCLASS_BAL_BYDAY --get closing balances i.e. current bal - sum of transactions occuring after end date
    select h.env
    ,h.fundcode
    ,h.fundclass
    ,SPprmEndDate
    ,h.units - decode(t.units,null,0,t.units)
    ,0 --not used for this query
    from (select env
    ,fundcode
    ,fundclass
    ,sum(settledunits + unsettledunits) units
    from IFASTHOLDING
    where env = SPprmEnv
    group by env
    ,fundcode
    ,fundclass) h
    ,TMP_IFAST_SUM_TRANS t
    where h.env = t.env(+)
    and h.fundcode = t.fundcode(+)
    and h.fundclass = t.fundclass(+);

    v_selectStmt := 'select ''ITA'' as registeredownercountry
    ,''ITALY'' as registeredownercountrydesc
    ,FUNDNAME || '' '' || decode(description,null,f.fundclass,description) funddescription
    ,sum (case when transactiontype in (''ED'',''PUR'',''WOP'')
    then grossamount
    else 0 end) "Sales in Italy - Value"
    ,sum (case when transactiontype in (''ED'',''PUR'',''WOP'')
    then t.units
    else 0 end) "Sales in Italy - Units"
    ,sum (case when transactiontype in (''PW'',''RED'',''WOR'')
    then grossamount
    else 0 end) "Redemptions in Italy - Value"
    ,sum (case when transactiontype in (''PW'',''RED'',''WOR'')
    then t.units
    else 0 end) "Redemptions in Italy - Units"
    ,sum (grossamount) "Net Sales in Italy - Value"
    ,sum (t.units) "Net Sales in Italy - Units"
    ,sum (case when transactiontype in (''ED'',''PUR'',''WOP'')
    then grossamount*p.nav
    else 0 end) "Shares placed in Italy - Value"
    ,sum (case when transactiontype in (''ED'',''PUR'',''WOP'')
    then t.units
    else 0 end) "Shares placed in Italy - Units"
    ,h.units*p.nav
    ,h.units
    from IFASTTRANSACTION t
    ,IFASTACCOUNT a
    ,IFASTFUND f
    ,IFASTCLASS c
    ,TMP_IFAST_START_PRICE p
    ,TMP_IFAST_FUNDCLASS_BAL_BYDAY h
    where t.ENV = ''' || SPprmEnv || '''
    and t.tradedate >= ''' || SPprmStartDate || '''
    and t.tradedate < = ''' || SPprmEndDate || '''
    and transactiontype in (''ED'',''PW'', ''RED'',''PUR'', ''WOR'',''WOP'')
    and t.env = a.env
    and t.accountnumber = a.accountnumber
    and registeredownercountry = ''ITA''
    and t.env = f.env
    and t.fundcode = f.fundcode
    and t.fundclass = f.fundclass
    and f.env = c.env(+)
    and f.fundclass = c.fundclass(+)
    and f.env = p.env
    and f.fundcode = p.fundcode
    and f.fundclass = p.fundclass
    and f.env = h.env
    and f.fundcode = h.fundcode
    and f.fundclass = h.fundclass
    group by FUNDNAME || '' '' || decode(description,null,f.fundclass,description)
    ,h.units*p.nav
    ,h.units';

    open SPprmReturn_Cursor
    for v_selectStmt;

    END PROC_BANK_OF_ITALY_SUBS_REDS;

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Did you miss any db columns referenced in the cursor in prod?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •