Create Oracle Stored Procedure and execute it in a SQL Stored Procedure

Below is SQL stored procedure I use to get data from a Oracle table (using Lined Server) and then display it in a SSRS report drop down parameter.

Could I create this stored procedure in Oracle then call the Oracle stored procedure from my SQL stored procedure.

I would like to eliminate the inserting of the Oracle data into a temporary table then running a Select over the temporary table.

I would rather execute a Oracle stored procedure from the SQL stored procedure that just does the Select (if possible).

I am guessing that would save some time and I do this in several stored procedures over much larger Oracle tables.

The Oracle table is called GPCOMP1.GPPROB.

Also, what would the Oracle stored procedure look like  (just to get me started in right direction).

Any help is appreciated.



ALTER PROCEDURE [NonFreight].[procRptLstSalesArea]  
AS
  BEGIN
      SET NOCOUNT ON;
     
      DECLARE @sql VARCHAR(MAX)
           
      --Build #SalesArea Sql table
      CREATE TABLE [NonFreight].[#SalesArea]
        (
           [SalesAreaCode] VARCHAR(10),
           [SalesAreaName] VARCHAR(50)
        )
      ON [PRIMARY]
                   
        SET @Sql = 'SELECT DISTINCT C.SALESAREA as SalesAreaCode, C.SALESAREA as SalesAreaName
                FROM GPCOMP1.GPPROB C'                  
                   
        SET @Sql = N'INSERT INTO [NonFreight].[#SalesArea]
             SELECT  * from openquery
             ([GPNF],    ''' + Replace(@Sql, '''', '''''') + ''')'        

      EXEC (@Sql)

      -- Dropdown query
      SELECT SalesAreaCode,
             SalesAreaName,
             Count(*) OVER(PARTITION BY NULL) AS SalesAreaCount
      FROM   NonFreight.#SalesArea
      WHERE  SalesAreaName != ''
      ORDER  BY SalesAreaName
  END
thayduckProgrammer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
Oracle and SQL Server are very different systems.  Yes, they are both "SQL compliant" and they both store data in tables that contain rows and columns.  But, that is about where their similarity ends.

Here are three of your questions/comments with my suggestions:
1. "Could I create this stored procedure in Oracle then call the Oracle stored procedure from my SQL stored procedure."
Yes, that should be possible.  This assumes that you will create the Oracle stored procedure just once, outside of your SQL Server job/procedure that will call the Oracle stored procedure.

2. "I would like to eliminate the inserting of the Oracle data into a temporary table then running a Select over the temporary table."
Usually in Oracle temporary tables are not required.  They are supported in Oracle.  And for some complex data conditions they can be helpful.  But unlike in SQL Server, "global temporary tables" in Oracle are *NOT* created dynamically!  In Oracle, they are created just once, then re-used when called.

3. "I would rather execute a Oracle stored procedure from the SQL stored procedure that just does the Select (if possible)."
I am not sure here.  Keep in mind that in Oracle, a stored procedure is not the only way (and not always the best way) to return a result set from the database.  Sometimes, simply submitting a SQL query to Oracle is the most efficient way to get the job done.  There may be security implications here though, so it may be safer (at least from the Oracle DBA's perspective) if the Oracle database does not support "ad-hoc" queries coming from a non-Oracle application.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.