Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
it's assigned by:
:delpt_cursor := radixbrc.retMMA ddrsList(:zip,: RtNum,:ih_date)
So, now I have all the data that was retrieved in the stored procedure
in a cursor, and I can loop through it:
for (;;)
{
get each piece of data
put data in flat file
}
The problem with this approach is that it is too slow..
So, i'm looking at instead of returning a cursor, returning a host
array... does it make sense to do this instead? If so, how do you get
the data out of the host array in the Pro*C code?
Thank you for any help!!
Michael 4 10904 mi***********@y ahoo.com (Michael Trosen) wrote in message news:<54******* *************** ****@posting.go ogle.com>... Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
it's assigned by: :delpt_cursor := radixbrc.retMMA ddrsList(:zip,: RtNum,:ih_date)
So, now I have all the data that was retrieved in the stored procedure in a cursor, and I can loop through it:
for (;;) { get each piece of data put data in flat file }
The problem with this approach is that it is too slow..
So, i'm looking at instead of returning a cursor, returning a host array... does it make sense to do this instead? If so, how do you get the data out of the host array in the Pro*C code?
Thank you for any help!!
Michael
First, is it really necessary to get the data via a stored procedure
rather than just querying it directly?
Either way make sure the problem is in passing the data back to the
program and not in the performance of the query that builds the
cursor. In order words make sure you do not have a quuery statement
tuning issue instead of a retrieval issue.
If you can query the data directly you should be able to replace your
single row processing loop with a much faster array fetch. Oracle
will shove the data into a C language array and you print it from
there.
HTH -- Mark D Powell --
Mark,
thank you for your reply.
The reason I want the pro*c program to call the stored procedure
instead of just getting a cursor with a select in the pro*c program is
because I want to keep all the business rules for getting the data in
a 'centralized' location. There are many different applications that
need to get data from the database, and rather than having a bunch of
business rules scattered throughout various programs, they're
'centralized' in the database and stored procedures.
Is there a way to recieve in the pro*C program an array of structures
returned from the stored procedure? For example, a new version of my
stored procedure works like this:
1. get the data
2. eliminate some of the data and put non eliminated data into a
record
with the following definition:
TYPE mm_addrs_rec IS RECORD (
walk_seq NUMBER (9),
dpbc_dgts NUMBER (2),
dpbc_chk_dgt NUMBER (1),
drop_seq NUMBER (3),
st_num VARCHAR (10),
st_pre_direct VARCHAR (2),
st_name VARCHAR2 (28),
st_suffix VARCHAR2 (4),
st_post_direct VARCHAR2 (2),
sud VARCHAR2 (4),
sun VARCHAR2 (8),
plus4 VARCHAR2 (4),
seas_ind VARCHAR2 (1),
atz VARCHAR2 (2),
profile_type_cd NUMBER,
addrs_id NUMBER (10),
supr_delv_ind VARCHAR2 (1)
);
3. each record is thrown into a varray, defined as:
TYPE varpcdaddrs IS VARRAY (10000) OF mm_addrs_rec;
4. The stored procedure returns the array to the pro*c
program
The problem I have is I don't know how to get the array in the pro*c
program
and use the data within it..
Any suggestions?
Thank you!
Michael Trosen Ma*********@eds .com (Mark D Powell) wrote in message news:<26******* *************** ****@posting.go ogle.com>... mi***********@y ahoo.com (Michael Trosen) wrote in message news:<54******* *************** ****@posting.go ogle.com>... Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
it's assigned by: :delpt_cursor := radixbrc.retMMA ddrsList(:zip,: RtNum,:ih_date)
So, now I have all the data that was retrieved in the stored procedure in a cursor, and I can loop through it:
for (;;) { get each piece of data put data in flat file }
The problem with this approach is that it is too slow..
So, i'm looking at instead of returning a cursor, returning a host array... does it make sense to do this instead? If so, how do you get the data out of the host array in the Pro*C code?
Thank you for any help!!
Michael
First, is it really necessary to get the data via a stored procedure rather than just querying it directly?
Either way make sure the problem is in passing the data back to the program and not in the performance of the query that builds the cursor. In order words make sure you do not have a quuery statement tuning issue instead of a retrieval issue.
If you can query the data directly you should be able to replace your single row processing loop with a much faster array fetch. Oracle will shove the data into a C language array and you print it from there.
HTH -- Mark D Powell --
"Michael Trosen" <mi***********@ yahoo.com> wrote in message
news:54******** *************** **@posting.goog le.com... Mark,
thank you for your reply.
The reason I want the pro*c program to call the stored procedure instead of just getting a cursor with a select in the pro*c program is because I want to keep all the business rules for getting the data in a 'centralized' location. There are many different applications that need to get data from the database, and rather than having a bunch of business rules scattered throughout various programs, they're 'centralized' in the database and stored procedures.
Is there a way to recieve in the pro*C program an array of structures returned from the stored procedure? For example, a new version of my stored procedure works like this:
1. get the data 2. eliminate some of the data and put non eliminated data into a record with the following definition: TYPE mm_addrs_rec IS RECORD ( walk_seq NUMBER (9), dpbc_dgts NUMBER (2), dpbc_chk_dgt NUMBER (1), drop_seq NUMBER (3), st_num VARCHAR (10), st_pre_direct VARCHAR (2), st_name VARCHAR2 (28), st_suffix VARCHAR2 (4), st_post_direct VARCHAR2 (2), sud VARCHAR2 (4), sun VARCHAR2 (8), plus4 VARCHAR2 (4), seas_ind VARCHAR2 (1), atz VARCHAR2 (2), profile_type_cd NUMBER, addrs_id NUMBER (10), supr_delv_ind VARCHAR2 (1) );
3. each record is thrown into a varray, defined as: TYPE varpcdaddrs IS VARRAY (10000) OF mm_addrs_rec;
4. The stored procedure returns the array to the pro*c program
The problem I have is I don't know how to get the array in the pro*c program and use the data within it..
Any suggestions?
Thank you! Michael Trosen Ma*********@eds .com (Mark D Powell) wrote in message
news:<26******* *************** ****@posting.go ogle.com>... mi***********@y ahoo.com (Michael Trosen) wrote in message
news:<54******* *************** ****@posting.go ogle.com>... Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
it's assigned by: :delpt_cursor := radixbrc.retMMA ddrsList(:zip,: RtNum,:ih_date)
So, now I have all the data that was retrieved in the stored procedure in a cursor, and I can loop through it:
for (;;) { get each piece of data put data in flat file }
The problem with this approach is that it is too slow..
So, i'm looking at instead of returning a cursor, returning a host array... does it make sense to do this instead? If so, how do you get the data out of the host array in the Pro*C code?
Thank you for any help!!
Michael
First, is it really necessary to get the data via a stored procedure rather than just querying it directly?
Either way make sure the problem is in passing the data back to the program and not in the performance of the query that builds the cursor. In order words make sure you do not have a quuery statement tuning issue instead of a retrieval issue.
If you can query the data directly you should be able to replace your single row processing loop with a much faster array fetch. Oracle will shove the data into a C language array and you print it from there.
HTH -- Mark D Powell --
What you are describing is a cursor. (using an array interface to retrieve
more than 1 row at a time) You can retrieve a cursor from a stored
procedure(ref cursor). You can certainly get the information from a stored
procedure and several tables.
Jim
"Jim Kennedy" <ke************ *************** *@attbi.net> wrote in message news:<rrI0c.945 47$4o.117648@at tbi_s52>... "Michael Trosen" <mi***********@ yahoo.com> wrote in message news:54******** *************** **@posting.goog le.com... Mark,
thank you for your reply.
The reason I want the pro*c program to call the stored procedure instead of just getting a cursor with a select in the pro*c program is because I want to keep all the business rules for getting the data in a 'centralized' location. There are many different applications that need to get data from the database, and rather than having a bunch of business rules scattered throughout various programs, they're 'centralized' in the database and stored procedures.
Is there a way to recieve in the pro*C program an array of structures returned from the stored procedure? For example, a new version of my stored procedure works like this:
1. get the data 2. eliminate some of the data and put non eliminated data into a record with the following definition: TYPE mm_addrs_rec IS RECORD ( walk_seq NUMBER (9), dpbc_dgts NUMBER (2), dpbc_chk_dgt NUMBER (1), drop_seq NUMBER (3), st_num VARCHAR (10), st_pre_direct VARCHAR (2), st_name VARCHAR2 (28), st_suffix VARCHAR2 (4), st_post_direct VARCHAR2 (2), sud VARCHAR2 (4), sun VARCHAR2 (8), plus4 VARCHAR2 (4), seas_ind VARCHAR2 (1), atz VARCHAR2 (2), profile_type_cd NUMBER, addrs_id NUMBER (10), supr_delv_ind VARCHAR2 (1) );
3. each record is thrown into a varray, defined as: TYPE varpcdaddrs IS VARRAY (10000) OF mm_addrs_rec;
4. The stored procedure returns the array to the pro*c program
The problem I have is I don't know how to get the array in the pro*c program and use the data within it..
Any suggestions?
Thank you! Michael Trosen Ma*********@eds .com (Mark D Powell) wrote in message news:<26******* *************** ****@posting.go ogle.com>... mi***********@y ahoo.com (Michael Trosen) wrote in message news:<54******* *************** ****@posting.go ogle.com>... > Hi Everyone, > > I hope someone can help, I'm pretty new to pro*c programming. > > I have the following application setup: > > a pro*c program calls a stored procedure and recieves a cursor back: > > the cursor is defined as: SQL_CURSOR delpt_cursor > > it's assigned by: > :delpt_cursor := radixbrc.retMMA ddrsList(:zip,: RtNum,:ih_date) > > So, now I have all the data that was retrieved in the stored procedure > in a cursor, and I can loop through it: > > for (;;) > { > get each piece of data > put data in flat file > } > > The problem with this approach is that it is too slow.. > > So, i'm looking at instead of returning a cursor, returning a host > array... does it make sense to do this instead? If so, how do you get > the data out of the host array in the Pro*C code? > > Thank you for any help!! > > Michael
First, is it really necessary to get the data via a stored procedure rather than just querying it directly?
Either way make sure the problem is in passing the data back to the program and not in the performance of the query that builds the cursor. In order words make sure you do not have a quuery statement tuning issue instead of a retrieval issue.
If you can query the data directly you should be able to replace your single row processing loop with a much faster array fetch. Oracle will shove the data into a C language array and you print it from there.
HTH -- Mark D Powell --
What you are describing is a cursor. (using an array interface to retrieve more than 1 row at a time) You can retrieve a cursor from a stored procedure(ref cursor). You can certainly get the information from a stored procedure and several tables. Jim
Mike, sorry I missed your response till now. I have never used the
varray datatype. I would have probably just used a pl/sql table
(array) of records.
And while using packaged or stored code is great for update processes
I would not use these for selecting data if a view or direct select
could be done.
HTH -- Mark D Powell -- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: June Moore |
last post by:
Hi all,
I have a stored procedure that return a resultset
e.g. stored proc: get_employee_details
select emp_id, emp_name, emp_salary, emp_position
from empoloyee
I would like to write another stored procedure that executes the above
stored procedure - returning the same number of records but it will
only show 2 columns
|
by: Jen S |
last post by:
I feel like I'm missing something obvious here, but I'm stumped...
I have a stored procedure with code that looks like:
INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('An error occurred in the stored proc.', 16, 1);
|
by: David Lozzi |
last post by:
Here is the proc:
CREATE PROCEDURE .
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS
IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
|
by: Learner |
last post by:
Hi there,
I have a storec proc that schedules a Sql job and finally it returns
0 then it was successfull and if it returns 1 then its unsuccessful.
Now when i run the stored proc in the query analyzer its returning
SQLServerAgent is not currently running so it cannot be notified of
this action.
Stored Procedure: aisinspect.dbo.SP_ChangeInspectionDateNew
|
by: balaji krishna |
last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i don't know how to return the rows the cursor has opened and I don't know how to handle the return set from the proc in my java code.
My main problem with that proc is that whether I can retun the result set from the proc without closing the cursor...
| |
by: =?Utf-8?B?Vmlua2k=?= |
last post by:
Hello Everyone,
I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is returning
some value. I cannot see that value in my .net code
Below is my .net code
OracleParameter mbrid = new OracleParameter("RET_MBRID", OracleType.Number);
mbrid.Direction = ParameterDirection.Output;
|
by: =?Utf-8?B?Vmlua2k=?= |
last post by:
Hello Everyone,
I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is returning
some value. I cannot see that value in my .net code
Below is my .net code
OracleParameter mbrid = new OracleParameter("RET_MBRID", OracleType.Number);
mbrid.Direction = ParameterDirection.Output;
|
by: bogdan |
last post by:
Hi,
I have a stored procedure that returns a single value. Example:
SELECT @RowCount = COUNT(*) FROM t WHERE
RETURN @RowCount
I created a data set, table adapter, and adapter's method configured for the
stored proc and as returning a single value. The wizard created an adapter
method that calls SqlCommand.ExecuteScalar(). The problem is that
|
by: Michael Trosen |
last post by:
Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |