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]

huh?

Badger.

Dave, help me here!

will call.. I sense that an Americanism is on the loose in the hoose!
T

Grinding away at things Oracular

 

[SantaMufasa]

FYI, Whatsin, I spoke on the phone just now to Tharg (in the UK) and explained about our penchant for choosing mascots for teams here in the U.S., which they don't have/do in the U.K.

So, Tharg understands now.

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

 

[Welshbird]

Am I in the right forum here?

Tharg - I'll stand you a Guinness in lieu. But not until next week.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[whatsinaname99]

Thanks for filling Tharg in, Santa.

To update you on what happened today, I should first say that the problem was a little more complicated than I'd outlined. There was more than one subprocedure to be called and some subprocedures took as paramaters the OUT variables of previous procedures.
I fought with it a little more. Ended up using the format code function in TOAD and was then able to execute the script in SQLPlus and TOAD without the numbers in my array being truncated. The original code with the array that was being truncated was prepped in notepad. Apparently this made some kind of difference.
So I sent my code off to the DBA to run in the prod environment. Lo and behold they got an error in SQL Plus (they don't have TOAD) saying sufficient memory could not be allocated from the shared pool. The actual file, now that it was properly formatted, still had an array of thousands of thousands of elements, so the procedure had thousands of lines. In any case at this point, I prevailed on the requestor who had returned from vacation to send me the original sql statement. Problem solved.

Thank you, T, for stretching yesterday and trying to help me out. You still deserve a star.

 

[sem]

First of all you don't need single pl/sql block: just declare bind variables in sql*plus and feel free to execute all 30000 small and similar commands:

var emp_id number
exec :emp_id:=1
EXEC STORED_PROCEDURE_NAME (:emp_id, PARAM1,PARAM2... PARAMN)
exec :emp_id:=2
EXEC STORED_PROCEDURE_NAME (:emp_id, PARAM1,PARAM2... PARAMN)

Note that the only changing statement is the emp_id assignment.


Another solution is to create [temporary] table, load that ID's there and make a simple loop through that records in pl/sql. Then just drop the table.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Dima,

an excellent point. I failed to even suggest the use of bind variables, for which omission I apologise. Wotsit, using bind variables is the way to go in Oracle, when it comes to achieving performance.

Kudos to you Dima.

Regards

T

Grinding away at things Oracular