Cursor Output variable

[thendrickson]

I am new to Oracle and have what should be a very basic question

Basically, I cannot find out the syntax to execute an Oracle proc that returns a cursor as output. I have an cursor defined as a output prameter from an Oracle proc as well as 2 input parameters

I need to call the Oracle proc from SQL Server, passing in the variables using the OpenQuery function.

SImply put I want:
Select * from OPENQUERY(OracleLinkedServer,"EXECUTE OracleProc Input1, input2, curResults") "curResults" Kills me.

I doubt the versions I am using effect this but I am using Oracle 10g and SQL 2008

 

[Dagon]

I don't know much about SQL Server but looking at the documentation for OpenQuery, it seems to take a SQL statement as its parameter. What you've passed isn't a SQL statement - it's a call to execute a stored procedure. Can you not just pass the SQL statement that your procedure calls directly into OpenQuery?

For Oracle-related work, contact me through Linked-In.

 

[thendrickson]

I am afraid I must have terrible communication skills so please bear with me.

What I intend to do is to execute an existinf oracle proc and use the results.

The OpenQuery function is a "pass through query" that would execute on the Oracle box.

Your suggestion worked for me in the past for simpler Oracle procs.

The Oracle proc I intend to call is quite complex (2000+ lines)

I have not figured out how to execute an Oracle proc that returns an output cursor

So, for sake of discussion How would I "EXECUTE OracleProc Input1, input2, curResults" where curResults is a cursor output parameter if I were doing it from another Oracle procedure? The syntax should be the same as it would be calling the proc from the OPENQUERY Function in SQL Server

I have to be missing something very basic.

 

[Turkbear]

Hi,
You probably need to create a varible to hold the results - but, from what I have read, OpenQuery will only return the first item niot multiple records, so if your cursor have multiple records it may fail. Try something like ( I do not know SqlServer syntax):

@MyDataFromOracle = Select * from OPENQUERY(OracleLinkedServer,"EXECUTE OracleProc Input1, input2")





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thendrickson]

I still must not be communicating.

What I do not know and cannot seem to find is how to call an Oracle proc that returns a cursor as an output parameter.
Even when calling the proc from another Oracle Proc.

If I understand Oracle at all (and I may not), Oracle returns explicitly defined cursors from a proc where SQL would return a recordset (not explicitly declared in the proc) under the same circumstances.

Every Oracle example I find uses input params or single output values. Not a cursor.

 

[LKBrwnDBA]

Does the procedure have this cursor parameter in the definition? Something like:

CREATE OR REPLACE PROCEDURE OracleProc 
(
  Input1            VARCHAR2
, Input2            NUMBER
, curResults    OUT SYS_REFCURSOR
) IS
-- etc ---
BEGIN
  OPEN curResults FOR 
    Select * From MyTable
    Where col1 = Input1
      And Col7 = Input2;
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
Actually Oracle's cursor acts just like a recordset when it returns the result of the proc's code.

What I was commenting on was in the docs that I read is was indicated that OpenQuery would not return multiple lines ( records), just the first - an Oracle REF CURSOR can have many records.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thendrickson]

LKBrwnDBA Exactly!!!!

I have yet to figure out how to call this from Oracle much less the OPENQuery function of SQL.

Thanks

TurkBear:
"Oracle REF CURSOR can have many records." As does a SQL Server recordset

But thanks for the input

 

[Turkbear]

Hi,
From SqlPlus you would declare a REFCURSOR variable and then use it to call the proc ( something like):

SQL>
SQL>variable cur1 REFCURSOR;
SQL> exec OracleProc Input1, input2(:cur1);
SQL>print cur1  ( or set Autoprint ON)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thendrickson]

As I said, I am really new at Oracle so here goes:

In your example where you show
"SQL> exec OracleProc Input1, input2cur1);" What does the : mean?

I have heard of SQL plus but I do not have it installed.

 

[Turkbear]

Hi,
The : identifies the the bind variable you declared and shows that it is what is to be used to receive the returned values.

SqlPlus ( or SqlWorksheet, its GUI conterpart) is part of the Oracle client tools package.

I suspect that much the same code can be used in SqlSever's client - declate a variable of the proper type ( like a recordset) and use it somethinglike I posted earlier:

@MyDataFromOracle = Select * from OPENQUERY(OracleLinkedServer,"EXECUTE OracleProc Input1, input2")


BUT,
The SqlServer docs have a different method of calling an Oracle proc :

http://msdn.microsoft.com/en-us/library/ms188332.aspx[/URL]]
J. Using EXECUTE to query an Oracle database on a linked server
The following example executes several SELECT statements at the remote Oracle server. The example begins by adding the Oracle server as a linked server and creating linked server login.

Copy-- Setup the linked server.
EXEC sp_addlinkedserver
@server='ORACLE',
@srvproduct='Oracle',
@provider='OraOLEDB.Oracle',
@datasrc='ORACLE10';

EXEC sp_addlinkedsrvlogin
@rmtsrvname='ORACLE',
@useself='false',
@locallogin=null,
@rmtuser='scott',
@rmtpassword='tiger';

EXEC sp_serveroption 'ORACLE', 'rpc out', true;
GO

-- Execute several statements on the linked Oracle server.
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;
GO
DECLARE @v INT;
SET @v = 7902;
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;
GO

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thendrickson]

Thank you very much!!!.

the ":" seems to be the piece I was missing.
I am suprised that I never turned that up in any of my Bing and Google Searches

I can now take it from here.

Thanks to everybody for the effort. And I apoligize for such a very basic question

 

[Turkbear]

Hi,
Glad it helped..shifting between database syntaxes ( syntaxen?) can be hard since many ( like Oracle and SqlServer ) are similar enough to confuse and different enough to make it hard to combine actions on both at once.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thendrickson]

Well, I have worked a lot with SQL Server, a fair bit with Informix, More Access than I will admit to in public and now Oracle.

I contract a lot due to my age and disabilities and mostly I get work that either nobody else wants or they cannot find a qualified person in time. I make a better living this way than anything else I can think of, but it does become very confusing at times.

This Oracle has been one of the toughest since there was so much information available, but so little of the very basics that I was overwhelmed.

Again, thanks

 

[Turkbear]

Hi,
Two sites always to have access to, if you need Oracle info:

http://www.oracle.com/pls/db102/homepage?remark=tahiti

and

http://www.oracle.com/technetwork/index.html

also the online courses here:

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=160&p_org_id=1001&lang=US

Not sure about cost, but your clients may have Oracle Support Contracts you could use if needed.



To Paraphrase:"The Help you get is proportional to the Help you give.."