Asked  10 Months ago    Answers:  5   Viewed   10 times

I have an Oracle linked server in SQL Server 2008 R2. I need to execute Oracle stored procedures (with output parameter in first, and input parameter in second procedure):

CREATE OR REPLACE PROCEDURE my1.spGetDate(CurrentDate OUT VARCHAR2)
IS
BEGIN
-- set output parameter, no select statements
END;

CREATE OR REPLACE PROCEDURE my1.spDeleteOldRecords(CurrentDate IN VARCHAR2)
IS
BEGIN
-- conditional delete from oracle table, no select statements
END;

I didn't found any complete documentation on this question, only simple examples with parameterless select/nonselect procedures, and want to know, how to call these procedures, procedures with select inside, multiparameter procedures with basic parameter types.

 Answers

3

It should work like this:

DECLARE @dateval DATETIME

EXECUTE ('begin my1.spGetDate(?); end;', @dateval OUTPUT) AT ORA_DBLINK_NAME;

EXECUTE ('begin my1.spDeleteOldRecords(?); end;', @dateval) AT ORA_DBLINK_NAME;

If you have several parameters, it could look like this:

EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;
Monday, August 2, 2021
 
2

You should return the value of your output parameter:

checkAccess = cmd.Parameters("@IsAllowed").Value

Also, output parameters in ADO don't require an initial value and adBoolean parameters don't require a size, so you could change your the last paramter to:

cmd.Parameters.Append(cmd.CreateParameter("@IsAllowed", adBoolean, adParamOutput))

You could also get rid of your isAllowed variable since it is no longer necessary.

Monday, August 16, 2021
 
TV Nath
 
1

When a stored procedure is executed it is parsed and compiled into a query plan, this is cached and you can access it via sys.dm_exec_cached_plans and sys.dm_exec_query_plan in XML format. The query plan records the 'output list' of each section of the parsed code. Seeing which columns are used by the stored procedure is just a matter of querying this XML, like this:

--Execute the stored procedure to put its query plan in the cache
exec sys.sp_columns ''

DECLARE @TargetObject nvarchar(100) = 'sys.sp_columns';

WITH XMLNAMESPACES (
    'http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns1
), CompiledPlan AS (
    SELECT 
        (SELECT query_plan FROM sys.dm_exec_query_plan(cp.plan_handle)) qp,
        (SELECT ObjectID FROM sys.dm_exec_sql_text(cp.plan_handle)) ob
    FROM sys.dm_exec_cached_plans cp
    WHERE objtype = 'Proc'
), ColumnReferences AS (
    SELECT DISTINCT
        ob,
        p.query('.').value('./ns1:ColumnReference[1]/@Database', 'sysname') AS [Database],
        p.query('.').value('./ns1:ColumnReference[1]/@Schema', 'sysname') AS [Schema],
        p.query('.').value('./ns1:ColumnReference[1]/@Table', 'sysname') AS [Table],
        p.query('.').value('./ns1:ColumnReference[1]/@Column', 'sysname') AS [Column]
    FROM CompiledPlan
        CROSS APPLY qp.nodes('//ns1:ColumnReference') t(p)
)

SELECT 
    [Database], 
    [Schema], 
    [Table], 
    [Column]
FROM ColumnReferences 
WHERE 
    [Database] IS NOT NULL AND 
    ob = OBJECT_ID(@TargetObject, 'P')

Caveat emptor this depends on how you define 'used'. It may be that a CTE within your stored procedure references 5 columns from a table, but then when this CTE is used only three of the columns are passed on. The query optimizer may ignore these extra fields and not include them in the plan. On the flip side the optimizer may decide that it can make a more efficient query by including extra fields in an output to enable it to use a better index later on. This code will return the columns used by the query plan, they may not exactly be the columns that are in the stored procedure code.

Saturday, September 18, 2021
 
spassen
 
1

To retrieve the value of a stored procedure's OUTPUT parameter you simply .Execute the ADODB.Command and then retrieve the .Value of the corresponding ADODB.Parameter, like so:

cmd.Execute
' retrieve and display the returned OUTPUT parameter value
Debug.Print cmd.Parameters("@NewPSID").Value
Thursday, October 21, 2021
 
Tak
 
Tak
3

one of the sql guys was able to resolve this by changing the "enable promotion of distributed transactions" to false.

Saturday, January 8, 2022
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :