This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 30, 2013 2:49 AM by 982895 RSS

Procedure

982895 Newbie
Currently Being Moderated
Hi, I have a procedure which should get the ',' instead of spaces. Its working fine but missing little data in the output. It can be run directly in your machines too. Please check and help me how to get the exact data. Since its a large data I am not pasting the output. The data like SNB in the very 4th line,SVBE in the next line etc are missing.
Thanks in advance.

CREATE OR REPLACE PROCEDURE Test AS


v_main VARCHAR2(1000) := 'SUBSCRIBER LINE TEST

VERSION 2

DEV SNB MP FCODE
LIBA-13 4222430012 11 3

FOREIGN VOLTAGE TESTS

DC AC
FVAE FVBE FVAB FVAE FVBE FVAB
LACC LACC 7.4 0.1 0.1 0.1
0.0 0.0 PASS PASS PASS PASS
PASS PASS


INSULATION RESISTANCE TESTS

IRAE IRBE IRABA IRBBA IRAB
LACC LACC LACC LACC 8113K
10000K >10000K >10000K >10000K PASS
PASS PASS PASS PASS


CAPACITANCE MEASUREMENTS

CAE CBE CAB BRK
LACC LACC 4428 NO
68 72


NT LOOPBACK TEST
PASS


NT DC SIGNATURE TEST

V-HIGH V-LOW
83.920K LACC
PASS >10000K
PASS

NT CONNECTED
INV


CIRCUIT TEST RESULT
FAIL';

v_trans_main VARCHAR2(1000);
j NUMBER;
i NUMBER;

BEGIN

SELECT translate(v_main,
' ',
',')
INTO v_trans_main
FROM dual;

i := 1;
-- FOR i IN 1 .. length(v_trans_main)
LOOP

BEGIN
IF (substr(v_trans_main,
i,
1) LIKE ',' AND
substr(v_trans_main,
i + 1,
1) LIKE ',')
THEN
BEGIN
j := i + 1;

LOOP
IF substr(v_trans_main,
j,
1) = ','
THEN
j := j + 1;
ELSE

v_trans_main := substr(v_trans_main,
1,
i - 1) || ',' ||
substr(v_trans_main,
j);

EXIT WHEN substr(v_trans_main,
j,
1) != ',';

END IF;
END LOOP;
END;

END IF;
i := i+1;
EXIT WHEN i = length(v_trans_main) ;
END;
END LOOP;

