Hi,
I'm having a heck of a time getting one particular Stored
Procedure to work with CF and Oracle. I keep getting a
"ORA-01036 Illegal Variable Name/Number" error. This
works
just fine from SQL*Plus, but from CF all I get is the
error.
Here's the code I'm using to invoke the SP:
<CFSTOREDPROC PROCEDURE="#session.dsnSchema#.pk_mca_prep.set_unicenter_flag"
DATASOURCE="#session.dsn#"
USERNAME="#session.dsnUsername#"
PASSWORD="#session.dsnPassword#"
RETURNCODE="YES"
DEBUG="Yes">
<CFPROCPARAM
VARIABLE="v_dir"
DBVARNAME="v_dir"
TYPE="IN"
VALUE="#trigger_file_path#"
CFSQLTYPE=CF_SQL_VARCHAR>
<CFPROCPARAM
VARIABLE="v_file"
DBVARNAME="v_file"
TYPE="IN"
VALUE="#session.dsnSchema#_#attributes.slsfc_id#.trigger"
CFSQLTYPE=CF_SQL_VARCHAR>
<CFPROCPARAM
VARIABLE="v_slsfc"
DBVARNAME="v_slsfc"
TYPE="IN"
VALUE="#attributes.slsfc_id#"
CFSQLTYPE=CF_SQL_VARCHAR>
</CFSTOREDPROC>
And here's the SP code:
function
set_unicenter_flag (v_dir in varchar, v_file in
varchar,
v_slsfc in varchar)
RETURN NUMBER
IS
fptr utl_file.file_type;
BEGIN
fptr := utl_file.fopen(v_dir,v_file,'a');
if utl_file.Is_open(fptr)
then
utl_file.putf(fptr,'SLSFC:%s Execution time: %s
Oracle ID: %s',v_slsfc, to_char(sysdate,'mm-dd-yyyy
hh24:mi:ss'), user);
utl_file.fclose(fptr);
return 0;
else
return -1;
end if;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Invalid Path');
RETURN 1;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('Invalid Operation');
RETURN 2;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('Invalid Mode');
RETURN 3;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Read Error');
RETURN 4;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Write Error');
RETURN 5;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Other Error');
RETURN -1;
END;
Any ideas?
Howard
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
