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
----------------------------------------------------------------------------
----
-- Bring the date_Format table into memory for reference
-- while cleaning up the date fields. They are all
-- different screwed up formats.
proc_name_v VARCHAR2(40) := 'fetch_date_formats_proc';
sqlcode_v VARCHAR2(40) := NULL;
sqlerrm_v VARCHAR2(500) := NULL;
----------------------------------------------------------------------------
----
CURSOR date_format_c
IS
SELECT column_name,
field_format
FROM date_format
WHERE table_name = in_table_name_v;
----------------------------------------------------------------------------
----
BEGIN
OPEN date_format_c;
FETCH date_format_c
BULK COLLECT
INTO io_date_col_name_tab,
io_date_field_fmt_tab;
CLOSE date_format_c;
EXCEPTION
WHEN OTHERS
THEN
-- None of the rest of the functionality of this package
-- will work. Record the error and raise it to the
-- calling program.
sqlcode_v := SQLCODE;
sqlerrm_v := ' fetch_date_formats_proc error: ' || SQLERRM(SQLCODE);
error_log_pk.log_error_proc(proc_name_v,
sqlcode_v,
sqlerrm_v,
in_load_job_id_v);
RAISE;
END fetch_date_formats_proc;
** Notice the SELECT INTO after bulk collect. Can't get around that.
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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