dbms_output.put_line(v_trans_main);
END;
  • 1. Re: Procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    welcome to the forum.

    Please read SQL and PL/SQL FAQ

    Additionally when you put some code or output please enclose it between two lines starting with {noformat}
    {noformat}
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    What is your input and expected output for your procedure?
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 2. Re: Procedure
    982895 Newbie
    Currently Being Moderated
    Hi,
    If you correctly read my thread, I have given you that the output is nothing but the input(v_main) with commas.
    As its a large data, I dint paste the output. If you run the whole procedure from your side as it is, You will get the output and gave you the example data missing.
    Orelse If you tel me how to attach a file ,Ill do that..
  • 3. Re: Procedure
    Paul Horth Expert
    Currently Being Moderated
    979892 wrote:
    Hi,
    If you correctly read my thread, I have given you that the output is nothing but the input(v_main) with commas.
    As its a large data, I dint paste the output. If you run the whole procedure from your side as it is, You will get the output and gave you the example data missing.
    Orelse If you tel me how to attach a file ,Ill do that..
    Once again you have ignored the request to read {message:id=9360002} and do as it asks there (as you did in your other post {message:id=10817445}).

    If you want people to help you, you must put some effort in yourself.
  • 4. Re: Procedure
    982895 Newbie
    Currently Being Moderated
    Ok Gurus, I will follow Your rules. As its little difficult to paste the Input and output, jus gave procedure. Anyways I am giving you sample Input and expected output. Please help me in achieving the same. This is regarding replacing the spaces to commas between the words but not between the lines.

    My Input is :
    ----------------------
    SUBSCRIBER LINE TEST

    VERSION 2

    DEV SNB MP FCODE
    LIBA-13 4222430012 11 3

    FOREIGN VOLTAGE TESTS

    DC AC
    FVAE FVBE FVAB FVAE FVBE FVAB
    LACC LACC 7.4 0.1 0.1 0.1
    0.0 0.0 PASS PASS PASS PASS
    PASS PASS


    INSULATION RESISTANCE TESTS

    IRAE IRBE IRABA IRBBA IRAB
    LACC LACC LACC LACC 8113K
    10000K >10000K >10000K >10000K PASS
    PASS PASS PASS PASS
    ---------------------------------------------------------------------------------
    Expected output is:
    ---------------------------------------------------
    SUBSCRIBER,LINE,TEST,

    VERSION,2

    DEV,SNB,MP,FCODE
    LIBA-13,4222430012,11,3

    FOREIGN,VOLTAGE,TESTS

    DC,AC
    FVAE,FVBE,FVAB,FVAE,FVBE,FVAB
    LACC,LACC,7.4,0.1,0.1,0.1
    0.0,PASS,PASS,PASS,PASS
    PASS,PASS


    INSULATION,RESISTANCE,TESTS

    IRAE,IRABA,LACC,8113K
    LACC,LACC, LACC, LACC,8113K
    10000K,>10000K,>10000K,>10000K,PASS
    PASS,PASS,PASS,PASS
    -------------------------------------------------------------------
    Thanks in advance.
  • 5. Re: Procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    979892 wrote:
    Hi,
    If you correctly read my thread, I have given you that the output is nothing but the input(v_main) with commas.
    As its a large data, I dint paste the output. If you run the whole procedure from your side as it is, You will get the output and gave you the example data missing.
    Orelse If you tel me how to attach a file ,Ill do that..
    Hi,

    I have correctly read your thread and all you have posted is:
    Hi, I have a procedure which should get the ',' instead of spaces.
    Apparently you are doing something else in your code otherwise a normal:
     REPLACE(v_main,' ',',')
    would be enough for your requirement. So do you want me to reverse engineering your procedure to understand what you want to do? I think it is not working in this way if you want to get some help.

    Also I suggest you to update your message and put your code between 2 lines starting with {noformat}
    {noformat}. I have already told you but probably don't bother. Look how your code would be more readable in this way:
    CREATE OR REPLACE PROCEDURE Test AS


    v_main VARCHAR2(1000) := 'SUBSCRIBER LINE TEST

    VERSION 2

    DEV SNB MP FCODE
    LIBA-13 4222430012 11 3

    FOREIGN VOLTAGE TESTS

    DC AC
    FVAE FVBE FVAB FVAE FVBE FVAB
    LACC LACC 7.4 0.1 0.1 0.1
    0.0 0.0 PASS PASS PASS PASS
    PASS PASS


    INSULATION RESISTANCE TESTS

    IRAE IRBE IRABA IRBBA IRAB
    LACC LACC LACC LACC 8113K
    10000K >10000K >10000K >10000K PASS
    PASS PASS PASS PASS


    CAPACITANCE MEASUREMENTS

    CAE CBE CAB BRK
    LACC LACC 4428 NO
    68 72


    NT LOOPBACK TEST
    PASS


    NT DC SIGNATURE TEST

    V-HIGH V-LOW
    83.920K LACC
    PASS >10000K
    PASS

    NT CONNECTED
    INV


    CIRCUIT TEST RESULT
    FAIL';

    v_trans_main VARCHAR2(1000);
    j NUMBER;
    i NUMBER;

    BEGIN

    SELECT translate(v_main,
    ' ',
    ',')
    INTO v_trans_main
    FROM dual;

    i := 1;
    -- FOR i IN 1 .. length(v_trans_main)
    LOOP

    BEGIN
    IF (substr(v_trans_main,
    i,
    1) LIKE ',' AND
    substr(v_trans_main,
    i + 1,
    1) LIKE ',')
    THEN
    BEGIN
    j := i + 1;

    LOOP
    IF substr(v_trans_main,
    j,
    1) = ','
    THEN
    j := j + 1;
    ELSE

    v_trans_main := substr(v_trans_main,
    1,
    i - 1) || ',' ||
    substr(v_trans_main,
    j);

    EXIT WHEN substr(v_trans_main,
    j,
    1) != ',';

    END IF;
    END LOOP;
    END;

    END IF;
    i := i+1;
    EXIT WHEN i = length(v_trans_main) ;
    END;
    END LOOP;

    dbms_output.put_line(v_trans_main);
    END;
    Was it too difficult?
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 6. Re: Procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    All you need is just a regular expression to replace multiple spaces with one comma:
    REGEXP_REPLACE(v_main, ' +',',')
    One line will do the job instead of loop.
    CREATE OR REPLACE PROCEDURE test AS
     
       v_main VARCHAR2(1000) := 'SUBSCRIBER LINE TEST 
    
    VERSION 2
    
    DEV              SNB           MP  FCODE
    LIBA-13          4222430012    11    3
    
    FOREIGN VOLTAGE TESTS
    
    DC                       AC
    FVAE     FVBE    FVAB    FVAE    FVBE    FVAB
    LACC     LACC    7.4     0.1     0.1     0.1
    0.0      0.0     PASS    PASS    PASS    PASS
    PASS     PASS
    
    
    INSULATION RESISTANCE TESTS
    
    IRAE        IRBE       IRABA      IRBBA      IRAB
    LACC        LACC       LACC       LACC       8113K
    {noformat}>{noformat}10000K     >10000K    >10000K    >10000K    PASS
    PASS        PASS       PASS       PASS
    
    
    CAPACITANCE MEASUREMENTS
    
    CAE      CBE     CAB     BRK
    LACC     LACC    4428    NO
    68       72
    
    
    NT LOOPBACK TEST
    PASS
    
    
    NT DC SIGNATURE TEST
    
    V-HIGH      V-LOW
    83.920K     LACC
    PASS        >10000K
                PASS
    
    NT CONNECTED
    INV
    
    
    CIRCUIT TEST RESULT
    FAIL';
     
    BEGIN
     
       DBMS_OUTPUT.PUT_LINE(REGEXP_REPLACE(v_main, ' +',','));
    
    END;
    /
    The output is
    SQL> exec test();
    SUBSCRIBER,LINE,TEST,
    
    VERSION,2
    
    DEV,SNB,MP,FCODE
    LIBA-13,4222430012,11,3
    
    FORE
    IGN,VOLTAGE,TESTS
    
    DC,AC
    FVAE,FVBE,FVAB,FVAE,FVBE,FVAB
    LACC,LACC,7.4,0.1,0.1,0.1
    
    0.0,0.0,PASS,PASS,PASS,PASS
    PASS,PASS
    
    
    INSULATION,RESISTANCE,TESTS
    
    IRAE,IRBE,
    IRABA,IRBBA,IRAB
    LACC,LACC,LACC,LACC,8113K
    {noformat}>{noformat}10000K,>10000K,>10000K,>10000K,PASS
    
    PASS,PASS,PASS,PASS
    
    
    CAPACITANCE,MEASUREMENTS
    
    CAE,CBE,CAB,BRK
    LACC,LACC,4428,N
    O
    68,72
    
    
    NT,LOOPBACK,TEST
    PASS
    
    
    NT,DC,SIGNATURE,TEST
    
    V-HIGH,V-LOW
    83.920K,LAC
    C
    PASS,>10000K
    ,PASS
    
    NT,CONNECTED
    INV
    
    
    CIRCUIT,TEST,RESULT
    FAIL
    
    PL/SQL procedure successfully completed.
    Not sure if this is exactly what you need but in case just post what is not fitting your requirements.

    Regards.
    Al

    Edited by: Alberto Faenza on Jan 29, 2013 3:11 PM
    Use noformat in the text to avoid Jive issue
  • 7. Re: Procedure
    982895 Newbie
    Currently Being Moderated
    Sir,
    Really appreciate your function and very much working fine..Thankyou so much. But my Boss needs the output without any function itseems as this REGEXP will not work for 9i. Although we are not using 9i, this is the order from my Boss :(..
    Please assist me in any alternate way..

    Thanks in advance..
  • 8. Re: Procedure
    Paul Horth Expert
    Currently Being Moderated
    979892 wrote:
    Sir,
    Really appreciate your function and very much working fine..Thankyou so much. But my Boss needs the output without any function itseems as this REGEXP will not work for 9i. Although we are not using 9i, this is the order from my Boss :(..
    Please assist me in any alternate way..

    Thanks in advance..
    Ask your boss why is worried about such an old release of Oracle, especially when you aren't using it! Even Release 10 is out of support.

    This is the kind of nonsense that gets bosses a bad name :-)
  • 9. Re: Procedure
    982895 Newbie
    Currently Being Moderated
    Hahhahaha..thats true but what exactly is... few systems still uses 9i..So, he wants my code to work in every machine..
    I m clueless why he wants to run in every machine.. nothng but raising my BP levels.. :)
  • 10. Re: Procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    979892 wrote:
    Hahhahaha..thats true but what exactly is... few systems still uses 9i..So, he wants my code to work in every machine..
    I m clueless why he wants to run in every machine.. nothng but raising my BP levels.. :)
    Regular expression has been implemented only in Oracle 10g. In Oracle 9i you can use OWA_PATTERN package:
    OWA_PATTERN.CHANGE(v_main, ' +',',','g');
    to do the same. However I would not suggest to use the same approach on Oracle 10g because Regular Expression have a better performance than OWA_PATTERN package.

    Regards.
    Al
  • 11. Re: Procedure
    982895 Newbie
    Currently Being Moderated
    Hi ALberto,

    will it works in 10g too.. because I am gettng error while executing the below query:

    DBMS_OUTPUT.PUT_LINE(OWA_PATTERN.CHANGE(v_main, ' +',',','g'));
  • 12. Re: Procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    it's a procedure, not a function. This is the way to use it:
    BEGIN
     
       OWA_PATTERN.CHANGE(v_main, ' +',',','g');
       DBMS_OUTPUT.PUT_LINE(v_main);
     
    END;
    /
    Using it in 10g, IMHO, it is not advisable. Why you should use a Tata when you have a Ferrari available? ;)

    Regards.
    Al
  • 13. Re: Procedure
    982895 Newbie
    Currently Being Moderated
    Yeah it worked pretty well. Yes We prefer Ferrari but my Boss is like he wants to use both Ferrari and TAta at a time.
    Anyways Thanku Alberto. I guess my task is completed atleast now if my boss gets satisfied.

    Edited by: 979892 on Jan 30, 2013 2:33 AM
  • 14. Re: Procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    979892 wrote:
    Yeah it worked pretty well. Yes We prefer Ferrari but my Boss is like he wants to use both Ferrari and TAta at a time.
    Anyways Thanku Alberto. I guess my task is completed atleast now if my boss gets satisfied.

    Edited by: 979892 on Jan 30, 2013 2:33 AM
    I would suggest to convince your boss to use regular expression when Oracle is 10g or 11g. OWA_PATTERN package, especially when using with large amount of data is really slow.

    Regards.
    Al
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points