Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO USE RECORD STRUCTURE IN A PROCEDURE (SQL PLUS/TOAD,WINDOWS XP)
HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291126] Thu, 03 January 2008 01:41 Go to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,


I WANTED TO CREATE A RECORD AND WANTED TO PASS THAT RECORD STRUCTURE VARIABLE TO A PROCEDURE AND THAT PROCEDURE SHUD BE ABLE TO DELETE DATA BASED ON THE INFORMATION FROM THIS CURSOR BUT WHEN I DOES IT IAM GETTG ERROR
HERE GOES WHAT I DID

ANONYMOUS PL/SQL BLOCK
declare
TYPE emp_det IS RECORD (
empno number(10),
ename varchar2(20),
deptno number(5));
begin
dbms_output.put_line('right program');
end;

PROCEDURE:

create or replace procedure collection_rec(emp_det1 emp_det) is
TYPE INS_DEL IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
INS_DEL1 INS_DEL ;
begin
DELETE FROM EMP3 WHERE empno=emp_det1.empno and ename =emp_det1.ename and DEPTNO =emp_det1.deptno RETURNING empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect INTO INS_DEL1;
FORALL I IN INS_DEL1.FIRST..INS_DEL1.LAST
INSERT INTO EMP2 VALUES INS_DEL1;
end;


ANYONE PLS TELL ME HOW TO USE A RECORD IN A PROCEDURE
SINCE user-defined records can be declared as the formal parameters of procedures and functions
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291128 is a reply to message #291126] Thu, 03 January 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could Oracle knows you define a type in an anonymous PL/SQL block? This type no more exists at the end of the block.

You have to either create a type for your record (create type) or define your type in a package and reference this type in your procedure.

Next time, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

DON'T POST IN UPPER CASE.

Regards
Michel

Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291167 is a reply to message #291128] Thu, 03 January 2008 04:20 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,


Yes u are right
i created a package and done it
later on only i got the idea
anyways thanks a lot for the help
now i have got another probs i need to call this package.procedure from an anonymous block by passing empno,ename and deptno as a record
how can i do it
I wanted to pass foll values as a single record :
empno =7369,ename =smith,deptno=10
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291169 is a reply to message #291167] Thu, 03 January 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your code.
Don't forget code tags.

Regards
Michel
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291178 is a reply to message #291169] Thu, 03 January 2008 05:06 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,


Iam hereby attaching the file pls have a luk on it
and tell me how to call this procedure by passing the the record consisting of 3 values
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291180 is a reply to message #291178] Thu, 03 January 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can call the procedure with
P_RECORD.collection_rec (P_RECORD.emp_det(7369,'smith',10))

Regards
Michel
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291807 is a reply to message #291126] Sun, 06 January 2008 23:54 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,


when i tried executing like

exec P_RECORD.collection_rec (P_RECORD.emp_det(7369,'smith',10));

its sayg error like

PLS-00222: no function with name 'EMP_DET' exists in this scope

Please help me out

Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291837 is a reply to message #291126] Mon, 07 January 2008 00:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Show us how did you execute .
Are you sure thet package is successfully copiled .

If youhave compiled the given code as it is for Package specification and body , it will not get compiled successfully.

FORALL I IN INS_DEL1.FIRST..INS_DEL1.LAST
INSERT INTO EMP2 VALUES INS_DEL1;


will not work in your code .
Change it to

FORALL I IN INS_DEL1.FIRST..INS_DEL1.LAST
INSERT INTO EMP2 VALUES INS_DEL1(I);


Thumbs Up
Rajuvan.
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291838 is a reply to message #291126] Mon, 07 January 2008 00:50 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,

Please refer to folling file
i compiled the package successfully
and when i tried giving out foll exec command like

SQL>exec P_RECORD.collection_rec (P_RECORD.emp_det(7369,'smith',10));

when i tried doing the above from sql plus prompt iam gettg error like

BEGIN P_RECORD.collection_rec (P_RECORD.emp_det(7369,'smith',10)); END; *


