Inserting records in a cursor 2004-03-04 - By GovindanK
May be this is what you need
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Jun 15
01:03:12 2002
Copyright (c) 1982, 2002, Oracle Corporation. All
rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.1.0 - Production
SQL >
SQL > set serveroutput on size 1000000;
SQL > set echo on
SQL > create table my_emp(empno number , empsal number)
2 /
Table created.
SQL > begin
2 insert into my_emp values(1001, 1000);
3 insert into my_emp values(1002, 2000);
4 insert into my_emp values(1003, 3000);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL > CREATE or REPLACE TYPE my_record_type as OBJECT
2 ( xempsal NUMBER )
3 /
Type created.
SQL > CREATE or REPLACE TYPE my_table_type as TABLE of
my_record_type
2 /
Type created.
SQL > declare
2 buf_data my_table_type := my_table_type() ;
3 begin
4 buf_data.EXTEND ;
5 buf_data(1) := my_record_type(1000) ;
6 buf_data.EXTEND ;
7 buf_data(2) := my_record_type(2000) ;
8 FOR CX in
9 (
10 select a.empno , a.empsal
11 from
12 (select empno , empsal from my_emp) a
13 ,TABLE ( cast( buf_data as my_table_Type) ) y
14 WHERE y.xempsal = a.empsal
15 )
16 loop
17 dbms_output.put_line( 'EmpNo = '||cx.empno|| ' qualifies ');
18 end loop;
19 end;
20 /
EmpNo = 1001 qualifies
EmpNo = 1002 qualifies
PL/SQL procedure successfully completed.
SQL > set echo off
SQL > set echo on
SQL > declare
2 buf_data my_table_type := my_table_type() ;
3 begin
4 buf_data.EXTEND ;
5 buf_data(1) := my_record_type(1000) ;
6 buf_data.EXTEND ;
7 buf_data(2) := my_record_type(2000) ;
8 FOR CX in
9 (
10 select a.empno , a.empsal
11 from
12 TABLE ( cast( buf_data as my_table_Type ) ) y
13 ,(select empno , empsal from my_emp) a
14 WHERE y.xempsal = a.empsal
15 )
16 loop
17 dbms_output.put_line( 'EmpNo = '||cx.empno|| ' qualifies ');
18 end loop;
19 end;
20 /
EmpNo = 1001 qualifies
EmpNo = 1002 qualifies
PL/SQL procedure successfully completed.
SQL >
Jamadagni, Rajendra wrote:
>Me too lazy ...
>
>Raj
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
>Rajendra dot Jamadagni at nospamespn dot com
>All Views expressed in this email are strictly personal.
>select standard_disclaimer from company_requirements;
>QOTD: Any clod can have facts, having an opinion is an art !
>
>
>-- --Original Message-- --
>From: oracle-l-bounce@(protected)
>[mailto:oracle-l-bounce@(protected)]On Behalf Of Chris Stephens
>Sent: Wednesday, March 03, 2004 3:59 PM
>To: 'oracle-l@(protected) '
>Subject: RE: Inserting records in a cursor
>
>
>Excellent spanglish!
>
>-- --Original Message-- --
>From: Jamadagni, Rajendra [mailto:Rajendra.Jamadagni@(protected)]
>Sent: Wednesday, March 03, 2004 2:56 PM
>To: oracle-l@(protected)
>Subject: RE: Inserting records in a cursor
>
>CREATE OR REPLACE TYPE ctb.tyo_cco_imputacion AS OBJECT(
>nTotal NUMBER,
>cCCO1 VARCHAR2(9),
>cCCO2 VARCHAR2(9),
>cCCO3 VARCHAR2(9),
>cCCO4 VARCHAR2(9),
>cCCO5 VARCHAR2(9),
>nMonto NUMBER(16,2))
>/
>
>CREATE OR REPLACE TYPE ctb.typ_cco_imputacion AS TABLE OF
>CTB.TYO_CCO_IMPUTACION;
>/
>
>create functoin load return ctb.typ_cco_imputacion is
>TuBLA ctb.typ_cco_imputacion;
>begin
> for i in 1 .. 10
> loop
> TUBLA(i).nTotal := i;
> TUBLA(i).cCCO1 := 'ccol1 ' || i;
> TUBLA(i).cCCO2 := 'ccol2 ' || i;
> TUBLA(i).cCCO3 := 'ccol3 ' || i;
> TUBLA(i).cCCO4 := 'ccol4 ' || i;
> TUBLA(i).cCCO5 := 'ccol5 ' || i;
> TUBLA(i).nMonto := i;
> end loop;
>retturn tubla;
>end;
>/
>
>something like this should work ...
>Raj
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
>----
>Rajendra dot Jamadagni at nospamespn dot com
>All Views expressed in this email are strictly personal.
>select standard_disclaimer from company_requirements;
>QOTD: Any clod can have facts, having an opinion is an art !
>
>
>-- --Original Message-- --
>From: oracle-l-bounce@(protected)
>[mailto:oracle-l-bounce@(protected)]On Behalf Of Juan Cachito Reyes
>Pacheco
>Sent: Wednesday, March 03, 2004 3:43 PM
>To: oracle-l@(protected)
>Subject: Re: Inserting records in a cursor
>
>
>Could you please give a complete example creating a cursor, and adding
>values and returning :) pleeease... if I 'm not abusing of you.
>I 'm getting other error messages.
>
>
>declare
>
> Tabla ctb.typ_cco_imputacion;
>
>begin
>
>Tabla(1).ntotal := 1;
>
>Tabla(2).ntotal := 1;
>
>Tabla(3).ntotal := 1;
>
>tabla(4).ntotal := 1;
>
>--RETURN Tabla;
>
>rollback;
>
>end;
>
>16:39:00 ORA-06531 (See ORA-06531.ora-code.com): Referencia a una recopilación no inicializada
>
>-- -- Original Message -- --
>From: "Jamadagni, Rajendra " <Rajendra.Jamadagni@(protected) >
>To: <oracle-l@(protected) >
>Sent: Wednesday, March 03, 2004 4:16 PM
>Subject: RE: Inserting records in a cursor
>
>
>you are probably confused between a table (a rdbms entity) and a collection
>(aka pl/sql table). Collections do not use DML statements, you need to treat
>them like arrays ... that 's what they are.
>
>tubla[1].ntotal := 1;
>tubla[2].ntotal := 1;
>tubla[3].ntotal := 1;
>tubla[4].ntotal := 1;
>
>Raj
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
>----
>Rajendra dot Jamadagni at nospamespn dot com
>All Views expressed in this email are strictly personal.
>select standard_disclaimer from company_requirements;
>QOTD: Any clod can have facts, having an opinion is an art !
>
>
>-- --Original Message-- --
>From: oracle-l-bounce@(protected)
>[mailto:oracle-l-bounce@(protected)]On Behalf Of Juan Cachito Reyes
>Pacheco
>Sent: Wednesday, March 03, 2004 3:07 PM
>To: oracle-l@(protected)
>Subject: Inserting records in a cursor
>
>
>Hi, maybe this is a stupid question, but I didn 't it before, I want to
>create a cursor load data, and return in in a funciton
>something like
>
>If you can please, thank you.
>CREATE OR REPLACE
>
>TYPE ctb.tyo_cco_imputacion AS OBJECT
>
>(
>
>nTotal NUMBER,
>
>cCCO1 VARCHAR2(9),
>
>cCCO2 VARCHAR2(9),
>
>cCCO3 VARCHAR2(9),
>
>cCCO4 VARCHAR2(9),
>
>cCCO5 VARCHAR2(9),
>
>nMonto NUMBER(16,2)
>
>)
>
>/
>
>CREATE OR REPLACE
>
>TYPE ctb.typ_cco_imputacion AS TABLE OF CTB.TYO_CCO_IMPUTACION;
>
>/
>
>
>
>create functoin load return ctb.typ_cco_imputacion
>
>TuBLA typ_cco_imputacion;
>
>begin
>
>insert into TUBLA values(1,2,3,4);
>
>insert into TUBLA values(1,4,3,4);
>
>....
>
>retturn tubla
>
>end;
>
>
>
>
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
