1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to create Procedure to create automated sequence and use it based on input

Discussion in 'SQL PL/SQL' started by heepth, Nov 7, 2014.

  1. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hey guys,
    I'm doing a project on library management.I have an issue in generating a track_id for each book
    for example: if the book_name: ORACLE AND STOCK=2 then track_id should be like this ORA1,ORA2...
    if any updation later on the book_stock again the track_id need to be generated from the old sequence.
    like stock =2+1 then track_id =ora3
    Kindly find the attachment below/I have tried but its not working out.

    stock is 4.
    track_id
    ora1
    ora2
    ora3
    ora4
     

    Attached Files:

  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi heepth,

    There are several issues with your script:

    1) It doesn't compile, so don't even try to run it...

    2) You're creating the sequence - even if it exists => this will fail when you add the same book to the BOOKS table

    3) The error you're getting (among other errors) is caused by incorrect call of sequence: you cannot construct the sequence name like this - you'll have to use dynamic SQL

    4) Why do you need a sequence ? You'll have at the end one sequence per book ... You can simply do a count of the book and increment a variable to track it.

    Is your requirement or project linked to SEQUENCE or just simply Library management ?
     
  3. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hi RajenB,

    thanks for your reply,

    actually my requirement is like when a new stock of book is need to be registered in library stock then my procedure need to create a new sequence then using that sequence values need to be inserted,later the same book has another 10 stocks extra so i can use the same procedure to update the stock then my track_id will get started from old_value of sequence.

    i need a procedure to generate a track_id for each book according to their stock rate for example:if the book is ORACLE then track_id should be like this ORA1,ORA2...

    suggest me with ur solution.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi heepth,

    If we stay on the CREATE SEQUENCE option of script, then try the following:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE xx_p_seq_trid(
       p_bkid  NUMBER,
       p_bknm  VARCHAR2,
       p_bkst  NUMBER,
       p_price NUMBER
       )
    IS
       v_seq      VARCHAR2(20);
       v_seq_sql  VARCHAR2(70);
       v_ins_sql  VARCHAR2(2000);
       v_exists   VARCHAR2(1) := 'N';
    BEGIN
       v_seq := 'xx_'||SUBSTR(p_bknm,1,3)||'_seq';
       -- Check if book exists first
       BEGIN
          SELECT 'Y'  INTO v_exists FROM dual WHERE EXISTS
            (SELECT 1 FROM book WHERE book_id = p_bkid);
          dbms_output.put_line('Sequence re-used:  '||v_seq);
       EXCEPTION
          -- If book doesn't exist in table => sequence has never been created
          WHEN NO_DATA_FOUND THEN
             v_seq_sql := 'CREATE SEQUENCE '||v_seq
                          ||' INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE';
             dbms_output.put_line('SQL:  '||v_seq_sql);
             dbms_output.put_line('Sequence created:  '||v_seq);
             EXECUTE IMMEDIATE v_seq_sql;
          --
       END;
       --
       INSERT INTO book VALUES (p_bkid,p_bknm,p_bkst,p_price);
       dbms_output.put_line('Book inserted');
       v_ins_sql := 'INSERT INTO book_details VALUES ('
                       || v_seq ||'.NEXTVAL,'
                       || p_bkid ||','
                       || '''Available'''
                       || ')';
       EXECUTE IMMEDIATE v_ins_sql;
       dbms_output.put_line('Book Details inserted');
       --
       COMMIT;
       --
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Error:'|| SQLERRM);
    END;
    /
     
     
    heepth likes this.
  5. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hi RajenB,
    Thanks for the code.I need to concat SUBSTR(p_bknm,1,3) in the insert statement of book_details but its throwing error.do u have any option.
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Please post copy of code + description of table BOOK_DETAILS + error message you're getting for me to propose something concrete...

    Thanks.
     
  7. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hi RajenB,
    The below procedure is getting compiled without any error,the actual problem is when we invoke it,the track_id is not getting inserted in the table.I want my track to be like this if it is JAVA book i'm inserting then track_id should be like JAV1,JAV2..

    Code ( (Unknown Language)):


     
    desc book
    Name Null Type
    -------------- -------- -------------
    BOOK_ID NOT NULL VARCHAR2(50)
    BOOK_NAME NOT NULL VARCHAR2(200)
    BOOK_STOCK NUMBER(10)
    PRICE NOT NULL NUMBER(10)
    CURRENT_STATUS VARCHAR2(10)

    desc book_details
    Name Null Type
    -------- -------- ------------
    TRACK_ID NOT NULL VARCHAR2(50)
    BOOK_ID VARCHAR2(50)
    STATUS NOT NULL VARCHAR2(10)

    output:
    anonymous block completed
    SQL: CREATE SEQUENCE xx_bha_seq INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE
    Sequence created: xx_bha_seq
    Book inserted
    Error:ORA-02289: sequence does not exist
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Show output v_ins_sql....


    pay attention :

    in your code :

    .... v_seq_name|| v_seq ||'.NEXTVAL ,

    you defined variables as :

    v_seq := 'xx_'||SUBSTR(p_bknm,1,3)||'_seq'; --1

    v_seq_name :=SUBSTR(p_bknm,1,3); --2



    I suppose that it is necessary to use such declaration ,because v_seq - is it full-name of sequence:


    .....
    v_ins_sql := 'INSERT INTO book_details VALUES ('|| v_seq ||'.NEXTVAL,'
    || p_bkid ||','
    || '''Available'''
    || ')';
     
  9. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hi,
    Thanks for your response.I have posted my output above so you can get a clear view about this issue,kindly follow the above post
     
  10. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi heepth,

    If you had used the code I had posted as-is, it would have worked.

    The error you did was the following:

    - Added a new variable for sequence name (v_seq_name): not needed as the variable v_seq is already doing the job

    You can remove the following lines:

    - The sequence which is being created is "v_seq" (not "v_seq_name") : in the INSERT statement, I had used v_seq||'.NEXTVAL' not v_seq_name||v_seq||'.NEXTVAL'.

    So if you correct the following line:
    with

    it will surely work !
     
  11. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hi Rajen,
    yeah I accept that but I need the track_id need to be inserted like

    track_id book_name
    ora1 oracle
    ora2 oracle
    jav1 java
    jav2 java

    so that i have made a try with concating the variable with it...
    Is there any way to sort it out.
     
  12. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi heepth,

    I see what you mean now :).

    So, try the following:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE xx_p_seq_trid(
       p_bkid  NUMBER,
       p_bknm  VARCHAR2,
       p_bkst  NUMBER,
       p_price NUMBER
       )
    IS
       v_seq      VARCHAR2(20);
       v_seq_val  NUMBER;
       v_seq_sql  VARCHAR2(70);
       v_ins_sql  VARCHAR2(2000);
       v_exists   VARCHAR2(1) := 'N';
       v_track_id VARCHAR2(20);
    BEGIN
       v_seq := 'xx_'||SUBSTR(p_bknm,1,3)||'_seq';
       -- Check if book exists first
       BEGIN
          SELECT 'Y'  INTO v_exists FROM dual WHERE EXISTS
            (SELECT 1 FROM book WHERE book_id = p_bkid);
          dbms_output.put_line('Sequence re-used:  '||v_seq);
       EXCEPTION
          -- If book doesn't exist in table => sequence has never been created
          WHEN NO_DATA_FOUND THEN
             v_seq_sql := 'CREATE SEQUENCE '||v_seq
                          ||' INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE';
             dbms_output.put_line('SQL:  '||v_seq_sql);
             dbms_output.put_line('Sequence created:  '||v_seq);
             EXECUTE IMMEDIATE v_seq_sql;
          --
       END;
       --
       INSERT INTO book VALUES (p_bkid,p_bknm,p_bkst,p_price);
       dbms_output.put_line('Book inserted');
       EXECUTE IMMEDIATE 'SELECT '||v_seq ||'.NEXTVAL FROM DUAL' INTO v_seq_val;  
       v_track_id := SUBSTR(p_bknm,1,3)|| TO_CHAR(v_seq_val);
       dbms_output.put_line('Track Id:'||v_track_id);
       v_ins_sql := 'INSERT INTO book_details VALUES ('
                       ||' :v ,'
                       || p_bkid ||','
                       || '''Available'''
                       || ')';
       EXECUTE IMMEDIATE v_ins_sql USING v_track_id;
       dbms_output.put_line('Book Details inserted');
       --
       COMMIT;
       --
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Error:'|| SQLERRM);
    END;
    /
     
    heepth likes this.
  13. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Hi RajenB,
    yeah but the sequence will generate like 1,2,3,4.. and I need to concat the book name with that sequence like oracle1,oracle2 ...and book name we can get it from a parameter ''p_bknm'' .I hope so u understand my requirement.
     
  14. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi heepth,

    In your previous post, you said:

    Sequence will always generate numbers 1,2,3,4, ...
    That's why in the last code I posted, I've done the following (concatenated sequence with 1st 3 characters of book name):

    And in the Insert statement, I used v_track_id=> this should populate book_details.track_id with values like 'ora1', 'ora2', ...'jav1, 'jav2',...

    Have you tried the script ? If yes, what results are you getting ?
     
  15. heepth

    heepth Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    chennai
    Yeah i got it.Thanks buddy