ERROR at line 1:
ORA-06550: line 1, column 32:
PLS-00222: no function with name 'EMP_DET' exists in this scope
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291843 is a reply to message #291838] Mon, 07 January 2008 00:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE emp3 AS SELECT * FROM emp WHERE deptno = 20
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> SELECT * FROM emp3
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SCOTT@orcl_11g> SELECT * FROM emp2
  2  /

no rows selected

SCOTT@orcl_11g> CREATE OR REPLACE TYPE emp_det AS OBJECT
  2  	 (empno  NUMBER   (10),
  3  	  ename  VARCHAR2 (15),
  4  	  deptno NUMBER   (20));
  5  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE p_record
  2  AS
  3    PROCEDURE  collection_rec (emp_det1 emp_det);
  4  END p_record;
  5  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY p_record
  2  AS
  3    PROCEDURE collection_rec (emp_det1 emp_det)
  4    IS
  5  	 TYPE ins_del IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  6  	 ins_del1 ins_del;
  7    BEGIN
  8  	 DELETE FROM emp3
  9  	 WHERE	empno = emp_det1.empno
 10  	 AND	ename = emp_det1.ename
 11  	 AND	deptno = emp_det1.deptno
 12  	 RETURNING empno, ename, job, mgr, hiredate, sal, comm, deptno
 13  	 BULK COLLECT INTO ins_del1;
 14  	 FORALL i IN ins_del1.FIRST .. ins_del1.LAST
 15  	   INSERT INTO emp2 VALUES ins_del1(i);
 16    END collection_rec;
 17  END p_record;
 18  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC p_record.collection_rec (emp_det (7369, 'SMITH', 20))

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM emp3
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SCOTT@orcl_11g> SELECT * FROM emp2
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SCOTT@orcl_11g>  


Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291847 is a reply to message #291126] Mon, 07 January 2008 01:12 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,

I wanted to use record type itself which shud be declared with the package

Please tell me how to call it

In ure eg u have created an object type that too outside the procedure

i wanna know how to call from the package the record type

Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291848 is a reply to message #291838] Mon, 07 January 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it that way.
You have to create an external type:
SQL> CREATE OR REPLACE PACKAGE P_RECORD AS
  2  PROCEDURE  collection_rec(emp_det1 emp_det);
  3  END;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY P_RECORD AS
  2  procedure collection_rec(emp_det1 emp_det) IS
  3  begin
  4    dbms_output.put_line(emp_det1.empno);
  5    dbms_output.put_line(emp_det1.ename);
  6    dbms_output.put_line(emp_det1.deptno);
  7  end collection_rec;
  8  end P_RECORD;
  9  /

Package body created.

SQL> exec P_RECORD.collection_rec (emp_det(7369,'smith',10));
7369
smith
10

PL/SQL procedure successfully completed.

Regards
Michel
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291864 is a reply to message #291126] Mon, 07 January 2008 01:44 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,

we cant do the creation like

CREATE OR REPLACE TYPE emp_det_ins as record
(empno NUMBER (10),
ename VARCHAR2 (15),
deptno NUMBER (20));

its throwing error

i think we cant do the declaration like above for records

thn how can we independently declare
only object types can be declared like this
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291866 is a reply to message #291864] Mon, 07 January 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

its throwing error

There is no error in your post.
Why can't you post the same way as I did?

Quote:

i think we cant do the declaration like above for records

Why?

Quote:

thn how can we independently declare
only object types can be declared like this

The way I did.

Regards
Michel
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291874 is a reply to message #291847] Mon, 07 January 2008 02:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> DROP TYPE emp_det
  2  /
DROP TYPE emp_det
*
ERROR at line 1:
ORA-04043: object EMP_DET does not exist


SCOTT@orcl_11g> CREATE TABLE emp3 AS SELECT * FROM emp WHERE deptno = 20
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> SELECT * FROM emp3
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SCOTT@orcl_11g> SELECT * FROM emp2
  2  /

