Invoking Oracle Stored-Procedure from Sybase Stored-Procedure

I have a peculiar design constraint.

I need to invoke a Oracle Stored-Procedure pass it 1
parameter and use the result-set returned by the oracle
stored procedure in my Sybase stored procedure.

In affect I need to invoke a Oracle Stored-Procedure
returning a result-set from a Sybase Stored-Procedure.

I checked up and found Direct-Connect could be one option
where-in I could invoke a rpc. Has any-one tried using
Direct-Connect ?

Also I remember Sybase also had a product\technology called
Omni-Connect which could be used to connect to Oracle
database.

Is Omni-Connect still available with ASE 12.x ? if so then
what are the trade-offs between using Omni-Connect to
Direct-Connect ?

Thanks,

Abishek
0
Abishek
5/21/2004 6:12:39 AM
sybase.ase.administration 7058 articles. 2 followers. Follow

2 Replies
740 Views

Similar Articles

[PageSpeed] 32

Abishek Bhargava wrote:

> I have a peculiar design constraint.
> 
> I need to invoke a Oracle Stored-Procedure pass it 1
> parameter and use the result-set returned by the oracle
> stored procedure in my Sybase stored procedure.
> 
> In affect I need to invoke a Oracle Stored-Procedure
> returning a result-set from a Sybase Stored-Procedure.
> 
> I checked up and found Direct-Connect could be one option
> where-in I could invoke a rpc. Has any-one tried using
> Direct-Connect ?
> 
> Also I remember Sybase also had a product\technology called
> Omni-Connect which could be used to connect to Oracle
> database.
> 
> Is Omni-Connect still available with ASE 12.x ? if so then
> what are the trade-offs between using Omni-Connect to
> Direct-Connect ?
> 
> Thanks,
> 
> Abishek

DirectConnect for Oracle is the solution for you.  OmniConnect was merged
into ASE (CIS/Omni) for the ASE <=> ASE connections.  Other connections
require DirectConnect *. 


-- 
Jason L. Froebe

Chris Knight: Moles and trolls, moles and trolls, work, work, work, work,
work. We never see the light of day. We plan this thing for weeks and all
they want to do is study. I'm disgusted. I'm sorry but it's not like me,
I'm depressed. There was what, no one at the mutant hamster races and we
had one entry into the Madame Curie look-alike contest and he was
disqualified later. Why do I bother? 

