Today I had an stored procedure defined in a package and I didn’t know how to call it.
And my stored procedure returned a cursor and I needed to see the results.
So after some tests this is the way to perform this. You can use code like the following:
variable r refcursor -- use this if you have an out parameter that is an out cursor
DECLARE
-- Declare a variable for each of your stored procedure arguments
VAR_IN_1 VARCHAR2(32767);
VAR_IN_2 VARCHAR2(32767);
VAR_IN_3 VARCHAR2(32767);
INFO sys_refcursor;
BEGIN
-- Init the variables with the parameter you want to use to test the stored procedure
VAR_IN_1 := 'Param1';
VAR_IN_2 := 'Param2';
VAR_IN_3 := 'Param3';
-- Call the stored procedure. You should write something like schema.package.storedprocedure( param1, param2, ..., paramN);
MYSCHEMA.PKG_TEST.TEST_STORED_PROC ( VAR_IN_1, VAR_IN_2, VAR_IN_3, INFO );
-- If one of the parameters was an out cursor assign it to the r variable to be able to see the results
:r := PC_INFO;
COMMIT;
END;
-- Execute this code in SQL PLUS
And after executing it just call in the SQL PLUS prompt
PRINT r to see cursor results
If I have a PInvoke call like the following:
[DllImport("Advapi32.dll", CharSet=CharSet.Auto)]
static extern Boolean FileEncryptionStatus(String filename,
out UInt32 status);
What is the difference between
[DllImport("Advapi32.dll", CharSet=CharSet.Auto)] static extern Boolean FileEncryptionStatus(String filename, out UInt32 status);
and
[DllImport("Advapi32.dll", CharSet=CharSet.Auto)] static extern Boolean FileEncryptionStatus(String filename, ref UInt32 status);
Well, as long as I have tested it, they exactly the same. From the MSDN you can even read
“I could have selected the ref keyword here as well, and in fact both result in the same machine code at run time. The out keyword is simply a specialization of a by-ref parameter that indicates to the C# compiler that the data being passed is only being passed out of the called function. In contrast, with the ref keyword the compiler assumes that data may flow both in and out of the called function.”
“When marshaling pointers through P/Invoke, ref and out are only used with value types in managed code. You can tell a parameter is a value type when its CLR type is defined using the struct keyword. Out and ref are used to marshal pointers to these data types”
So what should you use? Well using the out keyword for PInvoke will just add some information or documentation to your method, but because these functions are implemented in C or C++ they might treat an out parameter as an IN parameter so I really prefere to use ref when I’m calling functions with PInvoke.