ORA-00917: missing comma in bods script

dataservices job design
1

Hi,

I am using script in bods 3.2 for this i created one Batch_control table in oracle database and i insert global varilables in job level

Here is my global variables

$SYSDATE DATE
$CDC_DATE DATE
$BATCH_END_DATE DATE
$BATCH_NAME VARCHAR(25)

And my Batch_control table format in oracle is here

Batch_name varchar2(25)
Batch_status varchar2(10)
Batch_start_Date Date
Batch_End_Date Date
Load_Date Date

in script i implement below commands

#SET TODDAYS DATE
$SYSDATE = cast( sysdate( ),‘DATE’);
print( 'TODAY DATE IS: ’ || cast( $SYSDATE ,‘VARCHAR(24)’));

#SET CDC_DATE
$CDC_DATE = nvl( cast( sql( ‘ODS_DS_TRG_ORACLE’,‘SELECT MAX(BATCH_END_DATE) FROM DS_TRG.BATCH_CONTROL WHERE BATCH_NAME = {$BATCH_NAME }
AND BATCH_STATUS='SUCCESS'’),‘DATE’),cast(to_date(‘1900.01.01’,‘YYYY.MM.DD’),‘DATE’));
print( ‘CDC_DATE IS:’||cast( $CDC_DATE,‘VARCHAR(24)’));

#MAKE AN ENTRY IN BATCH_CONTROL
#BATCH_NAME BATCH_STATUS BATCH_START_DATE BATCH_END_DATE LOAD_DATE
sql( ‘ODS_DS_TRG_ORACLE’,‘INSERT INTO VASU_DS_TRG.BATCH_CONTROL VALUES ({$BATCH_NAME},'STARTED',[TO_CHAR($CDC_DATE,'YYYY.MM.DD')],NULL,[TO_CHAR($SYSDATE,'YYYY.MM.DD')])’);

#BATCH_NAME BATCH_STATUS BATCH_START_DATE BATCH_END_DATE LOAD_DATE
#PRODUCT_DIM STARTED 1900.01.01 2013-12-09(SYSDATE)

when I validate this script its not shows any errors but when I run this script I has been getting below error.

4844 6008 RUN-050304 12/10/2013 11:57:45 AM Function call <sql ( ODS_DS_TRG_ORACLE, INSERT INTO DS_TRG.BATCH_CONTROL VALUES
4844 6008 RUN-050304 12/10/2013 11:57:45 AM (‘PRODUCT_DIM’,‘STARTED’,1900.01.01,NULL,2013.12.10) ) > failed, due to error <70301>: <Oracle error message for
4844 6008 RUN-050304 12/10/2013 11:57:45 AM operation : <ORA-00917: missing comma

please help me to solve this issue
:hb: :hb:

Thanks
Venki

ursfriend77 (BOB member since 2011-03-02)

2

If the columns will be DATE in the Oracle DBMS, then I bet the below should be your INSERT Script

#MAKE AN ENTRY IN BATCH_CONTROL
#BATCH_NAME BATCH_STATUS BATCH_START_DATE BATCH_END_DATE LOAD_DATE
sql( ‘ODS_DS_TRG_ORACLE’,‘INSERT INTO VASU_DS_TRG.BATCH_CONTROL VALUES ({$BATCH_NAME},‘STARTED’,{$CDC_DATE},NULL,{$SYSDATE));’);

Another side note, you are doing too many conversions, it is not nice to have too much conversions too, it basically drags your execution time.

ganeshxp :us: (BOB member since 2008-07-17)

3

Hi Ganesh,

Thanks for your response,I used in my script what you mentioned

sql( ‘ODS_DS_TRG_ORACLE’,‘INSERT INTO DS_TRG.BATCH_CONTROL VALUES ({$BATCH_NAME},‘STARTED’,{$CDC_DATE},NULL,{$SYSDATE})’);

but i have been getting below error.It is very crucial to me convert oracle datatypes.

5068 5392 RUN-050304 12/10/2013 1:20:12 PM Function call <sql ( ODS_DS_TRG_ORACLE, INSERT INTO DS_TRG.BATCH_CONTROL VALUES
5068 5392 RUN-050304 12/10/2013 1:20:12 PM (‘PRODUCT_DIM’,‘STARTED’,‘1900.01.01’,NULL,‘2013.12.10’) ) > failed, due to error <70301>: <Oracle error message for
5068 5392 RUN-050304 12/10/2013 1:20:12 PM operation : <ORA-01861: literal does not match format string

Thanks
venki

ursfriend77 (BOB member since 2011-03-02)

4

Got you…My bad…Below should work i believe.

sql( ‘ODS_DS_TRG_ORACLE’,‘INSERT INTO VASU_DS_TRG.BATCH_CONTROL VALUES ({$BATCH_NAME},‘STARTED’,TO_DATE($CDC_DATE,‘YYYY.MM.DD’),NULL,TO_DATE($SYSDATE,‘YYYY.MM.DD’))’);

ganeshxp :us: (BOB member since 2008-07-17)

5

Thanks Ganesh,

what you mentioned about i tried it before,but i was getting “Invalid character error”

Here

sql( ‘ODS_DS_TRG_ORACLE’,‘INSERT INTO DS_TRG.BATCH_CONTROL VALUES ({$BATCH_NAME},‘STARTED’,to_date($CDC_DATE,‘YYYY.MM.DD’),NULL,to_date($SYSDATE,‘YYYY.MM.DD’)’);

Error is

5752 2448 RUN-050304 12/10/2013 2:12:37 PM Function call <sql ( ODS_DS_TRG_ORACLE, INSERT INTO DS_TRG.BATCH_CONTROL VALUES
5752 2448 RUN-050304 12/10/2013 2:12:37 PM (‘PRODUCT_DIM’,‘STARTED’,to_date($CDC_DATE,‘YYYY.MM.DD’),NULL,to_date($SYSDATE,‘YYYY.MM.DD’) ) > failed, due to error <70301>:
5752 2448 RUN-050304 12/10/2013 2:12:37 PM <Oracle error message for operation : <ORA-00911: invalid character

Thanks
Venki

ursfriend77 (BOB member since 2011-03-02)

6

Hi,
please help anyone on this issue. I am troubling here.

Thanks
Venki

ursfriend77 (BOB member since 2011-03-02)

7

Wrap the DATE variables with {} and it should work I believe. I don’t have an Oracle system to try it out here.

ganeshxp :us: (BOB member since 2008-07-17)