Question

PL/SQL variable not set

Asked by: ksummers

I have a stored proc with a variable that I need to set based on what parameters are passed, and then include that variable in my 'where' clause. After I set the variable, my cursor does not see what is in the variable called SQLText?

procedure        Assignment(JOB_ID_KEY    in NUMBER,
                         TASK_ID_KEY   in NUMBER,
                         Lender_Number in NUMBER
                         ) is
cursor CCSDStates is
 SELECT e.state_abbreviation, e.employee_key
      FROM (SELECT state_abbreviation,
                   employee_key,
                   lender_number,
                   ROW_NUMBER() OVER(PARTITION BY state_abbreviation ORDER BY employee_key) rn,
                   COUNT(*) OVER(PARTITION BY state_abbreviation) cnt
              FROM ccsd_assignment SQLText) e;
rcdCCSDStates CCSDStates%RowType;
begin
SQLText := 'where lender_number = ' || to_char(Lender_Number);
Open CCSDStates;
  loop
    begin
      Fetch CCSDStates
        into rcdCCSDStates; 
      Exit when CCSDStates%NotFound;
      DBMS_OUTPUT.PUT_LINE(rcdCCSDStates.State_Abbreviation);
    END;
  end loop;
close CCSDStates;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

Select all Open in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2009-02-02 at 12:40:31ID24106054
Tags: PL/SQL
Topic: PL / SQL
Participating Experts: 2
Points: 300
Comments: 30

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: johnsonePosted on 2009-02-02 at 12:50:48ID: 23531185

I believe this should do what you are looking for. You can pass the parameter into the cursor.

procedure        Assignment(JOB_ID_KEY    in NUMBER,
                         TASK_ID_KEY   in NUMBER,
                         Lender_Number in NUMBER
                         ) is
cursor CCSDStates(lnum number) is
 SELECT e.state_abbreviation, e.employee_key
      FROM (SELECT state_abbreviation,
                   employee_key,
                   lender_number,
                   ROW_NUMBER() OVER(PARTITION BY state_abbreviation ORDER BY employee_key) rn,
                   COUNT(*) OVER(PARTITION BY state_abbreviation) cnt
              FROM ccsd_assignment where lender_number = lnum) e;
rcdCCSDStates CCSDStates%RowType;
begin
Open CCSDStates (Lender_number);
  loop
    begin
      Fetch CCSDStates
        into rcdCCSDStates; 
      Exit when CCSDStates%NotFound;
      DBMS_OUTPUT.PUT_LINE(rcdCCSDStates.State_Abbreviation);
    END;
  end loop;
close CCSDStates;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

Select all Open in new window

by: sdstuberPosted on 2009-02-02 at 13:07:30ID: 23531350

where do you want the lender number filtering to be applied?

before or after row numbers and counts are generated?

by: sdstuberPosted on 2009-02-02 at 13:11:47ID: 23531399

johnsone's query above applies the filtering before.

I'm not implying it's wrong, but you didnt' specify

if you want the filtering to apply AFTER the the row_number and count then try this... (note, I switched to cursor FOR syntax)

PROCEDURE assignment(
    job_id_key      IN NUMBER,
    task_id_key     IN NUMBER,
    lender_number   IN NUMBER
)
IS
    CURSOR ccsdstates(
        p_lender_number   IN            VARCHAR2
    )
    IS
        SELECT   e.state_abbreviation, e.employee_key
          FROM   (SELECT   state_abbreviation,
                           employee_key,
                           lender_number,
                           ROW_NUMBER()
                               OVER (PARTITION BY state_abbreviation ORDER BY employee_key)
                               rn,
                           COUNT( * ) OVER (PARTITION BY state_abbreviation) cnt
                    FROM   ccsd_assignment sqltext)
         WHERE   lender_number = p_lender_number;
 
    rcdccsdstates   ccsdstates%ROWTYPE;
