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  |
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 #291167 is a reply to message #291128] |
Thu, 03 January 2008 04:20   |
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 #291838 is a reply to message #291126] |
Mon, 07 January 2008 00:50   |
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   |
 |
Barbara Boehmer
Messages: 9104 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   |
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 #291864 is a reply to message #291126] |
Mon, 07 January 2008 01:44   |
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 #291874 is a reply to message #291847] |
Mon, 07 January 2008 02:05   |
 |
Barbara Boehmer
Messages: 9104 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   |
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 #291887 is a reply to message #291126] |
Mon, 07 January 2008 02:28   |
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   |
 |
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>

Rajuvan
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 23:07:01 CST 2025
|