variable datatype returning

[thepercival]

Hello,

I have a stored procedure and the return data type is number(16) as you
can see. but I get it back in the code as a var_numeric and then the
precision depends on the value of the returndata. So 0 fits into a short
so I have to convert to a short, although I say that my stored procedure
has to return a NUMBER(16), thus int. Can anyone tell me how I can
determine my return datatype beforehand?

many thanks

CREATE OR REPLACE PROCEDURE ish.applsp_IsFo reignKeyProtect ed

(

cDbd IN VARCHAR2,

cTable IN VARCHAR2,

cFKey IN VARCHAR2,

rc1 IN OUT refcurpkg.rct1

) AS

IsProtected NUMBER(16);

BEGIN

IsProtected := 1;

OPEN rc1 FOR

SELECT IsProtected FROM DUAL;

END;
--
Posted via http://dbforums.com

[mcstock]

it is very unclear what you are trying to do, especially since the code you
included does not have a valid syntax

strictly speaking, procedures do not have return values (only functions have
return values, but both functions and procedures can have OUT or IN OUT
variables, as in your example) -- so you may be using the wrong construct

the return datatype, or OUT variable datatype, will be whatever you declare
it to be -- if the value you assign to it is of a different datatype (not
recommended, oracle does implicit datatype conversion (also not recommended
in most cased)

i'm guessing that you might be referring to determining return datatypes for
dynamic sql (although that's not apparent from your post), the DBMS_SQL
package has procedures for determining the datatypes returned by SELECT
statements.

at any rate, some clarification on what you're trying to accomplish would be
helpful
--mcs

"theperciva l" <me*********@db forums.com> wrote in message
news:35******** ********@dbforu ms.com...

Hello,

I have a stored procedure and the return data type is number(16) as you
can see. but I get it back in the code as a var_numeric and then the
precision depends on the value of the returndata. So 0 fits into a short
so I have to convert to a short, although I say that my stored procedure
has to return a NUMBER(16), thus int. Can anyone tell me how I can
determine my return datatype beforehand?

many thanks

CREATE OR REPLACE PROCEDURE ish.applsp_IsFo reignKeyProtect ed

(

cDbd IN VARCHAR2,

cTable IN VARCHAR2,

cFKey IN VARCHAR2,

rc1 IN OUT refcurpkg.rct1

) AS

IsProtected NUMBER(16);

BEGIN

IsProtected := 1;

OPEN rc1 FOR

SELECT IsProtected FROM DUAL;

END;
--
Posted via http://dbforums.com