BEGIN
    FOR r IN ccsdstates(lender_number)
    LOOP
        DBMS_OUTPUT.put_line(r.state_abbreviation);
    END LOOP;
END;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

Select all Open in new window

by: ksummersPosted on 2009-02-02 at 13:12:38ID: 23531404

What if this is a varchar2 variable lender_list?? I get a ORA-01722 invalid number error at the FETCH statement.

by: sdstuberPosted on 2009-02-02 at 13:13:21ID: 23531413

oops, I accidentally dropped the "e" alias for in your inline view.

but it's not needed, you can simply change ...

"SELECT e.state_abbreviation, e.employee_key"

to

"SELECT state_abbreviation, employee_key"

by: ksummersPosted on 2009-02-02 at 13:21:20ID: 23531506

I have changed the code below. The lender variable is a VARCHAR2. But I am getting error.

procedure        Assignment(JOB_ID_KEY    in NUMBER,
                         TASK_ID_KEY   in NUMBER,
                         Lender_List in VARCHAR2
                         ) is
cursor CCSDStates(SQLText varchar2) is
 SELECT e.state_abbreviation, e.employee_key
      FROM (SELECT state_abbreviation,
                   employee_key,
                   lender_number,
                   ROW_NUMBER() OVER(PARTITION BY state_abbreviation ORDER BY employee_key) rn,
                   COUNT(*) OVER(PARTITION BY state_abbreviation) cnt
              FROM ccsd_assignment where lender_number in (SQLText)) e;
rcdCCSDStates CCSDStates%RowType;
begin
Open CCSDStates (Lender_List);
  loop
    begin
      Fetch CCSDStates  <--- FAILS HERE ORA-07122 ERROR
        into rcdCCSDStates; 
      Exit when CCSDStates%NotFound;
      DBMS_OUTPUT.PUT_LINE(rcdCCSDStates.State_Abbreviation);
    END;
  end loop;
close CCSDStates;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

Select all Open in new window

by: sdstuberPosted on 2009-02-02 at 13:29:35ID: 23531586

is "sql_text" a comma delimited string? If so,

lender_number in (SQLText) this will not work. if you pass in '1,2,3' as your sqltext, you are literally looking for a number '1,2,3' which, obviously is not a number.

by: sdstuberPosted on 2009-02-02 at 13:30:48ID: 23531601

change your code back to what it was before, when lender_number was a single numeric parameter. then either of our versions should work.

you have not, however specified where you want your filtering applied.

by: ksummersPosted on 2009-02-02 at 13:37:01ID: 23531677

Well, I think that I want the filtering AFTER. Which is in my initial question. You are correct though, it won't work if I change it to varchar2. Maybe I should delete this post and redo my questio with what I just asked. How do I do it with varchar2? Where lender_number in (SQLText)?

by: sdstuberPosted on 2009-02-02 at 13:37:06ID: 23531679

or, if you want the string-list to be kept, try this...

first create the type and function and then you'll be able to use a string as an "in list by letting the table function split it into a set for you

CREATE OR REPLACE TYPE VCARRAY;
 
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);
 
            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;
 
        RETURN;
    END str2tbl;
 
 
 
PROCEDURE assignment(
    job_id_key    IN NUMBER,
    task_id_key   IN NUMBER,
    lender_list   IN VARCHAR2
)
IS
    CURSOR ccsdstates
    IS
        SELECT   state_abbreviation, employee_key
          FROM   (SELECT   state_abbreviation,
                           employee_key,
                           lender_number,
                           ROW_NUMBER()
                               OVER (PARTITION BY state_abbreviation ORDER BY employee_key)
                               rn,
                           COUNT( * ) OVER (PARTITION BY state_abbreviation) cnt
                    FROM   ccsd_assignment
                   WHERE   lender_number IN
                                   (SELECT   TO_NUMBER(COLUMN_VALUE)
                                      FROM   table(str2tbl(lender_list))));
BEGIN
    FOR r IN ccsdstates
    LOOP
        DBMS_OUTPUT.put_line(r.state_abbreviation);
    END LOOP;
END;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:

Select all Open in new window

by: sdstuberPosted on 2009-02-02 at 13:38:51ID: 23531698

the above code has the filtering "BEFORE" which is like you have it in your original question and in johnsone's and your follow up.

if you really do want it after, then create the type and function as in my previous post and then write your procedure like this...

PROCEDURE assignment(
    job_id_key    IN NUMBER,
    task_id_key   IN NUMBER,
    lender_list   IN VARCHAR2
)
IS
    CURSOR ccsdstates
    IS
        SELECT   state_abbreviation, employee_key
          FROM   (SELECT   state_abbreviation,
                           employee_key,
                           lender_number,
                           ROW_NUMBER()
                               OVER (PARTITION BY state_abbreviation ORDER BY employee_key)
                               rn,
                           COUNT( * ) OVER (PARTITION BY state_abbreviation) cnt
                    FROM   ccsd_assignment)
                   WHERE   lender_number IN
                                   (SELECT   TO_NUMBER(COLUMN_VALUE)
                                      FROM   table(str2tbl(lender_list)));
BEGIN
    FOR r IN ccsdstates
    LOOP
        DBMS_OUTPUT.put_line(r.state_abbreviation);
    END LOOP;
END;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:

Select all Open in new window

by: ksummersPosted on 2009-02-03 at 07:29:03ID: 23538068

I can't get this to compile in my package. Where should I declare your solution sdstuber?

by: sdstuberPosted on 2009-02-03 at 07:34:07ID: 23538126

put the above code
in the package body, not the package itself

just the declaration would go ito the package.

PROCEDURE assignment(
job_id_key IN NUMBER,
task_id_key IN NUMBER,
lender_list IN VARCHAR2
);

by: ksummersPosted on 2009-02-03 at 07:38:25ID: 23538173

I got that part, but when I compile, PL/SQL says str2tbl may not be used in SQL.

by: sdstuberPosted on 2009-02-03 at 07:48:48ID: 23538298

or sorry I misunderstood
str2tbl and the type do not go into your package, they are stand alone

by: ksummersPosted on 2009-02-03 at 07:53:21ID: 23538357

Like this? Because I still get the same error.


TYPE v_arr IS VARRAY(25) of NUMBER(3);
 
 
function str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN v_arr PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);
 
            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;
 
        RETURN;
    END str2tbl;
 
create or replace package body CCSD is

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Select all Open in new window

by: sdstuberPosted on 2009-02-03 at 07:57:02ID: 23538404

no, you need the "create or replace" on the type and function exactly as I had them

create each of those first

then modify your package body to put the procedure in

by: ksummersPosted on 2009-02-03 at 07:59:45ID: 23538444

Okay. This gives me PLS-00101: Encountered the symbol "CREATE" error.

CREATE OR REPLACE TYPE VCARRAY;
 
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);
 
            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;
 
        RETURN;
    END str2tbl;
 
 
create or replace package body CCSD is

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

Select all Open in new window

by: sdstuberPosted on 2009-02-03 at 08:02:52ID: 23538485

ooops, no sorry I didn't realize I had cut off my vcarray definition...

create it like this

CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);

and then the function should compile which will then allow the procedure to compile

by: ksummersPosted on 2009-02-03 at 08:09:19ID: 23538564

I don't understand. I am still getting the PLS-00101: Encountered the symbol "CREATE" error.

CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);
 
            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;
 
        RETURN;
    END str2tbl;
 
 
 
create or replace package body CCSD is

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Select all Open in new window

by: sdstuberPosted on 2009-02-03 at 08:15:41ID: 23538637

how are you creating them?

here's my SQLPLUS output

if you're using sqlplus you need the trailing "/"

if you're using some other tool you won't.

SQL> CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);
  2  /
 
Type created.
 
