Dynamically create TOraStoredProc and overloaded function

[gharris]

Hi

I am dynamically creating a TOraStoredProc to connect to an overloaded function and I am not getting the return value from the function (see code snippet below). Yet when I add the call to the same function on a form (i.e. I stick TOraStoreProc object on a form) it returns a value.

------%<


GetOraType := TOraStoredProc.Create(nil);
try
GetOraType.Session := FSession;
GetOraType.StoredProcName := 'SDS.CDF_FIELD.GET_ORA_TYPE';
GetOraType.Overload := 2;
GetOraType.Params.CreateParam(ftString, 'RESULT', ptOutput);
GetOraType.Params.CreateParam(ftFloat, 'MCP_ID', ptInput).Value := MCP_ID;
GetOraType.Execute;

FTranslationType := GetOraType.ParamByName('RESULT').Value;
finally
GetOraType.Free;
end;
------%<

Version of ODAC: 5.70.0.28
Oracle Version: 10.2.0.4.0
Oracle Client: 10
Delphi Version: 6 (fully patched)

TIA

Graham Harris

[bork]

Hello

Unfortunately, the information you have provided is not enough to reproduce your issue. We need additional information. Please provide us the DDL script for creating the overloaded functions that are used in your example.

[gharris]

CREATE OR REPLACE PACKAGE sds.gfm_label_import
AS

ERROR_CREATING_LABEL EXCEPTION;
PRAGMA EXCEPTION_INIT(ERROR_CREATING_LABEL, -20000);

ERROR_DUPLICATE_LABEL EXCEPTION;
PRAGMA EXCEPTION_INIT(ERROR_DUPLICATE_LABEL, -20001);

ERROR_OVERLAPPING_LABEL EXCEPTION;
PRAGMA EXCEPTION_INIT(ERROR_OVERLAPPING_LABEL, -20002);

PROCEDURE create_log_entry
(ACTION VARCHAR2,
BEFORE_VALUE VARCHAR2,
IMPORTED_DATA sds.gfm_check_for_overlaps.IMPORTED_DATA_ROW,
UDP_TYPE NUMERIC,
PARAM_ID NUMERIC);

PROCEDURE create_log_entry
(ACTION VARCHAR2,
BEFORE_VALUE VARCHAR2,
AFTER_VALUE VARCHAR2,
UDP_TYPE NUMERIC,
PARAM_ID NUMERIC,
USER_NAME VARCHAR2);

FUNCTION get_label_id
(LABEL_TEXT VARCHAR2) RETURN NUMERIC;

FUNCTION insert_label
(LABEL_TEXT VARCHAR2) RETURN NUMERIC;

PROCEDURE initialize_package
(P_GFMCP_ID NUMERIC,
P_MCP_ID NUMERIC,
P_LOG_IMPORT NUMERIC);
/* The order of the method declaration MUST remain as is. This is
a requirement for the ODAC components to work. */
PROCEDURE import_translation
(FROM_VALUE NUMBER,
TO_VALUE NUMBER,
TRANSLATION VARCHAR2,
IS_DEFAULT VARCHAR2,
BLANK VARCHAR2);

PROCEDURE import_translation
(FROM_VALUE DATE,
TO_VALUE DATE,
TRANSLATION VARCHAR2,
IS_DEFAULT VARCHAR2,
BLANK VARCHAR2);

PROCEDURE import_translation
(FROM_VALUE VARCHAR2,
TO_VALUE VARCHAR2,
TRANSLATION VARCHAR2,
IS_DEFAULT VARCHAR2,
BLANK VARCHAR2);

PROCEDURE import_translation
(FROM_VALUE NUMBER,
TO_VALUE NUMBER,
TRANSLATION NUMBER,
IS_DEFAULT VARCHAR2,
BLANK VARCHAR2);

PROCEDURE import_translation
(FROM_VALUE DATE,
TO_VALUE DATE,
TRANSLATION NUMBER,
IS_DEFAULT VARCHAR2,
BLANK VARCHAR2);

PROCEDURE import_translation
(FROM_VALUE VARCHAR2,
TO_VALUE VARCHAR2,
TRANSLATION NUMBER,
IS_DEFAULT VARCHAR2,
BLANK VARCHAR2);
END;
/

[bork]

Hello

In your previous post you provide an example where the "CDF_FIELD" package and the "GET_ORA_TYPE" procedure from this package are used. In your second post you provide the DLL script for the "gfm_label_import" package. To reproduce your issue I need a DDL script for creating the "CDF_FIELD" package and the package body.

[gharris]

Here is the CDF_FIELD specification:

CREATE OR REPLACE PACKAGE SDS.cdf_field AS
/* Please leave all overloaded methods in same order as they are here */
--------------------------------------------------
-- Cursor return type
--------------------------------------------------
TYPE lookup_id_t IS REF CURSOR;
TYPE enumeration_list_t IS REF CURSOR;

CDF_FIELD_NOT_TRANSLATED VARCHAR2(200) := '____CDF_FIELD_____NOT_TRANSLATED____';
CDF_FIELD_NOT_UNTRANSLATED NUMBER := -8675309;


FUNCTION create_field( id NUMBER,
name VARCHAR2,
sas_length VARCHAR2,
sas_fmt VARCHAR2,
tab_id NUMBER,
par_type VARCHAR2)
RETURN sds.cdfe01%rowtype;

FUNCTION create_generated_field( name VARCHAR2,
sas_length VARCHAR2,
sas_fmt VARCHAR2,
tab_id NUMBER)
RETURN sds.cdfe01%rowtype;

FUNCTION create_module_field( mod_id NUMBER,
name VARCHAR2,
sas_length VARCHAR2,
sas_fmt VARCHAR2,
tab_id NUMBER)
RETURN sds.cdfe01%rowtype;

FUNCTION clone_field( src_mcp_id NUMBER,
new_name VARCHAR2)
RETURN sds.cdfe01%rowtype;

FUNCTION generate_ora_name( mcp_name VARCHAR2)
RETURN VARCHAR2;

FUNCTION generate_mcp_varn( mcp_id NUMBER)
RETURN VARCHAR2;

FUNCTION get_field(field_mcp_id NUMBER)
RETURN sds.cdfe01%rowtype;

FUNCTION get_field(field_mcp_id NUMBER, field OUT sds.cdfe01%rowtype)
RETURN NUMBER;

FUNCTION find_field_ora_name(ora_name_in IN VARCHAR2)
RETURN NUMBER;


TYPE ora_info_t IS RECORD
(
name VARCHAR2(30),
type VARCHAR2(30),
type_declaration VARCHAR2(50),
type_size NUMBER
);

FUNCTION get_ora_info(field sds.cdfe01%rowtype)
RETURN ora_info_t;

FUNCTION get_ora_size(field sds.cdfe01%rowtype)
RETURN NUMBER;

FUNCTION get_ora_size(mcp_id NUMBER)
RETURN NUMBER;
FUNCTION get_ora_type(field sds.cdfe01%rowtype)
RETURN VARCHAR2;

FUNCTION get_ora_type(mcp_id NUMBER)
RETURN VARCHAR2;

FUNCTION get_ora_type_declaration(field sds.cdfe01%rowtype)
RETURN VARCHAR2;

FUNCTION get_ora_type_declaration(mcp_id NUMBER)
RETURN VARCHAR2;

/***
* Returns 1 if the field correspons to an enumeration
* 0 otherwise
***/
FUNCTION is_enumeration(mcp_id NUMBER)
RETURN NUMBER;

/***
* Returns a cursor (id, value) with all the elements in an enumeration.
***/
FUNCTION get_enumeration_cur(mcp_id NUMBER)
RETURN enumeration_list_t;

/***
* Gets translation lookup table information based on TAB_ID from CDFE0C table:
* p_tab_id IN - identification of lookup table in CDFE0C for translating a field IN LTW
* p_tab_name OUT - the lookup table for translating a field in LTW
* p_par_id OUT - column name of lookup table that represents the key field for the translation
* p_par_name OUT - column name of lookup table that represents the translated value for the translation
***/
PROCEDURE get_translation_info(p_tab_id IN NUMBER, p_tab_name OUT VARCHAR2, p_par_id OUT VARCHAR2, p_par_name OUT VARCHAR2);

TYPE TRANSLATION_INFO IS RECORD
(TABLE_NAME sds.cdfe0c.tab_name%TYPE,
PAR_ID sds.cdfe0c.par_id%TYPE,
PAR_NAME sds.cdfe0c.par_name%TYPE);

FUNCTION get_translation_info(p_tab_id NUMBER) RETURN TRANSLATION_INFO;


/***
* Take the mcp_id an the enumeration id and returns the translated field.
***/
FUNCTION translate_field(mcp_id NUMBER, val NUMBER)
RETURN VARCHAR2;

FUNCTION untranslate_field_cur(mcp_id NUMBER, val VARCHAR2)
RETURN lookup_id_t;

