A question about SYNONYMS of VIEWS

weberm · Sep 14, 2007

I am writing a stored procedure which uses a SYNONYM of a VIEW with a database link that is giving me a "PLS-00201: identifier 'IALIS_VIEW_FCCS' must be declared" error message when I try to compile it. However, if I use the explicit name it compiles. Even more mysterious, both versions work just fine in stand-alone SELECT staements! Is there some limitation to SYNONYMS that I am unaware of, and is there a way to make it use the SYNONYM?

More specifically,
IALIS_VIEW_FCCS is a synonym of ialis.viw_receipt@RCPItoFCCS. This

Code:

select count(*) from ialis_view_fccs;

and

Code:

select count(*) from ialis.viw_receipt@RCPItoFCCS;

work fine but only

Code:

   CURSOR alis_cur
   IS
      SELECT DISTINCT ALIS.payment_invoice_id,
                      ALIS.trans_auth_date_time,
                      ALIS.confirmation_number,
                      ALIS.total_ticket_amt
                 FROM ialis.viw_receipt@RCPItoFCCS ALIS
                WHERE alis.payment_status IN('A', 'E')
                  AND alis.trans_auth_date_time < SYSDATE - 2
                  AND alis.dfs_receipt_number IS NULL;

compiles. Using the synomym in cursor alis_cur returns an "indentified must be declared" error.

SantaMufasa · Sep 14, 2007

Weberm,

There is nothing inherently problematic about using synonyms of database-linked objects:

Code:

select table_name from user_tables@riv817;

TABLE_NAME
------------------------
AQ$_QUEUES
AQ$_QUEUE_TABLES
AQ$_SCHEDULES
DEF$_AQCALL
DEF$_AQERROR
DEF$_CALLDEST
DEF$_DEFAULTDEST
DEF$_DESTINATION
DEF$_ERROR
DEF$_LOB
DEF$_ORIGIN
DEF$_PROPAGATOR
DEF$_PUSHED_TRANSACTIONS
DEF$_TEMP$LOB
LOGONS
PROTECTED_ROLLBACK_SEG
SQLPLUS_PRODUCT_PROFILE
TEMP_CONS
TEST
YADA

20 rows selected.

SQL> create synonym abc for user_tables@riv817;

Synonym created.

set serveroutput on
begin
    for x in (select * from abc) loop
        dbms_output.put_line(x.table_name);
    end loop;
end;
/

AQ$_QUEUES
AQ$_QUEUE_TABLES
AQ$_SCHEDULES
DEF$_AQCALL
DEF$_AQERROR
DEF$_CALLDEST
DEF$_DEFAULTDEST
DEF$_DESTINATION
DEF$_ERROR
DEF$_LOB
DEF$_ORIGIN
DEF$_PROPAGATOR
DEF$_PUSHED_TRANSACTIONS
DEF$_TEMP$LOB
LOGONS
PROTECTED_ROLLBACK_SEG
SQLPLUS_PRODUCT_PROFILE
TEMP_CONS
TEST
YADA

PL/SQL procedure successfully completed.

As you can see, there is no problem using a synonym of a db-linked table within a PL/SQL block.

But, when you say:

Weberm said:
I am writing a stored procedure which uses a SYNONYM of a VIEW

...and...

Weberm said:
Using the synomym in cursor alis_cur returns an "indentified must be declared" error.

I don't see illustrations in your post of either of these issues:

I don't see:

1) your reference to a SYNONYM of a VIEW, and
2) your use of "the synonym in cursor alis_cur"

Could you please post the actual code and its accompanying error message that is producing the problem to which you refer?

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

weberm · Sep 17, 2007

Gladly! I hope this isn't too long (I trimmed it a bit):

