AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
Question QUESTION: can i print my output in pl/sql in a single line
please write statement for this
ANSWER: Good day,
You can print your PL/SQL output in a single line using concatenation.
Here is an example:
SQL> set serveroutput on;
SQL>
SQL> declare
2 v1 varchar2(16);
3 v2 varchar2(16);
4 v3 varchar2(16);
5
6 begin
7 -- Set variable
8 v1 := '2008-06-23';
9 v2 := '8 o''clock';
10 v3 := 'now';
11
12 -- Concatenate
13 dbms_output.put_line ('It is ' || v1 || ' ' || v2 || ' ' || v3 || '.');
14
15 end;
16 /
It is 2008-06-23 8 o'clock now.
PL/SQL procedure successfully completed.
SQL>
Hope it helps. If you have any questions, please don't hestitate to ask.
Cheers
Elliot
---------- FOLLOW-UP ----------
QUESTION: I want to print this format on screen.
*
***
*****
*******
but dbms-output.put_line() print this format in
*
*
*
*
*
*
*
*
*
*
*
*
*
format . please help me in this program thanks
Answer Hi
In order for you to write a program like this, the program will need to:
- know how many rows it needs to print
- calculate how many stars you need to print for the last line
- determine where to print the first row with a single "*"
- once it is determined, then you can write the algorithm for that.
Here is a just a sample program that I wipe up. It is an anonymous PL/SQL and it is hardcoded to 5 rows. I will recommend you to write a named PL/SQL so that you can pass a parameter to tell the program how many rows you want to print
-- ********************
-- Program Starts
-- *******************
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
number_of_rows integer:=5;
print_length integer;
min_to_print integer:=1;
max_to_print integer;
lpad_length integer;
character_to_print varchar2(1):='*';
print_character varchar2(10000);
line_print varchar2(10000);
BEGIN
-- Max stars to print
max_to_print := number_of_rows * 2 - 1;
FOR i in min_to_print..number_of_rows LOOP
-- Reset the line_print
line_print := '';
print_character := '';
-- Calculate how many * to print
max_to_print := i * 2 - 1;
-- Construct the number of '*'
FOR k in 1..max_to_print LOOP
print_character := print_character || character_to_print;
END LOOP;
-- right pad the length with the *
FOR j IN 1..number_of_rows-i LOOP
line_print := line_print || CHR(32);
END LOOP;
DBMS_OUTPUT.PUT_LINE(line_print || print_character);
END LOOP;
END;
/
-- ********************
-- Program Ends
-- ********************
Instead of copying this code, try to understand why it is written in this way. It will go a lot further
