Oracle Multiple datasource Procedure

[jarom_z]

I am trying to run a procedure that queries two separate databases for
comparison purposes. The Database names are passed to the procedure as
parameters. The procedure runs just fine from a sqlplus command prompt. However
if I pass to the procedure a database parameter that is different than the
datasourc named in the <cfprocedure> tags, the procedure fails to run and gives
the following error:

ORA-01017: invalid username/password; logon denied ORA-02063: preceding line
from A2005 - OTHERS HANDLER

It also won't run if both or either one of the database parameters are
different than the Datasource in the <cfprocedure> tag. Any Ideas on how this
can be done? I haven't found anything about it on the Macromedia site. I
believe the Oracle term for what is happening in the procedure is database
linking.

<cfstoredproc procedure="slco.parcel_diff" datasource="as_cama">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="database"
value="A2005">
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="parcelyear"
value=2005>
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="YearofSale"
value=0>
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="MonthOfSale"
value=0>
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="parcel_id"
value="09333760080000">
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="recid_1" value=0>
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="recid_2" value=0>
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="table"
value="PARCEL">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="childObject"
value="N">

<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="database2"
value="A2005">
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="parcelyear2"
value=2005>
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="YearOfSale2"
value=0>
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="MonthofSale2"
value=0>
<cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="Parecel2"
value="09333760090000">
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="recid_1_2" value=0>
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="recid_2_2" value=0>
<cfprocparam type="in" cfsqltype="cf_sql_integer" variable="sequence_num"
value="#SEQUENCE_NUMBER.NEXTVAL#">
</cfstoredproc>

[paross1]

I would be interested in seeing your PL/SQL stored procedure to see how you are using the database and database2 parameters.

Phil

[jarom_z]

I didn't write the procedure so I asked our DBA for the code inside the
procedure. He gave me what he thought might be pertinent. It's all foriegn to
me but I've attached it any way. I'm not sure how helpful it will be.

return number
is
l_db varchar2(8);
l_link_id number;
par_link_id number;
l_tbl_name varchar2(30);
l_parcel_year number;
l_year_of_sale number;
l_month_of_sale number;
l_parcel_id varchar2(14);
l_recid1 number;
l_recid2 number;
type cv_typ is REF CURSOR;
cv cv_typ;
query_stmt varchar2(1000);
cursor tblcur (my_tbl varchar2) is
select table_name, key_level,
subkey1_name, subkey1_size, subkey2_name, subkey2_size
from saldba.table_list
where table_name = my_tbl
;

tblrec tblcur%ROWTYPE;



begin
l_db := p_db;
l_tbl_name := p_tbl_name;
l_parcel_year := p_parcel_year;
l_year_of_sale := p_year_of_sale;
l_month_of_sale := p_month_of_sale;
l_parcel_id := p_parcel_id;
l_recid1 := p_recid1;
l_recid2 := p_recid2;

l_link_id := 0;

query_stmt := 'SELECT LINK_ID FROM PARCEL' || '@' || l_db || ' WHERE
PARCEL_YEAR = :pyear AND YEAR_OF_SALE = :pyos';
query_stmt := query_stmt || ' AND MONTH_OF_SALE = :pmos AND PARCEL_ID =
:pparcel_id';
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt, ' INITIAL PARCEL QUERY ');
EXECUTE IMMEDIATE query_stmt INTO par_link_id USING l_parcel_year,
l_year_of_sale, l_month_of_sale, l_parcel_id;
open tblcur(l_tbl_name);
fetch tblcur into tblrec;
if (tblcur%NOTFOUND)
then
close tblcur;
dbms_output.put_line(l_tbl_name || ' NOT A VALID TABLE NAME');
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt,' TBLCUR CURSOR ');
return l_link_id;
end if;
close tblcur;




