Correct EXECUTE syntax (embarrassing but very frustrating)

[tradle]

Hi All -

I've compiled the package below and cannot get the darn thing to run correctly. I've tried the following syntax in SQL*Plus:

exec tqr135t.cois_swexpense_preprocess.process_fixed_assets(tqr135t.cois_swexpense_preprocess.fa_extract);

and gotten the following error:

While processing data from the Fixed Assets source, ORA-06504: PL/SQL: Return
types of Result Set variables or query do not match

What do I need to add to the syntax to make this run correctly? Nothing that I've tried works correctly.

HELP!!!!

CREATE OR REPLACE PACKAGE BODY TQR135T.COIS_SWEXPENSE_PREPROCESS IS

FUNCTION fa_extract RETURN sys_refcursor IS

  fa_rc sys_refcursor;

BEGIN

    OPEN fa_rc FOR
      SELECT co, center, asset, description, catgry, curr_depr, proj_num, addl_desc
      FROM caa.cois_fixed_assets_stage
      WHERE asset IN (
	SELECT asset FROM caa.cois_swexpense_definitions)
      AND catgry IN (
        SELECT category FROM caa.cois_swexpense_definitions)
      AND co = '2001';

  RETURN fa_rc;

END fa_extract;

PROCEDURE process_fixed_assets(fa_rc sys_refcursor) IS 

f   caa.cois_fixed_assets_stage%ROWTYPE;

BEGIN

   LOOP

     FETCH fa_rc INTO f;
     EXIT WHEN fa_rc%NOTFOUND; 

	   UPDATE caa.cois_swexpense_source
	   SET app_id = f.proj_num,
	   cost_center=f.center,
	   asset = f.asset,
           amount = f.curr_depr,
	   description = f.addl_desc,
	   category = f.catgry,
	   modified_date=sysdate;

	   UPDATE caa.cois_swexpense_source
	   SET org_unit_id = TO_NUMBER(CONCAT(f.co, f.center), '99999999999.')
	   WHERE cost_center = f.center;

   END LOOP;

COMMIT;

EXCEPTION
     WHEN others THEN DBMS_OUTPUT.PUT_LINE('While processing data from the Fixed Assets source, '||sqlerrm);

END process_fixed_assets;
 

FUNCTION mt_extract RETURN sys_refcursor IS

  mt_rc sys_refcursor;

BEGIN

  OPEN mt_rc FOR
    SELECT bank, gl_account, cost_center, source_code, amount, description_2
    FROM caa.cois_master_trans_stage;

  RETURN mt_rc;

END mt_extract;

PROCEDURE process_master_transaction(mt_rc sys_refcursor) IS

m   caa.cois_master_trans_stage%ROWTYPE;

BEGIN

   LOOP

      FETCH mt_rc INTO m;
      EXIT WHEN mt_rc%NOTFOUND;

	    UPDATE caa.cois_swexpense_source
	    SET gl_account=m.gl_account,
	    cost_center=m.cost_center,
	    source_code=m.source_code,
	    amount=m.amount,
	    app_id=m.description_2,
	    modified_date=sysdate;

 	    UPDATE caa.cois_swexpense_source
	    SET org_unit_id = TO_NUMBER(CONCAT(m.bank, m.cost_center), '99999999999.')
	    WHERE cost_center = m.cost_center;


   END LOOP;

COMMIT;

EXCEPTION
     WHEN others THEN DBMS_OUTPUT.PUT_LINE('While processing data from the Master Transaction source, '||sqlerrm);

END process_master_transaction;


PROCEDURE swexpense_validation IS

CURSOR bai IS
	SELECT app_id FROM caa.cois_swexpense_source
	WHERE app_id NOT IN
	  (SELECT app_id FROM caa.cois_app_id);

bad_app_id		VARCHAR2(5);

BEGIN

FOR app_code IN (SELECT app_id FROM caa.cois_swexpense_source) LOOP

OPEN bai;
FETCH bai INTO bad_app_id;

   INSERT INTO caa.cois_process_validation (source, value, error, process_date)
   VALUES ('Software Expense, APP_ID', bad_app_id, 'APP_ID '||bad_app_id||' is not contained in the COIS_APP_ID table.', sysdate);

CLOSE bai;
END LOOP;

COMMIT;

EXCEPTION
     WHEN others THEN DBMS_OUTPUT.PUT_LINE('While validating cois_swexpense_source, '||sqlerrm);
   

END swexpense_validation;

END;
/

 

