AboutSuchitra Joshi Expertise I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader and some basic dba activities or performance tuning for Oracle 8i database. I cannot answer questions related to major dba activities, backup and recovery.
Experience 10+ years D2K
Awards and Honors OCP - SQL and PL/SQL
Brainbench certifications in Oracle Administration, Database, PL/SQL
Expert: Suchitra Joshi Date: 6/11/2008 Subject: Calling SQL Stored procedure from PLSQL function
Question Hi,
I have created a simple PLSQL function to return a value that is pulled back from SQL server.
******************************************************
Oracle Function
******************************************************
CREATE OR REPLACE FUNCTION TEMP_ORACLE_FUNCTION ( i_param IN VARCHAR2
)
RETURN NUMBER
IS
tmp_result NUMBER;
BEGIN
"dbo"."TempSQLSP"@PLCSQL.PLCOADM(i_param,tmp_result);
RETURN tmp_result;
END TEMP_ORACLE_FUNCTION;
******************************************************
SQL Server Stored Procedure
******************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TempSQLSP]
(
@InParam varchar(50),
@OutParam INT OUTPUT
)
AS
BEGIN
IF @InParam = 'OADM'
select @OutParam = 1
ELSE
select @OutParam = 0
END
******************************************************
Execution
******************************************************
SELECT TEMP_ORACLE_FUNCTION('OADM') FROM dual;
I am expecting an output as 1. But unfortunately i get the error
ORA-06571: Function TempSQLSP does not guarantee not to update database.
Any input will be greatly appreciated.
Thanks,
Shashi
PS: I am using the DB Link (@PLCSQL.PLCOADM) setup using DG4MSQL - link between oracle and sql server database.
Answer Hi Shashi,
I am not quite sure of this. But you can try writing a packaged function as follows -
