Re: Inserting records in a cursor
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 12:54:36 -0400
Thanks, I did something similar
----- Original Message -----
From: "GovindanK" <gkatteri@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 04, 2004 11:45 AM
Subject: Re: Inserting records in a cursor
> 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@xxxxxxxxxxxxx
> >[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Chris Stephens
> >Sent: Wednesday, March 03, 2004 3:59 PM
> >To: 'oracle-l@xxxxxxxxxxxxx'
> >Subject: RE: Inserting records in a cursor
> >
> >
> >Excellent spanglish!
> >
> >-----Original Message-----
> >From: Jamadagni, Rajendra [mailto:Rajendra.Jamadagni@xxxxxxxx]
> >Sent: Wednesday, March 03, 2004 2:56 PM
> >To: oracle-l@xxxxxxxxxxxxx
> >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@xxxxxxxxxxxxx
> >[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Cachito Reyes
> >Pacheco
> >Sent: Wednesday, March 03, 2004 3:43 PM
> >To: oracle-l@xxxxxxxxxxxxx
> >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: Referencia a una recopilación no inicializada
> >
> >----- Original Message -----
> >From: "Jamadagni, Rajendra" <Rajendra.Jamadagni@xxxxxxxx>
> >To: <oracle-l@xxxxxxxxxxxxx>
> >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@xxxxxxxxxxxxx
> >[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Cachito Reyes
> >Pacheco
> >Sent: Wednesday, March 03, 2004 3:07 PM
> >To: oracle-l@xxxxxxxxxxxxx
> >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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
