Menu ▾ ▴

MS SQL Server + stored procedure + out cursor

OTL
Superantimonitor
2017-09-12
2017-09-12

  • Superantimonitor

    Superantimonitor - 2017-09-12

    Hi Sergei,

    I'm porting an OTL program from Oracle to SQL Server.
    When change:
    define OTL_ORA8 for OTL_ODBC_MSSQL_2008 makes otl_refcur_stream undefined.

    So I'm trying to figure it out how to get it work again.
    this is one version of my SP

    CREATE PROCEDURE MICROWAVE.LIST_SITE_CALL_SIGNS(@i_SiteName VARCHAR(255), @o_Cursor CURSOR VARYING OUTPUT)
    AS
    BEGIN
    SET NOCOUNT ON;
    SET @o_Cursor = CURSOR FORWARD_ONLY STATIC FOR
    SELECT S.SITE_NAME, CS.CALL_SIGN_NAME, CS.STATUS, CONVERT(VARCHAR(19), CS.MODIFIED_DATE, 121) MODIFIED_DATE
    FROM SITE S
    INNER JOIN CALL_SIGN CS
    ON CS.SITE_ID = S.SITE_ID
    WHERE CS.SITE_ID = (SELECT SITE_ID FROM SITE WHERE SITE_NAME = @i_SiteName);

    END;

    This is other (without cursor, only with the select statement):

    CREATE PROCEDURE MICROWAVE.LIST_SITE_CALL_SIGNS(@i_SiteName VARCHAR(255))
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT S.SITE_NAME, CS.CALL_SIGN_NAME, CS.STATUS, CONVERT(VARCHAR(19), CS.MODIFIED_DATE, 121) MODIFIED_DATE
    FROM SITE S
    INNER JOIN CALL_SIGN CS
    ON CS.SITE_ID = S.SITE_ID
    WHERE CS.SITE_ID = (SELECT SITE_ID FROM SITE WHERE SITE_NAME = @i_SiteName);

    END;

    So basically what I need is an example of how to pull a cursor or query result from SQL Server with OTL + stored procedure.

    Thanks in advance.

     

Log in to post a comment.