Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Stored Procedure Question

    I have a stored procedure that uses a cursor that returns a bunch of values. Before populating the variable that inserts the values into the table, I concatinate the values.

    ls_cloth_description := ls_cloth_description||' '||c1_rownum||') '||c1_description||'-'||c1_quantity;


    Here is the result:

    1) Value1-1 2) Value2-1 3) Value3-1 4) Value4-1 5) Value5-1 6) Value6-1 7) Value7-1 8) Value8-1 9) Value9-1 10) Value10-1 11) Value11-2 12) Value12-2 13) Value13-2

    I need to put values 1-7 on one line and values 8-13 on the line below. Could you tell me how this can be accomplished? Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    What exactly is the justification for placing 7 values on the line - Do you just want to do rows of 7?

    Explain a little more about what you're after. In the example you give info for 13 rows. What about 15 rows...23 rows?..etc -
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    The maximum values returned is 13. I am using this stored procedure as a bookmark on the word document from. It looks good on the form to display 7 values on one line and the rest on the second line.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    So, I assume by "I need to put values 1-7 on one line and values 8-13 on the line below" you would be using a cursor or something related to fetch those records, if so, you can try:

    Code:
     select *
       from (
     select substr(ls_cloth_description,1,instr(a,'8)')-1) from dual
      union all
     select substr(ls_cloth_description,instr(a,'8)')) from dual
            )

  5. #5
    Join Date
    Jul 2004
    Posts
    268
    This is my store procedure. Can you modify it to get the result that I am looking for? Thanks.

    CURSOR c1 IS

    SELECT rownum,table2.val2,table1.val3
    FROM table1, table2
    WHERE table1.col1 = table2.col1


    v1 number;
    v2 table2.col1%TYPE;
    v3 table1.col1%TYPE;



    BEGIN


    OPEN c1;

    loop



    fetch c1 into v1, v2, v3;
    exit when c1r%notfound;



    ls_cloth_description := ls_cloth_description||' '||v1||') '||v2||'-'||v3;



    end loop;
    CLOSE c1;

    INSERT INTO table3
    VALUES (col1, col2, ls_cloth_description, col4);


    EXCEPTION
    WHEN OTHERS THEN
    INSERT INTO table3
    VALUES (col1, col2, ' ', col4);
    END;
    END;

Posting Permissions

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