Home » SQL & PL/SQL » SQL & PL/SQL » try to insert data in to table using procedure (sql developer)
try to insert data in to table using procedure [message #652153] Thu, 02 June 2016 17:34 Go to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
Hi

please suggest where to modify this one so that i can insert data in to table ?


CREATE OR replace PACKAGE p21 IS 
  PROCEDURE p21(p_do IN NUMBER, p_xy OUT VARCHAR); 
  FUNCTION F21(p_no IN NUMBER,p_nm  OUT VARCHAR) RETURN VARCHAR; 
  END;

CREATE OR replace PACKAGE BODY p21 IS 
  PROCEDURE p21 (p_do IN NUMBER) 
  IS 
  p_xy VARCHAR(20); 
   BEGIN 
  SELECT department_name IN TO p_xy FROM departments WHERE department_id=p_do; 
  INSERT INTO table1 VALUES(p_xy,p_do); 
  END; 
     
  FUNCTION F21    (p_no IN NUMBER) 
    RETURN VARCHAR 
    IS p_nm VARCHAR(20); 
    BEGIN 
    SELECT last_name INTO p_nm FROM employees WHERE employee_id=p_no; 
    RETURN p_nm; 
    END; 
     
    CREATE TABLE table1(pxy VARCHAR(30),p_do NUMBER); 
     
    show errors:
Re: try to insert data in to table using procedure [message #652154 is a reply to message #652153] Thu, 02 June 2016 18:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
why are you duplicating from DEPARTMENTS table?

How will we know when correct solution has been posted here?

What problem are you really trying to solve?
Re: try to insert data in to table using procedure [message #652155 is a reply to message #652154] Thu, 02 June 2016 19:11 Go to previous messageGo to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
I am trying to create a package which contains procedure and function .
I want to insert data from department in to new table using procedure
Re: try to insert data in to table using procedure [message #652163 is a reply to message #652155] Fri, 03 June 2016 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First make sure your package compile, and in first step be sure the prototypes of the functions and procedures in body match those in the specification.
Use SQL*Plus, it will tell you what are the errors:
SQL> CREATE OR replace PACKAGE p21 IS
  2    PROCEDURE p21(p_do IN NUMBER, p_xy OUT VARCHAR);
  3    FUNCTION F21(p_no IN NUMBER,p_nm  OUT VARCHAR) RETURN VARCHAR;
  4    END;
  5  /

Package created.

SQL> sho err
No errors.

SQL> CREATE OR replace PACKAGE BODY p21 IS
  2    PROCEDURE p21 (p_do IN NUMBER)
  3    IS
  4    p_xy VARCHAR(20);
  5     BEGIN
  6    SELECT department_name IN TO p_xy FROM departments WHERE department_id=p_do;
  7    INSERT INTO table1 VALUES(p_xy,p_do);
  8    END;
  9
 10    FUNCTION F21    (p_no IN NUMBER)
 11      RETURN VARCHAR
 12      IS p_nm VARCHAR(20);
 13      BEGIN
 14      SELECT last_name INTO p_nm FROM employees WHERE employee_id=p_no;
 15      RETURN p_nm;
 16      END;
 17  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY P21:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
6/26     PL/SQL: ORA-00923: FROM keyword not found where expected
16/8     PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
         begin end function pragma procedure
Re: try to insert data in to table using procedure [message #652180 is a reply to message #652163] Fri, 03 June 2016 14:50 Go to previous messageGo to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
it is showing as from keyword is not found , can you please suggest what i am missing here

CREATE OR replace PACKAGE BODY 
  p21 
IS 
PROCEDURE p21 (p_do IN NUMBER) 
IS 
  p_xy VARCHAR(20); 
BEGIN 
  SELECT department_name IN TO p_xy 
  FROM   departments 
  WHERE  department_id=p_do; 
   
  INSERT INTO table1 VALUES 
              ( 
                          p_xy, 
                          p_do 
              ); 

END; 
FUNCTION F21 (p_no IN NUMBER) 
  RETURN VARCHAR 
IS 
  p_nm VARCHAR(20); 
BEGIN 
  SELECT last_name 
  INTO   p_nm 
  FROM   employees 
  WHERE  employee_id=p_no; 
   
  RETURN p_nm; 
END; 
END; 
CREATE TABLE table1 
             ( 
                          pxy  VARCHAR(30), 
                          p_do NUMBER 
             );
Re: try to insert data in to table using procedure [message #652181 is a reply to message #652180] Fri, 03 June 2016 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> SELECT department_name IN TO p_xy
not as above but as below
SELECT department_name INTO p_xy
Re: try to insert data in to table using procedure [message #652184 is a reply to message #652180] Fri, 03 June 2016 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR replace PACKAGE BODY
  2    p21
  3  IS
  4  PROCEDURE p21 (p_do IN NUMBER)
  5  IS
  6    p_xy VARCHAR(20);
  7  BEGIN
  8    SELECT department_name IN TO p_xy
  9    FROM   departments
 10    WHERE  department_id=p_do;
 11
 12    INSERT INTO table1 VALUES
 13                (
 14                            p_xy,
 15                            p_do
 16                );
 17
 18  END;
 19  FUNCTION F21 (p_no IN NUMBER)
 20    RETURN VARCHAR
 21  IS
 22    p_nm VARCHAR(20);
 23  BEGIN
 24    SELECT last_name
 25    INTO   p_nm
 26    FROM   employees
 27    WHERE  employee_id=p_no;
 28
 29    RETURN p_nm;
 30  END;
 31  END;
 32  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY P21:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
8/3      PL/SQL: SQL Statement ignored
8/26     PL/SQL: ORA-00923: FROM keyword not found where expected


Quote:
  8    SELECT department_name IN TO p_xy
  9    FROM   departments
 10    WHERE  department_id=p_do;

IN TO != INTO.


Re: try to insert data in to table using procedure [message #652190 is a reply to message #652184] Fri, 03 June 2016 16:19 Go to previous messageGo to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
Please suggest here , what i am missing, i declared both in package and body p21 and f21. ?

CREATE OR replace PACKAGE xab 
IS 
  PROCEDURE p21( 
    p_do IN NUMBER, 
    p_xy OUT VARCHAR); 
  FUNCTION F21( 
    p_no IN NUMBER, 
    p_nm OUT VARCHAR) 
  RETURN VARCHAR; 
END; 

/ 
CREATE OR replace PACKAGE BODY xab 
IS 
  PROCEDURE P21 (p_do IN NUMBER) 
  IS 
    p_xy VARCHAR(20); 
  BEGIN 
      SELECT department_name 
      INTO   p_xy 
      FROM   departments 
      WHERE  department_id = p_do; 

      INSERT INTO table1 
      VALUES     (p_xy, 
                  p_do); 
  END; 
  FUNCTION F21 (p_no IN NUMBER) 
  RETURN VARCHAR 
  IS 
    p_nm VARCHAR(20); 
  BEGIN 
      SELECT last_name 
      INTO   p_nm 
      FROM   employees 
      WHERE  employee_id = p_no; 

      RETURN p_nm; 
  END; 
END; 




  PLS-00323: subprogram or cursor 'P21' is declared in a package
         specification and must be defined in the package body

3/16     PLS-00323: subprogram or cursor 'F21' is declared in a package
         specification and must be defined in the package body

[Updated on: Fri, 03 June 2016 16:59]

Report message to a moderator

Re: try to insert data in to table using procedure [message #652194 is a reply to message #652190] Fri, 03 June 2016 19:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The parameters in the package specification and package body must match. For example, in your package body, you are missing the p_xy parameter for the p21 procedure that you have in your package, as shown below.

PROCEDURE p21(
p_do IN NUMBER,
p_xy OUT VARCHAR);

PROCEDURE P21 (p_do IN NUMBER)

It is generally a good programming practice, to write and test one little piece of code at a time and test it, so that it makes it easier to tell where the problem is. Once that compiles, then add another little piece and test again.

Re: try to insert data in to table using procedure [message #652196 is a reply to message #652194] Fri, 03 June 2016 19:48 Go to previous messageGo to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
Hi barbara

what i should change in p21, i want to insert data in to table1
it is showing error in p21 ?

CREATE OR replace PACKAGE xab 
IS 
  PROCEDURE p21( 
    p_do IN NUMBER, 
    p_xy OUT VARCHAR); 
  FUNCTION F21( 
    p_no IN NUMBER) 
  RETURN VARCHAR; 
END; 

/ 
CREATE OR replace PACKAGE BODY xab 
IS 
  PROCEDURE P21 (p_do IN NUMBER, 
                 p_xy OUT VARCHAR) 
  IS 
    p_xy VARCHAR(20); 
  BEGIN 
      SELECT department_name 
      INTO   p_xy 
      FROM   departments 
      WHERE  department_id = p_do; 

      INSERT INTO table1 
      VALUES     (p_xy, 
                  p_do); 
  END; 
  FUNCTION F21 (p_no IN NUMBER) 
  RETURN VARCHAR 
  IS 
    p_nm VARCHAR(20); 
  BEGIN 
      SELECT last_name 
      INTO   p_nm 
      FROM   employees 
      WHERE  employee_id = p_no; 

      RETURN p_nm; 
  END; 
END; 
Re: try to insert data in to table using procedure [message #652197 is a reply to message #652196] Fri, 03 June 2016 19:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=pl/sql+tutorial

When all else fails, Read The Fine Manual

https://docs.oracle.com/database/121/LNPLS/toc.htm

Have you ever programmed in any language other than PL/SQL? If so, which language?
Re: try to insert data in to table using procedure [message #652198 is a reply to message #652196] Fri, 03 June 2016 20:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You have the same variable name p_xy in your procedure, as an out parameter and as a local variable. The same name can't be used for both and you don't need both. You need to remove one or the other. If you remove the out parameter from the procedure in the package body, then you must also remove it from the procedure in the package declaration. Which you remove, depends on what you want. If you expect it to return that value, then leave the out parameter and remove the local variable. If you don't need to return that value, and just want to insert, then leave the local variable and remove the out parameter.

PROCEDURE P21 (p_do IN NUMBER, 
                 p_xy OUT VARCHAR) 
  IS 
    p_xy VARCHAR(20); 

Re: try to insert data in to table using procedure [message #652199 is a reply to message #652198] Fri, 03 June 2016 21:54 Go to previous messageGo to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
Yes thankyou for explaining this one

got the answer

[Updated on: Fri, 03 June 2016 21:56]

Report message to a moderator

Re: try to insert data in to table using procedure [message #652201 is a reply to message #652199] Fri, 03 June 2016 22:08 Go to previous message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
Hi Swan

coming from non-programming background, trained on basics of plsql and trying to figure on my own.
Previous Topic: Issue with UTL_FILE.INVALID_OPERATION
Next Topic: Transpose rows to columns without aggregation
Goto Forum:
  


Current Time: Tue Jul 27 20:26:26 CDT 2021