Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting (Oracle 9i based on Unix Sun Solaris)
ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328258] Thu, 19 June 2008 09:47 Go to next message
moreshk
Messages: 4
Registered: June 2008
Junior Member
Hi

I am getting the below error when i try to run a PL SQL code

====================================================
06/18/2008 18:20:21-> Level:1, [SERVERERROR] ORA-20101: Record 1 Load SecMaster History - SQL(-6550) - ORA-06550: line 1, column 20:
PLS-00103: Encountered the symbol "" when expecting one of the following:

begin function package pragma procedure subtype type use
form
current cursor
The symbol "" was ignored.
ORA-06550: line 2, column 132:
PLS-00103: Encountered the symbol "" when expecting one of the following:

( - + all case mod new null


06/18/2008 18:20:21-> Level:7, UpLoader: Stored Procedure PACE_MASTERDBO.FOFBloomberg_pkg.FofBloomberg failed ...
06/18/2008 18:20:21-> Level:1, UpLoader: SQL Error Messge: [SERVERERROR] ORA-20101: Record 1 Load SecMaster History - SQL(-6550) - ORA-06550: line 1, column 20:
PLS-00103: Encountered the symbol "" when expecting one of the following:

begin function package pragma procedure subtype type use
form
current cursor
The symbol "" was ignored.
ORA-06550: line 2, column 132:
PLS-00103: Encountered the symbol "" when expecting one of the following:

( - + all case mod new null
06/18/2008 18:20:21-> Level:7, UpLoader: string: '1','2','B39F374','442488BB7','US442488BB76','20080528','HOV','K HOVNANIAN ENTERPRISES','USD','INDUSTRIAL','11.500000','2','20130501','FIXED','Corp','K HOVNANIAN ENTERPRISES','TRACE','','HOV 11 1/2 05/01/13','N','30/360','PRIV PLACEMENT','600000000.00','US','Y','N','Y','EH3719315','','COEH3719315','N','','','','','','','','','','','','','','','','','','','', '','','','','USD','USD','','US','Y','','N','','1','','','','','','','','','','',
06/18/2008 18:20:21-> Level:1, UpLoader: SQL Server: Severity 0, Message Number 20101
06/18/2008 18:20:21-> Level:1, UpLoader: ServerMessage: [SERVERERROR] ORA-20101: Record 1 Load SecMaster History - SQL(-6550) - ORA-06550: line 1, column 20:
PLS-00103: Encountered the symbol "" when expecting one of the following:

========================================================

The PL SQL code essentialy is used to load a file into the database.

The same piece of code runs fine on different environments and the problem has been occuring since we refreshed our Database in this paricular environment.

The SQL body looks as below.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE PACKAGE BODY Sec_Master_Hist_Pkg
AS

PROCEDURE Insert_Secmaster_history (table_values IN securitydbo.secmaster_history%ROWTYPE)
AS
BEGIN
INSERT INTO Securitydbo.Secmaster_history
(
DEFAULT_FLAG,
ISSUER_IND,
DAY_CNT,
OID_BOND,
ZERO_CPN,
PRVT_PLACE,
SETTLEMENT_CURRENCY,
QUANTITY_TYPE,
TRADING_UNIT,
ISO_CURRENCY_NUMBER,
CURRENCY_PRECISION,
USER_GROUP_SECTOR8,
USER_GROUP_SECTOR9,
USER_GROUP_SECTOR10,
USER_GROUP_SECTOR11,
USER_GROUP_SECTOR12,
USER_GROUP_SECTOR13,
USER_GROUP_SECTOR14,
USER_GROUP_SECTOR15,
ACCOUNTANT,
USER_TAG,
PAY_PRIN_CURR,
REC_PRIN_CURR,
PAY_COUPON,
REC_COUPON_TYPE_CODE,
REC_COUPON_FREQ_CODE,
REC_COUPON_DAY_OF_MONTH,
PAY_VRRSTBASE,
REC_VRRSTBASE,
PAY_VRRSTADJ,
REC_VRRSTDADJ,
CREATE_DATE,
COUPON_MONTH,
ISISSUERCORRECT,
ACCOUNT_ID,
SOURCE_NAME,
SOURCE_DATE,
REC_COUPON,
INCOME_CURRENCY,
REDEMPTION_CURRENCY,
TIER2_IND,
COUPON_FREQ_CODE,
COUPON_TYPE_CODE,
SECURITY_MINOR_TYPE,
LEGAL_STATUS_CODE,
COUPON_DAY_OF_MONTH,
CUSIP_CHANGE,
ANALYST_CODE,
EXCHANGE,
PRICE_MULTIPLIER,
DEF_PRICE_SOURCE,
ALT_ASSET_ID,
REGISTERED_FLAG,
DTC_ELIGIBLE_FLAG,
FOREIGN_SHARES_FLAG,
ADR_ADS_GDR_FLAG,
LIQUID_FLAG,
SEC_144A_FLAG,
SEC_RELATED_COMPANY_FLAG,
AFFLIATED_COMPANY_FLAG,
CLOSED_END_FUND_FLAG,
USER_GROUP_SECTOR1,
USER_GROUP_SECTOR2,
USER_GROUP_SECTOR3,
USER_GROUP_SECTOR4,
USER_GROUP_SECTOR5,
USER_GROUP_SECTOR6,
USER_GROUP_SECTOR7,
SECURITY_ALIAS,
SRC_INTFC_INST,
EFFECTIVE_DATE,
UPDATE_TYPE,
UPDATE_DATE,
UPDATE_SOURCE,
ISSUER_ID,
COUPON,
COUPON_FRACTIONAL_INDICATOR,
CURRENCY_CODE,
DUMMY_SWITCH,
EXPIRATION_DATE,
INVESTMENT_TYPE,
ISSUE_AMOUNT,
ISSUE_DESCRIPTION,
ISSUE_ID,
ISSUE_NAME,
MAT_DATE,
NRA_TAX_COUNTRY,
POOL_NUMBER,
PRIMARY_ASSET_ID,
SECURITY_TYPE,
TICKER,
MINOR_IND,
PRIMARY_ASSET_ID_TYPE,
SECURITY_DESC2,
PROCESS_SEC_TYPE,
VALIDATION_PROCESS_FLAG,
SETTLEMENT_METHOD,
RELEASE_DATE,
RELEASE_STATUS,
ORIGINAL_STATUS,
CURRENCY_START_DATE,
CURRENCY_END_DATE,
COA_NAME,
CONTRA_SEC_ALIAS,
ALT_ASSET_ID_TYPE
)
VALUES
(
table_values.DEFAULT_FLAG,
table_values.ISSUER_IND,
table_values.DAY_CNT,
table_values.OID_BOND,
table_values.ZERO_CPN,
table_values.PRVT_PLACE,
table_values.SETTLEMENT_CURRENCY,
table_values.QUANTITY_TYPE,
table_values.TRADING_UNIT,
table_values.ISO_CURRENCY_NUMBER,
table_values.CURRENCY_PRECISION,
table_values.USER_GROUP_SECTOR8,
table_values.USER_GROUP_SECTOR9,
table_values.USER_GROUP_SECTOR10,
table_values.USER_GROUP_SECTOR11,
table_values.USER_GROUP_SECTOR12,
table_values.USER_GROUP_SECTOR13,
table_values.USER_GROUP_SECTOR14,
table_values.USER_GROUP_SECTOR15,
table_values.ACCOUNTANT,
table_values.USER_TAG,
table_values.PAY_PRIN_CURR,
table_values.REC_PRIN_CURR,
table_values.PAY_COUPON,
table_values.REC_COUPON_TYPE_CODE,
table_values.REC_COUPON_FREQ_CODE,
table_values.REC_COUPON_DAY_OF_MONTH,
table_values.PAY_VRRSTBASE,
table_values.REC_VRRSTBASE,
table_values.PAY_VRRSTADJ,
table_values.REC_VRRSTDADJ,
table_values.CREATE_DATE,
table_values.COUPON_MONTH,
table_values.ISISSUERCORRECT,
table_values.ACCOUNT_ID,
table_values.SOURCE_NAME,
table_values.SOURCE_DATE,
table_values.REC_COUPON,
table_values.INCOME_CURRENCY,
table_values.REDEMPTION_CURRENCY,
table_values.TIER2_IND,
table_values.COUPON_FREQ_CODE,
table_values.COUPON_TYPE_CODE,
table_values.SECURITY_MINOR_TYPE,
table_values.LEGAL_STATUS_CODE,
table_values.COUPON_DAY_OF_MONTH,
table_values.CUSIP_CHANGE,
table_values.ANALYST_CODE,
table_values.EXCHANGE,
table_values.PRICE_MULTIPLIER,
table_values.DEF_PRICE_SOURCE,
table_values.ALT_ASSET_ID,
table_values.REGISTERED_FLAG,
table_values.DTC_ELIGIBLE_FLAG,
table_values.FOREIGN_SHARES_FLAG,
table_values.ADR_ADS_GDR_FLAG,
table_values.LIQUID_FLAG,
table_values.SEC_144A_FLAG,
table_values.SEC_RELATED_COMPANY_FLAG,
table_values.AFFLIATED_COMPANY_FLAG,
table_values.CLOSED_END_FUND_FLAG,
table_values.USER_GROUP_SECTOR1,
table_values.USER_GROUP_SECTOR2,
table_values.USER_GROUP_SECTOR3,
table_values.USER_GROUP_SECTOR4,
table_values.USER_GROUP_SECTOR5,
table_values.USER_GROUP_SECTOR6,
table_values.USER_GROUP_SECTOR7,
table_values.SECURITY_ALIAS,
table_values.SRC_INTFC_INST,
table_values.EFFECTIVE_DATE,
table_values.UPDATE_TYPE,
table_values.UPDATE_DATE,
table_values.UPDATE_SOURCE,
table_values.ISSUER_ID,
table_values.COUPON,
table_values.COUPON_FRACTIONAL_INDICATOR,
table_values.CURRENCY_CODE,
table_values.DUMMY_SWITCH,
table_values.EXPIRATION_DATE,
table_values.INVESTMENT_TYPE,
table_values.ISSUE_AMOUNT,
table_values.ISSUE_DESCRIPTION,
table_values.ISSUE_ID,
table_values.ISSUE_NAME,
table_values.MAT_DATE,
table_values.NRA_TAX_COUNTRY,
table_values.POOL_NUMBER,
table_values.PRIMARY_ASSET_ID,
table_values.SECURITY_TYPE,
table_values.TICKER,
table_values.MINOR_IND,
table_values.PRIMARY_ASSET_ID_TYPE,
table_values.SECURITY_DESC2,
table_values.PROCESS_SEC_TYPE,
table_values.VALIDATION_PROCESS_FLAG,
table_values.SETTLEMENT_METHOD,
table_values.RELEASE_DATE,
table_values.RELEASE_STATUS,
table_values.ORIGINAL_STATUS,
table_values.CURRENCY_START_DATE,
table_values.CURRENCY_END_DATE,
table_values.COA_NAME,
table_values.CONTRA_SEC_ALIAS,
table_values.ALT_ASSET_ID_TYPE
);

END Insert_Secmaster_history;

PROCEDURE
Update_Secmaster_history (table_values IN securitydbo.secmaster_history%ROWTYPE)
AS
BEGIN

UPDATE Securitydbo.Secmaster_history SET

DEFAULT_FLAG = NVL(table_values.DEFAULT_FLAG,DEFAULT_FLAG),
ISSUER_IND = NVL(table_values.ISSUER_IND,ISSUER_IND),
DAY_CNT = NVL(table_values.DAY_CNT,DAY_CNT),
OID_BOND = NVL(table_values.OID_BOND,OID_BOND),
ZERO_CPN = NVL(table_values.ZERO_CPN,ZERO_CPN),
PRVT_PLACE = NVL(table_values.PRVT_PLACE,PRVT_PLACE),
SETTLEMENT_CURRENCY = NVL(table_values.SETTLEMENT_CURRENCY,SETTLEMENT_CURRENCY),
QUANTITY_TYPE = NVL(table_values.QUANTITY_TYPE,QUANTITY_TYPE),
TRADING_UNIT = NVL(table_values.TRADING_UNIT,TRADING_UNIT),
ISO_CURRENCY_NUMBER = NVL(table_values.ISO_CURRENCY_NUMBER,ISO_CURRENCY_NUMBER),
CURRENCY_PRECISION = NVL(table_values.CURRENCY_PRECISION,CURRENCY_PRECISION),
USER_GROUP_SECTOR8 = NVL(table_values.USER_GROUP_SECTOR8,USER_GROUP_SECTOR8),
USER_GROUP_SECTOR9 = NVL(table_values.USER_GROUP_SECTOR9,USER_GROUP_SECTOR9),
USER_GROUP_SECTOR10 = NVL(table_values.USER_GROUP_SECTOR10,USER_GROUP_SECTOR10),
USER_GROUP_SECTOR11 = NVL(table_values.USER_GROUP_SECTOR11,USER_GROUP_SECTOR11),
USER_GROUP_SECTOR12 = NVL(table_values.USER_GROUP_SECTOR12,USER_GROUP_SECTOR12),
USER_GROUP_SECTOR13 = NVL(table_values.USER_GROUP_SECTOR13,USER_GROUP_SECTOR13),
USER_GROUP_SECTOR14 = NVL(table_values.USER_GROUP_SECTOR14,USER_GROUP_SECTOR14),
USER_GROUP_SECTOR15 = NVL(table_values.USER_GROUP_SECTOR15,USER_GROUP_SECTOR15),
ACCOUNTANT = NVL(table_values.ACCOUNTANT,ACCOUNTANT),
USER_TAG = NVL(table_values.USER_TAG,USER_TAG),
PAY_PRIN_CURR = NVL(table_values.PAY_PRIN_CURR,PAY_PRIN_CURR),
REC_PRIN_CURR = NVL(table_values.REC_PRIN_CURR,REC_PRIN_CURR),
PAY_COUPON = NVL(table_values.PAY_COUPON,PAY_COUPON),
REC_COUPON_TYPE_CODE = NVL(table_values.REC_COUPON_TYPE_CODE,REC_COUPON_TYPE_CODE),
REC_COUPON_FREQ_CODE = NVL(table_values.REC_COUPON_FREQ_CODE,REC_COUPON_FREQ_CODE),
REC_COUPON_DAY_OF_MONTH = NVL(table_values.REC_COUPON_DAY_OF_MONTH,REC_COUPON_DAY_OF_MONTH),
PAY_VRRSTBASE = NVL(table_values.PAY_VRRSTBASE,PAY_VRRSTBASE),
REC_VRRSTBASE = NVL(table_values.REC_VRRSTBASE,REC_VRRSTBASE),
PAY_VRRSTADJ = NVL(table_values.PAY_VRRSTADJ,PAY_VRRSTADJ),
REC_VRRSTDADJ = NVL(table_values.REC_VRRSTDADJ,REC_VRRSTDADJ),
CREATE_DATE = NVL(table_values.CREATE_DATE,CREATE_DATE),
COUPON_MONTH = NVL(table_values.COUPON_MONTH,COUPON_MONTH),
ISISSUERCORRECT = NVL(table_values.ISISSUERCORRECT,ISISSUERCORRECT),
ACCOUNT_ID = NVL(table_values.ACCOUNT_ID,ACCOUNT_ID),
SOURCE_NAME = NVL(table_values.SOURCE_NAME,SOURCE_NAME),
SOURCE_DATE = NVL(table_values.SOURCE_DATE,SOURCE_DATE),
REC_COUPON = NVL(table_values.REC_COUPON,REC_COUPON),
INCOME_CURRENCY = NVL(table_values.INCOME_CURRENCY,INCOME_CURRENCY),
REDEMPTION_CURRENCY = NVL(table_values.REDEMPTION_CURRENCY,REDEMPTION_CURRENCY),
TIER2_IND = NVL(table_values.TIER2_IND,TIER2_IND),
COUPON_FREQ_CODE = NVL(table_values.COUPON_FREQ_CODE,COUPON_FREQ_CODE),
COUPON_TYPE_CODE = NVL(table_values.COUPON_TYPE_CODE,COUPON_TYPE_CODE),
SECURITY_MINOR_TYPE = NVL(table_values.SECURITY_MINOR_TYPE,SECURITY_MINOR_TYPE),
LEGAL_STATUS_CODE = NVL(table_values.LEGAL_STATUS_CODE,LEGAL_STATUS_CODE),
COUPON_DAY_OF_MONTH = NVL(table_values.COUPON_DAY_OF_MONTH,COUPON_DAY_OF_MONTH),
CUSIP_CHANGE = NVL(table_values.CUSIP_CHANGE,CUSIP_CHANGE),
ANALYST_CODE = NVL(table_values.ANALYST_CODE,ANALYST_CODE),
EXCHANGE = NVL(table_values.EXCHANGE,EXCHANGE),
PRICE_MULTIPLIER = NVL(table_values.PRICE_MULTIPLIER,PRICE_MULTIPLIER),
DEF_PRICE_SOURCE = NVL(table_values.DEF_PRICE_SOURCE,DEF_PRICE_SOURCE),
ALT_ASSET_ID = NVL(table_values.ALT_ASSET_ID,ALT_ASSET_ID),
REGISTERED_FLAG = NVL(table_values.REGISTERED_FLAG,REGISTERED_FLAG),
DTC_ELIGIBLE_FLAG = NVL(table_values.DTC_ELIGIBLE_FLAG,DTC_ELIGIBLE_FLAG),
FOREIGN_SHARES_FLAG = NVL(table_values.FOREIGN_SHARES_FLAG,FOREIGN_SHARES_FLAG),
ADR_ADS_GDR_FLAG = NVL(table_values.ADR_ADS_GDR_FLAG,ADR_ADS_GDR_FLAG),
LIQUID_FLAG = NVL(table_values.LIQUID_FLAG,LIQUID_FLAG),
SEC_144A_FLAG = NVL(table_values.SEC_144A_FLAG,SEC_144A_FLAG),
SEC_RELATED_COMPANY_FLAG = NVL(table_values.SEC_RELATED_COMPANY_FLAG,SEC_RELATED_COMPANY_FLAG),
AFFLIATED_COMPANY_FLAG = NVL(table_values.AFFLIATED_COMPANY_FLAG,AFFLIATED_COMPANY_FLAG),
CLOSED_END_FUND_FLAG = NVL(table_values.CLOSED_END_FUND_FLAG,CLOSED_END_FUND_FLAG),
USER_GROUP_SECTOR1 = NVL(table_values.USER_GROUP_SECTOR1,USER_GROUP_SECTOR1),
USER_GROUP_SECTOR2 = NVL(table_values.USER_GROUP_SECTOR2,USER_GROUP_SECTOR2),
USER_GROUP_SECTOR3 = NVL(table_values.USER_GROUP_SECTOR3,USER_GROUP_SECTOR3),
USER_GROUP_SECTOR4 = NVL(table_values.USER_GROUP_SECTOR4,USER_GROUP_SECTOR4),
USER_GROUP_SECTOR5 = NVL(table_values.USER_GROUP_SECTOR5,USER_GROUP_SECTOR5),
USER_GROUP_SECTOR6 = NVL(table_values.USER_GROUP_SECTOR6,USER_GROUP_SECTOR6),
USER_GROUP_SECTOR7 = NVL(table_values.USER_GROUP_SECTOR7,USER_GROUP_SECTOR7),
SECURITY_ALIAS = NVL(table_values.SECURITY_ALIAS,SECURITY_ALIAS),
SRC_INTFC_INST = NVL(table_values.SRC_INTFC_INST,SRC_INTFC_INST),
EFFECTIVE_DATE = NVL(table_values.EFFECTIVE_DATE,EFFECTIVE_DATE),
UPDATE_TYPE = NVL(table_values.UPDATE_TYPE,UPDATE_TYPE),
UPDATE_DATE = NVL(table_values.UPDATE_DATE,UPDATE_DATE),
UPDATE_SOURCE = NVL(table_values.UPDATE_SOURCE,UPDATE_SOURCE),
ISSUER_ID = NVL(table_values.ISSUER_ID,ISSUER_ID),
COUPON = NVL(table_values.COUPON,COUPON),
COUPON_FRACTIONAL_INDICATOR = NVL(table_values.COUPON_FRACTIONAL_INDICATOR,COUPON_FRACTIONAL_INDICATOR),
CURRENCY_CODE = NVL(table_values.CURRENCY_CODE,CURRENCY_CODE),
DUMMY_SWITCH = NVL(table_values.DUMMY_SWITCH,DUMMY_SWITCH),
EXPIRATION_DATE = NVL(table_values.EXPIRATION_DATE,EXPIRATION_DATE),
INVESTMENT_TYPE = NVL(table_values.INVESTMENT_TYPE,INVESTMENT_TYPE),
ISSUE_AMOUNT = NVL(table_values.ISSUE_AMOUNT,ISSUE_AMOUNT),
ISSUE_DESCRIPTION = NVL(table_values.ISSUE_DESCRIPTION,ISSUE_DESCRIPTION),
ISSUE_ID = NVL(table_values.ISSUE_ID,ISSUE_ID),
ISSUE_NAME = NVL(table_values.ISSUE_NAME,ISSUE_NAME),
MAT_DATE = NVL(table_values.MAT_DATE,MAT_DATE),
NRA_TAX_COUNTRY = NVL(table_values.NRA_TAX_COUNTRY,NRA_TAX_COUNTRY),
POOL_NUMBER = NVL(table_values.POOL_NUMBER,POOL_NUMBER),
PRIMARY_ASSET_ID = NVL(table_values.PRIMARY_ASSET_ID,PRIMARY_ASSET_ID),
SECURITY_TYPE = NVL(table_values.SECURITY_TYPE,SECURITY_TYPE),
TICKER = NVL(table_values.TICKER,TICKER),
MINOR_IND = NVL(table_values.MINOR_IND,MINOR_IND),
PRIMARY_ASSET_ID_TYPE = NVL(table_values.PRIMARY_ASSET_ID_TYPE,PRIMARY_ASSET_ID_TYPE),
PROCESS_SEC_TYPE = NVL(table_values.PROCESS_SEC_TYPE,PROCESS_SEC_TYPE),
SECURITY_DESC2 = NVL(table_values.SECURITY_DESC2,SECURITY_DESC2),
VALIDATION_PROCESS_FLAG = NVL(table_values.VALIDATION_PROCESS_FLAG, VALIDATION_PROCESS_FLAG),
SETTLEMENT_METHOD = NVL(table_values.SETTLEMENT_METHOD, SETTLEMENT_METHOD),
RELEASE_DATE = NVL(table_values.RELEASE_DATE, RELEASE_DATE),
RELEASE_STATUS = NVL(table_values.RELEASE_STATUS, RELEASE_STATUS),
ORIGINAL_STATUS = NVL(table_values.ORIGINAL_STATUS, ORIGINAL_STATUS),
CURRENCY_START_DATE = NVL(table_values.CURRENCY_START_DATE, CURRENCY_START_DATE),
CURRENCY_END_DATE = NVL(table_values.CURRENCY_END_DATE, CURRENCY_END_DATE),
COA_NAME = NVL(table_values.COA_NAME, COA_NAME),
CONTRA_SEC_ALIAS = NVL(table_values.CONTRA_SEC_ALIAS, CONTRA_SEC_ALIAS),
ALT_ASSET_ID_TYPE = NVL(table_values.ALT_ASSET_ID_TYPE, ALT_ASSET_ID_TYPE)

WHERE
Security_alias = table_values.security_alias AND src_intfc_inst = table_values.src_intfc_inst AND
effective_date = table_values.effective_date;

END Update_Secmaster_history;

PROCEDURE Compare_Secmaster_history
(owner_name IN VARCHAR2, t_name IN VARCHAR2, table_values1 IN VARCHAR2, table_values2 IN VARCHAR2, result OUT BOOLEAN)
AS
TYPE CompareRecType IS REF CURSOR;
CompareRec CompareRecType;

L_CurSqlStmt VARCHAR2(2000);
L_Column_Name VARCHAR2(100);
L_Column_Type VARCHAR2(100);
L_Stmt VARCHAR2(10);
G_UpdStmt VARCHAR2(4000);
L_SqlStmt VARCHAR2(32767);
P_NVL VARCHAR2(20);
L_Cnt INT;


BEGIN

IF Sec_mas_comp_stmt IS NOT NULL
THEN
EXECUTE IMMEDIATE Sec_mas_comp_stmt USING IN OUT L_Cnt;
IF L_Cnt = 1 THEN
result := TRUE;
ELSIF L_Cnt = 0 THEN
result := FALSE;
END IF;
RETURN;
END IF;

L_CurSqlStmt := 'SELECT column_name, data_type
FROM All_Tab_Columns
WHERE Table_Name = :B AND owner = :C
ORDER BY Column_Id';

L_SqlStmt := 'DECLARE CNT NUMBER;
BEGIN SELECT COUNT(*) INTO :CNT FROM DUAL WHERE ';


OPEN CompareRec
FOR L_CurSqlStmt
USING t_name, owner_name;

LOOP
FETCH CompareRec INTO L_Column_Name, L_Column_Type;
EXIT WHEN CompareRec%NOTFOUND;

IF L_Column_type = 'DATE' THEN
P_NVL := ',''01-MAY-01'')';
ELSE
P_NVL := ',''-1111'')';
END IF;

IF L_Column_Name NOT IN ('UPDATE_DATE', 'UPDATE_SOURCE','EFFECTIVE_DATE','SOURCE_DATE') THEN

L_SqlStmt := L_SqlStmt||L_Stmt||'NVL('||table_values1||'.'||L_Column_name||P_NVL||' =
NVL('||table_values2||'.'||L_Column_name||P_NVL;
END IF;

L_Stmt := ' AND ';
END LOOP;

CLOSE CompareRec;
L_SqlStmt := L_SqlStmt||';END;';
L_Stmt := NULL;



EXECUTE IMMEDIATE L_sqlstmt USING OUT L_Cnt;

IF L_Cnt = 1 THEN
result := TRUE;
ELSIF L_Cnt = 0 THEN
result := FALSE;
END IF;

Sec_mas_comp_stmt := L_SqlStmt;

END Compare_Secmaster_history;

PROCEDURE Sec_master_update_date (secalias IN INT)
AS
BEGIN
UPDATE Securitydbo.Security_master SET
Update_date = SYSDATE
WHERE Security_alias = secalias;
END Sec_master_update_date;




PROCEDURE Load_SecMaster_History (in_sec IN Securitydbo.secmaster_history%ROWTYPE,
master IN Securitydbo.security_master%ROWTYPE) AS

CURSOR Get_Sec_Id (in_alias Securitydbo.secmaster_history.security_alias%TYPE,
in_src_id Securitydbo.secmaster_history.src_intfc_inst%TYPE,
in_eff_dt Securitydbo.secmaster_history.effective_date%TYPE)
IS
SELECT ROWID
FROM Securitydbo.secmaster_history
WHERE security_alias = in_alias
AND src_intfc_inst = in_src_id
AND effective_date = in_eff_dt;


Get_Sec_Id_Rec Get_Sec_Id%ROWTYPE;
I_SOURCENAME Securitydbo.security_master.Source_name%TYPE ;


CURSOR get_all_sec (in_alias Securitydbo.secmaster_history.security_alias%TYPE,
in_src_id Securitydbo.secmaster_history.src_intfc_inst%TYPE)
IS
SELECT sh.*
FROM Securitydbo.secmaster_history sh
WHERE security_alias = in_alias
AND src_intfc_inst = in_src_id
ORDER BY effective_date DESC;



Get_all_sec_rec Securitydbo.secmaster_history%ROWTYPE;
l_rowid UROWID;

CURSOR get_prev_record (in_alias Securitydbo.secmaster_history.security_alias%TYPE,
in_src_id Securitydbo.secmaster_history.src_intfc_inst%TYPE,
in_eff_dt Securitydbo.secmaster_history.effective_date%TYPE) IS

SELECT ROWID
FROM securitydbo.secmaster_history
WHERE Security_alias = In_alias
AND Src_intfc_inst = In_src_id
AND Effective_date < In_eff_dt
ORDER BY Effective_Date DESC;

get_prev_record_rec get_prev_record%ROWTYPE;

CURSOR get_next_record (in_alias Securitydbo.secmaster_history.security_alias%TYPE,
in_src_id Securitydbo.secmaster_history.src_intfc_inst%TYPE,
in_eff_dt Securitydbo.secmaster_history.effective_date%TYPE) IS
SELECT ROWID
FROM securitydbo.secmaster_history
WHERE security_alias = in_alias
AND src_intfc_inst = in_src_id
AND effective_date > in_eff_dt
ORDER BY Effective_Date ASC;


get_next_record_rec get_next_record%ROWTYPE;

before_record Securitydbo.secmaster_history%ROWTYPE;

in_compare_rec Securitydbo.secmaster_history%ROWTYPE;

out_flag BOOLEAN;
next_found BOOLEAN := FALSE;
prev_found BOOLEAN := FALSE;

in_prev_compare_rec securitydbo.secmaster_history%ROWTYPE;
in_next_compare_rec securitydbo.secmaster_history%ROWTYPE;

BEGIN
Pace_Package.Secmaster_hist_record := in_sec;
OPEN get_all_sec(in_sec.security_alias,in_sec.src_intfc_inst);
FETCH get_all_sec INTO get_all_sec_rec;
IF (get_all_sec%NOTFOUND) THEN
----Added logic Anil Kumar on 10/19/2001 to take care of the shell security created by Trades (for N Trust only)

BEGIN
SELECT SOURCE_NAME INTO I_SOURCENAME FROM Securitydbo.Security_master
WHERE Security_Alias = master.SECURITY_ALIAS;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;


IF ((I_SOURCENAME = 'TRD') AND (I_SOURCENAME IS NOT NULL)) THEN

Sec_Master_Pkg.Update_secmaster(master);
ELSE
----End of modification by Anil Kumar on 10/19/2001

UPDATE Securitydbo.Security_Master
SET update_Date = master.update_date,
update_source = master.Update_source
WHERE security_alias = master.SECURITY_ALIAS;

IF (SQL%ROWCOUNT = 0) THEN
Sec_Master_Pkg.Insert_Secmaster(master);

END IF;
END IF;

/*----------------------------------------------------------
Completly a new instrument to the history table
----------------------------------------------------------*/
--pace_history_loader.
insert_secmaster_history(in_sec);
Sec_master_update_date (in_sec.security_alias);
CLOSE get_all_sec;
RETURN;
ELSE
/*--------------------------------------------------------------
Most Probable case. This happens on a regular load process
--------------------------------------------------------------*/
IF (get_all_sec_rec.effective_date < in_sec.effective_date) THEN
--pace_history_loader.

Pace_Package.Secmaster_hist_record_temp := get_all_sec_rec;
compare_secmaster_history('SECURITYDBO','SECMASTER_HISTORY','pace_package.Secmaster_hist_record','pace_package.Secmaster_hist_record_ temp',out_flag);
IF (NOT out_flag) THEN
--pace_history_loader.
Insert_Secmaster_History(in_sec);
Sec_master_update_date (in_sec.security_alias);
END IF;
CLOSE get_all_sec;
RETURN;

END IF;
END IF;
CLOSE get_all_sec;


OPEN
Get_Sec_Id(in_sec.security_alias,in_sec.src_intfc_inst,in_sec.effective_date);
FETCH Get_Sec_Id INTO get_sec_id_rec;
IF (Get_Sec_Id%FOUND) THEN

/*-----------------------------------------------------------------
Found a match for the effective date Happend when a load is rerun
-----------------------------------------------------------------*/
--pace_history_loader.
Update_Secmaster_History(in_sec);
Sec_master_update_date (in_sec.security_alias);
CLOSE Get_Sec_Id;
RETURN;
END IF;
CLOSE Get_Sec_Id;


/*---------------------------------------------------------------------------
This section of code is executed when the data load is a history load
-----------------------------------------------------------------------------*/

OPEN
get_prev_record(in_sec.security_alias,in_sec.src_intfc_inst,in_sec.effective_date);
FETCH get_prev_record INTO get_prev_record_rec;
IF (get_prev_record%FOUND) THEN

SELECT *
INTO in_prev_compare_rec
FROM securitydbo.secmaster_history
WHERE ROWID = get_prev_record_rec.ROWID;

prev_found := TRUE;

END IF;

OPEN
get_next_record(in_sec.security_alias,in_sec.src_intfc_inst,in_sec.effective_date);
FETCH get_next_record INTO get_next_record_rec;
IF (get_next_record%FOUND) THEN
SELECT *
INTO in_next_compare_rec
FROM securitydbo.secmaster_history
WHERE ROWID = get_next_record_rec.ROWID;

next_found := TRUE;

END IF;


/*----------------------------------------------------------
If previous date is found and next date is found.Compare incoming record
with prev record.If the records are
different compare the incoming record with next record.If it is same as
next record then update the next date
to current date and return.If it is different than the next record then
insert a record with current date
----------------------------------------------------------*/

IF (prev_found AND next_found) THEN
-- pace_history_loader.
Pace_Package.Secmaster_hist_record_temp := in_prev_compare_rec;
Compare_Secmaster_History('SECURITYDBO','SECMASTER_HISTORY','pace_package.Secmaster_hist_record_temp','pace_package.Secmaster_hist_re cord_temp',out_flag);

IF (NOT out_flag) THEN
-- pace_history_loader.
Pace_Package.Secmaster_hist_record_temp := in_next_compare_rec;
Compare_secmaster_history('SECURITYDBO','SECMASTER_HISTORY','pace_package.Secmaster_hist_record','pace_package.Secmaster_hist_record_ temp',out_flag);
IF (out_flag) THEN
UPDATE Securitydbo.secmaster_history
SET effective_date = in_sec.effective_date
WHERE ROWID = get_next_record_rec.ROWID;

ELSE
-- pace_history_loader.
Insert_Secmaster_History(in_sec);
Sec_master_update_date (in_sec.security_alias);
END IF;
CLOSE get_prev_record;
CLOSE get_next_record;

RETURN;
END IF;

CLOSE get_prev_record;
CLOSE get_next_record;
RETURN;
END IF;


/*----------------------------------------------------------
If previous date is not found and next date is found.Compare incoming
record with next record.If the records are
different insert the incoming record.If it is same as next record then
update the next date to current date and return.
----------------------------------------------------------*/


IF (NOT prev_found AND next_found) THEN
-- pace_history_loader.
Pace_Package.Secmaster_hist_record_temp := in_next_compare_rec;
Compare_secmaster_history('SECURITYDBO','SECMASTER_HISTORY','pace_package.Secmaster_hist_record','pace_package.Secmaster_hist_record_ temp',out_flag);

IF (out_flag) THEN
UPDATE Securitydbo.secmaster_history
SET effective_date = in_sec.effective_date
WHERE ROWID = get_next_record_rec.ROWID;
ELSE
-- pace_history_loader.
Insert_Secmaster_History(in_sec);
Sec_master_update_date (in_sec.security_alias);
END IF;
CLOSE get_prev_record;
CLOSE get_next_record;
RETURN;
END IF;

next_found := FALSE;
prev_found := FALSE;

END Load_SecMaster_History;

END Sec_Master_Hist_Pkg;
/

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Please advise what the issue could be.

Thanks
Moresh
Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328259 is a reply to message #328258] Thu, 19 June 2008 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328262 is a reply to message #328259] Thu, 19 June 2008 09:57 Go to previous messageGo to next message
moreshk
Messages: 4
Registered: June 2008
Junior Member
Hi

I am new over here , so i will definetely try and follow the guidelines.

However is there anything specefic you would like to point out in the post ? Will help me in rectifying it me faster.

Thanks
Moresh
Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328264 is a reply to message #328262] Thu, 19 June 2008 10:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

However is there anything specefic you would like to point out in the post ? Will help me in rectifying it me faster.



You have a syntax error somewhere. Where is impossible to tell with the information you posted.
Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328268 is a reply to message #328264] Thu, 19 June 2008 10:12 Go to previous messageGo to next message
moreshk
Messages: 4
Registered: June 2008
Junior Member
Hi ThomasG

I was reffering to the posting guidelines in the quote you pointed out , whether there was any sepcefic guideline i should keep in mind.

Coming back to the request , let me elaborate.

We have a pipe delimited file which is uploaded in our database using a PL SQL code . The piece of code below is called from a master package body

l_Debug_str := 'Load SecMaster History';
Sec_Master_Hist_Pkg.Load_SecMaster_History(Pace_Package.Secmaster_hist_record,Pace_Package.Security_master_record);
END IF;

The object is compiled and is used at run time to load the file. The package compiles succesfully however when i try to invoke it from a seperate application , it fails and the log files show the below error.

Any ideas ?

Thanks
Moresh
Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328273 is a reply to message #328258] Thu, 19 June 2008 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You have a syntax error somewhere. Where is impossible to tell with the information you posted.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

[Updated on: Thu, 19 June 2008 10:21] by Moderator

Report message to a moderator

Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328278 is a reply to message #328258] Thu, 19 June 2008 10:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
According to
Quote:
I am getting the below error when i try to run a PL SQL code
...
ORA-06550: line 1, column 20:
PLS-00103: Encountered the symbol "" when expecting one of the following:
...
ORA-06550: line 2, column 132:
PLS-00103: Encountered the symbol "" when expecting one of the following:
...

and the fact the code contains dynamic SQL (execute immediate), it is most likely that the generated SQL statement is wrong.
To debug it, you may write/log the SQL statement before execution and (after it fails) examine what is wrong with it.
I doubt anybody here will do it as nobody has your tables and the code is complex and (with the lack of formatting) almost unreadable.
Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328286 is a reply to message #328278] Thu, 19 June 2008 10:49 Go to previous messageGo to next message
moreshk
Messages: 4
Registered: June 2008
Junior Member
Yes , the code is complex and there are lot of tables which will also require setup so it is unfeasible for someone to debug it here.

However just as a sidenote i wanted to point out that we have a replica of the setup in our production environment and the same seems to run fine over there

It is our UAT environment which is throwing this error since we refreshed our tables. A table compare between the 2 environments didnt reveal any differences apart from instance numbers

[Updated on: Thu, 19 June 2008 10:50]

Report message to a moderator

Re: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting [message #328529 is a reply to message #328286] Fri, 20 June 2008 07:07 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is the main problem with dynamic SQL, and why it should be avoided at all cost.

It's pretty much impossible to debug when you don't have added exception handlers which log the failed SQL statement right from the start.
Previous Topic: Finding specific date
Next Topic: Date format
Goto Forum:
  


Current Time: Thu Dec 08 18:10:14 CST 2016

Total time taken to generate the page: 0.05788 seconds