Home » SQL & PL/SQL » SQL & PL/SQL » Please help
Please help [message #236404] Wed, 09 May 2007 22:09 Go to next message
sasha400
Messages: 17
Registered: May 2007
Junior Member
what am i doing wrong?
declare
   cursor inventorycursor is
     select order_Line.o_id,order_Line.oL_quantity,inventory.inv_price
      from inventory
       join order_Line on order_Line.inv_id =inventory.inv_id; 

   cursor order_Linecursor is
     select order_Line.o_id, order_Line.oL_quantity, inventory.inv_price
       from order_Line
         join inventory on inventory.item_id = order_Line.o_id;

      o_id         Number(8);
      item_id      Number(8);
      inv_price    Numeric(10,2);
      oL_quantity  Number(4);
      totamt       Numeric(10,2);
      disamt1      Numeric(10,2);
      disamt2      Numeric(10,2);
    
 begin
        totamt:=(inv_price * oL_quantity);
        IF ( totamt>100)
               THEN
                RETURN(totamt*.10);

                 IF ( totamt>200)
                  THEN
                   RETURN(totamt*.20);
                     ELSE 
                      RETURN (0);
                  END IF;
                 

      open inventoryCursor;
        fetch inventoryCursor into o_id ,oL_quantity,inv_price;
         dbms_output.put_line('Order ID'||' '||' Original.Amt'||' '||' Dis Amt');
          dbms_output.put_line('----------------------------');
             
           while inventoryCursor%FOUND loop
            fetch inventoryCursor into o_id,oL_quantity,inv_price;  
        dbms_output.put_lineo_id||''||inv_price*oL_quantity||''||disamt );  
            end loop;    
          close inventoryCursor;
              end;
     /
ORA-06550: line 44, column 18:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

   if2.    cursor inventorycursor is
3.      select order_Line.o_id,order_Line.oL_quantity,inventory.inv_price
4.       from inventory
5.        join order_Line on order_Line.inv_id =inventory.inv_id; 
6. 
 
Re: Please help [message #236405 is a reply to message #236404] Wed, 09 May 2007 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Below the
begin
statement appear to be 2 IF statements, but I see only 1 END IF.
Re: Please help [message #236406 is a reply to message #236405] Wed, 09 May 2007 22:43 Go to previous messageGo to next message
sasha400
Messages: 17
Registered: May 2007
Junior Member
ORA-06550: line 24, column 17:
I got these errors

PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 24, column 17:
PL/SQL: Statement ignored
ORA-06550: line 28, column 20:
PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 28, column 20:
PL/SQL: Statement ignored
ORA-06550: line 30, column 23:
PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 30, column 23:
PL/SQL: Statement ignored
ORA-06550: line 42, column 1. declare
2.    cursor inventorycursor is
3.      select order_Line.o_id,order_Line.oL_quantity,inventory.inv_price
4.       from inventory
 
Re: Please help [message #236407 is a reply to message #236404] Wed, 09 May 2007 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would help everyone (but you) if we knew the line numbers.

Do you know the difference between a procedure & a function?

If you are having problems getting syntax correct, maybe you should drop this class & select one that you master on you own.
Re: Please help [message #236410 is a reply to message #236407] Wed, 09 May 2007 23:23 Go to previous messageGo to next message
sasha400
Messages: 17
Registered: May 2007
Junior Member
I just started 2 weeks ago and pl/sql is just a very small part of the course.Function

declare
   cursor inventorycursor is
     select order_Line.o_id,order_Line.oL_quantity,inventory.inv_price
      from inventory
       join order_Line on order_Line.inv_id =inventory.inv_id; 

   cursor order_Linecursor is
     select order_Line.o_id, order_Line.oL_quantity, inventory.inv_price
       from order_Line
         join inventory on inventory.item_id = order_Line.o_id;

      o_id         Number(8);
      item_id      Number(8);
      inv_price    Numeric(10,2);
      oL_quantity  Number(4);
      totamt       Numeric(10,2);
      disamt1      Numeric(10,2);
      disamt2      Numeric(10,2);

        FUNCTION cal_dis(totamt Numeric)Return Numeric IS
 begin
        totamt:=(inv_price * oL_quantity);
        IF ( totamt>100)
               THEN
                RETURN(totamt*.10);

                 IF ( totamt>200)
                  THEN
                   RETURN(totamt*.20);
                     ELSE 
                      RETURN (0);
                  END IF;
                  END cal_dis ;
                 

      open inventoryCursor;
        fetch inventoryCursor into o_id ,oL_quantity,inv_price;
         dbms_output.put_line('Order ID'||' '||' Original.Amt'||' '||' Dis Amt');
          dbms_output.put_line('------------------------------------------------');
             
           while inventoryCursor%FOUND loop
            fetch inventoryCursor into o_id,oL_quantity,inv_price;  
              dbms_output.put_line( o_id||'   '||inv_price*oL_quantity||'  '||disamt );  
            end loop;    
          close inventoryCursor;
              end;
     /
Re: Please help [message #236411 is a reply to message #236410] Wed, 09 May 2007 23:25 Go to previous messageGo to next message
sasha400
Messages: 17
Registered: May 2007
Junior Member
I got these errors
ORA-06550: line 33, column 23:
PLS-00103: Encountered the symbol "CAL_DIS" when expecting one of the following:

   if
ORA-06550: line 46, column 18:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map1. declare
2.    cursor inventorycursor is
3.      select order_Line.o_id,order_Line.oL_quantity,inventory.inv_price
4.       from inventory
5.        join order_Line on order_Line.inv_id =inventory.inv_id; 
 
Re: Please help [message #236420 is a reply to message #236411] Wed, 09 May 2007 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the execution WITH LINE NUMBERS.

Regards
Michel
Re: Please help [message #236424 is a reply to message #236404] Wed, 09 May 2007 23:51 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
You should just re-read anacedent's first post.
Re: Please help [message #236427 is a reply to message #236404] Wed, 09 May 2007 23:53 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Some folks subscribe to the Ready, Fire, Aim school of software development.
Previous Topic: Insert into
Next Topic: Latest Transaction From Diffrent Table
Goto Forum:
  


Current Time: Fri Dec 06 02:11:47 CST 2024