Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2011
    Posts
    17

    Unanswered: Oracle Store Procedure

    I have created this store proc:

    create or replace PROCEDURE INSERT_TESTTABLE
    (
    PrimaryKey IN NUMBER
    ,One IN VARCHAR2
    ,Two IN VARCHAR2
    ,Three IN VARCHAR2
    ,Four IN VARCHAR2
    ,Five IN VARCHAR2
    )
    AS

    BEGIN

    INSERT INTO TestTable
    (
    PrimaryKey,
    One,
    Two,
    Three,
    Four,
    Five
    )

    VALUES
    (
    1234,
    irstValue,
    SecondValue,
    ThirdValue,
    FourthValue,
    FithValue
    );

    NULL;

    END INSERT_TESTTABLE;

    And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist

    Any one can help I think there's something I get wrong that I can't see.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by stahorse1 View Post
    Any one can help I think there's something I get wrong that I can't see.
    Here is a hint: "table or view does not exist"

  3. #3
    Join Date
    Nov 2011
    Posts
    17

    reply

    That's the thing because TestTable does exist, which is my Table

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by stahorse1 View Post
    That's the thing because TestTable does exist
    Apparently not...

  5. #5
    Join Date
    Nov 2011
    Posts
    17

    reply

    I don't know if you have went through my code, you will notice that there is:

    INSERT INTO TestTable
    (
    PrimaryKey,
    One,
    Two,
    Three,
    Four,
    Five
    )

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by stahorse1 View Post
    I don't know if you have went through my code
    Yes I did.


    you will notice that there is:

    INSERT INTO TestTable
    (
    PrimaryKey,
    One,
    Two,
    Three,
    Four,
    Five
    )
    That's where the error comes from

    An INSERT statement does not create a table, it inserts into an existing one.

    Did you run the approriate CREATE TABLE before you created the procedure?

  7. #7
    Join Date
    Nov 2011
    Posts
    17

    reply

    Yes I have, here's my .sql script:

    CREATE TABLE "TestTable"(
    "PrimaryKey" Integer NOT NULL,
    "One" Varchar2(30 ),
    "Two" Varchar2(30 ),
    "Three" Varchar2(30 ),
    "Four" Varchar2(30 ),
    "Five" Varchar2(30 )
    )

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stahorse1 View Post
    That's the thing because TestTable does exist, which is my Table
    "TestTable" may exist, but you are inserting into "TESTTABLE" - unquoted identifiers are implicitly converted to upper case (that concerns column names as well).

  9. #9
    Join Date
    Nov 2011
    Posts
    17

    reply

    So far thanks for your help,

    Now I converted everything to upper case to avoid anything;


    create or replace PROCEDURE INSERT_TESTTABLE
    (
    PRIMARYKEY IN NUMBER
    , ONE IN VARCHAR2
    , TWO IN VARCHAR2
    , THREE IN VARCHAR2
    , FOUR IN VARCHAR2
    , FIVE IN VARCHAR2
    )
    AS
    BEGIN

    INSERT INTO TESTTABLE
    (
    PRIMARYKEY,
    ONE,
    TWO,
    THREE,
    FOUR,
    FIVE
    )

    VALUES
    (
    1234,
    firstValue,
    SecondValue,
    ThirdValue,
    FourthValue,
    FithValue
    );

    NULL;
    END INSERT_TESTTABLE;

    And I sit with this error: Error(30,15): PL/SQL: ORA-00984: column not allowed here

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    What are FIRSTVALUE, SECONDVALUE, ... identifiers in VALUES clause?
    Procedure parameters are named ONE, TWO, ...
    The same names are used for table column.

    Just one recommendation: do not use the same name for procedure parameter and table column name. If doing so, you must be very careful with identifying the scope. E.g. the expression
    Code:
    WHERE one = one
    would compare column ONE with itself, so it would be equivalent to
    Code:
    WHERE one is not null
    Prefixing it with table/procedure name would help, but it is quite inconvenient to keep it in mind and use it in whole code.

    Below are some articles about naming convention. As I am not aware of any "standard" one for Oracle, I will let on you to pick the most suitable one for you (or just search anywhere else):
    http://ss64.com/ora/syntax-naming.html
    http://www.oracle-base.com/articles/...onventions.php

  11. #11
    Join Date
    Nov 2011
    Posts
    17

    reply

    Thanks

    firstvalue to firth are data I insert in the database


    CREATE OR REPLACE PROCEDURE NEWPROC
    (
    PRIMARYKEY IN NUMBER
    , ONE IN VARCHAR2
    , TWO IN VARCHAR2
    , THREE IN VARCHAR2
    , FOUR IN VARCHAR2
    , FIVE IN VARCHAR2
    )
    AS
    BEGIN

    INSERT INTO TESTTABLE
    (
    PRIMARYKEY,
    ONE,
    TWO,
    THREE,
    FOUR,
    FIVE
    )

    VALUES
    (
    1234,
    firstvalue,
    secondvalue,
    thirdvalue,
    fouthvalue,
    firthvalue
    );

    NULL;
    END NEWPROC;

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by stahorse1 View Post
    firstvalue to firth are data I insert in the database
    Maybe in your real code, but not in those pseudo ones you are posting here.
    There is a difference between an identifier (FIRSTVALUE) and a character literal ('FIRSTVALUE').

    I am sorry that I (and probably most members here) cannot debug the imaginary code. Maybe you should enhance your skill for doing it by studying Oracle documentation, which is available e.g. online on http://tahiti.oracle.com/ SQL Language Reference and PL/SQL User's Guide and Reference are good enough for the PL/SQL syntax and language elements.

    Good luck.

  13. #13
    Join Date
    Nov 2011
    Posts
    17

    reply

    Ohk Thanks I will. Cause this is my first project using oracle

    I changed FIRSTVALUE to firstvalue. this code below is what I have now:

    CREATE OR REPLACE PROCEDURE NEWPROC
    (
    PRIMARYKEY IN NUMBER
    , ONE IN VARCHAR2
    , TWO IN VARCHAR2
    , THREE IN VARCHAR2
    , FOUR IN VARCHAR2
    , FIVE IN VARCHAR2
    )
    AS
    BEGIN

    INSERT INTO TESTTABLE
    (
    PRIMARYKEY,
    ONE,
    TWO,
    THREE,
    FOUR,
    FIVE
    )

    VALUES
    (
    1234,
    firstvalue,
    secondvalue,
    thirdvalue,
    fouthvalue,
    firthvalue
    );

    NULL;
    END NEWPROC;

  14. #14
    Join Date
    Nov 2011
    Posts
    17

    reply

    Ohk Thanks I will. Cause this is my first project using oracle

    I changed FIRSTVALUE to firstvalue. this code below is what I have now:

    CREATE OR REPLACE PROCEDURE NEWPROC
    (
    PRIMARYKEY IN NUMBER
    , ONE IN VARCHAR2
    , TWO IN VARCHAR2
    , THREE IN VARCHAR2
    , FOUR IN VARCHAR2
    , FIVE IN VARCHAR2
    )
    AS
    BEGIN

    INSERT INTO TESTTABLE
    (
    PRIMARYKEY,
    ONE,
    TWO,
    THREE,
    FOUR,
    FIVE
    )

    VALUES
    (
    1234,
    firstvalue,
    secondvalue,
    thirdvalue,
    fouthvalue,
    firthvalue
    );

    NULL;
    END NEWPROC;

    And get this error: Error(30,15): PL/SQL: ORA-00984: column not allowed here

  15. #15
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by stahorse1 View Post
    Ohk Thanks I will. Cause this is my first project using oracle
    You really should, especially the asic parts about language elements.
    I already posted here, so once again (and last):
    FIRSTVALUE, firstvalue, FirstValue etc. are identifiers (variable, table column, whatever), they are not data
    'FIRSTVALUE', 'firstvalue', 'FirstValue' etc. (note the single quotes around) are string literals, so they are treated as data.

Posting Permissions

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