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  10901  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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
