Programming Forums > Application Development > Other Programming Languages

java_roshan · Oct 10th, 2005, 11:27 AM

I was trying a PL/SQL code this morning (IST) at my computer LAB in college on oracle 9i.

I tried the following code that will ask for 10 numbers (two-three digit) one by one and calculate their sum and print it.

(Toggle Plain Text)

declare 
a number(3);
sumof number(3);
ctr number(3);
begin
         ctr:= 1;
         sumof:=0;
         while ctr < 11 loop
                 a:=&a;
                 sumof:=sumof + a; 
                 dbms_output.put_line('Result of '||ctr||' numbers is '||sumof);
                 ctr:=ctr+1 
                 end loop;
end;

THe problem that I am facing is that input from this program is only taken once and the next results are calculated considering the old value of a.
i.e.
Say I put
a = 1
then,
the sum is 10 (considering all values of a entered as 1)

What's wrong?

swmk · Oct 10th, 2005, 11:48 AM

Oh, I have faced such problem. And I thought is "a:=&a;" is not overwriting the old value.

java_roshan · Oct 10th, 2005, 9:30 PM

So does anyone know the solution?

jim mcnamara · Oct 12th, 2005, 8:59 AM

PL/SQL was never meant to work interactively. It was originally designed to use tables for all I/O. The DBMS_OUTPUT package was created a lot later.

You can use UTL_FILE to read a file as input.

Try DESC UTL_FILE for more information.

java_roshan · Oct 12th, 2005, 9:28 AM

Quote:

Originally Posted by jim mcnamara

PL/SQL was never meant to work interactively. It was originally designed to use tables for all I/O. The DBMS_OUTPUT package was created a lot later.

You can use UTL_FILE to read a file as input.

Try DESC UTL_FILE for more information.

OH..ok...

However, we are told to write such a program, and I fail to understand why oracle is not ready to accept ten values for a variable!

jim mcnamara · Oct 12th, 2005, 2:25 PM

SQLPLUS is supposed to be the user interface. PL/SQL was developed as an internal programming language.

There isn't anything to understand - it's a design decision.

There is always a workaround -

(Toggle Plain Text)

SET SERVEROUT ON SIZE 1000000
DECLARE
    TYPE TBL IS TABLE OF NUMBER(10);
    V_myarray TBL:=TBL(&1);
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
	FOR I IN 1..10
	LOOP
	   DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_myarray(I),'999') );
	END LOOP;
END;
/

If you enter 1,2,3,4,5,6,7,8,9,10 when it asks to replace the &1 you'll get what you wanted. But it isn't Visual Basic.

Oct 10th, 2005, 11:27 AM	#1
java_roshan Professional Programmer Join Date: Mar 2005 Location: Student of University of Mumbai, Maharashtra State, India Posts: 344 Rep Power: 4	PL/SQL code question I was trying a PL/SQL code this morning (IST) at my computer LAB in college on oracle 9i. I tried the following code that will ask for 10 numbers (two-three digit) one by one and calculate their sum and print it. (Toggle Plain Text) declare a number(3); sumof number(3); ctr number(3); begin ctr:= 1; sumof:=0; while ctr < 11 loop a:=&a; sumof:=sumof + a; dbms_output.put_line('Result of '\|\|ctr\|\|' numbers is '\|\|sumof); ctr:=ctr+1 end loop; end; declare a number(3); sumof number(3); ctr number(3); begin ctr:= 1; sumof:=0; while ctr < 11 loop a:=&a; sumof:=sumof + a; dbms_output.put_line('Result of '\|\|ctr\|\|' numbers is '\|\|sumof); ctr:=ctr+1 end loop; end; THe problem that I am facing is that input from this program is only taken once and the next results are calculated considering the old value of a. i.e. Say I put a = 1 then, the sum is 10 (considering all values of a entered as 1) What's wrong? __________________ Visit: http://www.somaiya.edu

Oct 10th, 2005, 9:30 PM	#3
java_roshan Professional Programmer Join Date: Mar 2005 Location: Student of University of Mumbai, Maharashtra State, India Posts: 344 Rep Power: 4	So does anyone know the solution? __________________ Visit: http://www.somaiya.edu

Oct 12th, 2005, 2:25 PM	#6
jim mcnamara Hobbyist Programmer Join Date: Jun 2005 Location: New Mexico Posts: 228 Rep Power: 4	SQLPLUS is supposed to be the user interface. PL/SQL was developed as an internal programming language. There isn't anything to understand - it's a design decision. There is always a workaround - (Toggle Plain Text) SET SERVEROUT ON SIZE 1000000 DECLARE TYPE TBL IS TABLE OF NUMBER(10); V_myarray TBL:=TBL(&1); BEGIN DBMS_OUTPUT.ENABLE(1000000); FOR I IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_myarray(I),'999') ); END LOOP; END; / SET SERVEROUT ON SIZE 1000000 DECLARE TYPE TBL IS TABLE OF NUMBER(10); V_myarray TBL:=TBL(&1); BEGIN DBMS_OUTPUT.ENABLE(1000000); FOR I IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_myarray(I),'999') ); END LOOP; END; / If you enter 1,2,3,4,5,6,7,8,9,10 when it asks to replace the &1 you'll get what you wanted. But it isn't Visual Basic.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Oct 10th, 2005, 11:48 AM	#2
swmk Newbie Join Date: Oct 2005 Posts: 19 Rep Power: 0	Oh, I have faced such problem. And I thought is "a:=&a;" is not overwriting the old value.

Oct 12th, 2005, 8:59 AM	#4
jim mcnamara Hobbyist Programmer Join Date: Jun 2005 Location: New Mexico Posts: 228 Rep Power: 4	PL/SQL was never meant to work interactively. It was originally designed to use tables for all I/O. The DBMS_OUTPUT package was created a lot later. You can use UTL_FILE to read a file as input. Try DESC UTL_FILE for more information.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Other Programming Languages

PL/SQL code question