if (tblrec.key_level = 0 or l_recid1 = 0)
then
query_stmt :=
'SELECT DISTINCT LINK_ID FROM ' || l_tbl_name || '@' || l_db || ' WHERE
LINK_ID = :lk';
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt, ' CASE 1 ');
EXECUTE IMMEDIATE query_stmt INTO l_link_id USING par_link_id;
elsif (tblrec.key_level = 1 and l_recid1 > 0 )
then
query_stmt :=
'SELECT DISTINCT LINK_ID FROM ' || l_tbl_name || '@' || l_db || '
WHERE LINK_ID = :lk AND RECID1 = :r1';
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt, ' CASE 2 ');
EXECUTE IMMEDIATE query_stmt INTO l_link_id USING par_link_id, l_recid1;
--not a valid case
--elsif (tblrec.key_level = 1 and l_recid1 > 0 and l_recid2 > 0 )
--then
-- query_stmt :=
-- 'SELECT DISTINCT LINK_ID FROM ' || l_tbl_name || '@' || l_db || '
WHERE LINK_ID = :lk AND RECID1 = :r1 AND RECID2 = :r2 ';
-- EXECUTE IMMEDIATE query_stmt INTO l_link_id USING par_link_id,
l_recid1, l_recid2;
elsif (tblrec.key_level = 2 and l_recid1 > 0 and l_recid2 > 0)
then
query_stmt :=
'SELECT DISTINCT LINK_ID FROM ' || l_tbl_name || '@' || l_db || '
WHERE LINK_ID = :lk AND RECID1 = :r1 AND RECID2 = :r2 ';
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt, ' CASE 3 ');
EXECUTE IMMEDIATE query_stmt INTO l_link_id USING par_link_id,
l_recid1, l_recid2;
elsif (tblrec.key_level = 2 and l_recid1 > 0 and l_recid2 = 0)
then
query_stmt :=
'SELECT DISTINCT LINK_ID FROM ' || l_tbl_name || '@' || l_db || '
WHERE LINK_ID = :lk AND RECID1 = :r1 ';
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt, ' CASE 4 ');
EXECUTE IMMEDIATE query_stmt INTO l_link_id USING par_link_id, l_recid1;
else
l_link_id := -1;
end if;
return l_link_id;
exception
when no_data_found then
l_link_id := -1403;
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt, ' NO DATA FOUND ');
return l_link_id;
when others then
l_link_id := -999;
writeprocs.writemsg
('ERROR','VALIDREC',0,0,query_stmt, SQLERRM || ' - OTHERS HANDLER ');
return l_link_id;
end;
/


/*
set serveroutput on size 1000000
declare
l_link_id number;
begin
l_link_id := validate_record('ATST','PARCEL',2006,0,0,'16143060 490000',0,0);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));

l_link_id := validate_record('A2005','LAND',2006,0,0,'161430604 90000',1,0);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));
l_link_id := validate_record('ATST','LAND',2006,0,0,'1614306049 0000',2,0);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));
--THIS RETURNS -1403 BECAUSE THERE IS NO LAND RECORD 3
l_link_id := validate_record('ATST','LAND',2006,0,0,'1614306049 0000',3,0);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));
--THIS RETURNS -1403 BECAUSE THERE IS NO LAND RECORD 4
l_link_id := validate_record('ATST','LAND',2006,0,0,'1614306049 0000',4,1);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));

l_link_id :=
validate_record('ATST','LAND_INFLUENCE',2006,0,0,' 16143060490000',1,1);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));
--THIS RETURNS -1403
l_link_id :=
validate_record('ATST','LAND_INFLUENCE',2006,0,0,' 16143060490000',1,2);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));
--THIS RETURNS -1403
l_link_id :=
validate_record('ATST','LAND_INFLUENCE',2006,0,0,' 16143060490000',2,1);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));
--THIS RETURNS -1403
l_link_id :=
validate_record('ATST','LAND_INFLUENCE',2006,0,0,' 16143060490000',2,2);
dbms_output.put_line('RESULT OF VALIDATE_RECORD: ' || to_char(l_link_id));
end;
/

*/

[paross1]

You cut off the first line of the PL/SQL procedure declaration (name,
parameters, etc.) when you pasted it in your post, but in reading the line
return number and the statement l_link_id :=
validate_record('ATST','PARCEL',2006,0,0,'16143060 490000',0,0);, this indicates
to me that this is a PL/SQL FUNCTION not a PROCEDURE, and you can NOT call
Oracle PL/SQL functions using CFSTOREDPROC. ColdFusion does not "know" how to
handle the return value of an Oracle function. You should either write this as
a procedure, and return l_link_id as a parameter, or "wrap" this function call
within a procedure to do so without having to have a procedure that duplicates
the function.

Phil

[jarom_z]

Thanks Phil,

I'm not sure I know the difference between a function and a procedure within
an Oracle database but our DBA let me know that the snipet of code that I
posted was part of a function that runs within the procedure. The procedure
compares two different parcels, or the same parcel from two seperate years, for
differences and creates a temporary table within the Oracle database. I then
select all the records from the temp table and display the results on a
webpage. So there are no results being sent directly back from the called
procedure to the CF server. I know that using the <cfprocedure> tag with the
same database parameters as the Procedures datasource database, the procedure
will run from the CF application server.

[paross1]

Well, I am somewhat at a loss as to why you would be getting this error from
CF, and not when running the same procedure from SQL*Plus. Your
procedure/function is using dynamic SQL to append the particular database name
to your PARCEL table, and I assume that the names that you are passing are, in
fact, valid databases with valid database links to the database from which you
are calling this procedure/ function. Perhaps it is a privilege issue with the
user that you are using to execute this procedure call. In other words, the
user ID used by CF for your as_cama datasource may have EXECUTE privileges on
this procedure/package, but may not have the required privileges to access the
other database to which you are linking.

Phil