no rows selected

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE p_record
  2  AS
  3    TYPE emp_det IS RECORD
  4  	 (empno  NUMBER   (10),
  5  	  ename  VARCHAR2 (15),
  6  	  deptno NUMBER   (20));
  7    PROCEDURE  collection_rec (emp_det1 p_record.emp_det);
  8  END p_record;
  9  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY p_record
  2  AS
  3    PROCEDURE collection_rec (emp_det1 p_record.emp_det)
  4    IS
  5  	 TYPE ins_del IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  6  	 ins_del1 ins_del;
  7    BEGIN
  8  	 DELETE FROM emp3
  9  	 WHERE	empno = emp_det1.empno
 10  	 AND	ename = emp_det1.ename
 11  	 AND	deptno = emp_det1.deptno
 12  	 RETURNING empno, ename, job, mgr, hiredate, sal, comm, deptno
 13  	 BULK COLLECT INTO ins_del1;
 14  	 FORALL i IN ins_del1.FIRST .. ins_del1.LAST
 15  	   INSERT INTO emp2 VALUES ins_del1(i);
 16    END collection_rec;
 17  END p_record;
 18  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> DECLARE
  2    v_emps p_record.emp_det;
  3  BEGIN
  4    v_emps.empno := 7369;
  5    v_emps.ename := 'SMITH';
  6    v_emps.deptno := 20;
  7    p_record.collection_rec (v_emps);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM emp3
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SCOTT@orcl_11g> SELECT * FROM emp2
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SCOTT@orcl_11g> 


Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291876 is a reply to message #291126] Mon, 07 January 2008 02:09 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
HEY HERE goes the perfect answer :

CREATE OR REPLACE PACKAGE P_RECORD AS
TYPE emp_det IS record (
empno number(10),ename varchar2(15),deptno number(20));
PROCEDURE collection_rec(emp_det1 emp_det);
END;
/

CREATE OR REPLACE PACKAGE BODY P_RECORD AS

procedure collection_rec(emp_det1 emp_det) is
TYPE INS_DEL IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
INS_DEL1 INS_DEL ;
begin
DELETE FROM EMP3 WHERE DEPTNO =emp_det1.deptno and ename =emp_det1.ename and DEPTNO =emp_det1.deptno RETURNING empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect INTO INS_DEL1;
FORALL I IN INS_DEL1.FIRST..INS_DEL1.LAST
INSERT INTO EMP2 VALUES INS_DEL1(I);
end collection_rec;
end P_RECORD;
/


Declare
det1 P_RECORD.emp_det;
Begin
det1.empno := 7839;
det1.ename := 'KING';
det1.deptno := 10;
P_RECORD.collection_rec(det1);
end;
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291877 is a reply to message #291864] Mon, 07 January 2008 02:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
dmerin wrote on Sun, 06 January 2008 23:44

Hi,

we cant do the creation like

CREATE OR REPLACE TYPE emp_det_ins as record
(empno NUMBER (10),
ename VARCHAR2 (15),
deptno NUMBER (20));

its throwing error

i think we cant do the declaration like above for records

thn how can we independently declare
only object types can be declared like this




If you are creating it in SQL, then use OBJECT. If you are creating it in PL/SQL, then use RECORD.

Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291887 is a reply to message #291126] Mon, 07 January 2008 02:28 Go to previous messageGo to next message
dmerin
Messages: 30
Registered: December 2007
Member
Thanks a lot friend

Hope everyone is clear with the correct answer
IF not pls let me know

So from this we can understand that its possible to declare record types within packages but we cant call with just an exec command instead we shud do it using anonymous declare begin block

Am i right ???

If anyone not understood with what i have stated pls let me know
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291903 is a reply to message #291126] Mon, 07 January 2008 03:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


There is no diffrence between an Anonymous block and EXEC statement .

ie,

Quote:

EXEC P_RECORD.collection_rec (P_RECORD.emp_det(7369,'smith',10))


is same as

Quote:

BEGIN
P_RECORD.collection_rec (P_RECORD.emp_det(7369,'smith',10));
END;


Anyway, if you really want to go or single EXEC Straight,
you can do a trick of calling a Fuction that initialize and return emp_det record as parameted to collection_rec.

Some thing like ,

