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 Jun 17, 2019 3:00 AM

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 Jun 17, 2019 6:09 AM (in response to Quanwen Zhao)
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,

SETECHOON
SETHEADINGON
SETFEEDBACKON
SETVERIFYON
SETLINESIZE250
SETPAGESIZE250
SELECT
(SELECThost_nameFROMv$instance)
||','||
(SELECTvalueFROMv$parameterWHEREname='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
FROMv$rman_backup_job_details
WHEREoutput_device_type='DISK'
--ANDTo_Char(start_time,'dd-mm-yy')=To_Char(sysdate-1,'dd-mm-yy')
)
FROMdual
/

ERRORatline6:
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.
Like Show 2 Likes(2)

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

Cookiemonster76 Jun 17, 2019 8:32 AM (in response to Quanwen Zhao)

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
Like Show 1 Likes(1)

Actions
3. Re: ORA-01427: single-row subquery returns more than one row

Manik Jun 17, 2019 12:29 PM (in response to Quanwen Zhao)

what is the output of this :

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

Cheers,
Manik.
Like Show 0 Likes(0)

Actions

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

Quanwen Zhao Jun 18, 2019 8:35 AM (in response to Cookiemonster76)

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 Jun 18, 2019 10:49 AM (in response to Quanwen Zhao)

It makes no obvious sense to me to write that as PL/SQL.
What did you have in mind?
Like Show 1 Likes(1)

Actions
6. Re: ORA-01427: single-row subquery returns more than one row

Sven W. Jun 18, 2019 2:52 PM (in response to Quanwen Zhao)

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
Like Show 1 Likes(1)

Actions

7. Re: ORA-01427: single-row subquery returns more than one row

Quanwen Zhao Jun 21, 2019 9:05 AM (in response to 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 Jun 19, 2019 8:40 AM (in response to Sven W.)

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
Like Show 0 Likes(0)

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

Cookiemonster76 Jun 19, 2019 12:30 PM (in response to Quanwen Zhao)

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
Like Show 1 Likes(1)

Actions
10. Re: ORA-01427: single-row subquery returns more than one row

Cookiemonster76 Jun 19, 2019 8:57 AM (in response to 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
Like Show 1 Likes(1)

Actions

11. Re: ORA-01427: single-row subquery returns more than one row

Quanwen Zhao Jun 20, 2019 3:13 AM (in response to Cookiemonster76)

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 Jun 20, 2019 6:09 AM (in response to 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.

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.