Home » SQL & PL/SQL » SQL & PL/SQL » Star printing in PL/SQL Program Problem (11g)
Star printing in PL/SQL Program Problem [message #579895] Sat, 16 March 2013 21:38 Go to next message
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
Member
I want the output to be like
1
1 2
1 2 3
1 2 3 4
1 2 3
1 2
1

Please correct my mistake in this program:-
declare 
v varchar2(50);
begin
for i in 1..10
loop
v:=v||' '||i;
display(v);
end loop;
v:=null;
for i in reverse 10..1
loop
v:=v||' '||i;
display(v);
end loop;
end;


Regards,
Ashish
Re: Star printing in PL/SQL Program Problem [message #579897 is a reply to message #579895] Sun, 17 March 2013 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare 
  2  v varchar2(50);
  3  begin
  4  for i in 1..10
  5  loop
  6  v:=v||' '||i;
  7  display(v);
  8  end loop;
  9  v:=null;
 10  for i in reverse 10..1
 11  loop
 12  v:=v||' '||i;
 13  display(v);
 14  end loop;
 15  end;
 16  /
display(v);
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00201: identifier 'DISPLAY' must be declared
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored
ORA-06550: line 13, column 1:
PLS-00201: identifier 'DISPLAY' must be declared
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored

What is DISPLAY?
Search in the documentation for DBMS_OUTPUT package.

Also please INDENT the code.
If you don't know, learn using SQL Formatter.

Regards
Michel
Re: Star printing in PL/SQL Program Problem [message #579899 is a reply to message #579897] Sun, 17 March 2013 01:48 Go to previous messageGo to next message
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
Member
Sorry. I forgot to mention display is a procedure.
You can replace display with dbms_output.put_line(<parameter>);
Re: Star printing in PL/SQL Program Problem [message #579902 is a reply to message #579899] Sun, 17 March 2013 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it and post the new code FORMATTED.

Regards
Michel
Re: Star printing in PL/SQL Program Problem [message #579907 is a reply to message #579895] Sun, 17 March 2013 06:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
You misunderstand IN REVERSE. Regardless of IN or IN REVERSE lower_bound is first and upper bound is second. And in your second loop:

for i in reverse 10..1


lower_bound > upper bound, therefore no loop iterations are executed. If you change bounds around:

SQL> declare
  2  v varchar2(50);
  3  begin
  4  for i in 1..10
  5  loop
  6  v:=v||' '||i;
  7  dbms_output.put_line(v);
  8  end loop;
  9  v:=null;
 10  for i in reverse 1..10
 11  loop
 12  v:=v||' '||i;
 13  dbms_output.put_line(v);
 14  end loop;
 15  end;
 16  /
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9 10
10
10 9
10 9 8
10 9 8 7
10 9 8 7 6
10 9 8 7 6 5
10 9 8 7 6 5 4
10 9 8 7 6 5 4 3
10 9 8 7 6 5 4 3 2
10 9 8 7 6 5 4 3 2 1

PL/SQL procedure successfully completed.

SQL>


What you want is:

declare
    v varchar2(50);
begin
    for i in 1..10 loop
      v := v || ' ' || i;
      dbms_output.put_line(v);
    end loop;
    --v:=null;
    for i in reverse 0..9 loop
      v := substr(v,1,i * 2);
      dbms_output.put_line(v);
    end loop;
end;
/
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1

PL/SQL procedure successfully completed.

SQL>


SY.
Re: Star printing in PL/SQL Program Problem [message #579909 is a reply to message #579907] Sun, 17 March 2013 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hopefully you are there to post beginner's code, maybe you can open a site where everyone that wants to cheat at his exam can post the questions and get the answers.

Regards
Michel
Re: Star printing in PL/SQL Program Problem [message #657968 is a reply to message #579895] Tue, 29 November 2016 09:27 Go to previous messageGo to next message
mansoor.cfc
Messages: 2
Registered: November 2016
Junior Member
with t1 as (select LISTAGG(level, '') WITHIN GROUP (ORDER BY level) as num from dual connect by level <=9),
t2 as (select rpad(num,level,' ') from t1 connect by level <=9),
t3 as (select rpad(num,level,' ') from t1 connect by level <9 order by 1 desc)
select * from t2
union all
select * from t3

the above will fail though for N>9
Re: Star printing in PL/SQL Program Problem [message #657969 is a reply to message #657968] Tue, 29 November 2016 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Star printing in PL/SQL Program Problem [message #657970 is a reply to message #657968] Tue, 29 November 2016 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, this question which is 3 years old, was about PL/SQL not SQL, your "solution" does not produce the wanted output and is far too much complex for the question and it gives me an idea.

@all, who will find the SQL who displays the wanted result for numbers 1 to 9 with the less number of characters? Smile
I start with this one (166 characters):
SQL> select substr('1 2 3 4 5 6 7 8 9',1,2*level) from dual connect by level <= 9
  2  union all
  3  select substr('1 2 3 4 5 6 7 8 9',1,18-2*level) from dual connect by level <= 8
  4  /
SUBSTR('123456789
-----------------
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1
Re: Star printing in PL/SQL Program Problem [message #657971 is a reply to message #657970] Tue, 29 November 2016 10:47 Go to previous messageGo to next message
mansoor.cfc
Messages: 2
Registered: November 2016
Junior Member
Yes, I knew it's SQL and I thought it's always good to find a solution in SQL rather than pl SQL.

It worked on my machine and area. Don't knowhy it didn't produce the required output for you .

Thanks for your Improved solution. Yes, it works much better and is simple. My idea was to try and generalize the solution, but I couldn't do it for N>9.
Re: Star printing in PL/SQL Program Problem [message #657972 is a reply to message #657971] Tue, 29 November 2016 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Don't knowhy it didn't produce the required output for you .
Please read How to use [code] tags and make your code easier to read.

Re: Star printing in PL/SQL Program Problem [message #657973 is a reply to message #657970] Tue, 29 November 2016 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Found one with 114 characters: Smile
SQL> select substr('1 2 3 4 5 6 7 8 9',1,decode(sign(level-10),-1,2*level,36-2*level))
  2  from dual connect by level <= 17
  3  /
SUBSTR('123456789
-----------------
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1

[Updated on: Tue, 29 November 2016 11:09]

Report message to a moderator

Re: Star printing in PL/SQL Program Problem [message #657981 is a reply to message #657973] Tue, 29 November 2016 14:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Tue, 29 November 2016 12:08

Found one with 114 characters: Smile
SQL> select substr('1 2 3 4 5 6 7 8 9',1,least(2*level,36-2*level))
  2  from dual connect by level<18
  3  /

SUBSTR('123456789
-----------------
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1

17 rows selected.

SQL> select length(q'[select substr('1 2 3 4 5 6 7 8 9',1,least(2*level,36-2*level))
  2  from dual connect by level<18]') len from dual
  3  /

       LEN
----------
        92

SQL> 

SY.

[Updated on: Tue, 29 November 2016 14:02]

Report message to a moderator

Re: Star printing in PL/SQL Program Problem [message #657984 is a reply to message #657981] Tue, 29 November 2016 14:06 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Previous Topic: How to pass date parameters in dynamic sql?
Next Topic: how to search for two characters in any sequence, with or without alphanumeric characters in between
Goto Forum:
  


Current Time: Sun Dec 04 17:02:51 CST 2016

Total time taken to generate the page: 0.24900 seconds