SQL> CREATE TABLE emp3 AS SELECT * FROM emp WHERE deptno = 20
  2  ;

Table created.

SQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1 = 2
  2  ;

Table created.

SQL> SELECT * FROM emp3
  2  ;

     EMPNO ENAME   JOB         MGR HIREDATE         SAL  COMM     DEPTNO
---------- ------- --------- ----- --------- ---------- ----- ----------
      7566 JONES   MANAGER    7839 02-APR-95      29750               20
      7788 SCOTT   ANALYST    7566 19-APR-97      30000               20
      7876 ADAMS   CLERK      7788 23-MAY-97       1100     0         20
      7902 FORD    ANALYST    7566 03-DEC-91      30000               20
      7369 SMITH   SALESMAN   7902 17-DEC-05       5000               20

SQL> SELECT * FROM emp2;

no rows selected

SQL> CREATE OR REPLACE PACKAGE P_RECORD
  2  AS
  3  TYPE emp_det IS RECORD
  4      (empno  NUMBER   (10),
  5       ename  VARCHAR2 (15),
  6       deptno NUMBER   (20));
  7  PROCEDURE  collection_rec(emp_det1 emp_det);
  8  FUNCTION   init_rec (empno  NUMBER , ename  VARCHAR2 ,deptno NUMBER)
  9             RETURN emp_det;
 10  END;
 11  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY p_record
  2  AS
  3
  4    FUNCTION   init_rec (empno  NUMBER , ename  VARCHAR2 ,deptno NUMBER)
  5    RETURN emp_det
  6    AS
  7     emp_rec  emp_det ;
  8    BEGIN
  9     emp_rec.empno := empno;
 10     emp_rec.ename := ename;
 11     emp_rec.deptno := deptno;
 12
 13     RETURN emp_rec;
 14
 15    END init_rec;
 16
 17    PROCEDURE collection_rec(emp_det1 emp_det)
 18    IS
 19      TYPE ins_del IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
 20      ins_del1 ins_del ;
 21    BEGIN
 22      DELETE FROM EMP3
 23     WHERE empno=emp_det1.empno
 24     AND ename =emp_det1.ename
 25     AND deptno =emp_det1.deptno
 26     RETURNING empno,ename,job,mgr,hiredate,sal,comm,deptno
 27     BULK COLLECT INTO INS_DEL1;
 28
 29      FORALL I IN INS_DEL1.FIRST..INS_DEL1.LAST
 30        INSERT INTO EMP2 VALUES INS_DEL1(i);
 31    END collection_rec;
 32
 33  END p_record;
 34  /

Package body created.

SQL> EXEC p_record.collection_rec(p_record.init_rec(7369, 'SMITH', 20));

PL/SQL procedure successfully completed.

SQL> SELECT * FROM emp3;

     EMPNO ENAME   JOB         MGR HIREDATE         SAL  COMM     DEPTNO
---------- ------- --------- ----- --------- ---------- ----- ----------
      7566 JONES   MANAGER    7839 02-APR-95      29750               20
      7788 SCOTT   ANALYST    7566 19-APR-97      30000               20
      7876 ADAMS   CLERK      7788 23-MAY-97       1100     0         20
      7902 FORD    ANALYST    7566 03-DEC-91      30000               20

SQL> SELECT * FROM emp2;

     EMPNO ENAME   JOB         MGR HIREDATE         SAL  COMM     DEPTNO
---------- ------- --------- ----- --------- ---------- ----- ----------
      7369 SMITH   SALESMAN   7902 17-DEC-05       5000               20

SQL>


Thumbs Up
Rajuvan
Re: HOW TO USE RECORD STRUCTURE IN A PROCEDURE [message #291905 is a reply to message #291126] Mon, 07 January 2008 03:16 Go to previous message
dmerin
Messages: 30
Registered: December 2007
Member
Hi,

So only with the help of function we can use exec command right no other way out
anyways thanks
Previous Topic: Host Name
Next Topic: index
Goto Forum:
  


Current Time: Sun Dec 04 20:45:48 CST 2016

Total time taken to generate the page: 0.06923 seconds