Database Connector Error:'ORA-24338'

[ekta22]

Hi,

I am receiving the above error (ORA-24338 statement handle not executed) when an exception is thrown from the stored procedure I am calling.

I am using Crystal XI and Oracle 10g.

Any ideas why I may be getting this error. My package compiles just fine.

Thanks,

-E

 

[hilfy]

Just because a stored procedure compiles, does not mean that it works correctly. Have you tested it outside of Crystal. From the Oracle documentation, the explanation for this error is:

ORA-24338 statement handle not executed
Cause: A fetch or describe was attempted before executing a statement handle.
Action: Execute a statement and then fetch or describe the data.

So it sounds to me like you're trying to do a fetch from a cursor without first opening the cursor.

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ekta22]

Hi,

It works fine in Oracle. It complies and when I run it I do see the results too. But when I try to connect to Crystal I get an error. I am posting the code below:

create or replace
PACKAGE BODY REPORT_P_LOGALL_TEST AS

 var_INNERSQL VARCHAR2(10000);
 var_WHERE VARCHAR2(10000);
 var_OWHERE VARCHAR2(10000);
 
 FUNCTION BUILDLPMLCESQL(pLOG_TYPE IN CODE_CATEGORY_LU.LOG_TYPE%TYPE) RETURN VARCHAR2 IS
  var_ILPMLCESQL VARCHAR2(10000);
  var_OLPMLCESQL VARCHAR2(10000);
 BEGIN
  BEGIN
    IF pLOG_TYPE = '' THEN
      var_ILPMLCESQL :=  var_INNERSQL || ' WHERE CAT.LOG_TYPE IN (''LPM'', ''LCE'') AND ' || var_WHERE;
    ELSE
      var_ILPMLCESQL :=  var_INNERSQL || ' WHERE CAT.LOG_TYPE = ''' || pLOG_TYPE || ''' AND ' || var_WHERE;
    END IF;  
    
    var_OLPMLCESQL := '
    SELECT LA.LOG_ID, LA.LEGACY_SEQUENCE, LA.LOG_STATUS, LA.FAC_TYPE, LA.FAC_IDENT, LA.CODE_CATEGORY, LA.SUPPLEMENTAL_CODE, 
      LA.INTERRUPT_CONDITION, LA.MAINT_ACTION_CODE, LA.START_DATETIME, LA.END_DATETIME, LA.MODIFIED_BY, LA.MODIFIED_DATETIME,
      LA.TIME_REQUIRED_HOURS, LA.TIME_REQUIRED_MINUTES,
      LA1.LOG_TYPE, 
      CSC.CERT_STATEMENT_CODE, 
      LA1.WR_AREA, LA1.DC, LA1.FAC_REPORT_FLAG,
      LA1.DC,
      LA1.SHORT_NAME, LA1.EQUIPMENT_IDENT, LA1.FA_CA_NUMBER, LA1.EQ_SERIAL_NUMBER, LA1.LOCATION, LA1.EQ_CAGE_CODE,
      LA1.MODULE_IDENT, LA1.MOD_SERIAL_NUMBER, LA1.MOD_DESCRIPTON, LA1.MOD_CAGE_CODE,
      P.LOGON_SECTOR_CODE, P.LOGON_INITIALS,    
      PM.LOG_ID, PM.INTERVAL_CODE, PM.ASSIGNMENT_NUMBER, PM.WR_CREW_UNIT, PM.WR_WATCH, PM.EARLIEST_DATE, PM.SCHEDULED_DATE, PM.LATEST_DATE,
      PM.NUMBER_OF_TASKS, PM.DIRECTIVE_LEVEL, PM.PUBLICATION_ORDER, PM.PUBLICATION_PARAGRAPH, PM.PUBLICATION_DETAIL, PM.GROUP_CODE,    
      T.TASK_GLOSSARY_CODE,
      NULL AS FAULT_LOCATION_CODE,       
      NULL AS BACKUP_SYSTEM_ID, 
      NULL AS BACKUP_START_DATETIME,
      NULL AS BACKUP_END_DATETIME,      
      NULL AS CHANNEL,
      NULL AS LINE_FREQ_FLAG,
      NULL AS UHF_VHF_INDIC,
      NULL AS T_TICKET,
      NULL AS LINE_NUMBER,
      NULL AS U_FREQ,
      NULL AS V_FREQ,
      NULL AS T_PROVIDER,
      NULL AS PRIMARY_SPARE,
      NULL AS BACKUP_SYSTEM,
      NULL AS PRIME_RECOVERY_MODE,
      NULL AS CAUSE_CODE,
      NULL AS DESCRIPTION
    FROM (' || var_ILPMLCESQL || 
    ') LA1
    JOIN LOG_AU LA ON LA.LOG_ID = LA1.LOG_ID 
    JOIN PEOPLE P ON LA.MODIFIED_BY = P.ID    
    LEFT JOIN LOG_PM_CERT_AU PM ON LA.LOG_ID = PM.LOG_ID AND LA.MODIFIED_DATETIME = PM.MODIFIED_DATETIME AND LA.LEGACY_SEQUENCE = PM.LEGACY_SEQUENCE
    LEFT JOIN CERT_STATEMENT_CODE CSC ON PM.CERT_STATEMENT_CODE_ID = CSC.CERT_STATEMENT_CODE_ID 
    LEFT JOIN TASK_GLOSSARY_CODE T ON PM.TASK_GLOSSARY_CODE_ID = T.TASK_GLOSSARY_CODE_ID ' || var_OWHERE;
    
    RETURN var_OLPMLCESQL;
    END;
 END;
 
  
 FUNCTION BUILDLIRSQL
  (pCAUSE LIR_CAUSE_CODE_LU.CAUSE_CODE%TYPE, 
  pCAUSE_DESC LOG_LIR_AU.CAUSE_DESCRIPTION_TEXT%TYPE) 
  RETURN VARCHAR2 IS
 
  var_ILIRSQL VARCHAR2(10000);
  var_OLIRSQL VARCHAR2(10000);
  var_LIRWHERE VARCHAR2(10000);
 BEGIN
  BEGIN
    var_ILIRSQL :=  var_INNERSQL || ' WHERE CAT.LOG_TYPE = ''LIR'' AND ' || var_WHERE;
    
    IF (LENGTH(pCAUSE) > 0) THEN
      var_LIRWHERE := var_LIRWHERE || 'LCC.CAUSE_CODE = ''' || pCAUSE || ''' AND ';
    END IF;
    
    IF (LENGTH(pCAUSE_DESC) > 0) THEN
      var_LIRWHERE := var_LIRWHERE || 'LIR.CAUSE_DESCRIPTION_TEXT = ''' || pCAUSE_DESC || ''' AND ';
    END IF;
    
    IF (LENGTH(var_LIRWHERE) > 0) THEN
      var_LIRWHERE := SUBSTR(var_LIRWHERE, 1, LENGTH(var_LIRWHERE) - 4);
      IF (LENGTH(var_OWHERE) > 0) THEN
        var_OWHERE := ' AND ' || var_LIRWHERE;
      ELSE
        var_OWHERE := ' WHERE ' || var_LIRWHERE;
      END IF;  
    END IF;
    
    var_OLIRSQL := '
    SELECT LA.LOG_ID, LA.LEGACY_SEQUENCE, LA.LOG_STATUS, LA.FAC_TYPE, LA.FAC_IDENT, LA.CODE_CATEGORY, LA.SUPPLEMENTAL_CODE, 
      LA.INTERRUPT_CONDITION, LA.MAINT_ACTION_CODE, LA.START_DATETIME, LA.END_DATETIME, LA.MODIFIED_BY, LA.MODIFIED_DATETIME,
      LA.TIME_REQUIRED_HOURS, LA.TIME_REQUIRED_MINUTES,
      LA1.LOG_TYPE, 
      NULL AS CERT_STATEMENT_CODE, 
      LA1.WR_AREA, LA1.DC, LA1.FAC_REPORT_FLAG,
      LA1.DC,
      LA1.SHORT_NAME, LA1.EQUIPMENT_IDENT, LA1.FA_CA_NUMBER, LA1.EQ_SERIAL_NUMBER, LA1.LOCATION, LA1.EQ_CAGE_CODE,
      LA1.MODULE_IDENT, LA1.MOD_SERIAL_NUMBER, LA1.MOD_DESCRIPTON, LA1.MOD_CAGE_CODE,
      P.LOGON_SECTOR_CODE, P.LOGON_INITIALS,    
      NULL AS LOG_ID, NULL AS INTERVAL_CODE, NULL AS ASSIGNMENT_NUMBER, NULL AS WR_CREW_UNIT, NULL AS WR_WATCH, NULL AS EARLIEST_DATE, NULL AS SCHEDULED_DATE, NULL AS LATEST_DATE,
      NULL AS NUMBER_OF_TASKS, NULL AS DIRECTIVE_LEVEL, NULL AS PUBLICATION_ORDER, NULL AS PUBLICATION_PARAGRAPH, NULL AS PUBLICATION_DETAIL, NULL AS GROUP_CODE,    
      NULL AS TASK_GLOSSARY_CODE,
      --LIR
      LIR.FAULT_LOCATION_CODE,       
      LIR.BACKUP_START_DATETIME,
      LIR.BACKUP_END_DATETIME,      
      LL.CHANNEL,
      LL.LINE_FREQ_FLAG,
      LL.UHF_VHF_FLAG,
      LL.T_TICKET,
      LL.LINE_NUMBER,
      LL.U_FREQ,
      LL.V_FREQ,
      LL.T_PROVIDER,
      LL.PRIMARY_SPARE,
      BSL.BACKUP_SYSTEM,
      PRL.PRIME_RECOVERY_MODE,
      LCC.CAUSE_CODE,
      LCC.DESCRIPTION
    FROM (' || var_ILIRSQL || 
    ') LA1
    JOIN LOG_AU LA ON LA.LOG_ID = LA1.LOG_ID 
    JOIN PEOPLE P ON LA.MODIFIED_BY = P.ID    
    LEFT JOIN LOG_LIR_AU LIR ON LA.LOG_ID = LIR.LOG_ID AND LA.MODIFIED_DATETIME = LIR.MODIFIED_DATETIME AND LA.LEGACY_SEQUENCE = LIR.LEGACY_SEQUENCE
    LEFT JOIN BACKUP_SYSTEM_LU BSL ON LIR.BACKUP_SYSTEM_ID = BSL.BACKUP_SYSTEM_ID
    LEFT JOIN PRIME_RECOVERY_LU PRL ON LIR.PRIME_RECOVERY_ID = PRL.PRIME_RECOVERY_ID 
    LEFT JOIN LIR_CAUSE_CODE_LU LCC ON LIR.CAUSE_CODE_ID = LCC.LIR_CAUSE_CODE_ID 
    LEFT JOIN LOG_LINE_AU LL ON LA.LOG_ID = LL.LOG_ID AND LA.MODIFIED_DATETIME = LL.MODIFIED_DATETIME ' || var_OWHERE;
          
    RETURN var_OLIRSQL;
    END;
 END;
  
 PROCEDURE LOGALL(
 LOGALL_CURSOR IN OUT LOGALL_TYPE,
 pDISTRICT_CODE IN VARCHAR,
 pSTART_DATE IN LOGGING.LOG.START_DATETIME%TYPE,  
 pEND_DATE IN LOGGING.LOG.END_DATETIME%TYPE,
 pFAC_TYPE IN FACILITIES.FAC_TYPE%TYPE,
 pFAC_IDENT IN FACILITIES.FAC_IDENT%TYPE,
 pLOG_TYPE IN CODE_CATEGORY_LU.LOG_TYPE%TYPE, 
 pCAUSE IN LIR_CAUSE_CODE_LU.CAUSE_CODE%TYPE,
 pCAUSE_DESC IN LOG_LIR_AU.CAUSE_DESCRIPTION_TEXT%TYPE) AS
 
 var_SQL VARCHAR2(32767);
 BEGIN
    
    var_INNERSQL := 'SELECT L.LOG_ID, L.LOG_STATUS, L.MODIFIED_DATETIME, L.START_DATETIME, L.END_DATETIME, L.CODE_CATEGORY,
      L.SUPPLEMENTAL_CODE, L.MAINT_ACTION_CODE,
      CAT.LOG_TYPE,
      F.FAC_TYPE, F.FAC_IDENT, F.WR_AREA, F.SERVICE_AREA||F.DISTRICT_OFFICE||F.DO_GROUP||F.DO_SSC AS DC, F.FAC_REPORT_FLAG,
      EQ.SHORT_NAME, EQ.EQUIPMENT_IDENT, EQ.FA_CA_NUMBER, EQ.SERIAL_NUMBER AS EQ_SERIAL_NUMBER, EQ.LOCATION, EQ.CAGE_CODE AS EQ_CAGE_CODE,
      MO.MODULE_ID, MO.MODULE_IDENT, MO.SERIAL_NUMBER AS MOD_SERIAL_NUMBER, MO.DESCRIPTION AS MOD_DESCRIPTON, MO.CAGE_CODE AS MOD_CAGE_CODE
      FROM LOG L
      JOIN FACILITIES F ON L.FAC_TYPE = F.FAC_TYPE AND L.FAC_IDENT = F.FAC_IDENT
      JOIN CODE_CATEGORY_LU CAT ON L.CODE_CATEGORY=CAT.CODE_CATEGORY
      LEFT JOIN EQUIPMENT EQ ON L.EQUIPMENT_ID = EQ.EQUIPMENT_ID 
      LEFT JOIN MODULE MO ON EQ.EQUIPMENT_ID = MO.EQUIPMENT_ID';
      
    IF (LENGTH(pDISTRICT_CODE) > 0) THEN
      var_WHERE := var_WHERE || 'F.SERVICE_AREA||F.DISTRICT_OFFICE||F.DO_GROUP||F.DO_SSC LIKE ''' || pDISTRICT_CODE || '%'' AND ';
    END IF;
    
    IF (LENGTH(pSTART_DATE) > 0) THEN
      var_WHERE := var_WHERE || 'L.START_DATETIME = ''' || pSTART_DATE || ''' AND ';
    END IF;
    
    IF (LENGTH(pEND_DATE) > 0) THEN
      var_WHERE := var_WHERE || 'L.END_DATETIME = ''' || pEND_DATE || ''' AND ';
    END IF;
    
    IF (LENGTH(pFAC_TYPE) > 0) THEN
      var_WHERE := var_WHERE || 'L.FAC_TYPE = ''' || pFAC_TYPE || ''' AND ';
    END IF;
    
    IF (LENGTH(pFAC_IDENT) > 0) THEN
      var_WHERE := var_WHERE || 'L.FAC_IDENT = ''' || pFAC_IDENT || ''' AND ';
    END IF;
    
    
    var_WHERE := SUBSTR(var_WHERE, 1, LENGTH(var_WHERE) - 4);
    
    var_OWHERE := '';
    IF (LENGTH(pLOGON_SEC_CODE) > 0) THEN
      var_OWHERE := var_OWHERE || 'P.LOGON_SECTOR_CODE = ''' || pLOGON_SEC_CODE || ''' AND ';
    END IF;
    
    IF (LENGTH(pINITIALS) > 0) THEN
      var_OWHERE := var_OWHERE || 'P.LOGON_INITIALS = ''' || pINITIALS || ''' AND ';
    END IF;
    
    IF (LENGTH(var_OWHERE) > 0) THEN
      var_OWHERE := SUBSTR(var_OWHERE, 1, LENGTH(var_OWHERE) - 4);
      var_OWHERE := ' WHERE ' || var_OWHERE;
    END IF;
    
    IF (LENGTH(pLOG_TYPE) > 0) THEN
      CASE
        WHEN pLOG_TYPE IN ('LPM', 'LCE') THEN var_SQL := BUILDLPMLCESQL(pLOG_TYPE);        
        WHEN pLOG_TYPE IN ('LIR') THEN var_SQL := BUILDLIRSQL(pCAUSE, pCAUSE_DESC);
      END CASE;
      
    ELSE
      var_SQL := BUILDLPMLCESQL('') || ' UNION ALL ' || BUILDLCMSQL() 
      || ' UNION ALL ' || BUILDLIRSQL(pCAUSE, 
      pCAUSE_DESC) || ' ORDER BY LA.LOG_ID, LA.MODIFIED_DATETIME';
    END IF;
   
    OPEN LOGALL_CURSOR FOR var_SQL;     
    
    
  END LOGALL;   
  
END REPORT_P_LOGALL_TEST;

Appreciate any help!

 

[hilfy]

Take a look at the SQL that Crystal is passing to the package. Specifially look at the parameters and what they're set to. Copy that SQL and run it without any changes in the database outside of Crystal.

I suspect that something isn't working right with the parameters - they're in the wrong order or something of the sort. It would be good if there was a way to get the SQL string that's being built inside your PL/SQL. That will indicate where the problem is.

-Dell


A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ekta22]

How do I look at the SQl crystal is passing to the package? I get the error as soon as I connect to the package via Crystal and enter the paramters. Even I think it has something to do with the parameters. Because I do see all the fields but as soon as I enter the paramters and run it I get the error message.

I ran the SQl created by my package along with the parameters and it runs fine in Oracle and gives me the data back. One question I have in mind is.. I have an inner query in my select statement and I am passing some of my parameters to that inner query. Do you think that crystal does not recognizes the parameters being passed to the inner query??

 

[hilfy]

What is the command that you're using in Crystal?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ekta22]

Command?

I click on New Report, connect to Oracle database using Native Oracle Connection. I select my package. It pops up the parameters dialog box. After entering the parameters when I select my fields and click Finish. That's when I get the error message. When I go to Database-> Show SQl Query this is what I see

BEGIN "LOGGING"."REPORT_P_LOGALL_TEST"."LOGALL"(:LOGALL_CURSOR, 'WW', 'GS', 'LCM'); END ;

 

[hilfy]

I haven't done a lot of work with stored procedures in Crystal, so I'm not sure how Crystal "sees" the cursor as a param. I would try this - in your stored procedure, change the Logall_Cursor from "In Out" to just "Out".

It may be that the procedure is actually looking for an actual input value from the parameter when it tries to validate it and that's what causing your error.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.