This discussion is archived
7 Replies Latest reply: Oct 8, 2013 8:58 AM by Santhu Kalewar RSS

ORA - 24324 : service handle not initialized

733774 Newbie
Currently Being Moderated
We are running on oracle 10g.
 
I am building the sql in the report builder 10g, 

the requirement is when the user passes :p_vendor, one value, then it should
get the result for that vendor, 

if the user does not pass anything then :p_vendor =  'ALL'. (all vendors)

here is my shot at that :
 
 
WITH vendorinfo AS
(
select
 primaryvendor,
   vendor,
   memocount,
   unreadcount,
   unreadperct,
   unreadlist
from inspivendormemo_vw
)
 
SELECT 
 primaryvendor,
   vendor,
   memocount,
   unreadcount,
   unreadperct,
   unreadlist
FROM vendorinfo
WHERE  (:p_vendor = 'ALL' and  primaryvendor IN (SELECT primaryvendor FROM vendorinfo))
or 
:p_vendor != 'ALL'
 
-----------------------------------------
I am getting the following error when i say ok in query builder. :
 
 
ORA - 24324 : service handle not initialized
WHERE  (:p_vendor = 'ALL' and  primaryvendor IN (SELECT primaryvendor FROM vendorinfo))
 
am i doing something wrong? syntax issue? please help.

Billu.
  • 1. Re: ORA - 24324 : service handle not initialized
    AmmadAhmed Guru
    Currently Being Moderated
    If i am getting u properly then try to use your query something like this...
     WITH vendorinfo AS
     (
     select
      primaryvendor,
        vendor,
        memocount,
        unreadcount,
        unreadperct,
        unreadlist
     from inspivendormemo_vw
    )
     SELECT 
      primaryvendor,
        vendor,
        memocount,
        unreadcount,
        unreadperct,
        unreadlist
     FROM vendorinfo
     WHERE primaryvendor = DECODE(:p_vendor,'ALL',primaryvendor,:p_vendor)
    -Ammad
  • 2. Re: ORA - 24324 : service handle not initialized
    733774 Newbie
    Currently Being Moderated
    I am new to the reports. if I were to use lexical parameters how do i proceed.

    I have the following sql that needs to go in where clause using lexical parameters.
    the entire sql code is
    
    WITH vendorinfo AS
    (
    select
     primaryvendor,
       vendor,
       memocount,
       unreadcount,
       unreadperct,
       unreadlist
    from inspivendormemo_vw
    )
     
    SELECT 
     primaryvendor,
       vendor,
       memocount,
       unreadcount,
       unreadperct,
       unreadlist
    FROM vendorinfo
    WHERE  (:p_vendor = 'ALL' and  primaryvendor IN (SELECT primaryvendor FROM vendorinfo))
    or 
    :p_vendor != 'ALL'
    
    if i use lexical paramters i want the below in &where clause, how do i do that?
    
    ((:p_vendor = 'ALL' and  primaryvendor IN (SELECT primaryvendor FROM vendorinfo))
    or 
    :p_vendor != 'ALL'
    
    
    Please help
  • 3. Re: ORA - 24324 : service handle not initialized
    AmmadAhmed Guru
    Currently Being Moderated
    Try to use the query i posted above.
    And the requirement you told in first post it can be solve without LEXICAL parameter. Just use the above query.

    -Ammad
  • 4. Re: ORA - 24324 : service handle not initialized
    733774 Newbie
    Currently Being Moderated
    Thanks for your help Ammad.

    Billu
  • 5. Re: ORA - 24324 : service handle not initialized
    AmmadAhmed Guru
    Currently Being Moderated
    If you got the solution then its better to close the thread.

    -Ammad
  • 6. Re: ORA - 24324 : service handle not initialized
    733774 Newbie
    Currently Being Moderated
    thanks for the help.
  • 7. Re: ORA - 24324 : service handle not initialized
    Santhu Kalewar Newbie
    Currently Being Moderated

    Hi Friend,

     

    Iam getting this error ORA-24324: service handle not initialized .

    the Concurrent Program(Report RDF) is also complete. error.

    and not giving any output file.

     

    MSG-00001: After SRWINIT

    ORA-24324: service handle not initialized

    ==> SELECT V . vendor_name C_VENDOR_NAME , upper ( V . vendor_name ) C_SORT_VENDOR_NAME , V . segment1 C_VENDOR_NUMBER , VS . vendor_site_code C_VENDOR_SITE_CODE , decode ( VS . address_line1 , null , '' , VS . address_REP-0069: Internal error

    REP-57054: In-process job terminated:Terminated with error:

    REP-300: service handle not initialized

    ==> SELECT V . vendor_name C_VENDOR_NAME , upper ( V . vendor_name ) C_SORT_VENDOR_NAME , V . segment1 C_VENDOR_NUMBER , VS . vendor_site_code C_VENDOR_SITE_CODE , decode ( VS . address_line1 , null , '' , VS . address_

     

    So please help on this issue.

     

    Thanks

    San

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points