Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    1

    Unanswered: procedure,trigger,cursor problem in oracle 10 G Express Edition

    First hie to everybody, this is my first posting in this site....

    I'm getting the problem in the stored procedure,cursor,trigger in oracle 10 g express edition..

    There is no problem in stored procedure,cursor,trigger creation

    But on the execution of stored procedure, if write "execute procedure_name' it give error ORA-00900: invalid SQL statement

    EXAMPLE: i created the procedure........

    CREATE OR REPLACE PROCEDURE TEST1(VTEST IN NUMBER) IS
    NAME VARCHAR2(20);

    BEGIN
    SELECT testname INTO NAME FROM TEST;

    END;


    OUTPUT : Procedure created.


    0.21 seconds


    ON THE EXECUTION: EXECUTE TEST1(1)

    OUTPUT : ORA-00900: invalid SQL statement


    Please help me....

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    for a start

    SQL> select keyword from V_$RESERVED_WORDS where keyword like 'NA%';

    KEYWORD
    ------------------------------
    NAME
    NATIONAL
    NAMED
    NAN
    NATIVE
    NAV
    NATURAL


    See a problem between my post & yours?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I can tell, using NAME keyword as a variable name really is not a very good idea. But, this won't prevent this code to compile and execute (on my 10g EE 10.2.0.1.0):
    Code:
    SQL> CREATE OR REPLACE PROCEDURE Test1(VTEST IN NUMBER) IS
      2    NAME VARCHAR2(20);
      3  BEGIN
      4    SELECT 'x' INTO NAME FROM dual;
      5  END;
      6  /
    
    Procedure created.
    
    SQL>
    SQL> EXECUTE Test1(1);
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Unfortunately, I don't have XE here to test it, but - do you, perhaps, miss a semicolon at the end of the EXECUTE statement?

  4. #4
    Join Date
    Jul 2009
    Posts
    3
    Hi
    after creating procedure sucessfully, say TEST1,
    BEGIN
    TEST1
    END;

    try this

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You certainly miss semicolon at the end of the "test1" procedure name.
    Code:
    SQL> create or replace procedure test1 is
      2  begin
      3    null;
      4  end;
      5  /
    
    Procedure created.
    
    SQL> exec test1;
    
    PL/SQL procedure successfully completed.
    
    SQL> begin
      2    test1;
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    Your way won't work:
    Code:
    SQL> begin
      2    test1
      3  end;
      4  /
    end;
    *
    ERROR at line 3:
    ORA-06550: line 3, column 1:
    PLS-00103: Encountered the symbol "END" when expecting one of the following:
    := . ( @ % ;
    The symbol ";" was substituted for "END" to continue.
    
    
    SQL>

  6. #6
    Join Date
    Jul 2009
    Posts
    3
    yeah you are correct..i missed out semicolon

Posting Permissions

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