Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: Encountered the symbol ";" when expecting one of the following:
PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278285] Fri, 02 November 2007 12:48 Go to next message
aks888
Messages: 4
Registered: November 2007
Junior Member

I have a stored procedure that runs absolutely fine when executed in the RAPID SQL. But when I call it from Java code it throws an exception.I have included the stored procedure and java code below. I would appreciate any help on this.

Thanks in advance.

Here is the stored procedure:
**************************************************************
PROCEDURE CORE_PROC_QUAL_EVENTS 
(
    in_proc_system IN varchar2, 
    in_batch_id    IN varchar2    
    --out_ret_code   OUT number,
    --out_ret_msg    OUT varchar2
    --out_records_processed OUT number
)
IS


    -------------------------------------------
    -- Control tables Needed for Qualification
    -------------------------------------------    
   CURSOR cur_core_proc_sys_control
    IS
        select proc_system,
               default_days_in_future,
               default_interest_peroid,
               position_check_ind,
               key_date_check_ind,
               event_type_ind,
               num_days_to_keep_position,
               file_format
         from core_proc_sys_control     
        where  proc_system = in_proc_system
          and logical_delete_ind = 'N';
           
    
    -------------------------------------------
    -- DATA Needing qualifying
    -------------------------------------------    

    CURSOR cur_unqual_ca_on_gateway
    IS
    select g.ca_id, g.max_seq 
    from ( select distinct(corp_action_id) as ca_id,
                  max(message_sequence) over (partition by corp_action_id) as max_seq       
             from core_event_gateway) g
            where g.ca_id not in 
         ( select distinct(corp_action_id)
            from core_events_of_interest);

   -------------------------------------------------------
   -- Variables to hold cursor values.
   -------------------------------------------------------
    v_corp_action_id            varchar2(20) := NULL;
    v_max_seq_num               number       := 0;
    v_proc_system               varchar2(3)  := Null;
    v_default_days_in_future    number       := 0;
    v_default_interest_peroid   number       := 0;
    v_sys_position_check_ind    varchar2(1)  := Null;
    v_key_date_check_ind        varchar2(1)  := Null;
    v_event_type_ind            varchar2(1)  := Null;
    v_num_days_to_keep_position number       := 0;
    v_file_format               varchar2(5)  := Null;
    v_core_event_type_id        number       := 0;
    v_event_type_cd             varchar2(4)  := null;
    v_event_type_desc           varchar2(50) := null;
    v_include_ind               varchar2(1)  := null;
    v_days_in_future            number       := 0;
    v_period_of_interest        number       := 0;
    v_position_check_ind        varchar2(1)  := null;
    v_records_getting_prod_keys number       := 0;  
   -------------------------------------------------------
   -- Variables need for work..
   -------------------------------------------------------
   v_corp_action_type               varchar2(20) := Null;
   v_identifying_security_id        varchar2(40) := Null;
   v_identifying_security_id_type   varchar2(40) := Null;
   v_records_processed              number       := 0;  
   v_records_to_qualify             number       := 0;  
   v_ca_qualified                   number       := 0;
   v_ca_qualified_count             number       :=0;
   v_min_event_id                   number       := 0;
   v_event_date_value               date         := null;
   v_core_event_id                  number       := null;
   v_date_added                     date         := null;
   v_product_keys_found             number       := 0;
   v_pos_found                      number       := 0;
   v_product_key_type               varchar2(12) := null;
   v_ignore_this_ca                 number       := 0;
   v_records_failed_posQ            number       := 0;
   v_records_with_pos_check         number       := 0;
   v_key_date_count                 number       := 0;
   V_RECORD_DATE_CD                 number       := 2;
   -------------------------------------------------------
   -- Variables to collect stats
   -------------------------------------------------------
   v_records_qualified_pos          number       := 0;
   v_records_qualified_other        number       := 0;
   v_records_unqualified            number       := 0;
   v_records_auto_qualified         number       := 0;
   v_records_qual_keyDate           number       := 0;
   v_records_ignored                number       := 0;
   v_records_with_kdc               number       := 0;
   
