DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: error in stored procedure

  1. #1
    Join Date
    May 2002
    Posts
    232
    CREATE OR REPLACE PROCEDURE IVR_GET_TPIN1(DIGI_LINE_NO VARCHAR2,

    PASSWORD OUT VARCHAR2,

    STATUS OUT VARCHAR2) AS



    x binary_integer;
    pass dd_user.password%type;
    sta dd_user.status%type;

    BEGIN
    SELECT PASSWORD,STATUS INTO pass,sta FROM DD_USER
    WHERE USER_ID = DIGI_LINE_NO;

    x :=DBMS_RANDOM.RANDOM;
    x :=substr(x,1,4);
    -- DBMS_OUTPUT.PUT_LINE(x);
    if pass is null then

    update dd_user set password = to_char(x),
    password1 = to_char(x),
    status = 'NEW' where USER_ID = digi_line_no;

    else

    update DD_USER set password1 = to_char(x),
    status = 'CH', where USER_ID = digi_line_no;
    end if;
    end;
    /


    i tried above procedure,but could not get


    exact out put,any one can modify the above one.

    CONDITIONS INSIDE STORED procedure

    if the passpassword not existed(new users) for a particuler user,
    new random 4 digit password will be rerturned.

    if the passsword existed and in the form of alpha-numeric,
    new random 4 digit password will be returned and update the database.

    if 4 digit password existed for that user,
    initial password will be returned.



    SQL> DESC DD_USER;
    Name Null? Type
    ----------------------------------------- -------- ----------------

    USER_ID NOT NULL VARCHAR2(20)
    PASSWORD VARCHAR2(20)
    PASSWORD1 NUMBER(4)
    STATUS VARCHAR2(6)


    This table stores the mobile information through web.
    Now we have modified above table ,i mean we added new column

    PASSWORD1 NUMBER(6)

    I AM GIVING ALL DETAILS BELOW REGARDING PROCEDURE.

    kavitha

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

Posting Permissions

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


Click Here to Expand Forum to Full Width