[sem]

f variable in process_fixed_assets is declared as of caa.cois_fixed_assets_stage%ROWTYPE type, but your cursor in mt_extract returns only some fields from that table in some order, that may differ from the order in table. To correct the situation you may OPEN mt_rc FOR SELECT * or change f variable definition to make the number of columns and order the same.

Regards, Dima

http://seminihin.narod.ru

 

[tradle]

Thanks, Dima -

I still don't think that I'm correctly passing the ref_cursor variable in my exec syntax. I've altered my code to what is below. The data sources in question have hundreds of rows, and the definitions table is populated with the required defining data. I honestly cannot figure out why I'm not seeing any resultant data, other than my exec syntax is not adequately referencing the ref_cursor.

I used this syntax: exec tqr135t.cois_swexpense_preprocess.process_master_transaction(tqr135t.cois_swexpense_preprocess.mt_extract);

tqr135t is the schema that this process resides in.

Anything that you could offer would be most helpful!

Thanks!

CREATE OR REPLACE PACKAGE BODY TQR135T.COIS_SWEXPENSE_PREPROCESS IS

FUNCTION fa_extract RETURN sys_refcursor IS

  fa_rc sys_refcursor;

BEGIN
DBMS_OUTPUT.PUT_LINE('Starting fa_extract');
    OPEN fa_rc FOR
      SELECT *
      FROM caa.cois_fixed_assets_stage
      WHERE co IN (
        SELECT bank FROM caa.cois_swexpense_definitions)
      AND catgry IN (
	SELECT category FROM caa.cois_swexpense_definitions);
/*      AND center IN (
	SELECT cost_center FROM caa.cois_swexpense_definitions);
*/
  RETURN fa_rc;

END fa_extract;

PROCEDURE process_fixed_assets(fa_rc sys_refcursor) IS 

f   caa.cois_fixed_assets_stage%ROWTYPE;

BEGIN
DBMS_OUTPUT.PUT_LINE('Starting procedure');
   LOOP

     FETCH fa_rc INTO f;
     EXIT WHEN fa_rc%NOTFOUND; 

	   UPDATE caa.cois_swexpense_source
	   SET cost_center=f.center,
	   asset = f.asset,
           category = f.catgry,
	   amount = f.curr_depr,
	   app_id = f.proj_num,
	   description = f.addl_desc,
	   modified_date=sysdate;

	   /*UPDATE caa.cois_swexpense_source
	   SET org_unit_id = TO_NUMBER(CONCAT(f.co, f.center), '99999999999.')
	   WHERE cost_center = f.center;*/

   END LOOP;

COMMIT;

EXCEPTION
     WHEN others THEN DBMS_OUTPUT.PUT_LINE('While processing data from the Fixed Assets source, '||sqlerrm);

END process_fixed_assets;
 

FUNCTION mt_extract RETURN sys_refcursor IS

  mt_rc sys_refcursor;

BEGIN

  OPEN mt_rc FOR
    SELECT *
    FROM caa.cois_master_trans_stage
      WHERE bank IN (
	SELECT bank FROM caa.cois_swexpense_definitions);
/*      AND cost_center IN (
	SELECT cost_center FROM caa.cois_swexpense_definitions)
      AND gl_account IN
	SELECT gl_account FROM caa.cois_swexpense_definitions);
*/
  RETURN mt_rc;

END mt_extract;

PROCEDURE process_master_transaction(mt_rc sys_refcursor) IS

m   caa.cois_master_trans_stage%ROWTYPE;

BEGIN

   LOOP

      FETCH mt_rc INTO m;
      EXIT WHEN mt_rc%NOTFOUND;

	    UPDATE caa.cois_swexpense_source
	    SET gl_account=m.gl_account,
	    cost_center=m.cost_center,
	    source_code=m.source_code,
	    amount=m.amount,
	    app_id=m.description_2,
	    modified_date=sysdate;

 	    UPDATE caa.cois_swexpense_source
	    SET org_unit_id = TO_NUMBER(CONCAT(m.bank, m.cost_center), '99999999999.')
	    WHERE cost_center = m.cost_center;


   END LOOP;

COMMIT;

EXCEPTION
     WHEN others THEN DBMS_OUTPUT.PUT_LINE('While processing data from the Master Transaction source, '||sqlerrm);

END process_master_transaction;


PROCEDURE swexpense_validation IS

CURSOR bai IS
	SELECT app_id FROM caa.cois_swexpense_source
	WHERE app_id NOT IN
	  (SELECT app_id FROM caa.cois_app_id);

