Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unhappy Unanswered: strange procedure behavior

    Hi, I have a problem with a procedure

    I have this procedure:

    CREATE OR REPLACE PROCEDURE pcylpqn (cv IN OUT pcyqatp.GenericCurTyp, MACHINE VARCHAR2,SB_UID VARCHAR2 )AS
    BEGIN
    if MACHINE='b542018' then
    insert into test3 values('sopa'||SB_UID||'sopa');
    commit;
    end if;
    if MACHINE='b542018' then
    OPEN cv FOR
    SELECT OID as DOC_OID
    FROM pcinfo.pcyqtqn
    WHERE requester = SB_UID;
    end if;
    END;
    .
    /

    I do add the SB_UID into a test3 table in order to chek that I'm sending the right parameters to the procedure.

    Thi thing is that this procedure is not sending anything back, even if I put the good parameters into it. i.e.:
    SQL> exec pcylpqn (:c,'b542018','e135520')

    Procédure PL/SQL terminée avec succès.

    SQL> print c

    aucune ligne sélectionnée

    In french: there was not row returned.

    When I take a look to the test3 table I can see the new entries on it:
    SQL> select * from test3;

    UID
    -----------------------
    sopae135520sopa

    then I change the procedure in order to put explicitely the SB_UID into it and to avoid passing the SB_UID as a parameter, so my procedure looks like this:

    CREATE OR REPLACE PROCEDURE pcylpqn (cv IN OUT pcyqatp.GenericCurTyp, MACHINE VARCHAR2,SB_UID VARCHAR2 )AS
    BEGIN
    if MACHINE='b542018' then
    insert into test3 values('sopa'||SB_UID||'sopa');
    commit;
    end if;
    if MACHINE='b542018' then
    OPEN cv FOR
    SELECT OID as DOC_OID
    FROM pcinfo.pcyqtqn
    WHERE requester ='e135520';
    end if;
    END;
    .
    /

    and when I run the procedure I have this:

    SQL> exec pcylpqn (:c,'b542018','e135520')

    Procédure PL/SQL terminée avec succès.

    SQL> print c

    DOC_OID
    ----------
    100000002
    100000003
    100000004
    100000005
    100000006
    100000007
    100000008
    .....
    .....
    .....
    100000079
    100000099

    59 ligne(s) sélectionnée(s).

    SQL>
    I got the 59 lines that I do have on my table!!!!!
    So my question is why why why my procedure is not working the first time? I don't see what's wrong with it, and I'm begining to bang my head against the wall if I don't find the answer real soon!!!

    thanks for your help!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Does the table pcinfo.pcyqtqn have a column called SB_UID?

  3. #3
    Join Date
    Dec 2003
    Posts
    8

    Table pcyqtqn

    No, it doesn't

    SQL> desc pcyqtqn
    Nom NULL ? Type
    ----------------------------------------- -------- -------------
    REQUESTER NOT NULL CHAR(20)
    OID NOT NULL NUMBER(12)
    READ_DATE DATE

    Any ideas about what can be possible wrong?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, I'm afraid not.

  5. #5
    Join Date
    Dec 2003
    Posts
    8

    Maybe this

    requester is of type CHAR(20)
    while in the procedure SB_UID is of type varchar2
    I'll change requester to varchar2, and give it a try.

    I'll tell you if that was the problem.

    Thanks

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Almost certainly. The CHAR datatype should (almost) never be used, because of issues like this that abound with it.

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    The CHAR datatype stores the trailing spaces to fill the field to it's max, so it very rarely matches the contents of a VARCHAR2. Always use consistent datatypes.

  8. #8
    Join Date
    Dec 2003
    Posts
    8

    Smile That was it

    Yes , the problem came from the different type from the table and the procedure, Thanks a lo for your help!

Posting Permissions

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