SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL * PLUS FORMAT

RengudiDec 5 2022

Hi
Good time for all you.
I want to get formatted output as like

set colsep '|'
SET RECSEPCHAR "-"
SELECT 'B' AS U_TYPE, 2468492 AS COUNTU_TYPE FROM DUAL
         UNION ALL
         SELECT 'The long you type -abcdefg hijl mno pqrst uvwx yz ABCDEFGH IKJLMON PRSTUVWZYX', 274503 FROM DUAL
         UNION ALL
         SELECT 'XFC', 162560 FROM DUAL
         UNION ALL
         SELECT 'BBBB', 40019 FROM DUAL
         UNION ALL
         SELECT 'GSLBVIP', 1271 FROM DUAL
         UNION ALL
         SELECT 'BVIVIP', 46 FROM DUAL
         UNION ALL
         SELECT 'SLBVIP', 4 FROM DUAL'
         
         
+--------------------------------------------------------------------------------+-------------+
| U_TYPE                                                                         | COUNTU_TYPE |
+================================================================================+=============+
| B                                                                              | 2468492     |
+--------------------------------------------------------------------------------+-------------+
| The long you type abcdefg hijl mno pqrst   uvwx yz ABCDEFGH IKJLMON PRSTUVWZYX | 274503      |
+--------------------------------------------------------------------------------+-------------+
| XFC                                                                            | 162560      |
+--------------------------------------------------------------------------------+-------------+
| BBBB                                                                           | 40019       |
+--------------------------------------------------------------------------------+-------------+
| GSLBVIP                                                                        | 1271        |
+--------------------------------------------------------------------------------+-------------+
| BVIVIP                                                                         | 46          |
+--------------------------------------------------------------------------------+-------------+
| SLBVIP                                                                         | 4           |
+--------------------------------------------------------------------------------+-------------+

And if the text is too long like second row the above example it has to be wrapped out.. How?

Thanks

This post has been answered by Solomon Yakobson on Dec 5 2022

Jump to Answer

Ora

Try this:

SELECT d.DEPARTMENT_ID,
d.DEPARTMENT_NAME,
(select count(*) from oehr_employees where department_id = d.department_id) as "Number of Employees",
substr(e.first_name,1,1)||'.'||e.last_name as "Manager Name",
c.COUNTRY_NAME as "Location"
FROM 
OEHR_DEPARTMENTS d,
OEHR_EMPLOYEES e,
OEHR_LOCATIONS l,
OEHR_COUNTRIES c
WHERE d.LOCATION_ID=l.LOCATION_ID
AND l.COUNTRY_ID=c.COUNTRY_ID
AND d.DEPARTMENT_ID=e.department_id
AND d.manager_id=e.employee_id
AND instr((d.department_name),(nvl(:P2_REPORT_SEARCH,d.department_name)))>0 ;

Edited by: Ora on Apr 25, 2011 5:38 AM

819888

You are not using instr function with correct number of arguments.

Regards

kendenny

Answer

Must be because you have a line break in the middle of p2_report_search.
Works for me without the line break.

SQL> variable p2_report_search  varchar2(10) := 'D';
SQL> 
SQL> WITH oehr_departments AS
  2    (SELECT 1 department_id, 'D1' department_name
  3           ,1 location_id, 1 manager_id FROM dual)
  4      ,oehr_locations AS
  5    (SELECT 1 location_id, 1 country_id FROM dual)
  6      ,oehr_countries AS
  7    (SELECT 1 country_id, 'US' country_name FROM dual)
  8      ,oehr_employees AS
  9    (SELECT 1 employee_id, 1 department_id, 'Ken' first_name, 'Denny' last_name FROM dual
 10     UNION
 11     SELECT 2 employee_id, 1 department_id, 'John' first_name, 'Smith' last_name FROM dual)
 12  SELECT d.DEPARTMENT_ID,
 13         d.DEPARTMENT_NAME,
 14         (select count(*)from oehr_employees where department_id = d.department_id)
 15            "Number of Employees",
 16          substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
 17          c.COUNTRY_NAME "Location"
 18    FROM OEHR_DEPARTMENTS d,
 19         OEHR_EMPLOYEES e,
 20         OEHR_LOCATIONS l,
 21         OEHR_COUNTRIES c
 22   WHERE d.LOCATION_ID=l.LOCATION_ID
 23     AND l.COUNTRY_ID=c.COUNTRY_ID
 24     AND d.DEPARTMENT_ID=e.department_id
 25     AND d.manager_id=e.employee_id
 26     AND instr((d.department_name),(nvl(:P2_REPORT_SEARCH,d.department_name)))>0 ;
 
DEPARTMENT_ID DEPARTMENT_NAME Number of Employees Manager Name Location
------------- --------------- ------------------- ------------ --------
            1 D1                                2 K.Denny      US

Marked as Answer by 857480 · Sep 27 2020

857480

Thanks ora... made it..!! :)

857480

Thanks Ken... fixed it..!! have a great day too...!!

857480

well what is the difference between the above and yours ora.....? could you pls explain.

thanks

1 - 6

Added on Dec 5 2022

#sql, #sqlplus

6 comments

140 views

SQL & PL/SQL

SQL * PLUS FORMAT

Comments

Post Details