Code:

   /*-------------------------------------------------------------------------------
   || PROGRAM:  Read and load FACS-ALIS credit card records
   || AUTHOR:   Michael R. Weber
   || CREATED:  August 27th, 2007
   || PURPOSE:  This procedure reads FACS tables and loads the CCRR table.
   ||REVISED:   
   */-------------------------------------------------------------------------------
   PROCEDURE load_ccrr_FNC
   IS 
      /* File processing variables */
      v_ccrr_rec_in_count        NUMBER    := 0;
      v_ccrr_rec_out_count       NUMBER    := 0;
      alis_error                 EXCEPTION;
      field_length_error         EXCEPTION;
      ft_is_null                 EXCEPTION;
	  
      /* record building variables */
      v_app_firm_cd              CHAR(1);
	  
      /* Program variables */

      /* constants */
      c_date_time       CONSTANT DATE      := SYSDATE;
	  
      /* variables */
      v_person_firm_id           CHAR(1)   := NULL;
	  
      /* record building constants */
      c_one_space       CONSTANT CHAR(1)   := ' ';
      c_fourty_spaces   CONSTANT CHAR(40)  := ' ';

      /* cursor declaration */
      /*                                           
      || This cursor will use an ALIS view containing the proper app_status,
      || payment_type, and app_service_type codes. SEL * FROM closeout, alis_view
      || where PID matches AND pull_date IS NULL AND status = 'P'.
      */
      CURSOR getfee_cur IS
         SELECT   CLOSE.doi_application_id                     CLOSE_PID,
                  ALIS.fee_detail_line_number                  ALIS_LINE_NUM,
                  ALIS.application_src,
                  CLOSE.proc_file_seq_nbr                      CLOSE_FSN,
                  ALIS.trans_processing_src,
                  CLOSE.total_application_services_amt         CLOSE_TOTAL_APP_AMT,
                  ALIS.total_ticket_amt,
                  ALIS.trans_auth_date_time,
                  CLOSE.processor_transaction_number           CLOSE_PROC_TRANS_NUM,
                  ALIS.confirmation_number,
                  CLOSE.payment_type_an,
                  CLOSE.cardholder_nm                          REMITTER_NAME,
                  ALIS.licensee_ssn_fein,
                  SUBSTR(ALIS.applicant_long_name, 1, 40)      ALIS_APPLICANT_NAME,
                  ALIS.svc_type_cd,
                  CLOSE.lic_tycl_svc_cd1,
                  ALIS.license_tycl                            REVENUE_TYCL,
                  ALIS.county_code,
                  CLOSE.fee_type_ind,
                  ALIS.fee_charged_amt,
                  ALIS.revenue_fee_type                        REVENUE_FT,
                  ALIS.receipt_record_pulled_dt
             FROM [highlight]ialis_view_fccs ALIS, [/highlight]                  
                  closeout_detail CLOSE
            WHERE ALIS.payment_invoice_id = CLOSE.doi_application_id(+)
              AND CLOSE.status_flag IN('P')
              AND ALIS.receipt_record_pulled_dt IS NULL
         ORDER BY CLOSE.doi_application_id, alis.fee_detail_line_number;
		 
   -- ******************* Start Program ********************
   BEGIN
      DBMS_OUTPUT.ENABLE(1000000);
      SAVEPOINT begining_of_proc;

      /*                                           
      || Test and load all the cursor rows to the CCRR table.
      || If an exception is encountered, quit loading and rollback all changes.
      */
      FOR v_getfee IN getfee_cur LOOP
         BEGIN
            v_ccrr_rec_in_count := v_ccrr_rec_in_count + 1;
            v_person_firm_id := person_firm(v_getfee.licensee_ssn_fein);

            IF v_person_firm_id IS NULL THEN
               DBMS_OUTPUT.put_line('Warning! There was no person/firm value for PID '
                  || v_getfee.close_pid || ', line ' || v_getfee.alis_line_num);
            END IF;

            IF LENGTH(v_getfee.licensee_ssn_fein) > 12 THEN
               DBMS_OUTPUT.put_line('Error: SSN/FEIN is too long in record ' || v_getfee.close_pid);
               RAISE field_length_error;
            END IF;

            IF v_getfee.alis_applicant_name IS NULL THEN
               DBMS_OUTPUT.put_line('Warning! There was no applicant name for PID '
                  || v_getfee.close_pid || ', line ' || v_getfee.alis_line_num
                  || ' -- Defaulting to spaces');
               v_getfee.alis_applicant_name := c_fourty_spaces;
            END IF;

            IF v_getfee.lic_tycl_svc_cd1 IS NULL THEN
               v_getfee.lic_tycl_svc_cd1 := c_one_space;
            END IF;

            IF v_getfee.svc_type_cd = '70' AND v_getfee.revenue_ft = 'J' THEN
               v_getfee.revenue_tycl := '7100';
            END IF;

            IF v_getfee.svc_type_cd = '71' AND v_getfee.revenue_ft = 'J' THEN
               v_getfee.revenue_tycl := '7101';
            END IF;

            IF v_getfee.county_code IS NULL THEN
               v_getfee.county_code := '00';
            END IF;

            IF v_getfee.revenue_tycl = '3707' AND v_getfee.revenue_ft = 'L' THEN
               v_getfee.revenue_tycl := '3706';
            END IF;

            IF v_getfee.revenue_ft IS NULL THEN
               RAISE ft_is_null;
            END IF;

            IF v_getfee.revenue_ft IN('J', 'L', 'T')
               AND v_getfee.county_code != '00' THEN
               v_getfee.county_code := '00';
            END IF;

            IF v_getfee.revenue_ft IN('B', 'C') AND v_getfee.county_code = '00' THEN
               DBMS_OUTPUT.put_line('Warning! Fee type ' || v_getfee.revenue_ft
                  || ' has county code 00 for PID ' || v_getfee.close_pid
                  || ', line ' || v_getfee.alis_line_num);
               v_getfee.county_code := '00';
            END IF;
            /*                                           
            || INSERT the record to the CCRR. We do not set receipt number and
            || abstract until after the batch is balanced.
            */
            INSERT INTO credit_card_records_received
                        (doi_application_id, 
			 [...])
                 VALUES (v_getfee.close_pid, 
		         [...]
                         v_getfee.county_code);

            alis_rdr_pkg_fccs.update_receipt_pulled_date(v_getfee.close_pid,
                                                              c_date_time,
                                                              c_doi_id,
                                                              v_error_number,
                                                              v_error_message);

            IF v_error_message != 0 THEN
               RAISE alis_error;
            END IF;

            v_ccrr_rec_out_count := v_ccrr_rec_out_count + 1;
         EXCEPTION
            WHEN alis_error THEN
                 DBMS_OUTPUT.put_line('Error: Unable to update ' || v_getfee.close_pid
                    || ' ALIS: ' || v_error_message);
                 RAISE NO_DATA_FOUND;
            WHEN DUP_VAL_ON_INDEX THEN
                 DBMS_OUTPUT.put_line('Error: Duplicate ID_INVOICE_PID encountered: '
                    || v_getfee.close_pid);
                 RAISE NO_DATA_FOUND;
            WHEN field_length_error THEN
                 RAISE NO_DATA_FOUND;
            WHEN ft_is_null THEN
                 DBMS_OUTPUT.put_line('Error: No fee type code for PID '
                  || v_getfee.close_pid || ', line ' || v_getfee.alis_line_num);
                 RAISE NO_DATA_FOUND;
            WHEN NO_DATA_FOUND THEN
                 DBMS_OUTPUT.put_line('There were no credit card transactions to process');
                 ROLLBACK TO begining_of_proc;
            WHEN VALUE_ERROR THEN
                 DBMS_OUTPUT.put_line('Error: Data value error in record ' || v_getfee.close_pid);
                 RAISE NO_DATA_FOUND;
         END;
      END LOOP;

      COMMIT;
      DBMS_OUTPUT.put_line(v_ccrr_rec_in_count || ' records read');
      DBMS_OUTPUT.put_line(v_ccrr_rec_out_count || ' records written');

      IF v_ccrr_rec_in_count != v_ccrr_rec_out_count THEN
         DBMS_OUTPUT.put_line('Warning! Not all records were processed!');
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.put_line('UPDATES NOT SAVED!');
           ROLLBACK TO begining_of_proc;
      WHEN OTHERS THEN
           DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
           DBMS_OUTPUT.put_line('UPDATES NOT SAVED!');
           ROLLBACK TO begining_of_proc;
   END load_ccrr_FNC;

