Results 1 to 9 of 9

Thread: Procedure not working

  1. #1
    Join Date
    Dec 2004
    Posts
    37

    Procedure not working

    Hey guys,

    I was trying to do a procedure so if the rate is greater than 20000 certain values will be inserted in a table but the following code doesn't work and I was just wondering where I went wrong?

    Thanks in advance.

    CREATE OR REPLACE PROCEDURE sch_1 IS
    BEGIN

    IF T.RATE > 20000

    INSERT INTO SCH
    (SCHID, NAME)
    vALUES(98, JO);
    END SCH_1;
    /

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    What is T.Rate and from where are you getting it?

  3. #3
    Join Date
    Dec 2004
    Posts
    37
    T is a different table with a rate column.

    but i haven't used procedures in pl/sql so im not sure how this would work.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You have to get the RATE in a variable and do the compare. Look for SELECT INTO in Oracle doc.

  5. #5
    Join Date
    Dec 2004
    Posts
    37
    Thanks for your advice but I am still having problems with this, have you got any advice on how I could fix my 2nd attempt?

    Thanks.

    DECLARE

    RATE_NO NUMBER;

    CREATE OR REPLACE PROCEDURE sch_1 IS
    BEGIN
    SELECT T.RATE
    INTO RATE_NO
    FROM T

    IF RATE_NO < 20000
    INSERT INTO SCH
    (SCHID, NAME)
    vALUES(98, JO);
    END;
    /
    Last edited by 182; 11-19-2005 at 08:04 PM.

  6. #6
    Join Date
    Oct 2005
    Posts
    2,557
    SQL> create table t
    2 (rate number);

    Table created.

    SQL>
    SQL> create table sch
    2 (schid number,
    3 name varchar2(40));

    Table created.

    SQL>
    SQL> insert into t values (19999);

    1 row created.

    SQL>
    SQL> CREATE OR REPLACE PROCEDURE sch_1
    2 AS
    3 RATE_NO NUMBER;
    4 v_stmt VARCHAR2(100);
    5 BEGIN
    6 SELECT RATE INTO RATE_NO
    7 FROM T;
    8 IF RATE_NO < 20000 THEN
    9 v_stmt := 'insert into sch values (98,'||chr(39)||'JO'||chr(39)||')';
    10 execute immediate(v_stmt);
    11 END IF;
    12 END;
    13 /

    Procedure created.

    SQL> show errors
    No errors.
    SQL> exec sch_1;

    PL/SQL procedure successfully completed.

    SQL> select * from sch;

    SCHID NAME
    ---------- ----------------------------------------
    98 JO

  7. #7
    Join Date
    Feb 2003
    Posts
    1,048
    You need to declare the variable inside fo the procedure, not before creating it. If it doesn't work, explain the problem to us. Is it giving you an error or simply not doing what you expect it to.

    Try this:

    CREATE OR REPLACE PROCEDURE sch_1 AS

    DECLARE

    RATE_NO NUMBER;

    BEGIN
    SELECT T.RATE
    INTO RATE_NO
    FROM T

    IF RATE_NO < 20000
    INSERT INTO SCH
    (SCHID, NAME)
    vALUES(98, JO);
    END;
    /

  8. #8
    Join Date
    Oct 2005
    Posts
    2,557
    There is no "declare" inside a procedure or function.

  9. #9
    Join Date
    Dec 2004
    Posts
    37
    Thanks for the advice guys I have got it working now. The problem before was just an error message. Thanks again.

Posting Permissions

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