Re: OCI question
Date: 1996/08/01
Message-ID: <4tr6rg$dol_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <31fe3177.685147019_at_news.tncnet.com>, simonl_at_dataworks.com (Simon Lee) writes:
|> From: simonl_at_dataworks.com (Simon Lee)
|> Newsgroups: comp.databases.oracle
|> Subject: Re: OCI question
|> Date: Tue, 30 Jul 1996 16:05:18 GMT
|> Organization: DataWorks Corporation
|> Lines: 40
|> Message-ID: <31fe3177.685147019_at_news.tncnet.com>
|> References: <31fd4110.623604205_at_news.tncnet.com> <4tjl62$o4b_at_inet-nntp-gw-1.us.oracle.com>
|> NNTP-Posting-Host: 206.126.107.41
|> X-Newsreader: WinVN 0.99.7
|>
|> On 30 Jul 1996 00:32:02 GMT, surman_at_dlsun338.us.oracle.com (Scott
|> Urman) wrote:
|>
|> :In article <31fd4110.623604205_at_news.tncnet.com>, simonl_at_dataworks.com (Simon Lee) writes:
|> :|>
|> :|> Is it just me or does Oracle's OCI library suck? I'm writing an
|> :|> application, and OCI doesn't even return the number of columns from a
|> :|> query. Does anyone know how I can write a "fetch" function that after
|> :|> given a SQL statement, I can call fetch(), which will retrieve any
|> :|> results pending? The problem I have is trying to figure out how many
|> :|> columns there will be, and to allocate memory for the define structure
|> :|> per column, without losing performance.
|> :|>
|> :
|> :Use odescr(). It will tell you exactly what Oracle is planning on returning -
|> :each column, its datatype, and its size. You then allocate memory to hold this
|> :info and point to it using odefin(). Then fetch using ofen() or ofetch().
|>
|> Hi Scott,
|>
|> The problem I'm having is not how to get the info, but to figure out
|> the most efficient way to allocate memory to store the info for a
|> dynamic SQL query. If I get a statement like "select * from table", I
|> don't know how many columns will be returned, so I don't know how much
|> memory to allocate without doing a lot of little allocations. I could
|> do allocate a description structure, call odescr(), allocate a define
|> structure, call odefin(), then do it again until I run out of columns,
|> but I have 2 problems then: 1. how do I keep track of these structures
|> so I can delete them after all my fetching, and 2. it's inefficient
|> having to allocate 2 chunks of memory for each column each time I do a
|> fetch. It would be nice if the odescr() function returned how many
|> columns are in the query.
You don't have to do the defines in the loop for odescr() - it is just convenient to do so. You can loop until odescr() returns the ORA-1007 error, saving the column information each time (name, datatype, length, etc.). Then you can allocate all necessary memory at once, and call odefin() in another loop.
|>
|> Also, when I do a ofetch(), does it only return those columns defined,
|> and if so, what happens when I call ofetch again? Can I get the next
|> column by call odefin() then ofetch() for each column, or does ofetch
|> move on to the next row?
ofetch() moves on to the next row. It will return all the columns for the current row, placing them into the addresses you specified in odefin(). The next ofetch() will overwrite the data fetched for the prior row. You can also do batch fetches by defining arrays rather than scalars, and using oexfet() and/or ofen() to fetch multiple rows at one time.
|>
|> Thanks,
|> -Simon
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Thu Aug 01 1996 - 00:00:00 CEST