2 Replies Latest reply: Feb 8, 2012 3:23 PM by 795049 RSS

    Proc compilation issue

    795049
      All,

      We are facing issue during compilation of proc code in 11g.

      Basically the program we are trying to compile makes connection to two different database instances.

      First it connects to database DB1 and there is no issues with the compilation upto this point.
      Then it makes connection to second database DB2 where we are facing issue.


      sh makefile.ksh bacs123_batch_pap1_target_stg apl_xxxxx9/xxxx

      Pro*C/C++: Release 11.2.0.2.0 - Production on Mon Feb 6 14:25:11 2012

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      System default option values taken from: /db/app/oracle/112/precomp/admin/pcscfg.cfg

      Error at line 2395, column 3 in file bacs123_batch_pap1_target_stg.pc
      INTO :g_unix_date FROM DUAL;
      ..1
      PLS-S-00201, identifier 'APL_SECURITY.USER_KEY_REFERENCE' must be declared
      Error at line 2395, column 3 in file bacs123_batch_pap1_target_stg.pc
      INTO :g_unix_date FROM DUAL;
      ..1
      PLS-S-00000, SQL Statement ignored
      Semantic error at line 2395, column 3, file bacs123_batch_pap1_target_stg.pc:
      INTO :g_unix_date FROM DUAL;
      ..1
      PCC-S-02346, PL/SQL found semantic errors
      xlc_r4: 1501-228 (W) input file bacs123_batch_pap1_target_stg.c not found
      /usr/vac/bin/xlc: 1501-228 (W) input file bacs123_batch_pap1_target_stg.o not found

      /* connect to the nondefault database */

      EXEC SQL DECLARE QASTG DATABASE;

      EXEC SQL CONNECT :g_userid2 IDENTIFIED BY :g_password2 AT QASTG;

      printf("\nConnected to ORACLE as user: %s\n", g_userid2);

      EXEC SQL WHENEVER NOT FOUND continue;
      EXEC SQL
      SELECT to_char(to_date(:g_unix_tstamp,'YYMMDDHH24MISS') ,'YYMMDDHH24MISS')
      INTO :g_unix_date FROM DUAL;
      EXEC SQL
      INSERT INTO apl_xxxxxx.user_key_reference
      (user_target_id_cd,
      web_session_tstamp,
      company_cd,
      oracle_create_tstamp)


      contents of makefile:

      $ORACLE_HOME/bin/oraxlc -qxflag=extend_eval -O3 -q64 -DSS_64BIT_SERVER -qwarn64 -qinfo=uni -DAIXRIOS -qflag=s:s -o $1 $1.o -L$ORACLE_HOME/lib -lclntst11 `cat $ORACLE_HOME/lib/ldflags` -lld -lm `cat $ORACLE_HOME/lib/sysliblist` -lm -lc_r -lpthreads

      Please let me know if there is an alternative solution to this or if I'm making any mistake in the program
        • 1. Re: Proc compilation issue
          Sudheendra
          proc command line is connecting to db1 with the specified username to pre-compile.
          If this db does not contain the schema/objects you are referring to as part of your connection to db2 you will run into this issue.
          Connecting to db2 and executing those sql statements is a runtime behavior of your application and hence the errors.

          To fix this you have few options
          1) If possible re-create the db2 schema in db1
          2) Seperate the db1 and db2 functionality into two pc files to pre-compile.
          You can call then call the db2 related function from the first pc file.
          • 2. Re: Proc compilation issue
            795049
            Unfortunately both the options listed doesn't work for me.
            1. We cannot have similar schema on db1 as the main application is hosted on db2.
            2. We cannot split the .pc file base on the logic into two as the output of data from first is sent as out parameters to insert into db2.