/***
* It is possible for lookup tables to have multiple keys translating to the same text.
* In such a case the default behavior is to throw an exception stating that the value
* supplied cannot be untranslated to the key. This can be overriden by setting the
* returnFirstWhenMultiple parameter to a non-zero value in which case the first
* matched key will be returned (ordered by key value from least to greated)
***/
FUNCTION untranslate_field(mcp_id NUMBER, val VARCHAR2, returnFirstWhenMultiple NUMBER DEFAULT 0)
RETURN NUMBER;
/******************************************************************************
* Series of functions to validate field data
*****************************************************************************/
ERROR_UNRECOGNISED_ORA_TYPE EXCEPTION;
PRAGMA EXCEPTION_INIT(ERROR_UNRECOGNISED_ORA_TYPE, -20003);

ERROR_UNRECOGNISED_MCP_FORMAT EXCEPTION;
PRAGMA EXCEPTION_INIT(ERROR_UNRECOGNISED_MCP_FORMAT, -20004);
FUNCTION validate_data
(FIELD sds.cdfe01%ROWTYPE,
VALUE VARCHAR2,
USE_MCP_FORMAT BOOLEAN) RETURN BOOLEAN;

FUNCTION validate_data
(FIELD sds.cdfe01%ROWTYPE,
VALUE NUMERIC,
USE_MCP_FORMAT BOOLEAN) RETURN BOOLEAN;

FUNCTION validate_data
(FIELD sds.cdfe01%ROWTYPE,
VALUE DATE,
USE_MCP_FORMAT BOOLEAN) RETURN BOOLEAN;

FUNCTION validate_data
(MCP_ID NUMERIC,
VALUE VARCHAR2,
USE_MCP_FORMAT BOOLEAN) RETURN BOOLEAN;

FUNCTION validate_data
(MCP_ID NUMERIC,
VALUE NUMERIC,
USE_MCP_FORMAT BOOLEAN) RETURN BOOLEAN;

FUNCTION validate_data
(MCP_ID NUMERIC,
VALUE DATE,
USE_MCP_FORMAT BOOLEAN) RETURN BOOLEAN;

FUNCTION validate_data
(FIELD sds.cdfe01%ROWTYPE,
START_VALUE VARCHAR2,
END_VALUE VARCHAR2,
USE_MCP_FORMAT BOOLEAN) RETURN NUMBER;

FUNCTION validate_data
(FIELD sds.cdfe01%ROWTYPE,
START_VALUE NUMERIC,
END_VALUE NUMERIC,
USE_MCP_FORMAT BOOLEAN) RETURN NUMBER;

FUNCTION validate_data
(FIELD sds.cdfe01%ROWTYPE,
START_VALUE DATE,
END_VALUE DATE,
USE_MCP_FORMAT BOOLEAN) RETURN NUMBER;

FUNCTION validate_data
(MCP_ID NUMERIC,
START_VALUE VARCHAR2,
END_VALUE VARCHAR2,
USE_MCP_FORMAT BOOLEAN) RETURN NUMBER;

FUNCTION validate_data
(MCP_ID NUMERIC,
START_VALUE NUMERIC,
END_VALUE NUMERIC,
USE_MCP_FORMAT BOOLEAN) RETURN NUMBER;

FUNCTION validate_data
(MCP_ID NUMERIC,
START_VALUE DATE,
END_VALUE DATE,
USE_MCP_FORMAT BOOLEAN) RETURN NUMBER;

END;
/

[bork]

Hello

You should set the correct parameter type:

Code: Select all

GetOraType := TOraStoredProc.Create(nil);
try
  GetOraType.Session := FSession;
  GetOraType.StoredProcName := 'SDS.CDF_FIELD.GET_ORA_TYPE';
  GetOraType.Overload := 2;
  GetOraType.Params.CreateParam(ftString, 'RESULT', ptResult); // parameter type is ptResult !!!
  GetOraType.Params.CreateParam(ftFloat, 'MCP_ID', ptInput).Value := MCP_ID;
  GetOraType.Execute;

  FTranslationType := GetOraType.ParamByName('RESULT').Value;
finally
  GetOraType.Free;
end; 

Or you should use parameters that are generated by ODAC:

Code: Select all

GetOraType := TOraStoredProc.Create(nil);
try
  GetOraType.Session := OraSession1;
  GetOraType.StoredProcName := 'SDS.CDF_FIELD.GET_ORA_TYPE';
  GetOraType.Overload := 2;
  GetOraType.Prepare;
  GetOraType.ParamByName('MCP_ID').Value := 1;
  GetOraType.Execute;

  ShowMessage(GetOraType.ParamByName('RESULT').Value);
finally
  GetOraType.Free;
end;

[gharris]

Thanks!!

Graham Harris