Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2008
    Posts
    26

    Question Unanswered: Strange error in Oracle procedures

    Hello !

    I have met many strange errors about spaces (space character) in Oracle. As a matter of fact, I register in the database my stored procedures by launching a sql script file (.sql) by a VB program using microsoft ADO !!
    After the execution, few procedures viewed in OEM are in red. And yet, the problem is so incomprehensible but the solution so simple:
    1) (v_ID varchar2, v_User varchar2, v_Computer varchar2, v_Result out int)
    There a 2 spaces between the type and the name, I delete the one and the procedure becomes OK

    2)v_cdef := 'TMP FLOAT';
    This is a variable in an other procedure, I delete the space and afterwards, I type a space, I compile and it works

    The problems are solved but I don't understand why Oracle didn't like my spaces before ???

    Thanks in advance !
    Last edited by malcolmrix; 03-11-08 at 11:05.

  2. #2
    Join Date
    Feb 2008
    Posts
    26
    Hello !

    Please, can someone answer to this thread ?

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    Your procedures are invalid.
    It isn't changing the space characters that is fixing your problem - it is the fact you are recompiling. Try compiling the procedures without making any changes to the code.

    ALTER PROCEDURE <procedure name> compile;

    .

  4. #4
    Join Date
    Feb 2008
    Posts
    26

    Cool

    I try to compile the procedure after creation, the problem remains. So, i need to erase by backspace the space and re-type a space, i recompile and the procedure becomes OK !!
    According to your experiences, what can be the cause of that ?
    Code:
    v_cdef := 'TMP FLOAT';
    EXECUTE IMMEDIATE ' ALTER TABLE '  ||   vnom  || ' ADD  ' || ( v_cdef );
    The procedure which uses these code lines is immediately called : CALL my_proc_name();
    So, there is an exception : execution of invalid procedure !!

    Besides, I would also ask : does Oracle support a table name with spaces ??

  5. #5
    Join Date
    Jan 2004
    Posts
    370
    How did you compile your procedure?
    Through a gui tool?
    If so, did the gui really compile source code that hadn't changed?

    Try this:

    Code:
    alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
    
    select status, LAST_DDL_TIME 
    from user_objects 
    where object_name = <procedure name>;
    Compile your procedure - do not change any code.

    Code:
    select status, last_ddl_time 
    from user_objects 
    where object_name = <procedure name>;

    Has last_ddl_time changed?
    Has status changed?


    Insert/delete spaces or whatever and compile your procedure.

    Code:
    select status, last_ddl_time 
    from user_objects 
    where object_name = <procedure name>;
    Has last_ddl_time changed?
    Has status changed?


    Aside: You can create tables with names that include spaces if you enclose the name with double quotes "table name". But why would you want to do this?

  6. #6
    Join Date
    Feb 2008
    Posts
    26

    Cool

    I don't compile the procedure, I just create it by sql squery :
    Code:
    CREATE  OR REPLACE PROCEDURE ....
    So, the compilation is automatic

    My script is executed by a separated program using Ms ADO for Oracle (MSDAORA.1)

    When Creating the procedure and afterwards call it, the error is :

    CALL MYSCHEMA.CONVERTTYPE()
    ...
    ORA-06575: Package or function CONVERTTYPE is in an invalid state


    When testing by ALTER PROCEDURE MYSCHEMA.CONVERTTYPE compile :

    Warning : altered procedure with compilation error

    When executing the last advice :
    Code:
    select status, LAST_DDL_TIME 
    from user_objects 
    where object_name = 'CONVERTTYPE';
    The status remains INVALID during the 2 first steps and becomes VALID when correcting the space !!

    So, why is the reason of this SPACE error ??
    Last edited by malcolmrix; 03-21-08 at 07:38.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    > So, why is the reason of this SPACE error ??

    Most probably, the script does not contain space (20h), but some other special character displayed as space. I do not know who created the script and why do you not correct it.

    Just see a demonstration with no-break space (A0h) before BEGIN in sqlplus in SCR1.TXT and normal space in SCR2.TXT. Run on Oracle 10.1, but I do not think it is version dependant.
    Code:
    SQL> @scr1.txt
    
    Warning: Procedure created with compilation errors.
    
    SQL> show error
    Errors for PROCEDURE P1:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    1/17     PLS-00103: Encountered the symbol " " when expecting one of the
             following:
             begin function package pragma procedure subtype type use
             <an identifier> <a double-quoted delimited-identifier> form
             current cursor external language
             The symbol " " was ignored.
    
    SQL> @scr2.txt
    
    Procedure created.
    
    SQL> show error
    No errors.
    SQL>
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2008
    Posts
    26
    I confirm flyboy that the space used is really 20h !!!
    As a matter of fact, I execute a SQL script file by using MS ADO; so, perhaps, is it an ADO bug ?

  9. #9
    Join Date
    Jan 2004
    Posts
    370
    After you create or replace the procedure, but BEFORE you run it, is it valid?
    It would appear there is a dependency on EED_CONVERTTYPE. Is that valid before you run the procedure?

    When testing by ALTER PROCEDURE MYSCHEMA.CONVERTTYPE compile :

    Warning : altered procedure with compilation error
    What exactly is the compilation error?

  10. #10
    Join Date
    Feb 2008
    Posts
    26
    Quote Originally Posted by SkyWriter
    After you create or replace the procedure, but BEFORE you run it, is it valid?
    It is invalid !!! space problem
    What exactly is the compilation error?
    The problem is this warning !! that is to say compilation errors, the space yet

    The message error written by OEM when recompiling (GUI) :

    ORA-06550: line 31, column 31:
    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the
    following:
    begin case declare end exit for goto if loop mod null pragma
    raise return select update while with <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    ..........................
    PL/SQL: Statement ignored
    Last edited by malcolmrix; 03-21-08 at 08:20.

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    > What exactly is the compilation error?

    Issue "SHOW ERRORS PROCEDURE <your_procedure_name>" in SQL*Plus and diagnose its output.
    Alternatively you may query USER_ERRORS from wherever to achieve it.

    > so, perhaps, is it an ADO bug ?

    It is possible, but I do not know ADO at all. To check it, examine the procedure code in USER_SOURCE. Copy/paste the problem line(s) to some editor showing hexa codes of characters (if it can be repaired by simply re-writing the "space", because the error PLS-00103 you posted points rather to wrong syntax).
    By the way where do you recompile the code?

  12. #12
    Join Date
    Feb 2008
    Posts
    26
    Oh ! I don't any know the cause of this problem : i try to put in one line the instruction with space error and the next instruction
    Code:
    v_cdef := 'TMP FLOAT';EXECUTE IMMEDIATE ' ALTER TABLE '  ||   vnom  || ' ADD  ' || ( v_cdef );
    but the problem is localized in the end of this new line (I remark that the line-column indicated by message error before this modification is the end of v_cdef := 'TMP FLOAT'; that's why I put the 2 lines in one to hope to solve the problem

    Result of : SHOW ERRORS PROCEDURE <your_procedure_name>
    Erreurs pour PROCEDURE CONVERTTYPE :

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    31/95 PLS-00103: Encountered the symbol "" when expecting one of the
    following:
    begin case declare end exit for goto if loop mod null pragma
    raise return select update while with <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall merge
    <a single-quoted SQL string> pipe
    Last edited by malcolmrix; 03-21-08 at 09:17.

Posting Permissions

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