12 Replies Latest reply on Jun 20, 2019 6:09 AM by Quanwen Zhao

    ORA-01427: single-row subquery returns more than one row

    Quanwen Zhao

      Hello, guys and friends

       

      I run this SQL on my Oracle Database Server 11.2.0.4.0 on SQL*Plus utility,

       

      SET ECHO      ON
      SET HEADING   ON
      SET FEEDBACK  ON
      SET VERIFY    ON
      
      
      
      
      SET LINESIZE 250
      SET PAGESIZE 250
      
      
      SELECT
            (SELECT host_name FROM v$instance)
            || ', ' ||
            (SELECT value FROM v$parameter WHERE name = 'db_unique_name')
            || ', ' ||
            (SELECT
                    start_time || ', ' ||
                    end_time || ', ' ||
                    output_device_type || ', ' ||
                    status || ', ' ||
                    input_type || ', ' ||
                    ltrim(input_bytes_display) || ', ' ||
                    ltrim(output_bytes_display) || ', ' ||
                    ltrim(input_bytes_per_sec_display) || ', ' ||
                    ltrim(output_bytes_per_sec_display) || ', ' ||
                    time_taken_display
             FROM  v$rman_backup_job_details
             WHERE output_device_type = 'DISK'
             -- AND To_Char(start_time,'dd-mm-yy') = To_Char(sysdate - 1,'dd-mm-yy')
            )
      FROM dual
      /
      

       

      ERROR at line 6:
      ORA-01427: single-row subquery returns more than one row
      

       

      This error ORA-01247 indicates that the 3rd SELECT statement for v$rman_backup_job_details should return the value of more than one row.

       

      If I use PL/SQL how to implement this function/requirement? Thanks in advance.

       

      Best Regards

      Quanwen Zhao

        • 1. Re: ORA-01427: single-row subquery returns more than one row
          AndrewSayer

          Quanwen Zhao wrote:

           

          Hello, guys and friends

           

          I run this SQL on my Oracle Database Server 11.2.0.4.0 on SQL*Plus utility,

           

          1. SETECHOON
          2. SETHEADINGON
          3. SETFEEDBACKON
          4. SETVERIFYON
          5. SETLINESIZE250
          6. SETPAGESIZE250
          7. SELECT
          8. (SELECThost_nameFROMv$instance)
          9. ||','||
          10. (SELECTvalueFROMv$parameterWHEREname='db_unique_name')
          11. ||','||
          12. (SELECT
          13. start_time||','||
          14. end_time||','||
          15. output_device_type||','||
          16. status||','||
          17. input_type||','||
          18. ltrim(input_bytes_display)||','||
          19. ltrim(output_bytes_display)||','||
          20. ltrim(input_bytes_per_sec_display)||','||
          21. ltrim(output_bytes_per_sec_display)||','||
          22. time_taken_display
          23. FROMv$rman_backup_job_details
          24. WHEREoutput_device_type='DISK'
          25. --ANDTo_Char(start_time,'dd-mm-yy')=To_Char(sysdate-1,'dd-mm-yy')
          26. )
          27. FROMdual
          28. /

           

          1. ERRORatline6:
          2. ORA-01427:single-rowsubqueryreturnsmorethanonerow

           

          This error ORA-01247 indicates that the 3rd SELECT statement for v$rman_backup_job_details should return the value of more than one row.

           

          If I use PL/SQL how to implement this function/requirement? Thanks in advance.

           

          Best Regards

          Quanwen Zhao

          What requirement?

          You can’t return multiple rows via a scalar subquery. It doesn’t make any sense to do so.

           

          If you want to return all the rows from your third subquery then left join to it instead (or use It instead of dual).

          If you only want to return one row then add filters to ensure that happens.

          • 2. Re: ORA-01427: single-row subquery returns more than one row
            Cookiemonster76

            Selects from dual are inherently going to give 1 row. If you want more than 1 row then select from something else.

             

            If you've got queries that by design return 1 row and you want to join them to a query that returns multiple rows then just join.

            And the fact that there is nothing to actually join on doesn't matter because of the fact that one of the queries only ever returns 1 row. So you can join on 1=1 or column = value in this case and you won't get a cartesian (well you do but result set multiplied by 1 is unchanged).

             

            SELECT i.host_name || ', ' || 

                   p.value || ', ' || 

                   rbjd.start_time || ', ' || 

                   rbjd.end_time || ', ' || 

                   rbjd.output_device_type || ', ' || 

                   rbjd.status || ', ' || 

                   rbjd.input_type || ', ' || 

                   ltrim(rbjd.input_bytes_display) || ', ' || 

                   ltrim(rbjd.output_bytes_display) || ', ' || 

                   ltrim(rbjd.input_bytes_per_sec_display) || ', ' || 

                   ltrim(rbjd.output_bytes_per_sec_display) || ', ' || 

                   rbjd.time_taken_display 

            FROM v$instance i

            JOIN v$parameter p ON p.name = 'db_unique_name'

            LEFT JOIN v$rman_backup_job_details rbjd ON rbjd.output_device_type = 'DISK';

            1 person found this helpful
            • 3. Re: ORA-01427: single-row subquery returns more than one row
              Manik

              what is the output of  this :

               

              select *  FROM  v$rman_backup_job_details  WHERE output_device_type = 'DISK' ;

               

              Cheers,

              Manik.

              • 4. Re: ORA-01427: single-row subquery returns more than one row
                Quanwen Zhao

                SELECT i.host_name || ', ' ||

                       p.value || ', ' ||

                       rbjd.start_time || ', ' ||

                       rbjd.end_time || ', ' ||

                       rbjd.output_device_type || ', ' ||

                       rbjd.status || ', ' ||

                       rbjd.input_type || ', ' ||

                       ltrim(rbjd.input_bytes_display) || ', ' ||

                       ltrim(rbjd.output_bytes_display) || ', ' ||

                       ltrim(rbjd.input_bytes_per_sec_display) || ', ' ||

                       ltrim(rbjd.output_bytes_per_sec_display) || ', ' ||

                       rbjd.time_taken_display

                FROM v$instance i

                JOIN v$parameter p ON p.name = 'db_unique_name'

                LEFT JOIN v$rman_backup_job_details rbjd ON rbjd.output_device_type = 'DISK';

                Hey Cookiemonster76 ,

                 

                Via joining three tables it's a actually good idea.

                 

                test, test, 2019-05-31 22:00:03, 2019-05-31 23:38:28, DISK, COMPLETED, DB INCR, 920.94G, 916.86G, 159.70M, 158.99M, 01:38:25
                test, test, 2019-06-01 22:00:05, 2019-06-01 22:45:13, DISK, COMPLETED, DB INCR, 1.24T, 298.87M, 480.48M, 113.01K, 00:45:08
                test, test, 2019-06-02 22:00:04, 2019-06-02 22:46:31, DISK, COMPLETED, DB INCR, 1.24T, 378.55M, 466.86M, 139.09K, 00:46:27
                test, test, 2019-06-03 22:00:04, 2019-06-03 22:45:11, DISK, COMPLETED, DB INCR, 1.24T, 209.50M, 480.65M, 79.25K, 00:45:07
                test, test, 2019-06-04 22:00:03, 2019-06-04 22:45:49, DISK, COMPLETED, DB INCR, 1.24T, 264.00M, 473.82M, 98.45K, 00:45:46
                test, test, 2019-06-05 22:00:03, 2019-06-05 22:55:32, DISK, COMPLETED, DB INCR, 1.26T, 23.53G, 395.74M, 7.24M, 00:55:29
                test, test, 2019-06-06 22:00:05, 2019-06-06 22:52:41, DISK, COMPLETED, DB INCR, 1.24T, 9.80G, 412.26M, 3.18M, 00:52:36
                test, test, 2019-06-07 22:00:04, 2019-06-07 23:46:25, DISK, COMPLETED, DB INCR, 921.05G, 916.91G, 147.81M, 147.14M, 01:46:21
                test, test, 2019-06-08 22:00:04, 2019-06-08 22:44:01, DISK, COMPLETED, DB INCR, 1.24T, 229.62M, 493.40M, 89.17K, 00:43:57
                test, test, 2019-06-09 22:00:04, 2019-06-09 22:47:30, DISK, COMPLETED, DB INCR, 1.24T, 257.46M, 457.18M, 92.63K, 00:47:26
                test, test, 2019-06-10 22:00:04, 2019-06-10 22:46:40, DISK, COMPLETED, DB INCR, 1.24T, 202.77M, 465.35M, 74.26K, 00:46:36
                test, test, 2019-06-11 22:00:04, 2019-06-11 22:50:50, DISK, COMPLETED, DB INCR, 1.24T, 2.78G, 427.17M, 956.58K, 00:50:46
                test, test, 2019-06-12 22:00:04, 2019-06-12 22:48:10, DISK, COMPLETED, DB INCR, 1.24T, 2.74G, 450.84M, 996.11K, 00:48:06
                test, test, 2019-06-13 22:00:04, 2019-06-13 22:45:10, DISK, COMPLETED, DB INCR, 1.24T, 244.26M, 480.82M, 92.43K, 00:45:06
                test, test, 2019-06-14 22:00:03, 2019-06-14 23:42:10, DISK, COMPLETED, DB INCR, 921.19G, 916.98G, 153.96M, 153.25M, 01:42:07
                test, test, 2019-06-15 22:00:03, 2019-06-15 22:45:10, DISK, COMPLETED, DB INCR, 1.24T, 240.09M, 480.65M, 90.82K, 00:45:07
                test, test, 2019-06-16 22:00:04, 2019-06-16 22:45:40, DISK, COMPLETED, DB INCR, 1.24T, 243.21M, 475.56M, 91.03K, 00:45:36
                test, test, 2019-06-17 22:00:04, 2019-06-17 22:46:30, DISK, COMPLETED, DB INCR, 1.24T, 206.82M, 467.02M, 76.02K, 00:46:26
                

                 

                Maybe I think this issue has a little complex, could you help me rewrite it using PL/SQL snippet?

                 

                Thank you very much.

                 

                Best Regards

                Quanwen Zhao

                • 5. Re: ORA-01427: single-row subquery returns more than one row
                  Cookiemonster76

                  It makes no obvious sense to me to write that as PL/SQL.

                  What did you have in mind?

                  • 6. Re: ORA-01427: single-row subquery returns more than one row
                    Sven W.

                    The view v$rman_backup_job_details will show information about all recent backups.

                    From this list, do you want to see all, or do you prefere only a single one. WHich one?

                     

                    The following select will only show the most recent backup job.

                    Also note that sqlcl, sqldeveloper and the most recent sql*plus version has an option to generate data in csv file format. So you don't need to concat the columns by your own. Just add the /*csv*/ comment.

                     

                    set sqlformat csv

                    select *

                    from (

                        SELECT

                           row_number() over (order by rbjd.start_time desc) as rn,

                           (select i.host_name from v$instance i) as host,

                           (select p.value from v$parameter p where p.name = 'db_unique_name') as db_unique_name,

                           rbjd.start_time,

                           rbjd.end_time,

                           rbjd.output_device_type,

                           rbjd.status,

                           rbjd.input_type,

                           rbjd.input_bytes_display,

                           rbjd.output_bytes_display,

                           rbjd.input_bytes_per_sec_display,

                           rbjd.output_bytes_per_sec_display,

                           rbjd.time_taken_display

                       FROM v$rman_backup_job_details rbjd

                       where rbjd.output_device_type = 'DISK'

                    )

                    where rn = 1

                    ;

                     

                    see also: Generate CSV with SQL*Plus 12.2.0.1 – DBA Blog

                    1 person found this helpful
                    • 7. Re: ORA-01427: single-row subquery returns more than one row
                      Quanwen Zhao

                      Hello everyone ,

                       

                      I'm just a newbies newbie on Oracle PL/SQL, which this time I try to use to rewrite code Cookiemonster76 has provided to me, what a pity I have a few confusion why has a series of errors .

                       

                      The both methods calling PL/SQL cursor for "common explicit cursor" and "implicit cursor" are as follows,

                       

                      (1) common explicit cursor (open ... fetch ... close ...):

                       

                      SET SERVEROUTPUT ON
                      
                      
                      DECLARE
                        v_name       varchar2(50);
                        v_value      varchar2(50);
                        v_rbjd_table varchar2(4000);
                        CURSOR c_rbjd_table IS
                        SELECT start_time
                             , end_time
                             , output_device_type AS odt
                             , status
                             , input_type
                             , ltrim(input_bytes_display) AS ibd
                             , ltrim(output_bytes_display) AS obd
                             , ltrim(input_bytes_per_sec_display) AS ibpd
                             , ltrim(output_bytes_per_sec_display) AS obpd
                             , time_taken_display AS ttd
                        FROM  v$rman_backup_job_details
                        WHERE output_device_type = 'DISK'
                        /
                        
                      BEGIN
                      SELECT host_name INTO v_name FROM v$instance;
                      SELECT value INTO v_value FROM v$parameter WHERE name = 'db_unique_name';
                      OPEN c_rbjd_table;
                      LOOP
                        FETCH c_rbjd_table INTO v_rbjd_table;
                        EXIT WHEN c_rbjd_table%NOTFOUND;
                        DBMS_OUTPUT.put_line(v_name
                                             || ', ' || v_value
                                             || ', ' || v_rbjd_table.start_time
                                             || ', ' || v_rbjd_table.end_time
                                             || ', ' || v_rbjd_table.odt
                                             || ', ' || v_rbjd_table.status
                                             || ', ' || v_rbjd_table.input_type
                                             || ', ' || v_rbjd_table.ibd
                                             || ', ' || v_rbjd_table.obd
                                             || ', ' || v_rbjd_table.ibpd
                                             || ', ' || v_rbjd_table.obpd
                                             || ', ' || v_rbjd_table.ttd
                                            );
                      END LOOP;
                      CLOSE c_rbjd_table;
                      END;
                      /
                      
                      
                      SELECT host_name INTO v_name FROM v$instance;
                                            *
                      ERROR at line 2:
                      ORA-06550: line 2, column 23:
                      PLS-00201: identifier 'V_NAME' must be declared
                      ORA-06550: line 2, column 30:
                      PL/SQL: ORA-00904: : invalid identifier
                      ORA-06550: line 2, column 1:
                      PL/SQL: SQL Statement ignored
                      ORA-06550: line 3, column 19:
                      PLS-00201: identifier 'V_VALUE' must be declared
                      ORA-06550: line 3, column 27:
                      PL/SQL: ORA-00904: : invalid identifier
                      ORA-06550: line 3, column 1:
                      PL/SQL: SQL Statement ignored
                      ORA-06550: line 4, column 6:
                      PLS-00201: identifier 'C_RBJD_TABLE' must be declared
                      ORA-06550: line 4, column 1:
                      PL/SQL: SQL Statement ignored
                      ORA-06550: line 6, column 9:
                      PLS-00201: identifier 'C_RBJD_TABLE' must be declared
                      ORA-06550: line 6, column 3:
                      PL/SQL: SQL Statement ignored
                      ORA-06550: line 7, column 13:
                      PLS-00201: identifier 'C_RBJD_TABLE' must be declared
                      ORA-06550: line 7, column 3:
                      PL/SQL: Statement ignored
                      ORA-06550: line 8, column 24:
                      PLS-00201: identifier 'V_NAME' must be declared
                      ORA-06550: line 8, column 3:
                      PL/SQL: Statement ignored
                      ORA-06550: line 22, column 7:
                      PLS-00201: identifier 'C_RBJD_TABLE' must be declared
                      ORA-06550: line 22, column 1:
                      PL/SQL: SQL Statement ignored
                      

                       

                      (2) implicit cursor (for ... in ...):

                       

                      SET SERVEROUTPUT ON
                      
                      
                      DECLARE
                        v_name       varchar2(50);
                        v_value      varchar2(50);
                        v_rbjd_table varchar2(4000);
                        CURSOR c_rbjd_table IS
                        SELECT start_time
                             , end_time
                             , output_device_type AS odt
                             , status
                             , input_type
                             , ltrim(input_bytes_display) AS ibd
                             , ltrim(output_bytes_display) AS obd
                             , ltrim(input_bytes_per_sec_display) AS ibpd
                             , ltrim(output_bytes_per_sec_display) AS obpd
                             , time_taken_display AS ttd
                        FROM  v$rman_backup_job_details
                        WHERE output_device_type = 'DISK'
                        /
                        
                      BEGIN
                      SELECT host_name INTO v_name FROM v$instance;
                      SELECT value INTO v_value FROM v$parameter WHERE name = 'db_unique_name';
                      FOR cur_rbjd_table IN c_rbjd_table
                      LOOP
                        DBMS_OUTPUT.put_line(v_name
                                             || ', ' || v_value
                                             || ', ' || v_rbjd_table.start_time
                                             || ', ' || v_rbjd_table.end_time
                                             || ', ' || v_rbjd_table.odt
                                             || ', ' || v_rbjd_table.status
                                             || ', ' || v_rbjd_table.input_type
                                             || ', ' || v_rbjd_table.ibd
                                             || ', ' || v_rbjd_table.obd
                                             || ', ' || v_rbjd_table.ibpd
                                             || ', ' || v_rbjd_table.obpd
                                             || ', ' || v_rbjd_table.ttd
                                            );
                      END LOOP;
                      END;
                      /
                      
                      
                      ERROR at line 2:
                      ORA-06550: line 2, column 23:
                      PLS-00201: identifier 'V_NAME' must be declared
                      ORA-06550: line 2, column 30:
                      PL/SQL: ORA-00904: : invalid identifier
                      ORA-06550: line 2, column 1:
                      PL/SQL: SQL Statement ignored
                      ORA-06550: line 3, column 19:
                      PLS-00201: identifier 'V_VALUE' must be declared
                      ORA-06550: line 3, column 27:
                      PL/SQL: ORA-00904: : invalid identifier
                      ORA-06550: line 3, column 1:
                      PL/SQL: SQL Statement ignored
                      ORA-06550: line 4, column 23:
                      PLS-00201: identifier 'C_RBJD_TABLE' must be declared
                      ORA-06550: line 4, column 1:
                      PL/SQL: Statement ignored
                      

                       

                      Best Regards

                      Quanwen Zhao

                      • 8. Re: ORA-01427: single-row subquery returns more than one row
                        Quanwen Zhao

                        Hello Sven W. ,

                         

                        Yep, you're right. Oracle 12c start to have this simply and easy function /*csv*/ and sqlformat. I forgot to illustrate my current oracle version is still 11.2.0.4.0.

                         

                        Thank you very much.

                         

                        Best Regards

                        Quanwen Zhao

                        • 9. Re: ORA-01427: single-row subquery returns more than one row
                          Cookiemonster76

                          You skipped some errors on the first block. You should have got this after the declare section:

                          ERROR at line 17:

                          ORA-06550: line 17, column 35:

                          PLS-00103: Encountered the symbol "end-of-file" when expecting one of the

                          following:

                          ;

                           

                          Reason for that is that you've put a / at the end of the declare section. / tells sqlplus to send the preceding code to the DB to process.

                          So the DB has got a declare section and no executable section and so has to throw an error (also you're missing a semi-colon at the end of the cursor declaration).

                          The rest of the errors are a knock-on from that, as the BEGIN is sent to the DB seperately which leads to oracle not knowing about any of the variables you're using.

                           

                           

                          fix those and you'll get:

                            FETCH c_rbjd_table INTO v_rbjd_table;

                            *

                          ERROR at line 24:

                          ORA-06550: line 24, column 3:

                          PLS-00394: wrong number of values in the INTO list of a FETCH statement

                          ORA-06550: line 24, column 3:

                          PL/SQL: SQL Statement ignored

                          ORA-06550: line 28, column 48:

                          PLS-00487: Invalid reference to variable 'V_RBJD_TABLE'

                          ORA-06550: line 26, column 3:

                          PL/SQL: Statement ignored

                           

                           

                          This is because you've declare V_RBJD_TABLE is a varchar2(4000) rather than a record type.

                          It should be:

                          v_rbjd_table c_rbjd_table%ROWTYPE;

                           

                           

                          and that needs to come after the cursor declaration.

                           

                           

                          Fix those and it'll work.

                          1 person found this helpful
                          • 10. Re: ORA-01427: single-row subquery returns more than one row
                            Cookiemonster76

                            In the second block you've also got the extraneous / and missing semi-colon.

                             

                            Plus you're still trying to use the scalar variable v_rbjd_table as though it's a record type. You need to ditch that variable and use the implicit record variable from the FOR LOOP instead.

                            1 person found this helpful
                            • 11. Re: ORA-01427: single-row subquery returns more than one row
                              Quanwen Zhao

                              ERROR at line 17:

                              ORA-06550: line 17, column 35:

                              PLS-00103: Encountered the symbol "end-of-file" when expecting one of the

                              following:

                              ;

                              Hi Cookiemonster76 ,

                               

                              Yes, you're right. Due to error is too much I don't paste the full error contents. According to your minutely interpretation now I've replaced "/" with ";" and also added the declaration of cursor variable.

                               

                              This time it's eventually Okay.

                               

                              SET SERVEROUTPUT ON FORMAT WRAPPED
                              
                              
                              DECLARE
                                v_name       varchar2(50);
                                v_value      varchar2(50);
                              --  v_rbjd_table varchar2(4000);
                                CURSOR c_rbjd_table IS
                                SELECT start_time
                                     , end_time
                                     , output_device_type AS odt
                                     , status
                                     , input_type
                                     , ltrim(input_bytes_display) AS ibd
                                     , ltrim(output_bytes_display) AS obd
                                     , ltrim(input_bytes_per_sec_display) AS ibpd
                                     , ltrim(output_bytes_per_sec_display) AS obpd
                                     , time_taken_display AS ttd
                                FROM  v$rman_backup_job_details
                                WHERE output_device_type = 'DISK';
                                v_rbjd_table c_rbjd_table%ROWTYPE;
                                
                              BEGIN
                              SELECT host_name INTO v_name FROM v$instance;
                              SELECT value INTO v_value FROM v$parameter WHERE name = 'db_unique_name';
                              OPEN c_rbjd_table;
                              LOOP
                                FETCH c_rbjd_table INTO v_rbjd_table;
                                EXIT WHEN c_rbjd_table%NOTFOUND;
                                DBMS_OUTPUT.put_line(v_name
                                                     || ', ' || v_value
                                                     || ', ' || v_rbjd_table.start_time
                                                     || ', ' || v_rbjd_table.end_time
                                                     || ', ' || v_rbjd_table.odt
                                                     || ', ' || v_rbjd_table.status
                                                     || ', ' || v_rbjd_table.input_type
                                                     || ', ' || v_rbjd_table.ibd
                                                     || ', ' || v_rbjd_table.obd
                                                     || ', ' || v_rbjd_table.ibpd
                                                     || ', ' || v_rbjd_table.obpd
                                                     || ', ' || v_rbjd_table.ttd
                                                    );
                              END LOOP;
                              CLOSE c_rbjd_table;
                              END;
                              /
                              

                               

                              test, test, 2019-06-01 22:00:05, 2019-06-01 22:45:13, DISK, COMPLETED, D
                              B INCR, 1.24T, 298.87M, 480.48M, 113.01K, 00:45:08
                              test, test, 2019-06-02 22:00:04, 2019-06-02 22:46:31, DISK, COMPLETED, D
                              B INCR, 1.24T, 378.55M, 466.86M, 139.09K, 00:46:27
                              test, test, 2019-06-03 22:00:04, 2019-06-03 22:45:11, DISK, COMPLETED, D
                              B INCR, 1.24T, 209.50M, 480.65M, 79.25K, 00:45:07
                              test, test, 2019-06-04 22:00:03, 2019-06-04 22:45:49, DISK, COMPLETED, D
                              B INCR, 1.24T, 264.00M, 473.82M, 98.45K, 00:45:46
                              test, test, 2019-06-05 22:00:03, 2019-06-05 22:55:32, DISK, COMPLETED, D
                              B INCR, 1.26T, 23.53G, 395.74M, 7.24M, 00:55:29
                              test, test, 2019-06-06 22:00:05, 2019-06-06 22:52:41, DISK, COMPLETED, D
                              B INCR, 1.24T, 9.80G, 412.26M, 3.18M, 00:52:36
                              test, test, 2019-06-07 22:00:04, 2019-06-07 23:46:25, DISK, COMPLETED, D
                              B INCR, 921.05G, 916.91G, 147.81M, 147.14M, 01:46:21
                              test, test, 2019-06-08 22:00:04, 2019-06-08 22:44:01, DISK, COMPLETED, D
                              B INCR, 1.24T, 229.62M, 493.40M, 89.17K, 00:43:57
                              test, test, 2019-06-09 22:00:04, 2019-06-09 22:47:30, DISK, COMPLETED, D
                              B INCR, 1.24T, 257.46M, 457.18M, 92.63K, 00:47:26
                              test, test, 2019-06-10 22:00:04, 2019-06-10 22:46:40, DISK, COMPLETED, D
                              B INCR, 1.24T, 202.77M, 465.35M, 74.26K, 00:46:36
                              test, test, 2019-06-11 22:00:04, 2019-06-11 22:50:50, DISK, COMPLETED, D
                              B INCR, 1.24T, 2.78G, 427.17M, 956.58K, 00:50:46
                              test, test, 2019-06-12 22:00:04, 2019-06-12 22:48:10, DISK, COMPLETED, D
                              B INCR, 1.24T, 2.74G, 450.84M, 996.11K, 00:48:06
                              test, test, 2019-06-13 22:00:04, 2019-06-13 22:45:10, DISK, COMPLETED, D
                              B INCR, 1.24T, 244.26M, 480.82M, 92.43K, 00:45:06
                              test, test, 2019-06-14 22:00:03, 2019-06-14 23:42:10, DISK, COMPLETED, D
                              B INCR, 921.19G, 916.98G, 153.96M, 153.25M, 01:42:07
                              test, test, 2019-06-15 22:00:03, 2019-06-15 22:45:10, DISK, COMPLETED, D
                              B INCR, 1.24T, 240.09M, 480.65M, 90.82K, 00:45:07
                              test, test, 2019-06-16 22:00:04, 2019-06-16 22:45:40, DISK, COMPLETED, D
                              B INCR, 1.24T, 243.21M, 475.56M, 91.03K, 00:45:36
                              test, test, 2019-06-17 22:00:04, 2019-06-17 22:46:30, DISK, COMPLETED, D
                              B INCR, 1.24T, 206.82M, 467.02M, 76.02K, 00:46:26
                              test, test, 2019-06-18 22:00:04, 2019-06-18 22:48:11, DISK, COMPLETED, D
                              B INCR, 1.24T, 214.46M, 450.68M, 76.07K, 00:48:07
                              test, test, 2019-06-19 22:00:04, 2019-06-19 22:47:40, DISK, COMPLETED, D
                              B INCR, 1.24T, 219.56M, 455.57M, 78.72K, 00:47:36
                              
                              
                              PL/SQL procedure successfully completed.
                              

                               

                              Unfortunately output result each row is not on the same line. (Meanwhile I've also modified my 2nd PL/SQL code, the output result is still same to the previous output).

                               

                              Thank you very much.

                               

                              Best Regards

                              Quanwen Zhao

                              • 12. Re: ORA-01427: single-row subquery returns more than one row
                                Quanwen Zhao

                                After I add "SET LINESIZE 300" the output effect is perfect.

                                 

                                test, test, 2019-06-01 22:00:05, 2019-06-01 22:45:13, DISK, COMPLETED, DB INCR, 1.24T, 298.87M, 480.48M, 113.01K, 00:45:08
                                test, test, 2019-06-02 22:00:04, 2019-06-02 22:46:31, DISK, COMPLETED, DB INCR, 1.24T, 378.55M, 466.86M, 139.09K, 00:46:27
                                test, test, 2019-06-03 22:00:04, 2019-06-03 22:45:11, DISK, COMPLETED, DB INCR, 1.24T, 209.50M, 480.65M, 79.25K, 00:45:07
                                test, test, 2019-06-04 22:00:03, 2019-06-04 22:45:49, DISK, COMPLETED, DB INCR, 1.24T, 264.00M, 473.82M, 98.45K, 00:45:46
                                test, test, 2019-06-05 22:00:03, 2019-06-05 22:55:32, DISK, COMPLETED, DB INCR, 1.26T, 23.53G, 395.74M, 7.24M, 00:55:29
                                test, test, 2019-06-06 22:00:05, 2019-06-06 22:52:41, DISK, COMPLETED, DB INCR, 1.24T, 9.80G, 412.26M, 3.18M, 00:52:36
                                test, test, 2019-06-07 22:00:04, 2019-06-07 23:46:25, DISK, COMPLETED, DB INCR, 921.05G, 916.91G, 147.81M, 147.14M, 01:46:21
                                test, test, 2019-06-08 22:00:04, 2019-06-08 22:44:01, DISK, COMPLETED, DB INCR, 1.24T, 229.62M, 493.40M, 89.17K, 00:43:57
                                test, test, 2019-06-09 22:00:04, 2019-06-09 22:47:30, DISK, COMPLETED, DB INCR, 1.24T, 257.46M, 457.18M, 92.63K, 00:47:26
                                test, test, 2019-06-10 22:00:04, 2019-06-10 22:46:40, DISK, COMPLETED, DB INCR, 1.24T, 202.77M, 465.35M, 74.26K, 00:46:36
                                test, test, 2019-06-11 22:00:04, 2019-06-11 22:50:50, DISK, COMPLETED, DB INCR, 1.24T, 2.78G, 427.17M, 956.58K, 00:50:46
                                test, test, 2019-06-12 22:00:04, 2019-06-12 22:48:10, DISK, COMPLETED, DB INCR, 1.24T, 2.74G, 450.84M, 996.11K, 00:48:06
                                test, test, 2019-06-13 22:00:04, 2019-06-13 22:45:10, DISK, COMPLETED, DB INCR, 1.24T, 244.26M, 480.82M, 92.43K, 00:45:06
                                test, test, 2019-06-14 22:00:03, 2019-06-14 23:42:10, DISK, COMPLETED, DB INCR, 921.19G, 916.98G, 153.96M, 153.25M, 01:42:07
                                test, test, 2019-06-15 22:00:03, 2019-06-15 22:45:10, DISK, COMPLETED, DB INCR, 1.24T, 240.09M, 480.65M, 90.82K, 00:45:07
                                test, test, 2019-06-16 22:00:04, 2019-06-16 22:45:40, DISK, COMPLETED, DB INCR, 1.24T, 243.21M, 475.56M, 91.03K, 00:45:36
                                test, test, 2019-06-17 22:00:04, 2019-06-17 22:46:30, DISK, COMPLETED, DB INCR, 1.24T, 206.82M, 467.02M, 76.02K, 00:46:26
                                test, test, 2019-06-18 22:00:04, 2019-06-18 22:48:11, DISK, COMPLETED, DB INCR, 1.24T, 214.46M, 450.68M, 76.07K, 00:48:07
                                test, test, 2019-06-19 22:00:04, 2019-06-19 22:47:40, DISK, COMPLETED, DB INCR, 1.24T, 219.56M, 455.57M, 78.72K, 00:47:36
                                
                                
                                PL/SQL procedure successfully completed.