Home » Developer & Programmer » Data Integration » ORA-06550 Error (OWB11GR2)
ORA-06550 Error [message #540343] Sun, 22 January 2012 08:49 Go to next message
kand
Messages: 20
Registered: November 2007
Location: USA
Junior Member
hi all
i'm getting the following error when i'm trying to deploy a mapping:
ORA-06550: PACKAGE BODY, line 684, column 15:
PL/SQL: ORA-00942: table or view does not exist
The GRANTS on the table are fine, what else could be the reason?
i'm using OWB 11GR2


Thanks
Re: ORA-06550 Error [message #540353 is a reply to message #540343] Sun, 22 January 2012 11:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you granted required privileges via a role, bad news for you - roles acquired via roles don't work in PL/SQL (and yes, packages ARE PL/SQL). Workaround? Try to grant privileges directly to that user.

If that's not the case, I believe you'll need to provide much more information.
Re: ORA-06550 Error [message #540354 is a reply to message #540353] Sun, 22 January 2012 11:13 Go to previous messageGo to next message
kand
Messages: 20
Registered: November 2007
Location: USA
Junior Member
Thanks
Littlefoot , what information do you want , the code?
BEGIN
  EXECUTE IMMEDIATE 'SELECT TABLE_NAME FROM OWB$TEMP_TABLES WHERE ROWNUM = 1';
  -- The OWB system table exists, now attempt to drop any previously deployed temp tables associated with this package
  DECLARE
  TYPE StageTableCur_T IS REF CURSOR;
  c1 StageTableCur_T;
  sql_stmt VARCHAR2(200);
  l_TableName VARCHAR2(32);
BEGIN
  -- Drop previously deployed temp tables associated with this map, if any
  sql_stmt := 'SELECT TABLE_NAME FROM OWB$TEMP_TABLES WHERE PACKAGE_NAME = ''MAP_DW_VVR_ELIGIBLE_IU''';
  OPEN c1 FOR sql_stmt;
  LOOP
    FETCH c1 INTO l_TableName;
    EXIT WHEN c1%NOTFOUND;
    -- process record
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ' || l_TableName;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
  END LOOP;
  CLOSE c1;
  EXECUTE IMMEDIATE 'DELETE FROM OWB$TEMP_TABLES WHERE PACKAGE_NAME = ''MAP_DW_VVR_ELIGIBLE_IU''';
  COMMIT;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END; 
EXCEPTION WHEN OTHERS THEN
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE OWB$TEMP_TABLES';
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;
  EXECUTE IMMEDIATE 'CREATE TABLE OWB$TEMP_TABLES(PACKAGE_NAME VARCHAR2(30), TABLE_NAME VARCHAR2(30))';
END;
/
CREATE OR REPLACE PACKAGE "MAP_DW_VVR_ELIGIBLE_IU" AS
OWB$MAP_OBJECT_ID VARCHAR2(32) := 'MAP_DW_VVR_ELIGIBLE_IU';
sql_stmt  VARCHAR2(32767);
get_abort BOOLEAN := FALSE;
get_abort_procedure BOOLEAN := FALSE;
get_trigger_success BOOLEAN := TRUE;
get_errors NUMBER(22) := 0;
get_status NUMBER(22) := 0;
get_error_ratio NUMBER(22) := 0;
get_global_names              VARCHAR2(10) := 'FALSE';
get_chunk_iterator            NUMBER(22) := 0;
get_exit_chunk_loop           BOOLEAN    := TRUE;
get_total_processed_rowcount  NUMBER(22) := 0;
-- Status variable for Batch cursors
"DW_VVR_ELIGIBLE_U_St" BOOLEAN := FALSE; "DW_VVR_ELIGIBLE_I_St" BOOLEAN := FALSE;

"CST_DEFAULTS_2_C_START_DT" DATE := TRUNC(SYSDATE);"CST_DEFAULTS_3_C_END_DT" DATE := TRUNC(SYSDATE)-1;"CST_DEFA_4_C_DEFAUL" DATE := TO_DATE('08/08/8888', 'MM/DD/YYYY');

-- Function Main
-- Entry point in package ""MAP_DW_VVR_ELIGIBLE_IU""
FUNCTION Main RETURN NUMBER; 

END ""MAP_DW_VVR_ELIGIBLE_IU"";

/

CREATE OR REPLACE PACKAGE BODY "MAP_DW_VVR_ELIGIBLE_IU" AS




---------------------------------------------------------------------------
-- Function "DW_VVR_ELIGIBLE_U_Bat"
--   performs batch extraction
--   Returns TRUE on success
--   Returns FALSE on failure
---------------------------------------------------------------------------
FUNCTION "DW_VVR_ELIGIBLE_U_Bat"
 RETURN BOOLEAN IS

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  BEGIN
    MERGE
/*+ APPEND PARALLEL("DW_VVR_ELIGIBLE_U") */
INTO
  "DW_VVR_ELIGIBLE"  "DW_VVR_ELIGIBLE_U"
USING
  (SELECT
/* SPL_DW_VVR_ELIGIBLE.OUT_TO_UPD */
  "SPLITTER_INPUT_SUBQUERY"."DW_VVR_ELIGIBLE_ID$1" "DW_VVR_ELIGIBLE_ID",
  NULL "MERGE_COL_ALIAS",
  NULL "MERGE_COL_ALIAS$1",
  NULL "MERGE_COL_ALIAS$2",
  NULL "MERGE_COL_ALIAS$3",
  NULL "MERGE_COL_ALIAS$4",
  NULL "MERGE_COL_ALIAS$5",
  NULL "MERGE_COL_ALIAS$6",
  NULL "MERGE_COL_ALIAS$7",
  NULL "MERGE_COL_ALIAS$8",
  NULL "MERGE_COL_ALIAS$9",
  NULL "MERGE_COL_ALIAS$10",
  "SPLITTER_INPUT_SUBQUERY"."CONT_END_DATE$1" "CONT_END_DATE",
  "SPLITTER_INPUT_SUBQUERY"."VALUE$1" "VALUE",
  NULL "MERGE_COL_ALIAS$11",
  "SPLITTER_INPUT_SUBQUERY"."LOCATION_NAME$1" "LOCATION_NAME",
  "SPLITTER_INPUT_SUBQUERY"."CHANNEL_NAME$1" "CHANNEL_NAME",
  "SPLITTER_INPUT_SUBQUERY"."TERRITORY_NAME$1" "TERRITORY_NAME",
  "SPLITTER_INPUT_SUBQUERY"."PAYMENT_METHOD$1" "PAYMENT_METHOD",
  "SPLITTER_INPUT_SUBQUERY"."CONTACT_METHOD$1" "CONTACT_METHOD",
  NULL "MERGE_COL_ALIAS$12",
  NULL "MERGE_COL_ALIAS$13",
  NULL "MERGE_COL_ALIAS$14",
  NULL "MERGE_COL_ALIAS$15",
  NULL "MERGE_COL_ALIAS$16",
  "SPLITTER_INPUT_SUBQUERY"."C_START_DT$1" "C_START_DT",
  "SPLITTER_INPUT_SUBQUERY"."C_AUDIT_USR$1" "C_AUDIT_USR",
  "SPLITTER_INPUT_SUBQUERY"."DW_SELLER_LOCATION_CD$1" "DW_SELLER_LOCATION_CD",
  "SPLITTER_INPUT_SUBQUERY"."DW_SELLER_ID$1" "DW_SELLER_ID",
  "SPLITTER_INPUT_SUBQUERY"."CONTACT_METHOD_CHANNEL$1" "CONTACT_METHOD_CHANNEL"
FROM
  (SELECT
/* CST_DEFAULTS.OUTGRP1 */
  SYSDATE/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_SYSDATE",
  REPLACE(get_model_name, '"', '')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_AUDIT_USR$1",
  TRUNC(SYSDATE)/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_START_DT$1",
  TRUNC(SYSDATE)-1/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_END_DT",
  TO_DATE('08/08/8888', 'MM/DD/YYYY')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_DEFAULT_END_DT",
  "SRC_VW_VVR_ELIGIBLE"."TRANSACTION_DT" "TRANSACTION_DT",
  "SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO",
  "SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO",
  "SRC_VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID",
  "SRC_VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC",
  "SRC_VW_VVR_ELIGIBLE"."NAME" "NAME",
  "SRC_VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1",
  "SRC_VW_VVR_ELIGIBLE"."CITY" "CITY",
  "SRC_VW_VVR_ELIGIBLE"."ST" "ST",
  "SRC_VW_VVR_ELIGIBLE"."ZIP" "ZIP",
  "SRC_VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE",
  "SRC_VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE$1",
  "SRC_VW_VVR_ELIGIBLE"."VALUE" "VALUE$1",
  "SRC_VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME$1",
  "SRC_VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME$1",
  "SRC_VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME$1",
  "SRC_VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD$1",
  "SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD$1",
  "LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID$1",
  "LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT",
  "SRC_VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
  "SRC_VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS",
  "LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT",
  "LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT",
  "SRC_VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD$1",
  "SRC_VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID$1",
  "SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL$1"
FROM
  (SELECT
/* JNR_SRC_VVR_ELIGIBLE.OUT_DW_VVR_ELIGIBLE */
  "VW_SEN_RENEWALS"."TRANSACTION_DT" "TRANSACTION_DT",
  "VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO",
  "VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO",
  "VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID",
  "VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC",
  "VW_VVR_ELIGIBLE"."NAME" "NAME",
  "VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1",
  "VW_VVR_ELIGIBLE"."CITY" "CITY",
  "VW_VVR_ELIGIBLE"."ST" "ST",
  "VW_VVR_ELIGIBLE"."ZIP" "ZIP",
  "VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE",
  "VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE",
  "VW_VVR_ELIGIBLE"."VALUE" "VALUE",
  "VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME",
  "VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME",
  "VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME",
  "VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD",
  "VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD",
  "VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
  "VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS",
  "VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD",
  "VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID",
  "VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL"
FROM
  (SELECT
/* AGG_VW_SEN_RENEWALS.OUTGRP1 */
  "AGG_VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
  "AGG_VW_SEN_RENEWALS"."SERVICE_NO" "SERVICE_NO",
  "AGG_VW_SEN_RENEWALS"."TRANSACTION_DT$1" "TRANSACTION_DT"
FROM
  (SELECT
/* VW_SEN_RENEWALS.INOUTGRP1 */
  "VW_SEN_RENEWALS"."SRC_ACCOUNT_ID"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SRC_ACCOUNT_ID */ "SRC_ACCOUNT_ID",
  "VW_SEN_RENEWALS"."SERVICE_NO"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SERVICE_NO */ "SERVICE_NO",
  MAX("VW_SEN_RENEWALS"."TRANSACTION_DT")/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.TRANSACTION_DT */ "TRANSACTION_DT$1"
FROM
  "CDW_STG"."VW_SEN_RENEWALS"  "VW_SEN_RENEWALS"
GROUP BY
"VW_SEN_RENEWALS"."SRC_ACCOUNT_ID","VW_SEN_RENEWALS"."SERVICE_NO" /* OPERATOR AGG_VW_SEN_RENEWALS: GROUP BY CLAUSE */) "AGG_VW_SEN_RENEWALS" ) "VW_SEN_RENEWALS"
,
"VW_VVR_ELIGIBLE"  "VW_VVR_ELIGIBLE"
  WHERE
  ( "VW_VVR_ELIGIBLE"."ACCOUNT_NO" =  "VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" (+) )  AND
  ( "VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" =  "VW_SEN_RENEWALS"."SERVICE_NO" (+)/* OPERATOR JNR_SRC_VVR_ELIGIBLE JOIN CONDITION */ ) ) "SRC_VW_VVR_ELIGIBLE" ,
  (SELECT
/* LKP_DW_VVR_ELIGIBLE.INOUTGRP1 */
  "LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID",
  "LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
  "LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" "SERVICE_NO",
  "LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT",
  "LKP_DW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
  "LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT",
  "LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT"
FROM
  "DW_VVR_ELIGIBLE"  "LKP_DW_VVR_ELIGIBLE") "LKP_DW_VVR_ELIGIBLE"
  WHERE
  ( "SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" =  "LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" (+) )  AND
  ( "SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" =  "LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" (+)/* OPERATOR JNR_LKP_DW_VVR_ELIGIBLE_ID JOIN CONDITION */ ) ) "SPLITTER_INPUT_SUBQUERY"
  WHERE
  ( "SPLITTER_INPUT_SUBQUERY"."DW_VVR_ELIGIBLE_ID$1" IS NOT NULL ) /* GROUP SPL_DW_VVR_ELIGIBLE.OUT_TO_UPD: SPLIT CONDITION*/
  )
    "MERGE_SUBQUERY"
ON (
  "DW_VVR_ELIGIBLE_U"."DW_VVR_ELIGIBLE_ID" = "MERGE_SUBQUERY"."DW_VVR_ELIGIBLE_ID"
   )
 
  WHEN MATCHED THEN
    UPDATE
    SET
                  "CONT_END_DT" = "MERGE_SUBQUERY"."CONT_END_DATE",
  "VALUE" = "MERGE_SUBQUERY"."VALUE",
  "LOCATION_NAME" = "MERGE_SUBQUERY"."LOCATION_NAME",
  "CHANNEL_NAME" = "MERGE_SUBQUERY"."CHANNEL_NAME",
  "TERRITORY_NAME" = "MERGE_SUBQUERY"."TERRITORY_NAME",
  "PAYMENT_METHOD" = "MERGE_SUBQUERY"."PAYMENT_METHOD",
  "CONTACT_METHOD" = "MERGE_SUBQUERY"."CONTACT_METHOD",
  "DW_UPDATE_DT" = "MERGE_SUBQUERY"."C_START_DT",
  "DW_UPDATE_BY" = "MERGE_SUBQUERY"."C_AUDIT_USR",
  "DW_SELLER_LOCATION_CD" = "MERGE_SUBQUERY"."DW_SELLER_LOCATION_CD",
  "DW_SELLER_ID" = "MERGE_SUBQUERY"."DW_SELLER_ID",
  "CHANNEL_NAME_CONTACT_METHOD" = "MERGE_SUBQUERY"."CONTACT_METHOD_CHANNEL"
      
  ;
    COMMIT;
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    COMMIT;
    RETURN FALSE;
  END;
  COMMIT;
  RETURN TRUE;
END "DW_VVR_ELIGIBLE_U_Bat";
---------------------------------------------------------------------------
-- Function "DW_VVR_ELIGIBLE_I_Bat"
--   performs batch extraction
--   Returns TRUE on success
--   Returns FALSE on failure
---------------------------------------------------------------------------
FUNCTION "DW_VVR_ELIGIBLE_I_Bat"
 RETURN BOOLEAN IS

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  BEGIN
    INSERT
    /*+ APPEND PARALLEL("DW_VVR_ELIGIBLE_I") */
    INTO
      "DW_VVR_ELIGIBLE"  "DW_VVR_ELIGIBLE_I"
      ("DW_VVR_ELIGIBLE_ID",
      "SRC_ACCOUNT_ID",
      "SERVICE_NO",
      "SRC_CONTRACT_ID",
      "RATE_PLAN_NAME",
      "NAME",
      "ADDRESS1",
      "CITY",
      "STATE_CD",
      "POSTAL_CD",
      "EMAIL_ADDRESS",
      "ACT_DT",
      "CONT_END_DT",
      "VALUE",
      "DW_LOCATION_ID",
      "LOCATION_NAME",
      "CHANNEL_NAME",
      "TERRITORY_NAME",
      "PAYMENT_METHOD",
      "CONTACT_METHOD",
      "RENEW_DT",
      "DW_CREATE_DT",
      "DW_CREATE_BY",
      "DW_UPDATE_DT",
      "DW_UPDATE_BY",
      "DW_SELLER_LOCATION_CD",
      "DW_SELLER_ID",
      "CHANNEL_NAME_CONTACT_METHOD")
      (SELECT
/* SEQ_DW_VVR_ELIGIBLE_ID.OUTGRP1 */
  "SEQ_DW_VVR_ELIGIBLE_ID".NEXTVAL/* OPERATOR SEQ_DW_VVR_ELIGIBLE_ID */ ,
  "SPLITTER_INPUT_SUBQUERY$1"."ACCOUNT_NO$2" "ACCOUNT_NO$1",
  "SPLITTER_INPUT_SUBQUERY$1"."MAIN_SERVICE_NO$2" "MAIN_SERVICE_NO$1",
  "SPLITTER_INPUT_SUBQUERY$1"."CONTRACT_ID$2" "CONTRACT_ID$1",
  "SPLITTER_INPUT_SUBQUERY$1"."RP_DESC$2" "RP_DESC$1",
  "SPLITTER_INPUT_SUBQUERY$1"."NAME$2" "NAME$1",
  "SPLITTER_INPUT_SUBQUERY$1"."ADDRESS1$2" "ADDRESS1$1",
  "SPLITTER_INPUT_SUBQUERY$1"."CITY$2" "CITY$1",
  "SPLITTER_INPUT_SUBQUERY$1"."ST$2" "ST$1",
  "SPLITTER_INPUT_SUBQUERY$1"."ZIP$2" "ZIP$1",
  "SPLITTER_INPUT_SUBQUERY$1"."EMAIL_ADDRESS$2" "EMAIL_ADDRESS$1",
  "SPLITTER_INPUT_SUBQUERY$1"."ACT_DATE$2" "ACT_DATE$1",
  "SPLITTER_INPUT_SUBQUERY$1"."CONT_END_DATE$3" "CONT_END_DATE$2",
  "SPLITTER_INPUT_SUBQUERY$1"."VALUE$3" "VALUE$2",
  "SPLITTER_INPUT_SUBQUERY$1"."DW_LOCATION_ID$2" "DW_LOCATION_ID$1",
  "SPLITTER_INPUT_SUBQUERY$1"."LOCATION_NAME$3" "LOCATION_NAME$2",
  "SPLITTER_INPUT_SUBQUERY$1"."CHANNEL_NAME$3" "CHANNEL_NAME$2",
  "SPLITTER_INPUT_SUBQUERY$1"."TERRITORY_NAME$3" "TERRITORY_NAME$2",
  "SPLITTER_INPUT_SUBQUERY$1"."PAYMENT_METHOD$3" "PAYMENT_METHOD$2",
  "SPLITTER_INPUT_SUBQUERY$1"."CONTACT_METHOD$3" "CONTACT_METHOD$2",
  CASE
 WHEN
 NVL(  "SPLITTER_INPUT_SUBQUERY$1"."TRANSACTION_DT$2"  , TO_DATE('01/01/1900', 'MM/DD/YYYY'))  >=    "SPLITTER_INPUT_SUBQUERY$1"."C_SYSDATE$2"  
 THEN
  "SPLITTER_INPUT_SUBQUERY$1"."TRANSACTION_DT$2" 
  ELSE
  NULL
  END/* ATTRIBUTE EXP_DEFAULTS.OUTGRP1.O_RENEWAL_DT: EXPRESSION */ "O_RENEWAL_DT",
  "SPLITTER_INPUT_SUBQUERY$1"."C_SYSDATE$2" "C_SYSDATE$1",
  "SPLITTER_INPUT_SUBQUERY$1"."C_AUDIT_USR$4" "C_AUDIT_USR$2",
  "SPLITTER_INPUT_SUBQUERY$1"."C_START_DT$3" "C_START_DT$2",
  "SPLITTER_INPUT_SUBQUERY$1"."C_AUDIT_USR$4" "C_AUDIT_USR$3",
  "SPLITTER_INPUT_SUBQUERY$1"."DW_SELLER_LOCATION_CD$3" "DW_SELLER_LOCATION_CD$2",
  "SPLITTER_INPUT_SUBQUERY$1"."DW_SELLER_ID$3" "DW_SELLER_ID$2",
  "SPLITTER_INPUT_SUBQUERY$1"."CONTACT_METHOD_CHANNEL$3" "CONTACT_METHOD_CHANNEL$2"
FROM
  (SELECT
/* CST_DEFAULTS.OUTGRP1 */
  SYSDATE/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_SYSDATE$2",
  REPLACE(get_model_name, '"', '')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_AUDIT_USR$4",
  TRUNC(SYSDATE)/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_START_DT$3",
  TRUNC(SYSDATE)-1/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_END_DT$1",
  TO_DATE('08/08/8888', 'MM/DD/YYYY')/* ATTRIBUTE CST_DEFAULTS.OUTGRP1.C_SYSDATE */ "C_DEFAULT_END_DT$1",
  "SRC_VW_VVR_ELIGIBLE"."TRANSACTION_DT" "TRANSACTION_DT$2",
  "SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO$2",
  "SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO$2",
  "SRC_VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID$2",
  "SRC_VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC$2",
  "SRC_VW_VVR_ELIGIBLE"."NAME" "NAME$2",
  "SRC_VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1$2",
  "SRC_VW_VVR_ELIGIBLE"."CITY" "CITY$2",
  "SRC_VW_VVR_ELIGIBLE"."ST" "ST$2",
  "SRC_VW_VVR_ELIGIBLE"."ZIP" "ZIP$2",
  "SRC_VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE$2",
  "SRC_VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE$3",
  "SRC_VW_VVR_ELIGIBLE"."VALUE" "VALUE$3",
  "SRC_VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME$3",
  "SRC_VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME$3",
  "SRC_VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME$3",
  "SRC_VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD$3",
  "SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD$3",
  "LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID$2",
  "LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT$1",
  "SRC_VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID$2",
  "SRC_VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS$2",
  "LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT$1",
  "LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT$1",
  "SRC_VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD$3",
  "SRC_VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID$3",
  "SRC_VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL$3"
FROM
  (SELECT
/* JNR_SRC_VVR_ELIGIBLE.OUT_DW_VVR_ELIGIBLE */
  "VW_SEN_RENEWALS"."TRANSACTION_DT" "TRANSACTION_DT",
  "VW_VVR_ELIGIBLE"."ACCOUNT_NO" "ACCOUNT_NO",
  "VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" "MAIN_SERVICE_NO",
  "VW_VVR_ELIGIBLE"."CONTRACT_ID" "CONTRACT_ID",
  "VW_VVR_ELIGIBLE"."RP_DESC" "RP_DESC",
  "VW_VVR_ELIGIBLE"."NAME" "NAME",
  "VW_VVR_ELIGIBLE"."ADDRESS1" "ADDRESS1",
  "VW_VVR_ELIGIBLE"."CITY" "CITY",
  "VW_VVR_ELIGIBLE"."ST" "ST",
  "VW_VVR_ELIGIBLE"."ZIP" "ZIP",
  "VW_VVR_ELIGIBLE"."ACT_DATE" "ACT_DATE",
  "VW_VVR_ELIGIBLE"."CONT_END_DATE" "CONT_END_DATE",
  "VW_VVR_ELIGIBLE"."VALUE" "VALUE",
  "VW_VVR_ELIGIBLE"."LOCATION_NAME" "LOCATION_NAME",
  "VW_VVR_ELIGIBLE"."CHANNEL_NAME" "CHANNEL_NAME",
  "VW_VVR_ELIGIBLE"."TERRITORY_NAME" "TERRITORY_NAME",
  "VW_VVR_ELIGIBLE"."PAYMENT_METHOD" "PAYMENT_METHOD",
  "VW_VVR_ELIGIBLE"."CONTACT_METHOD" "CONTACT_METHOD",
  "VW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
  "VW_VVR_ELIGIBLE"."EMAIL_ADDRESS" "EMAIL_ADDRESS",
  "VW_VVR_ELIGIBLE"."DW_SELLER_LOCATION_CD" "DW_SELLER_LOCATION_CD",
  "VW_VVR_ELIGIBLE"."DW_SELLER_ID" "DW_SELLER_ID",
  "VW_VVR_ELIGIBLE"."CONTACT_METHOD_CHANNEL" "CONTACT_METHOD_CHANNEL"
FROM
  (SELECT
/* AGG_VW_SEN_RENEWALS.OUTGRP1 */
  "AGG_VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
  "AGG_VW_SEN_RENEWALS"."SERVICE_NO" "SERVICE_NO",
  "AGG_VW_SEN_RENEWALS"."TRANSACTION_DT$1" "TRANSACTION_DT"
FROM
  (SELECT
/* VW_SEN_RENEWALS.INOUTGRP1 */
  "VW_SEN_RENEWALS"."SRC_ACCOUNT_ID"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SRC_ACCOUNT_ID */ "SRC_ACCOUNT_ID",
  "VW_SEN_RENEWALS"."SERVICE_NO"/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.SERVICE_NO */ "SERVICE_NO",
  MAX("VW_SEN_RENEWALS"."TRANSACTION_DT")/* ATTRIBUTE AGG_VW_SEN_RENEWALS.OUTGRP1.TRANSACTION_DT */ "TRANSACTION_DT$1"
FROM
  "CDW_STG"."VW_SEN_RENEWALS"  "VW_SEN_RENEWALS"
GROUP BY
"VW_SEN_RENEWALS"."SRC_ACCOUNT_ID","VW_SEN_RENEWALS"."SERVICE_NO" /* OPERATOR AGG_VW_SEN_RENEWALS: GROUP BY CLAUSE */) "AGG_VW_SEN_RENEWALS" ) "VW_SEN_RENEWALS"
,
"VW_VVR_ELIGIBLE"  "VW_VVR_ELIGIBLE"
  WHERE
  ( "VW_VVR_ELIGIBLE"."ACCOUNT_NO" =  "VW_SEN_RENEWALS"."SRC_ACCOUNT_ID" (+) )  AND
  ( "VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" =  "VW_SEN_RENEWALS"."SERVICE_NO" (+)/* OPERATOR JNR_SRC_VVR_ELIGIBLE JOIN CONDITION */ ) ) "SRC_VW_VVR_ELIGIBLE" ,
  (SELECT
/* LKP_DW_VVR_ELIGIBLE.INOUTGRP1 */
  "LKP_DW_VVR_ELIGIBLE"."DW_VVR_ELIGIBLE_ID" "DW_VVR_ELIGIBLE_ID",
  "LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" "SRC_ACCOUNT_ID",
  "LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" "SERVICE_NO",
  "LKP_DW_VVR_ELIGIBLE"."RENEW_DT" "RENEW_DT",
  "LKP_DW_VVR_ELIGIBLE"."DW_LOCATION_ID" "DW_LOCATION_ID",
  "LKP_DW_VVR_ELIGIBLE"."DW_CREATE_DT" "DW_CREATE_DT",
  "LKP_DW_VVR_ELIGIBLE"."DW_UPDATE_DT" "DW_UPDATE_DT"
FROM
  "DW_VVR_ELIGIBLE"  "LKP_DW_VVR_ELIGIBLE") "LKP_DW_VVR_ELIGIBLE"
  WHERE
  ( "SRC_VW_VVR_ELIGIBLE"."ACCOUNT_NO" =  "LKP_DW_VVR_ELIGIBLE"."SRC_ACCOUNT_ID" (+) )  AND
  ( "SRC_VW_VVR_ELIGIBLE"."MAIN_SERVICE_NO" =  "LKP_DW_VVR_ELIGIBLE"."SERVICE_NO" (+)/* OPERATOR JNR_LKP_DW_VVR_ELIGIBLE_ID JOIN CONDITION */ ) ) "SPLITTER_INPUT_SUBQUERY$1"
  WHERE
  ( "SPLITTER_INPUT_SUBQUERY$1"."DW_VVR_ELIGIBLE_ID$2" IS NULL ) /* GROUP SPL_DW_VVR_ELIGIBLE.OUT_TO_INS: SPLIT CONDITION*/
      )
    ;

    COMMIT;
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    COMMIT;
    RETURN FALSE;
  END;
  COMMIT;
  RETURN TRUE;
END "DW_VVR_ELIGIBLE_I_Bat";

FUNCTION Main RETURN NUMBER IS
get_batch_status           BOOLEAN := TRUE;
BEGIN
 




 
PROCEDURE EXEC_AUTONOMOUS_SQL(CMD IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE (CMD);
  COMMIT;
END;
  -- Initialize all batch status variables
    "DW_VVR_ELIGIBLE_U_St" := FALSE;
    "DW_VVR_ELIGIBLE_I_St" := FALSE;



           
"DW_VVR_ELIGIBLE_I_St" := "DW_VVR_ELIGIBLE_I_Bat";            
"DW_VVR_ELIGIBLE_U_St" := "DW_VVR_ELIGIBLE_U_Bat";







RETURN get_status;
END Main;
END ""MAP_DW_VVR_ELIGIBLE_IU"";

/



[mod-edit: code tags fixed by bb]

[Updated on: Sun, 22 January 2012 13:45] by Moderator

Report message to a moderator

Re: ORA-06550 Error [message #540355 is a reply to message #540354] Sun, 22 January 2012 11:31 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you saying that you double checked the way privileges were granted to user who is trying to create a package, and everything is OK (i.e. privileges were granted directly to user, not via role)?
Re: ORA-06550 Error [message #540356 is a reply to message #540354] Sun, 22 January 2012 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: ORA-06550 Error [message #540357 is a reply to message #540355] Sun, 22 January 2012 12:27 Go to previous messageGo to next message
kand
Messages: 20
Registered: November 2007
Location: USA
Junior Member
yes.
Re: ORA-06550 Error [message #540358 is a reply to message #540357] Sun, 22 January 2012 12:31 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Previous Topic: ODI installation queries - Linux and Windows
Next Topic: Mapping is not providing the desire result
Goto Forum:
  


Current Time: Thu Mar 28 11:13:21 CDT 2024