Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Selecting a variable similar to a column

RE: Selecting a variable similar to a column

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Wed, 10 Apr 2002 13:54:03 -0800
Message-ID: <F001.004411DB.20020410135403@fatcity.com>


Hi Rick,

in pl/sql any time there is a select statement outside of a cursor, it expects you to select it into a variable of some sort.

You can pass refcursors or pl/sql tables back and forth and buzz through the values like a cursor. Here's an example from some of my living code:

The pl/sql table types are declared globally in my utility package as follows:

TYPE typeColName IS TABLE OF date_format.column_name%TYPE

          INDEX BY BINARY_INTEGER;
TYPE typeFieldFmt IS TABLE OF date_format.field_format%TYPE

          INDEX BY BINARY_INTEGER; And here's the proc:

PROCEDURE fetch_date_formats_proc(in_table_name_v  IN VARCHAR2,
                                  in_load_job_id_v IN NUMBER,
                                  io_date_col_name_tab  IN OUT typeColName,
                                  io_date_Field_fmt_tab IN OUT typeFieldFmt)
IS

The global pl/sql table declaration in the package that calls the utility procedure above is as follows

date_col_name_tab cleanup_raw_tables_util_pk.typeColName; date_field_fmt_tab cleanup_raw_tables_util_pk.typeFieldFmt;

So I pass these two tables into the fetch_date_formats_proc, and when they return they are populated with the data from the cursor.

THIS IS NOT THE ONLY WAY TO DO IT, and it is not the best way as far as memory management goes. Refcursors would be much better suited to this, but I didn't learn enough about it to get it done. Since my result set is small here, I decided to bite the bullet and use the pl/sql tables since I already knew how.

These pl/sql tables speed up my data load by bringing a lookup table into memory and making it not necessary to continually hit the database every time I want to look up a date format.

Let me know if you have any questions. Hope this helps you.

Lisa Koivu
Oracle Database TANK
Fairfield Resorts, Inc.
954-935-4117

> -----Original Message-----
> From: Rick Stephenson [SMTP:rstephenson_at_ovid.com]
> Sent: Wednesday, April 10, 2002 5:03 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Selecting a variable similar to a column
>
> I am using Oracle EE 8.1.7
>
>
>
> Is there a way to select a variable similar to selecting a normal column
> of a table? Or do I have to user dbms_output.put_line....
>
>
>
> For example, can this:
>
>
>
> declare
>
> lv_FirstName varchar2(20);
>
> lv_LastName varchar2(20);
>
> begin
>
> lv_FirstName := 'rick';
>
> lv_LastName := 'stephenson';
>
> dbms_output.put_line(lv_FirstName||' '||lv_LastName);
>
> end;
>
> /
>
>
>
>
>
> be written something like this:
>
>
>
> declare
>
> lv_FirstName varchar2(20);
>
> lv_LastName varchar2(20);
>
> begin
>
> lv_FirstName := 'rick';
>
> lv_LastName := 'stephenson';
>
> select lv_FirstName, lv_LastName from dual;
>
> end;
>
>
>
>
>
> Maybe I am off my rocker, but I thought I would at least ask.
>
> When I run this, it tells me that it is looking for an INTO variable. I
> don't want to put it into a variable, I want it to return as if it were a
> select statement.
>
>
>
>
>
> Thanks for your help,
>
>
>
> Rick Stephenson
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: lisa.koivu_at_efairfield.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Apr 10 2002 - 16:54:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US