We help IT Professionals succeed at work.

how do i fix procedure producing ORA-00907 error

sikyala
sikyala asked
on
I have a procedure that produces an error message the following error when i execute:

execute UPDATELEGACYSTIRECORD ('TEMSWS', 'TEMS_ADM_ORIG', 'IATAC', 1143287);
BEGIN UPDATELEGACYSTIRECORD ('TEMSWS', 'TEMS_ADM_ORIG', 'IATAC', 1143287); END;

*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at "TEMSWS.UPDATELEGACYSTIRECORD", line 118
ORA-06512: at line 1

Everything appears ok when I look at the code. Can someone tell me what I am missing. procedure is attached.
test-procedure.txt
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
easiest way is to display the generated statement and try to run it:

dbms_output.put_line(l_update_stmt);
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
you have

REPLACE(REPLACE( ...  ))
in your SET clause


you can't set a function = to a value.

put the replace in the assignment part
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I'm not positive, but I think you might be able to simply reverse the SET and SELECT values
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
maybe something like this...


    l_update_stmt      :=
           'UPDATE  '
        || targetschema
        || '.WS_STI_RECORD SET
(
        ABSTRACT,
        ABSTRACT2,
        AD_ID,
        AUTHOR,
        CATEGORY,
        EXTRA_TEXT_FIELD2,
        COMMENTS,
        COPIES,
        COPYRIGHT_EXISTS,
        COPYRIGHT_INFO,
        DB_UPDATED,
        DISTRIBUTION_CODE,

        ENTERED_BY,
        EXTRA_NUMBER_FIELD2,
        EXTRA_TEXT_FIELD5,
        FILE_NAME,
        FOR_PAY,
        CONTRACT_GRANT_ID,
        HIERARCHY_1,
        HIERARCHY_2,
        HIERARCHY_3,
        HIERARCHY_4,
        HIERARCHY_5,
        HIERARCHY_6,
        EXPORT_CONTROL,

        EXTRA_DATE_FIELD1,
        EXTRA_DATE_FIELD2,
        EXTRA_NUMBER_FIELD1,
        LOCATION,
        IAC_REPORT_ID,
        MODIFIED_DATE,
        MONITOR_REPORT_ID,
        EXTRA_TEXT_FIELD4,
        EXTRA_TEXT_FIELD1,
        NUM_PAGES,
        ORG_MONITORING,
        OTHER_CLASSIFICATION_MARKINGS,
        PDF_EXISTS,

        PERFORMING_REPORT_ID,
        PUBLICATION_DATE,
        PUBLISHER,
        PURCHASE_INFO,
        PURCHASE_PRICE,
        CLASSIFICATION,
        EXTRA_TEXT_FIELD3,
        SECONDARY_DIST,
        SUBJECTKEYWORDS,
        SUPPLEMENTARY_NOTES,
        TITLE,
        TYPE
 
) =

( SELECT
              ABSTRACT,
        ABSTRACT2,
        AD_ID,
        AUTHOR,
        CATEGORY,
        replace(replace(CNWDI, 1, ''Y''), 0, ''N''),
        COMMENTS,
        COPIES,
        replace(replace(COPYRIGHT_EXISTS, 1, ''Y''), 0, ''N''),
        COPYRIGHT_INFO,
        DB_UPDATED,
        DISTRIBUTION_CODE,
        ENTERED_BY,
        replace(replace(EXPORT_CONTROL, 1, ''Y''), 0, ''N''),
        EXTRA_DATE_FIELD1,

        EXTRA_DATE_FIELD2,
        EXTRA_NUMBER_FIELD1,
        EXTRA_NUMBER_FIELD2,
        EXTRA_TEXT_FIELD5,
        replace( replace(FILE_NAME, ''/ret-data/tems-ws-uploads/'', ''''), ''/usr/local/tems-ws-uploads/'', ''''),

        replace(replace(FOR_PAY, 1, ''Y''), 0, ''N''),
        CONTRACT_NUMBER,
        HIERARCHY_1,
        HIERARCHY_2,
        HIERARCHY_3,
        HIERARCHY_4,

        HIERARCHY_5,
        HIERARCHY_6,
        LOCATION,
        NVL(BCO_REPORT_ID, MAC_REPORT_ID ),
        MODIFIED_DATE,
        MONITOR_REPORT_ID,
        NATO_CLASSIFICATION,
        replace(replace(NATO_CLASSIFIED, 1, ''Y''),0, ''N''),
        NUM_PAGES,
        ORG_MONITORING,
        OTHER_CLASSIFICATION_MARKINGS,
        replace(replace(PDF_EXISTS, 1, ''Y''), 0, ''N''),
        PERFORMING_REPORT_ID,

        PUBLICATION_DATE,
        PUBLISHER,
        PURCHASE_INFO,
        PURCHASE_PRICE,
        REPORT_CLASSIFICATION,
        replace(replace(RESTRICTED_DATA, 1, ''Y''), 0, ''N''),
        SECONDARY_DIST,
        SUBJECTKEYWORDS,
        SUPPLEMENTARY_NOTES,
        TITLE,
        TYPE


FROM ';
sikyalaSenior Database Administrator

Author

Commented:
ah I see what you are saying I will try that
sikyalaSenior Database Administrator

Author

Commented:
now i get error

ERROR at line 1:
ORA-00947: not enough values
ORA-06512: at "TEMSWS.UPDATELEGACYSTIRECORD", line 115
ORA-06512: at line 1


see attached procedure
test-procedure.txt
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
missing a comma at the end of:
        replace(replace(EXTRA_TEXT_FIELD2, 1, ''Y''), 0, ''N'')


Honestly I would do this with a case statement or the old decode:

decode(EXTRA_TEXT_FIELD2,1,''Y'',''N''),
or
case EXTRA_TEXT_FIELD2 when 1 then ''Y'' else ''N'' end,
sikyalaSenior Database Administrator

Author

Commented:
awesome thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.