Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Unexpected token...can't see problem

    Hello again experts,

    Using oracle 9.2 and have the following package/stored procedure.

    Code:
    CREATE OR REPLACE PACKAGE BODY Pack_Test_Info 
    AS
      PROCEDURE p_Test_Info (results_cursor IN OUT CURSOR_TYPE)
      IS
      BEGIN
        OPEN results_cursor FOR
        SELECT  FName,
                TRIM(SUBSTR(title, 0, INSTR(title, ',', 1)-1)) AS Test, 
                Phone 
        FROM TableX    
        WHERE TRIM(SUBSTR(title, 0, INSTR(title, ',', 1)-1)) = 'Sales' 
        AND TRIM(SUBSTR(title, 0, INSTR(title, ',', 1)-1)) IS NOT NULL 
        ORDER BY FName;
      END;
    END;
    I am getting the error Unexpected token in this line
    TRIM(SUBSTR(title, 0, INSTR(title, ',', 1)-1)) AS Test.

    Any ideas?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Maybe the zero (0) here:
    +---------------------V
    TRIM(SUBSTR(title, 0, INSTR(title, ',', 1)-1)) AS Test.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    Hmmm I'm sure you knew that I was going to write you back. I'm still not seeing it. I want the search to begin at the first character. Reading about substring I see
    Code:
    substr( string, start_position, [ length ] )
    Even if I substituted the 0 with a 1 I still get the error. Could you elaborate for me LKBrwn. That would really help. Thanks.

    My strings contain values like
    Field 1 = sales,engineering
    field 1 = marketing, research,accounting,

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Works for me:
    Code:
    SQL>var title varchar2(100);
    SQL>exec :title:='marketing, research,accounting,';
    
    PL/SQL procedure successfully completed.
    
    SQL>select TRIM(SUBSTR(:title, 0, INSTR(:title, ',', 1)-1)) from dual;
    
    TRIM(SUBSTR(:TITLE,0,INSTR(:TITLE,',',1)-1))
    --------------------------------------------------------------------------------
    marketing
    
    SQL>



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    Hmmm that's really weird....maybe I should ignore the error. I am using sql developer and it could possibly be related to the tool. It does work for me too if I run it but it notifies me in SQL Developer. I guess I just wanted to make sure that my function was coded correctly. Thanks so much LKBRWN_DBA. Much appreciated.

Posting Permissions

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