Home » SQL & PL/SQL » SQL & PL/SQL » SQL Server to Oracle
SQL Server to Oracle [message #207572] Wed, 06 December 2006 01:35 Go to next message
HoriaC
Messages: 6
Registered: December 2006
Location: Brasov
Junior Member
Hello,

I am beginner in PL/SQL and I need some urgent support for migrating a stored procedure from SQL Server to Oracle; this procedure is running a simple query as 'select * from <Table_name>'.
Could anybody please help me?
Horia

Re: SQL Server to Oracle [message #207589 is a reply to message #207572] Wed, 06 December 2006 02:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you post the SP code, we should be able to have a crack at it.
Re: SQL Server to Oracle [message #207591 is a reply to message #207572] Wed, 06 December 2006 02:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
In general, you would open a ref cursor for the select statement. I have provided a simple example below.

SCOTT@10gXE> CREATE OR REPLACE PROCEDURE your_procedure_name
  2    (p_results OUT SYS_REFCURSOR)
  3  AS
  4  BEGIN
  5    OPEN p_results FOR
  6    SELECT *
  7    FROM dept;
  8  END your_procedure_name;
  9  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> VARIABLE g_results REFCURSOR
SCOTT@10gXE> SET AUTOPRINT ON
SCOTT@10gXE> EXECUTE  your_procedure_name (:g_results)

PL/SQL procedure successfully completed.


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 PERSONNEL      DALLAS

SCOTT@10gXE>  



Re: SQL Server to Oracle [message #207600 is a reply to message #207572] Wed, 06 December 2006 03:05 Go to previous messageGo to next message
HoriaC
Messages: 6
Registered: December 2006
Location: Brasov
Junior Member
Thank you very much for your reply. It helped me.

Mrs. Barbara, I come again with the primary Keys question. I thought I've solved it using the DBA_CONSTRAINTS table but I didn't.
I need a list of all Primary Keys from one user; in the DBA_CONSTRAINTS I can see the table and the name of the constraint but for constraint_type='P' I cannot see which field is the primary key...
Do you have any sugestion?
thank you,
Horia
Re: SQL Server to Oracle [message #207601 is a reply to message #207600] Wed, 06 December 2006 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to query DBA_CONS_COLUMNS.
Re: SQL Server to Oracle [message #207602 is a reply to message #207601] Wed, 06 December 2006 03:14 Go to previous message
HoriaC
Messages: 6
Registered: December 2006
Location: Brasov
Junior Member
This is what I need.
Thank you very much.
Regards,
Previous Topic: Convert TABLE type object into CLOB
Next Topic: calculate age in year, month and days
Goto Forum:
  


Current Time: Tue Dec 06 04:31:21 CST 2016

Total time taken to generate the page: 0.08038 seconds