1 2 Previous Next 15 Replies Latest reply: Jan 30, 2013 4:49 AM by 982895 RSS

    Procedure

    982895
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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