BEGIN
    v_records_processed := 0;
    -- LOAD CONTROL TABLE
    if not cur_core_proc_sys_control%ISOPEN then
        open cur_core_proc_sys_control;
    end if;
    
    fetch cur_core_proc_sys_control
     into  v_proc_system,               
           v_default_days_in_future,    
           v_default_interest_peroid,   
           v_sys_position_check_ind,        
           v_key_date_check_ind,        
           v_event_type_ind,            
           v_num_days_to_keep_position, 
           v_file_format;               
     
    --LOAD DATA TO QUALIFY
    if not cur_unqual_ca_on_gateway%ISOPEN then
        open cur_unqual_ca_on_gateway;
    end if;
    
    fetch cur_unqual_ca_on_gateway
     into v_corp_action_id,
          v_max_seq_num;
          
    while cur_unqual_ca_on_gateway%FOUND 
    Loop
        BEGIN
         -- Get Data required for qualification
            select e.corp_action_type_cd, 
                   e.identifying_security_id,
                   e.identifying_security_id_type,
                   decode (e.identifying_security_id_type,
                           '02', 'CUSIP',
                           '03', 'SEDOL',
                           '04', 'ISIN',
                           'XX') as product_key_type,     
                   t.key_date_check_ind,
                   t.position_check_ind,
                   e.core_event_id
              into v_corp_action_type,
                   v_identifying_security_id, 
                   v_identifying_security_id_type,
                   v_product_key_type,
                   v_key_date_check_ind,
                   v_position_check_ind,
                   v_core_event_id
              from core_event e,
                   core_event_gateway g,
                   core_event_type t
             where g.corp_action_id      = v_corp_action_id
               and g.message_sequence    = v_max_seq_num
               and g.status              = 'R'
               and g.core_event_id       = e.core_event_id
               and t.event_type_cd       = e.corp_action_type_cd
               and t.proc_system         = in_proc_system
               and t.include_ind         = 'Y'
               and t.logical_delete_ind  = 'N';               
              
        EXCEPTION
            when NO_DATA_FOUND then
                 v_ignore_this_ca :=1;   
                 v_records_ignored := v_records_ignored + 1;
        end; 
        
        if v_ignore_this_ca = 0 then
            -- START OF QUALIFICATION FOR THIS CA
            v_records_to_qualify := v_records_to_qualify + 1;
            v_ca_qualified := 0;
            
            -----------------------------------
            -- Start of key date check
            -----------------------------------            
            if v_key_date_check_ind = 'Y' then        
               v_records_with_kdc :=  v_records_with_kdc + 1;      
               begin
               select count(*)
                 into v_key_date_count
                 from (select event_date_value as recDate                   
                         from core_event_diary_date 
                        where core_event_id = v_core_event_id
                          and event_date_cd = V_RECORD_DATE_CD
                          and logical_delete_ind = 'N'
                          and event_date_value is not null)recDate,
                      (select stamp_add_dtime as addDate
                        from core_event_gateway
                       where core_event_id in
                      ( select min(core_event_id)
                         from core_event_gateway
                        where corp_action_id = v_corp_action_id ))addDate
               where recDate.recDate < addDate.addDate;
               EXCEPTION
                   when NO_DATA_FOUND then
                      v_key_date_count := 0;
               end;
               
               if v_key_date_count > 0 then
                  v_ca_qualified := 1;   
                  v_records_qual_keyDate := v_records_qual_keyDate +1;
               end if;
            end if; 
            -----------------------------------
            -- End of key date check
            -----------------------------------            
        
            -----------------------------------
            -- Start of Position check
            -----------------------------------                    
            if v_ca_qualified = 0 then
               --position check 
               if v_position_check_ind = 'Y' then
                  v_records_getting_prod_keys := v_records_getting_prod_keys + 1;              
       
                  -- if we don't have keys for this product then auto qualify it.
                  begin
                      select count(*) as ProducKeyCount
                        into  v_product_keys_found
                        from  core_master_product m
                       where  m.logical_delete_ind = 'N'
                         and  m.product_identifier_cd = v_identifying_security_id
                         and  m.product_identifier_type = v_product_key_type;
                  EXCEPTION
                       when NO_DATA_FOUND then
                         v_ca_qualified := 1;
                         v_records_auto_qualified := v_records_auto_qualified + 1;
                  end;
                  
                  if v_product_keys_found = 0 then
                     v_ca_qualified := 1;
                     v_records_auto_qualified := v_records_auto_qualified + 1;
                  end if;
     
                  if v_ca_qualified = 0 then 
                     begin
                       v_records_with_pos_check := v_records_with_pos_check + 1;                    
                        select count(*) 
                          into v_pos_found
                        from  core_holding h,
                              (select s.product_identifier_cd as prodID,
                                     s.product_identifier_type as prodType
                                from core_slave_product s,
                                     core_master_product m
                               where s.core_master_product_id = m.core_master_product_id
                                 and m.product_identifier_cd     = v_identifying_security_id
                                 and m.product_identifier_type   = v_product_key_type 
                                 and m.logical_delete_ind = 'N'
                                 and s.logical_delete_ind = 'N') prodKeys 
                         where h.logical_delete_ind = 'N'
                           and h.product_identifier_cd     = prodKeys.prodID
                           and h.product_identifier_type   = prodKeys.prodType
                                       and h.entity_cd in
                                    ( select entity_cd
                                        from core_legal_entity 
                                       where proc_system = 'XSP'
                                         and logical_delete_ind = 'N');
                     EXCEPTION
                       when others then
                         raise;
                     end;
                     
                     if v_pos_found > 0 then
                         v_ca_qualified := 1;
                     --else                     
                     --   dbms_output.put_line('v_identifying_security_id   : '|| v_identifying_security_id);
                     --   dbms_output.put_line('v_product_key_type   : '|| v_product_key_type);
                     end if;
                        
                     if v_ca_qualified =1 then
                         v_records_qualified_pos := v_records_qualified_pos + 1;
                     else    
                         v_records_failed_posQ   := v_records_failed_posQ + 1;
                     end if;

                  end if; --v_ca_qualified = 0
               end if; -- position check
               -----------------------------------
               -- End of Position check
               -----------------------------------                       
            end if;     
     
            if v_ca_qualified = 0 then
               v_records_unqualified := v_records_unqualified + 1;
            else     
                --------------------------------------
                -- Insert into detail control
                --------------------------------------
                begin
                    insert into core_extract_detail_control
                            ( core_extract_detail_control_id,
                              batch_id,
                              core_event_id,
                              corp_action_id,
                              sequence)
                        values (CORE_DETAIL_CONTROL_SEQ.nextval,
                                in_batch_id,
                                v_core_event_id,
                                v_corp_action_id,
                                v_max_seq_num);
                        
                EXCEPTION
                    WHEN OTHERS THEN
                        --out_ret_code := SQLCODE;
                        --out_ret_msg  := SUBSTR (SQLERRM, 1, 100); 
                        --rollback;
                        raise;    
                end; 

            end if;
            -----------------------
            -- END OF QUALIFICATION
            -----------------------
        end if; --ignore this ca
        
          --------------------------------------
        -- Reset Control variables
        --------------------------------------
        v_ignore_this_ca                := 0;
        v_corp_action_type              := null;
        v_identifying_security_id       := null; 
        v_identifying_security_id_type  := null;
        v_product_key_type              := null;
      
        v_core_event_id                 := 0;
        v_pos_found                     := 0;
        v_product_keys_found            := 0;
        v_ca_qualified                  := 0;
        
        fetch cur_unqual_ca_on_gateway
        into  v_corp_action_id,
              v_max_seq_num;
        
    end loop;
    --commit;
    dbms_output.put_line('Records Found for Qualification   : '|| v_records_to_qualify);
    dbms_output.put_line('Records failed qualification      : '|| v_records_unqualified);
    dbms_output.put_line('Records qualified with pos check  : '|| v_records_qualified_pos);
    dbms_output.put_line('Records auto-qualified            : '|| v_records_auto_qualified);
    dbms_output.put_line('Records auto-qualified keyDate    : '|| v_records_qual_keyDate);
    dbms_output.put_line('Records ignored                   : '|| v_records_ignored);
    dbms_output.put_line('Records trying key date check     : '|| v_records_with_kdc);
    dbms_output.put_line('Records trying pos check          : '|| v_records_with_pos_check);
    dbms_output.put_line('Number of attempts to fetch keys  : '|| v_records_getting_prod_keys);
    
    
    -- Close Cursors
    if cur_core_proc_sys_control%ISOPEN then
       close cur_core_proc_sys_control;
    end if;
    
    if cur_unqual_ca_on_gateway%ISOPEN then
       close cur_unqual_ca_on_gateway;
    end if;
    
  
 END CORE_PROC_QUAL_EVENTS;

