Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    5

    Unanswered: simple pl/sql program in sqlplus

    Hi, Ijust joined the forum i hope i can find help here whith my learning, I just started to learn PL/SQL and I have a problem in my first simple program when i try to use IF THEN ELSE ...

    here is my too tables :

    Code:
    CREATE TABLE  "E_SERVICE" 
       (	"NO" NUMBER(7,0) NOT NULL ENABLE, 
    	"NOM" VARCHAR2(25) NOT NULL ENABLE, 
    	"CONTINENT_NO" NUMBER(7,0), 
    	 PRIMARY KEY ("NO") ENABLE
       )
    CREATE TABLE  "E_EMP" 
       (	"NO" NUMBER(7,0), 
    	"NOM" VARCHAR2(25), 
    	"PRENOM" VARCHAR2(25), 
    	"UTILISATEUR" VARCHAR2(8), 
    	"DT_ENTREE" DATE, 
    	"COMMENTAIRES" VARCHAR2(255), 
    	"SUPERIEUR_NO" NUMBER(7,0), 
    	"TITRE" VARCHAR2(25), 
    	"SERVICE_NO" NUMBER(7,0), 
    	"SALAIRE" NUMBER(11,2), 
    	"PCT_COMMISSION" NUMBER(4,2), 
    	 CONSTRAINT "E_EMP_PK" PRIMARY KEY ("NO") ENABLE
       )
    Here is my program :

    Code:
    ACCEPT 	nom	PROMPT	'Nom Employe: ' 
    DECLARE
    	salaire_emp	e_emp.salaire%TYPE ;
    	service_emp	e_service.nom%TYPE ;
    	mes		e_resultat.lb_resultat%TYPE ;
    BEGIN
    	SELECT	
    		e_emp.salaire, e_service.nom
    	INTO
    		salaire_emp, service_emp
    	FROM
    		e_emp, e_service 
    	WHERE	
    		e_emp.service_no = e_service.no
    	AND
    		e_emp.nom = '&nom'
    	;
    	IF
    		service_emp = 'VENTE'
    	THEN
    		salaire_emp := salaire_emp + 500 ;
    		mes := '&nom' || ': salaire augmenté de 500' ;
    	ELSEIF
    		service_emp = 'OPERATIONS'
    	THEN	
    		salaire_emp := salaire_emp + 300 ;
    		mes := '&nom' || ': salaire augmenté de 300' ;
    	ELSE
    		mes := '&nom' || ' appartient au service: ' || service_emp ;
    	
    	UPDATE 
    		e_emp 
    	SET 	
    		e_emp.salaire = nvl(salaire_emp,0)
    	WHERE	
    		e_emp.nom = '&nom'
    	;
    	INSERT INTO
    		e_resultat
    	VALUES
    		(1, mes, salaire_emp)
    	;
    	COMMIT ;
    END ;
    I have this Errors :
    Code:
    ERROR at line 19:
    ORA-06550: line 19, column 3:
    PLS-00103: Encountered the symbol "SERVICE_EMP" when expecting one of the
    following:
    := . ( @ % ;
    ORA-06550: line 39, column 5:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    if
    I hope somebody can help !

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    perhaps it would work better after providing
    END IF;
    as needed & appropriate
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2010
    Posts
    5
    Yes you're right, I made some changes in the code it's somehow better .. but I still have errors,
    and it asks me for 'NOM' a lot of times ..
    Code:
    SQL> DECLARE
    	salaire_emp	e_emp.salaire%TYPE ;
    	service_emp	e_service.nom%TYPE ;
    	mes		e_resultat.lb_resultat%TYPE ;
    
    BEGIN
    	ACCEPT nom_emp PROMPT 'NOM :' ; 
    
    	SELECT	
    		e_emp.salaire, e_service.nom
    	INTO
    		salaire_emp, service_emp
    	FROM
    		e_emp, e_service 
    	WHERE	
    		e_emp.service_no = e_service.no
    	AND
    		e_emp.nom = '&nom_emp'
    	;
    	IF
    		service_emp = 'VENTE'
    	THEN
    		salaire_emp := salaire_emp + 500 ;
    		mes := '&nom_emp' || ': salaire augmenté de 500' ;
    	ELSIF
    		service_emp = 'OPERATIONS'
    	THEN	
    		salaire_emp := salaire_emp + 300 ;
    		mes := '&nom_emp' || ': salaire augmenté de 300' ;
    	ELSE
    		mes := '&nom_emp' || ' appartient au service: ' || service_emp ;
    	END IF ;
    
    	UPDATE 
    		e_emp 
    	SET 	
    		e_emp.salaire = nvl(salaire_emp,0)
    	WHERE	
    		e_emp.nom = '&nom_emp'
    	;
    	INSERT INTO
    		e_resultat
    	VALUES
    		(1, mes, salaire_emp)
    	;
    	COMMIT ;
    END ;
    /  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48  
    Enter value for nom_emp: MADURO
    old  18: 		e_emp.nom = '&nom_emp'
    new  18: 		e_emp.nom = 'MADURO'
    Enter value for nom_emp: MADURO
    old  24: 		mes := '&nom_emp' || ': salaire augmenté de 500' ;
    new  24: 		mes := 'MADURO' || ': salaire augmenté de 500' ;
    Enter value for nom_emp: MADURO
    old  29: 		mes := '&nom_emp' || ': salaire augmenté de 300' ;
    new  29: 		mes := 'MADURO' || ': salaire augmenté de 300' ;
    Enter value for nom_emp: MADURO
    old  31: 		mes := '&nom_emp' || ' appartient au service: ' || service_emp ;
    new  31: 		mes := 'MADURO' || ' appartient au service: ' || service_emp ;
    Enter value for nom_emp: MADURO
    old  39: 		e_emp.nom = '&nom_emp'
    new  39: 		e_emp.nom = 'MADURO'
    	ACCEPT nom_emp PROMPT 'NOM :' ;
    	       *
    ERROR at line 7:
    ORA-06550: line 7, column 9:
    PLS-00103: Encountered the symbol "NOM_EMP" when expecting one of the
    following:
    := . ( @ % ;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ACCEPT is a sqlplus command & does NOT belong inside a PL/SQL procedure.
    ACCEPT needs to be placed before DECLARE line.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2010
    Posts
    5
    Umm thanks for your help, it works now, i write the code in a file .sql and run it with '@' instead of copying the code directly to sqlplus command line ..
    Code:
    ACCEPT nom_emp PROMPT 'NOM: '
    DECLARE
    	salaire_emp	e_emp.salaire%TYPE ;
    	service_emp	e_service.nom%TYPE ;
    	mes		e_resultat.lb_resultat%TYPE ;
    
    BEGIN
    
    	SELECT	
    		e_emp.salaire, e_service.nom
    	INTO
    		salaire_emp, service_emp
    	FROM
    		e_emp, e_service 
    	WHERE	
    		e_emp.service_no = e_service.no
    	AND
    		e_emp.nom = '&nom_emp'
    	;
    	IF
    		service_emp = 'VENTES'
    	THEN
    		salaire_emp := salaire_emp + 500 ;
    		mes := '&nom_emp' || ': salaire augmenté de 500' ;
    	ELSIF
    		service_emp = 'OPERATIONS'
    	THEN	
    		salaire_emp := salaire_emp + 300 ;
    		mes := '&nom_emp' || ': salaire augmenté de 300' ;
    	ELSE
    		mes := '&nom_emp' || ' appartient au service: ' || service_emp ;
    	END IF ;
    
    	UPDATE 
    		e_emp 
    	SET 	
    		e_emp.salaire = nvl(salaire_emp,0)
    	WHERE	
    		e_emp.nom = '&nom_emp'
    	;
    	INSERT INTO
    		e_resultat
    	VALUES
    		(1, mes, salaire_emp)
    	;
    	COMMIT ;
    END ;
    /
    i think the reason is that you said :
    ACCEPT is a sqlplus command & does NOT belong inside a PL/SQL procedure.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A suggestion, if I may: perhaps you could convert that anonymous PL/SQL code into a procedure. It would have one parameter: a NOM_EMP, which you now acquire via ACCEPT SQL*Plus command. Once the procedure is written and successfully compiled, you'd simply call it by specifying its name and passing any "nom_emp" value as a parameter.

    Something like this:
    Code:
    SQL> create or replace procedure prc_insert_sal (par_salary in number) is
      2  begin
      3    insert into test
      4      (sal)
      5    values
      6      (par_salary);
      7  end;
      8  /
    
    Procedure created.
    
    SQL> exec prc_insert_sal (100);
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test;
    
           SAL
    ----------
           100

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •