Newbie Question- Wrapping Text in Procedure 2

[whatsinaname99]

I am using TYPE to build a varchar array of about 5000 items (unfortunately I'm not able to use a SQL statement). The problem is that that when I execute the procedure in TOAD, it doesn't return the values I'd expect when I loop through the array. For example, if the number '6789' is part of the array and in TOAD '678 appears at the end of a line and 9' appears at the beginning of the next line, '678 will be passed as one value and 9' will be passed as another value instead of just '6789'. Is there a way to get around this? Thank you.

 

[Thargy]

wotsit,

er, there may well be, but you actually don't care.

If you have an array of VARCHAR2's, the way in which TOAD displays them is immaterial. It's probably wrapping to try and give you the best display it can.

What you're really interested in is how the array performs when used in PL/SQL, and is handled programmatically by your application.

TOAD is a development tool, not an app, so why is wrapping of significance?

Regards

T

Grinding away at things Oracular

 

[whatsinaname99]

The environment may be a red herring, but I'm certain that my values aren't being returned as expected because they are being written out to the buffer. It only seems to work if I adjust each line in toad so that the values are not split by lines.

 

[whatsinaname99]

That is, split between lines.

 

[whatsinaname99]

Also, this is just a one-time data update. It's not going to be saved in the database as a stored procedure for future use.
I know it's inefficient but I don't have access to a sql statement, only a list of primary key values. There are 30000 + values in my array, so I've split the update accross 6 scripts. Very messy, but the requestor is not available to provide a sql statement.

 

[Thargy]

whoa there wotsit!

What's going on here.

Are you attempting a one-off bulk update of a table, with 30,000 values?

Please state what you're trying to achieve in English, not Oracle-ish, so I can get a handle on it a wee bit.

T

Grinding away at things Oracular

 

[whatsinaname99]

Sure. And thank you in advance for help.

I have been given a list of 30,000+ employee IDs. For each of these employees, I need to execute a stored procedure that accepts the employee ID as one of several parameters. I don't have access to change the stored procedure. I also don't have access to the SQL statement(s) the requestor used to pull the data.

 

[Thargy]

wotsit,

in English, what are you tasked to do?

If you need to update a table with 30,000 values, then the MERGE command looks like a good idea.

If you have to load gigabytes of data from text files, use external tables.

If you have to do complex number crunching, then some pl/sql is your best buddie.

Please let me know what you're trying to do, based on what I presume is a unique identifier for each employee. Is it a primary key on the table in question?

T

Grinding away at things Oracular

 

[whatsinaname99]

I need to execute a stored procedure for 30K employees. I've been given a list of employee IDs which uniquely identify each employee. Now, it would have been easy if I had a sql statement that contained the employee IDs. Then, I'd just create a cursor and loop through it, passing each employee ID to the stored procedure. However, I don't have a SQL statement, just a list of employee IDs in Excel.

 

[Thargy]

wotsit,

I've just scanned my last post, and it's probably useless to you - sorry.

If you need to execute a stored procedure multiple times, then use sqlplus.

you can issue a command such as

EXEC PROCEDURE_WOTSIT(EMPLOYEE_ID, PARAMETER2, PARAMETER3);

This will run the procedure for you, with the passed parameters and do the job.

You just have to create a text file with the 30,000 calls (generate it, don't write it!) and then run from sqlplus.

TOAD can also do this, but be advised that it is significantly slower that sqlplus when dealing with large amounts of text, because it is a GUI, and suffers the overhead of updating the screen all the time.

T

Grinding away at things Oracular

 

[whatsinaname99]

What if I wanted to do it within the context of an Oracle stored procedure? Is there any data structure that could handle 30K values?

Thanks again!

 

[Thargy]

wotsit,

I reckon you're in serious danger of succeeding here.

If you have an excel list 30,000 long, that's cool.

Create a text file with an execute statement as I just suggested.

I love Excel for its ability to add columns.
With your data, insert a column before it all, and autofill it with the word 'EXEC' and then create a column which is the concatenation of the exec, the employee id, and the other parameters involved.

I do this a lot (which is why I am familiar) and you should end up with a right most column in Excel which is something like

EXEC STORED_PROCEDURE_NAME (EMPLOYEE_ID, PARAM1,PARAM2... PARAMN);

Concatenation is easy in excel, (don't forget the trailing seim_colon). Save this to a text file, and then run it from sql plus.

Obviously do a test with 2 or 3 lines, and when you've got the syntax right, go for the 30,000.

T

Grinding away at things Oracular

 

[Thargy]

oh,

and if you're worried, you can always cut and paste the generated text into TOAD, and let it run the stored procedure for you.

T

Grinding away at things Oracular

 

[whatsinaname99]

Thank you! Much appreciated.

 

[Thargy]

oooo! oooo!

So, how did things turn out wotsit?

My nose in bothering me, so 'fess up, what's the skinny?

T

Grinding away at things Oracular

 

[whatsinaname99]

I have yet to try this. I am at home and researching the problem but will be back in the office tomorrow to execute. I'll let you know how I fare. Thank you! I do appreciate your help.

 

[Thargy]

ah ha!

I hereby invoke the "out of hours" clause (which is known to all users of TT).

There was I, thinking that a lonesome Yank was beavering away at his PC, when all along, you're my side of the pond, and at home, right?

My rates for out of hours support, average out at one pint of Guinness for every half hour of effort. Can you pony up?

Yours in Thirst

T

Grinding away at things Oracular

 

[whatsinaname99]

I'm sorry! I didn't mean to be disingenuous.
I am in the US and am indeed under pressure. I was to have this done today at the latest but, along with my coworkers, had to leave the office due to inclement weather (snowstorm in Midwest). What would ordinarily be time off is now being spent researching a big technical problem. I know this will be waiting for me first thing tomorrow morning, with everyone asking for a status check.

 

[SantaMufasa]

Actually, Tharg, I believe that WhatsInAName is more likely a badger (from Wisconsin) which is 6 hours behind you (if I'm not mistaken).

None the less, Tharg deserves a pint or two for his fine efforts in your behalf, Whats.
[tt]
,----------- Root Beer
| ,--------- Ginger Ale
| | ,---- Martinelli's Sparkling Cider
| | | ,-- Vanilla Caffeine-free Diet Coke
V V V V [/tt]



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[whatsinaname99]

I've officially thanked T (sorry, didn't understand the jargon of this site).
BTW, I am indeed a Badger and the weather here is quite dangerous.