********************************************************

[b]Here is the Java code:[/b]

**************************************************************

    /**
     * Test method for AdatperNotificationDataoDao
     */
    public void testStoreProcedure()throws Exception {
        System.out.println("[testStoreProcedure] start");
        ProcSysControlHibDao sysControlDao = new ProcSysControlHibDao();
        Session session = sysControlDao.getSession();
        boolean returnResult = false ;
        try {
            String procSystem = "XSP";
            String batchID = "XSP10312007";
            
           CallableStatement callableStatement = session.connection().prepareCall("{call GEM_EVENT_QUALIFICATION.CORE_PROC_QUAL_EVENTS((?,?)}");
           callableStatement.setString(1, procSystem);
           callableStatement.setString(2, batchID);
           System.out.println("[testStoreProcedure]before calling the CORE_PROC_QUAL_EVENTS stored procedure");
           callableStatement.execute();
           System.out.println("[testStoreProcedure]before calling the CORE_PROC_QUAL_EVENTS stored procedure");
       
        } catch (HibernateException ex) {
            String errStr = "[testStoreProcedure] Error performing qualifyChangedEvents ";
            System.err.println(errStr);
            ex.printStackTrace();
        }         
        catch (SQLException ex) {
            String errStr = "[testStoreProcedure] Error performing qualifyChangedEvents ";
            System.err.println(errStr);
            ex.printStackTrace();

        }                    
    }
