Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Toronto, Canada
    Posts
    3

    Unanswered: program lvl query to package procedure problem

    I have been converting all the SQL queries from our application into stored procedures and have run into a point of confusion with my one remaining query. Please note i am not looking for specific code to solve my problem just an explanation of why a certain query that works fine as a stand alone query does not work from with in a stored procedure.

    the following is the SQL query which works fine when executed from SQLPlus: (when the parameters are assigned the appropriate values.)

    select distinct a.FND_ID,
    (select CATG.CATG_NM_EN from FND, CATG where FND.CATG_ID =
    CATG.CATG_ID and FND.FND_ID = a.FND_ID),
    (select FND_NM_EN from FND where FND.FND_ID = a.FND_ID),
    (select URL_EN from FND where FND.FND_ID = a.FND_ID),
    (select YTD from FND_MTHLY where a.FND_ID = FND_MTHLY.FND_ID AND AS_AT_DT = (select MAX(AS_AT_DT) from FND_MTHLY) and TO_CHAR(FND_MTHLY.AS_AT_DT,'YYYY') = year),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 1),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 2),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 3),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 4),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 5),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 6),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 7),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 8),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 9),
    (select ANN_RET from ANN_RET where a.FND_ID = FND_ID and RET_YR = MaxYear - 10),
    LastDayOfPreviousMonth,
    (select FND.CATG_ORDER from FND where FND.FND_ID = a.FND_ID),
    (select FND.NOTE_SYMBOLS from FND where FND.FND_ID = a.FND_ID),
    (select CATG.CATG_ID from FND, CATG where FND.CATG_ID = CATG.CATG_ID and FND.FND_ID = a.FND_ID),
    (select max(AS_AT_DT) from FND_DLY) as DLY_DT

    from ANN_RET a, FND
    where FND.SRS_TYP = Series
    and FND.PUBLIC_IND = PubAccess
    and FND.FND_ID = a.FND_ID;


    Within the package procedure, I try to open a reference cursor which is returned to the application. The error occurs when calling the first braketed select statement. Could someone explain why such a statement is can be executed indipendantly but not from with in a stored procedure. I am running Oracle 8.1.7.

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    One reason could be permissions. Privs won't be inherited from roles when running a package or stored procedure, so make sure the user is granted the necessary privs on each table or view directly and not through roles.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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