Compiling this package in TOAD gives me this error message:

Code:

[highlight]PLS-00201: identifier 'IALIS_VIEW_FCCS' must be declared[/highlight]

SantaMufasa · Sep 17, 2007

Michael,

To help isolate whether TOAD is contributing to the problems, here are a few troubleshooting steps I would try next:

1) Connect, via SQL*Plus, to the user that you want to own the above procedure, "load_ccrr_FNC".
2) At the SQL> prompt, issue this SQL*Plus command:

Code:

describe ialis_view_fccs

[tt]3) a) If the DESCRIBE is successful, then re-attempt, in
SQL*Plus, to compile your code.
b) If the DESCRIBE is unsuccessful, then resolve why the
DESCRIBE could not locate "ialis_view_fccs".[/tt]
4) Post your findings here.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

weberm · Sep 18, 2007

Hello again!

I tried using SQL*PLUS already. I was able to use DESCRIBE with "ialis_view_fccs" without any problems but when I tried to compile the package body it returned the "compiled with errors" message.

SantaMufasa · Sep 18, 2007

Then here is your solution:

Code:

DROP SYNONYM IALIS_VIEW_FCCS;

CREATE OR REPLACE VIEW IALIS_VIEW_FCCS AS
SELECT * FROM ialis.viw_receipt@RCPItoFCCS;

...then re-run your code and advise us of the results.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

weberm · Sep 18, 2007

Ah, so instead of using a synonym, use a view of the view? Let me see what happens...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

A question about SYNONYMS of VIEWS

weberm

Programmer

SantaMufasa

Technical User

weberm

Programmer

SantaMufasa

Technical User

weberm

Programmer

SantaMufasa

Technical User

weberm

Programmer

Similar threads

Part and Inventory Search

Sponsor