Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Unanswered: Testing Condition in SP??

    I am attempting to create a condition within my SP to check for a row in my table...then if the row is found i want to BEGIN an UPDATE statement, else an INSERT statement.

    But each time I have done this by using an OUT variable, I get an error stating that the table or view is not found...it refers to the variable name used within the INSERT INTO statement. Below is my code:

    Code:
    create or replace PROCEDURE insertComment( 
    l_exists OUT NUMBER,
    p_member_id IN sc_comment_test.member_id%TYPE, 
    p_member_lastname IN sc_comment_test.member_lastname%TYPE, 
    p_member_firstname IN sc_comment_test.member_firstname%TYPE,
    p_member_startdate IN sc_comment_test.member_startdate%TYPE,
    p_member_enddate IN sc_comment_test.member_enddate%TYPE,
    p_product_cat_code IN sc_comment_test.product_cat_code%TYPE, 
    p_comment IN sc_comment_test.comments%TYPE default null, 
    result1 OUT PK_COMMENTS.t_comment1 ) 
    IS 
    p_com sc_comment_test.comments%TYPE := p_comment;
    BEGIN 
    IF p_comment ='zzzz' THEN 
    BEGIN 
    p_com := null; 
    END; 
    END IF;
    BEGIN
    select count(*) into l_exists from sc_comments_test where member_id = p_member_id;
    IF l_exists = 0 THEN
    BEGIN
    INSERT 
    INTO SC_COMMENT_TEST( 
    member_id, 
    member_lastname, 
    member_firstname,
    member_startdate,
    member_enddate,
    product_cat_code, 
    comments,
    load_date
    ) 
    VALUES( 
    p_member_id, 
    p_member_lastname,
    p_member_firstname,
    p_member_startdate,
    p_member_enddate,
    p_product_cat_code,
    p_comment,
    SYSDATE); 
    commit;
    END;
    ELSE
    BEGIN 
    UPDATE SC_COMMENT_TEST
    SET COMMENTS = p_comment, load_date = SYSDATE  where member_id = p_member_id;
    commit;
    END;
    END IF;
    OPEN result1 FOR SELECT * FROM sc_comment_test; 
    END;
    END;
    Can someone point me in the right direction?

    Thanks!

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Use Merge.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Was the table name exact?
    Quote Originally Posted by Torre2014 View Post

    Code:
    ...
    ...
    p_com sc_comment_test.comments%TYPE := p_comment;
    ...
    ...
    select count(*) into l_exists from sc_comments_test where member_id = p_member_id;
    IF l_exists = 0 THEN
    BEGIN
    INSERT 
    INTO SC_COMMENT_TEST( 
    ...
    ) 
    ...
    ...

  4. #4
    Join Date
    Mar 2014
    Posts
    2
    WOW!! I am so sorry!...embarassing

    I usually check on things like this...!

    Thanks for the quick reply!

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Even if the immediate cause of your issue was different(e.g. in spelling),
    it might be worth to consider utilization of functionality of SQL( on Oracle) like...
    Quote Originally Posted by pablolee View Post
    Use Merge.
    before inventing skillful code by yourself.

Posting Permissions

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