Re: Get ref cursor data dynamically - HOW ????

Remember if you dynamically create a table then it issues a commit and you can't roll it back. eg
insert into table_blah(1);
create temp_table (number not null);
rollback;
if you then do
select * from table_blah;
You will get
1
(assuming nothing else in the table)
Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"V. Pryadkin" <vpryadkin_at_yahoo.com> wrote in message
news:33c9717f.0302141517.60754848_at_posting.google.com...


> Jim,

>

> I totally agree with you, and what is more I am pretty sure that any


> dynamical things must be avoided whenever it's possible, especially

> dynamic DDL's. So I'd say my strange topic is rather an indicator of

> poor "dynamic" experience, but not poor programming bents :o).

>

> If it's interesting I can explain what I really want, probably there

> are known solutions which I just don't know or you can just give me

> good advice. The issue is called "regression test". Shortly that means

> "run pl/sql stuff -> save the results somehow -> rollback the database

> to the initial state -> run same stuff again -> match out what you've

> got now with what you saved during the first pass". In fact results

> mean three things:

> 1) output variables

> 2) ref cursors

> 3) changes in database tables

>

> Sure, within the session I can use global temporary tables, nested

> tables etc as a storage. Actually there is no problem with (1) and

> (2), there are almost no dynamic nor temporary things to do. But (3)

> means a bunch of various record structures, so honestly I haven't

> decided yet how to "save a ref cursor twice & compare" easily. But at

> least I need to manage ref cursors dynamically, there are no

> questions. Let's say QA guys will use my testing API's, but definitely

> they are not supposed to be very good in pl/sql to create their

> testing scripts, especially hardcoding record types for each given ref

> cursor or something like that.

>

> Steve.

>

> "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message

 news:<Wh83a.97484$2H6.1439_at_sccrnsc04>...


> > In addition to Brian's observation I strongly recommend you don't

> > dynamically create temporary tables.  This is usually an indication of

 poor


> > programming practice. (read crutch from other databases where it is a

> > necessity)  It will lead to piss poor performance and scalability.  If

 you


> > really really really just gotta have them then use global temporary

 tables


> > (see docs).  The use of inline views etc. makes it highly unlikely that

 you


> > need to create temporary tables on the fly.  (see asktom.oracle.com for

> > discussions and other options)

> > Jim