SQL>
SQL> CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VAR
CHAR2 := ',')
  2          RETURN vcarray PIPELINED
  3      AS
  4          v_length   NUMBER := LENGTH(p_string);
  5          v_start    NUMBER := 1;
  6          v_index    NUMBER;
  7      BEGIN
  8          WHILE(v_start <= v_length)
  9          LOOP
 10              v_index    := INSTR(p_string, p_delimiter, v_start);
 11
 12              IF v_index = 0
 13              THEN
 14                  PIPE ROW(SUBSTR(p_string, v_start));
 15                  v_start    := v_length + 1;
 16              ELSE
 17                  PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
 18                  v_start    := v_index + 1;
 19              END IF;
 20          END LOOP;
 21
 22          RETURN;
 23      END str2tbl;
 24  /
 
Function created.
 
SQL>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:

Select all Open in new window

by: ksummersPosted on 2009-02-03 at 08:18:38ID: 23538666

I am using PL/SQL Developer. I hit F8 to compile my package and it fails with that error.

by: sdstuberPosted on 2009-02-03 at 08:20:21ID: 23538693

Don't put all 3 objects in the same window.

create the vcarray type, then clear the window or create a new one create the function, clear the window or create a new one and then compile you package.

or use sqlplus , simply cut and paste the above commands with "/" after each

by: ksummersPosted on 2009-02-03 at 08:22:53ID: 23538738

But I need this to ALL be in one package?? Help!

by: sdstuberPosted on 2009-02-03 at 08:41:01ID: 23538968

first,

the type MUST be declared separately.

you "can" put str2tbl in your package if you want, but it is a generic utility, I recommend leaving it standalone, or putting it into a package where you have other similar utilities.

however, if you "really" want to put the str2tbl function in your package you simply have to include the full function in the package body (without "create or replace") and then declare it in the package.

by: sdstuberPosted on 2009-02-03 at 08:42:47ID: 23538991

if you don't want use str2tbl, you don't have to. But you must find some other way to turn a delimited string into a set.

You could, alternately create a temp table (which, of course, wouldn't be in your package either)
and then parse the string yourself and insert each element into the temp table
then change the IN clause from

WHERE lender_number IN
(SELECT TO_NUMBER(COLUMN_VALUE)
FROM table(str2tbl(lender_list)));

to

WHERE lender_number IN
(SELECT your_column from your_temp_table )

by: ksummersPosted on 2009-02-03 at 08:48:41ID: 23539080

Okay, I actually like your idea using a temp table instead, but I need help declaring a temp table in my package. I already had a function to parse the lender list, so now I need to know how to create the temp table and fill it with values.

I am going to increase the points for this question. You deserve it LOL!

by: sdstuberPosted on 2009-02-03 at 08:59:58ID: 23539243

you don't create the temp table in the package

it's a separately created entity. It might be "temporary" but it's still a real table.

CREATE GLOBAL TEMPORARY TABLE your_temp_table(
your_column number
)
ON COMMIT PRESERVE ROWS;

in your function where you parse the list...
as you iterate through each value you must have some variable where you put them
simply put that variable into an insert statement

insert into your_temp_table (your_column) values (your_variable);

Remember to purge your temp table afterward (or change it to delete on commit, or simply end your session)

I suggest going with the str2tbl option. not only does it make your code smaller and easier to maintain but it's a general purpose solution, you can use it again and again for any string splitting.

you "could" even use it populate your temp table (this is for illustration only, DON'T do this)

insert into your_temp_table (your_column)
(select column_value from table(str2tbl(lender_list));

that would parse and populate the table all at once.
it would be silly (and inefficient) to do so in this instance though because you don't want to parse and the query the data twice.

by: ksummersPosted on 2009-02-03 at 09:42:25ID: 23539768

I think your temp table suggestion is going to work for me. I do agree that I should probably use the str2tbl option and would be easier to maintain. I will let you know if I have any other problems after I look at this some more. Thank you so much.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

PL/SQL variable not set

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

PL/SQL

PL / SQL

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press