Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Code VARCHAR2 with PROGRESSIVE

    I've this table (with more 1000 records):

    CREATE TABLE MY_TAB
    (
    SERVICE_PK VARCHAR2(32),
    SERVICE VARCHAR2(32),
    NAME_SERVICE VARCHAR2(32),
    DESC_SERVICE VARCHAR2(32),
    CITY VARCHAR2(32),
    CELLULAR_NUMBER VARCHAR2(32),
    FLAG CHAR(1)
    );

    ALTER TABLE MY_TAB
    ADD (CONSTRAINT PK_SERVICE_PK PRIMARY KEY (SERVICE_PK));


    I'd like a stored procedure with in INPUT (NAME_SERVICE,DESC_SERVICE,CITY,CELLULAR_NUMBER) that insert a new record
    with these input values and with:
    SERVICE_PK and SERVICE = SERV||progressive(4 digit) (SERV0001, SERV0002, SERV0003,.....)
    FLAG='Y'

    FOR EXAMPLE:

    Code:
    execute INS_SERVICES ('MYSEREVICE','MYDESCSERVICE','LONDON', '3345691231');
    
    PL/SQL procedure successfully completed
    My Output Will be:
    SERVICE_PK........SERVICE.......NAME_SERVICE...... .DESC_SERVICE.......CITY.....CELLULAR_NUMBER.....F LAG
    SERV0001...........SERV0001.........MYSEREVICE.... ....... MYDESCSERVICE......LONDON.......3345691231........ .Y

    Code:
    execute INS_SERVICES ('MYSEREVICE2','MYDESCSERVICE2','ROME', '7777777777');
    
    PL/SQL procedure successfully completed
    My Output Will be:
    SERVICE_PK........SERVICE.......NAME_SERVICE...... .DESC_SERVICE.......CITY.....CELLULAR_NUMBER.....F LAG
    SERV0001...........SERV0001.........MYSEREVICE.... ....... MYDESCSERVICE......LONDON.......3345691231........ .Y
    ERV0002...........SERV0002.........MYSEREVICE2.... ....... MYDESCSERVICE2......ROME.......7777777777......... Y

    Code:
    execute INS_SERVICES ('MYSEREVICEXXX','MYDESCSERVICEYYY','PARIS', '2333222333');
    
    PL/SQL procedure successfully completed
    My Output Will be:
    SERVICE_PK........SERVICE.......NAME_SERVICE...... .DESC_SERVICE.......CITY.....CELLULAR_NUMBER.....F LAG
    SERV0001...........SERV0001.........MYSEREVICE.... ....... MYDESCSERVICE........LONDON.......3345691231...... ...Y
    SERV0002...........SERV0002.........MYSEREVICE2... ........ MYDESCSERVICE2.........ROME.......7777777777...... ...Y
    SERV0003...........SERV0003.........MYSEREVICEXXX. .......... MYDESCSERVICEYYY......PARIS.......2333222333...... ...Y

    ..and so on

    How Can I write my stored procedure to get this output?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How Can I write my stored procedure to get this output?
    First post INSERT & SELECT statement that need to be inside new procedure.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by anacedent View Post
    >How Can I write my stored procedure to get this output?
    First post INSERT & SELECT statement that need to be inside new procedure.
    I tried this procedure:

    Code:
    CREATE OR REPLACE PROCEDURE INS_NEW_SERV (NAME_SERVICE_IN IN         VARCHAR2, 
                                                        DESC_SERVICE_IN IN  VARCHAR2, 
                                                        CITY_IN IN                 VARCHAR2, 
                                                        CELLULAR_NUMBER_IN IN       VARCHAR2) IS 
      
        BEGIN 
      
          INSERT INTO MY_TAB (SERVICE_PK, SERVICE, NAME_SERVICE, DESC_SERVICE, CITY, CELLULAR_NUMBER, FLAG) 
          SELECT 'SERV' || SUBSTR ('0000' || SEQ_SERV.NEXTVAL, -4) SERVICE_PK, 'SERV' || SUBSTR ('0000' || SEQ_SERV.NEXTVAL, -4) SERVICE, 
                  NAME_SERVICE_IN,DESC_SERVICE_IN,CITY_IN,CELLULAR_NUMBER_IN,'Y' FLAG 
          FROM MY_TAB 
          WHERE ROWNUM=1; 
           
          COMMIT; 
           
       END INS_NEW_SERV;
    Have someoune anyidea?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What happens when you use your procedure? What is wrong with what it does?

  5. #5
    Join Date
    May 2004
    Posts
    95
    Instead of using a SUBSTR, I recomend using a LPAD

Posting Permissions

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