***************************************************************



code tags added

[Updated on: Fri, 02 November 2007 12:50] by Moderator

Report message to a moderator

Re: PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278286 is a reply to message #278285] Fri, 02 November 2007 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

And you think that posting the full exception message you got is irrelevant?

Regards
Michel

[Updated on: Fri, 02 November 2007 12:51]

Report message to a moderator

Re: PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278287 is a reply to message #278285] Fri, 02 November 2007 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Correcting a simple syntax error does NOT require an EXPERT response.
Re: PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278290 is a reply to message #278286] Fri, 02 November 2007 13:02 Go to previous messageGo to next message
aks888
Messages: 4
Registered: November 2007
Junior Member

The exception seems wierd since if it had been a syntax error, I could not have been able to run it in RAPID SQL successfully.
Any clue?
Here is the exception I get.



java.sql.SQLException: ORA-06550: line 1, column 60:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

) , * & | = - + < / > at in is mod remainder not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ as between from overlaps using || multiset year
DAY_ member SUBMULTISET_
The symbol ")" was substituted for ";" to continue.

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at com.csg.asds.common.dao.orm.TestStoreProcedure.testStoreProcedure(TestStoreProcedure.java:62)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:474)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:342)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:194)
Re: PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278291 is a reply to message #278285] Fri, 02 November 2007 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What happens when you CUT & PASTE PL/SQL code into SQL*PLUS?

[Updated on: Fri, 02 November 2007 13:11] by Moderator

Report message to a moderator

Re: PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278296 is a reply to message #278291] Fri, 02 November 2007 13:28 Go to previous messageGo to next message
aks888
Messages: 4
Registered: November 2007
Junior Member
When I run the code in SQL plus, it works perfectly fine without any exception.
Re: PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278299 is a reply to message #278285] Fri, 02 November 2007 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>LIKE4_ LIKEC_ as between from overlaps using || multiset year
Above is from the 'error results' you posted.
I have a saying.
When things do not appear to be as they should, they probably aren't as they should be.
Since I could find NO occurance of the error string in the posted code, you need to determine from where the error code originates.
From my viewpoint there is a total disconnect between the posted PL/SQL & the posted error results.
Something other than the posted PL/SQL is the root cause of this error.
Re: PLS-00103: Encountered the symbol ";" when expecting one of the following: [message #278307 is a reply to message #278299] Fri, 02 November 2007 14:33 Go to previous message
aks888
Messages: 4
Registered: November 2007
Junior Member
I realized my mistake. There was a curly brace instead of a round brace in the call(in Java code) to the stored procedure.

Thanks to all who replied to this topic.
Previous Topic: Why DML operation cannot be performed inside query---ora:14451
Next Topic: Inserting a new page
Goto Forum:
  


Current Time: Thu Dec 08 21:49:45 CST 2016

Total time taken to generate the page: 0.23596 seconds