Re: Inserting records in a cursor

From: GovindanK <gkatteri_at_fastmail.fm>
Date: Thu, 04 Mar 2004 07:45:20 -0800
Message-ID: <40474F10.1040002@fastmail.fm>

May be this is what you need

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Jun 15 01:03:12 2002

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_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Chris Stephens
>Sent: Wednesday, March 03, 2004 3:59 PM
>To: 'oracle-l_at_freelists.org'
>Subject: RE: Inserting records in a cursor
>
>
>Excellent spanglish!
>
>-----Original Message-----
>From: Jamadagni, Rajendra [mailto:Rajendra.Jamadagni_at_espn.com]
>Sent: Wednesday, March 03, 2004 2:56 PM
>To: oracle-l_at_freelists.org
>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_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Cachito Reyes
>Pacheco
>Sent: Wednesday, March 03, 2004 3:43 PM
>To: oracle-l_at_freelists.org
>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_at_espn.com>
>To: <oracle-l_at_freelists.org>
>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_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Cachito Reyes
>Pacheco
>Sent: Wednesday, March 03, 2004 3:07 PM
>To: oracle-l_at_freelists.org
>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_at_freelists.org 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
-----------------------------------------------------------------

Received on Thu Mar 04 2004 - 10:12:19 CST