Bookcrossing (http://www.bookcrossing.com)
WebBlog http://www.livejournal.com/users/jfroebe

TeamSybase (http://www.teamsybase.com)
ISUG member (http://www.isug.com)
Chicago Sybase Tools User Group (http://www.cpbug.com)
0
Jason
5/22/2004 9:40:41 PM
Jason,

Thanks for your response. I'll try and use Direct-Connect to
solve my problem and will soon update you on how it went.

Thanks a lot !!!

Abishek


> Abishek Bhargava wrote:
>
> > I have a peculiar design constraint.
> >
> > I need to invoke a Oracle Stored-Procedure pass it 1
> > parameter and use the result-set returned by the oracle
> > stored procedure in my Sybase stored procedure.
> >
> > In affect I need to invoke a Oracle Stored-Procedure
> > returning a result-set from a Sybase Stored-Procedure.
> >
> > I checked up and found Direct-Connect could be one
> > option where-in I could invoke a rpc. Has any-one tried
> > using Direct-Connect ?
> >
> > Also I remember Sybase also had a product\technology
> > called Omni-Connect which could be used to connect to
> > Oracle database.
> >
> > Is Omni-Connect still available with ASE 12.x ? if so
> > then what are the trade-offs between using Omni-Connect
> > to Direct-Connect ?
> >
> > Thanks,
> >
> > Abishek
>
> DirectConnect for Oracle is the solution for you.
> OmniConnect was merged into ASE (CIS/Omni) for the ASE <=>
> ASE connections.  Other connections require DirectConnect
> *.
>
>
> --
> Jason L. Froebe
>
> Chris Knight: Moles and trolls, moles and trolls, work,
> work, work, work, work. We never see the light of day. We
> plan this thing for weeks and all they want to do is
> study. I'm disgusted. I'm sorry but it's not like me, I'm
> depressed. There was what, no one at the mutant hamster
> races and we had one entry into the Madame Curie
> look-alike contest and he was disqualified later. Why do I
> bother?
>
> Bookcrossing (http://www.bookcrossing.com)
> WebBlog http://www.livejournal.com/users/jfroebe
>
> TeamSybase (http://www.teamsybase.com)
> ISUG member (http://www.isug.com)
> Chicago Sybase Tools User Group (http://www.cpbug.com)
0
Abishek
5/24/2004 3:47:59 AM
Reply:

Similar Artilces:

Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')  but I also have a second stored procedure that needs to make reference to&n...

How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you.    Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...

calling a stored procedure from a stored procedure
Hi Group! I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL. I have a sproc which breaks a given string-parameter into pieces. It's result set contains the pieces, e.g. last line of sproc: "select item from mytable;". This sproc works fine. Now I need to call this procedure from another procedure which has a variable result set. I tried the following: <snip... set ll_retval = 0; call sp_breakstring(as_bzp,','); BreakLoop: while ll_retval <> 100 and not ll_retval < 0 loop set li_zaehler = li_zaehler + 1; fetch next sp_breakstrin...

Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

Call as stored procedure from another stored procedure
I am using a stored procedure (SP_A) to insert rows to a table. This is done by the cursor within the procedure. Value for one of the column is provided by SP_B. My problem is that when i call SP_B in SP_A it does not return any value (rather i do not know how to capture it from SP_A). If executed individually it returns a value. Please help with example if something specific needs to be taken care. Suresh wrote: > > Please help with example if something specific needs to be > taken care. Please always post the version and build number of ASA that you are using (e....

Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - Verify if protocol exists and fetch protocol id - add activity - add document - add activity protocol reeturn document id */ select id from protocols where protocols.protname = :protocol into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...

Calling Stored Procedure fromanother Stored Procedure
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames I believe you want to use 'EXEC' abad...

Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...

Calling stored procedure from another stored procedure
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath Yes, you can. Just use EXEC usp_secondStoredProc @params inside your first SP.Nick...

Running stored procedure within a stored procedure
I have a custom built users table for storing some values and I am also utilizing the aspnet_Users table. I want to delete a user from my users tables then execute the aspnet_Users_DeleteUser sproc and pass into the stored procedure the username of the user to delete because the DeleteUser method requires this. When I execute the command from within my asp.net web application I get the exception below. Both values are being obtained from the asp.net application and are represented in my DAL that is also below. Any thoughts as to why I am receiving this exception? Thanks. Procedure or functi...

how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------ Inside your first Stored Procedure: exec <name of SP to run> <Eventual Parameters this SP requires> Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se--------------------------------...

Running a stored procedure within a stored procedure
I have a stored procedure (A), which I need to call from another stored procedure (B). The data pulled from (B) is then inserted into a temporary table. The problem I am having is the stored procedure is in a loop, and it only runs once. I need it to run each time the statement loops. For example: In the cursor I have, there are 3 rows, so the statement should return 3 rows. However, only 1 row is returned, it looks like the stored procedure is only one once. What do I have to do to get it to loop?? DECLARE dCURSOR CURSOR FOR select ID, value1, value2 from table; OPEN dCU...

help creating a procedure that iterates thru all my stored procedures and copies them to new stored procedures with new names
I have this sql DB and I want to clone it in a way. I want to run a second web site off the same SQL database. So all my tables and stores procedures currently aare named with a 3 letter prefix that identifies the application. I need to clone all the stored procedures and give them a new name basicly changing the 3 letter prefix to another one. Also if that procedure could once it has a stored procedure open, replace all the 3 letter prefix (of the table names) inside the stored procedure definition itself.  I know I could do all this manualy but there are over 400 stored procedures a...

stored procedure. The c# code run stored procedure.
 CREATE PROCEDURE GetThreadMessages @iThreadID int AS SELECT message_id, thread_id, user_id, first_names, last_name, email, subject, body, date_submitted, category_name, category_id, last_edited FROM message_view WHERE thread_id = @iThreadID ORDER BY date_submitted asc For update ---------------------------------------------------------------------------------------- CREATE PROCEDURE UpdateThreadMessages @iThreadID int @name varchar(50) AS UPDATE message_view SET first_names = @name FROM message_view ...

Web resources about - Invoking Oracle Stored-Procedure from Sybase Stored-Procedure - sybase.ase.administration

Electronically stored information (Federal Rules of Civil Procedure) - Wikipedia, the free encyclopedia ...
^ Electronically Stored Information: The December 2006 Amendments to the Federal Rules of Civil Procedure , Kenneth J. Withers, Northwestern ...

Calling an Oracle stored procedure with a Table parameter with Spring’s StoredProcedure class
I don’t normally do this sort of thing, but this took my colleague Tony Lawrence and me a while to figure out and we didn’t find a good explanation ...

Strategy: Cache Stored Procedure Results
... I've heard of caching store procedure results before. It's like memoization in the database. Brent Ozar covers this idea in How to Cache Stored ...

Espresso Logic Turns Stored Procedures into RESTful APIs
Relational databases have been around for multiple decades, and there’s a lot of logic tied up in stored procedures that most organizations would ...

Resources last updated: 11/25/2015 4:01:20 AM