bad_app_id		VARCHAR2(5);

BEGIN

FOR app_code IN (SELECT app_id FROM caa.cois_swexpense_source) LOOP

OPEN bai;
FETCH bai INTO bad_app_id;

   INSERT INTO caa.cois_process_validation (source, value, error, process_date)
   VALUES ('Software Expense, APP_ID', bad_app_id, 'APP_ID '||bad_app_id||' is not contained in the COIS_APP_ID table.', sysdate);

CLOSE bai;
END LOOP;

COMMIT;

EXCEPTION
     WHEN others THEN DBMS_OUTPUT.PUT_LINE('While validating cois_swexpense_source, '||sqlerrm);
   

END swexpense_validation;

END;
/

 

[sem]

What is the error code/text? You expose your TASK rather then PROBLEM. I'm not going to make your job instead of you but ready to help, so I suggest you to localize the problem and not send the whole bunch of irrelevant code (like swexpense_validation) at least. BTW your first attempt was to pass fa_extract while the last - mt_extract.

Regards, Dima

http://seminihin.narod.ru

 

[tradle]

Dima -

I did mention what the problem was in my post - I am getting absolutely no data in the table that is the target of my processing. I believe that I'm not using the correct syntax in my exec statement to properly pass the ref_cursor variable. Do I need to in any way reference the ref_cursor variable (for either of the functions in the package) in my exec statement?

My apologies if I wasn't clear in my previous post. I only posted the code such that you could see what changes had been made to accomodate the earlier error.

 

[sem]

You posted the code with changes and completely another call to that code. In general the syntax of the call is valid, but you again have some kind of type mismatch as you can not fetch employee info into a variable desined for storing department info. Did you try the same call

exec tqr135t.cois_swexpense_preprocess.process_fixed_assets(tqr135t.cois_swexpense_preprocess.fa_extract);

whith suggested changes to the code? If so, what was the error? If it was the same just write that!

Regards, Dima

http://seminihin.narod.ru

 

[tradle]

Dima -

Yes, I did run the following with the change made to the ref_cursor logic as suggested. The result yielded no data in the destination table - the procedure completed successfully with no errors. A query run with the same select critera as listed in the ref-cursor definition yielded several hunderd rows of data, which leads me to believe that I'm not adequately calling the ref_cursor in my exec statement.

exec tqr135t.cois_swexpense_preprocess.process_fixed_assets(tqr135t.cois_swexpense_preprocess.fa_extract);

FUNCTION fa_extract RETURN sys_refcursor IS

  fa_rc sys_refcursor;

BEGIN
DBMS_OUTPUT.PUT_LINE('Starting fa_extract');
    OPEN fa_rc FOR
      SELECT *
      FROM caa.cois_fixed_assets_stage
      WHERE co IN (
        SELECT bank FROM caa.cois_swexpense_definitions)
      AND catgry IN (
	SELECT category FROM caa.cois_swexpense_definitions);
      AND center IN (
	SELECT cost_center FROM caa.cois_swexpense_definitions);

  RETURN fa_rc;

END fa_extract;

 

[sem]

I think that the problem is in your process_master_transaction code, not in call, that is correct. Which table you call destination and what data do you expect? Your code is quite suspicious as within a loop you each time update all rows in some table and then update only some records, so the results are dismessed on the next step Is that what you really want?

Regards, Dima

http://seminihin.narod.ru

 

[tradle]

My destination table is caa.cois_swexpense_source. I agree with your statement - I have actually commented out the second UPDATE statement within the LOOP. Using the process_master_transaction procedure as an example, when I run it, I can tell that the select statement for the cursor is gathering data - there is a significant pause between the start and completion of the procedure whcih makes sense as the caa.cois_master_trans_stage table, the source table, is quite large. However, when the procedure completes, no rows have been added to the destination table.

Thanks for your help.
Tim

 

[sem]

Again, I don't see any called INSERT statement in your code, revise it again. UPDATE statements (1 or 2 - doesn't matter) can not ADD rows to a table, even when you call it "destination".

Regards, Dima

http://seminihin.narod.ru

 

[tradle]

Dima,

My code executes correctly as originally written, with but a couple formatting changes for number formats. An unknown restriction was placed on my destination table by our DBA, and he's been gone for the past few days. That's why I constantly go no results though there was valid data in my data sources.

Thanks for your suggestions. Apologies for the frustrations.

Tim