Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    5

    Unanswered: error in compiling a procedure

    create procedure newuser_add_database
    (
    eUSER_ID varchar(100),
    eEMP_ID varchar(100),
    eUSER_NAME varchar(100),
    eORGANISATION varchar(100),
    eEMAIL_ID varchar(100),
    eMOBILE_NO varchar(100),
    eCIRCLE_ACCESS varchar(100),
    eAPPROVER varchar(100),
    eCREATION_DATE date)
    Language SQL
    modifies sql data

    insert into security.user_database_telemedia (USER_ID, EMP_ID, USER_NAME, ORGANISATION, IP_ADDRESS, HOSTNAME, EMAIL_ID, MOBILE_NO, TELEMEDIA, CIRCLE_ACCESS, APPROVER, OLDID, CREATION_DATE)
    values( eUSER_ID,eEMP_ID,eUSER_NAME,eORGANISATION,'10.13.2 1.145','CNDABBCUDBDP11',eEMAIL_ID,eMOBILE_NO,
    'Y',eCIRCLE_ACCESS,eAPPROVER,eEMP_ID,eCREATION_DAT E);

    insert into security.sms_list (MOB_NO,
    NAME, CATEGORY, MAIL_ID)
    values (eMOBILE_NO,
    eUSER_NAME,eCIRCLE_ACCESS,eEMAIL_ID);
    commit
    end p1
    @


    When I am trying with only one insert , it is working fine. But when I am trying to give two insert statements it is throwing the error in the second statement,I suppose the scope of the parameter is ending at the terminating semicolon.

    Error:
    S0022(-206)[IBM][CLI Driver][DB2/AIX64] SQL0206N "EMOBILE_NO" is not valid in the context where it is used. SQLSTATE=42703


    The scope of the parameter is becoming local and gets terminated by semi colon.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try...

    create procedure newuser_add_database
    (
    eUSER_ID varchar(100),
    eEMP_ID varchar(100),
    eUSER_NAME varchar(100),
    eORGANISATION varchar(100),
    eEMAIL_ID varchar(100),
    eMOBILE_NO varchar(100),
    eCIRCLE_ACCESS varchar(100),
    eAPPROVER varchar(100),
    eCREATION_DATE date)
    Language SQL
    modifies sql data
    p1:
    BEGIN

    insert into security.user_database_telemedia
    ...
    ...;

    insert into security.sms_list
    ...
    ...;
    commit;
    end p1
    @

  3. #3
    Join Date
    May 2010
    Posts
    5
    I tried this way but i think i am wrong somewhere in compiling it.
    Should i use some other command to compile it...
    db2 -tvf c:\proc.txt

    The 1st error is:
    Expected token may include: psm_semicolon. SQLSTATE:42601

    2nd error is:
    emobile_no is not valid SQLSTATE:42703

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are using the wrong options to create it. Instead of "db2 -tvf ..." you need "db2 -td@ -vf ...". You need to use the correct statement termination character.

    Andy

  5. #5
    Join Date
    May 2010
    Posts
    5
    Thanks a lot..its working now....

Posting Permissions

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