I want to update one column in the Oralce database

SQL SQLORA SET SERVER MYSERVER
UPDATE PERSON
SET LAST_UPDATED_DATE = SYSDATE
WHERE EMPLOYEE_ID='000001'
END

However, i got the following error:

(FOC1517) UNRECOGNIZED COMMAND UPDATE PERSON
0 ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC324) THE PARAMETER TO BE SET IS NOT RECOGNIZED: LAST_UPDATED
(FOC1517) UNRECOGNIZED COMMAND WHERE EMPLOYEE_ID='N067569'

Thanks in advance for your help

This message has been edited. Last edited by: Kerry,

Add in SQL or SQL SQLORA before UPDATE PERSON

You should check out the syntax of SQL passthru

Yes. It works.

However, how can i customize output message?


--------------------------------------------------------------------------------

No HTML Output!

--------------------------------------------------------------------------------


(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 1/UPDATE

You need to capture the results of the SQL command, and check it.

Then you can show an appropriate message.

Thanks.

Can you give me an example as how to capture the results of the SQL command and check it and show the message?

Regards

Maria, here is an example, there are others in the forum as well.

-*******************************************************************************
-* Set the trace file

-SET &TEMPPATH = TEMPPATH(80,'A80');
-SET &TRACEFL  = &TEMPPATH || ('cmd_op.trc');

-*******************************************************************************
-* Set Commands To Set Tracing

SET TRACEOFF  = ALL
SET TRACEON=R1H
SET TRACEUSER = ON
SET TRACEUSER = &TRACEFL

-RUN
SQL SQLORA
SELECT *
FROM MISSING_TABLE;
END
-RUN
-RUN
-* The previous two -RUN's must be there for this to work.
SET TRACEOFF  = ALL
SET TRACEUSER = OFF
-RUN

-*******************************************************************************
-* Allocate The Trace File
FILEDEF TRACEFIL DISK &TRACEFL

-RUN

-*******************************************************************************
-* Write out a master to read the TRACEFIL list
EX -LINES 4 EDAPUT MASTER,TRACEFIL,CV,FILE
FILENAME=TRACEFIL, SUFFIX=FIX,$
SEGNAME=TRACEFIL, $
  FIELD=LINE ,ALIAS=  ,A500 ,A500 ,$

-RUN

-*******************************************************************************
-* Process the trace file to get the results of the command.

DEFINE  FILE TRACEFIL
 Item3/A20     = GETTOK(LINE,500,3,' ',20,'A20') ;
 MSG_Item/A493 = SUBSTR(500,GETTOK(LINE,500,2,'"',500,'A500'),8,500,493,'A493') ;
 CMD_Flag/A1   = IF EDIT(MSG_Item,'9') EQ ':' THEN 'Y' ELSE 'N' ;
END

TABLE   FILE TRACEFIL
 PRINT  
        MSG_Item

 WHERE        Item3    EQ 'NGtrEvnt:'

-* ON     TABLE HOLD AS TMP_CHCK

END

-RUN

Maria - Here is piece of SQL-RC check code that we use. The -803 is a duplicate insert failure. It is an acceptable condition in our situation, you may take it out if you do not want it.

-*
SQL SQLORA
....
....
;
TABLE
END
-RUN
-*
-SET &SQL_ERROR1 = &RETCODE;                                            
-IF (&SQL_ERROR1.EVAL EQ 0 OR &SQL_ERROR1.EVAL EQ -803) GOTO LBL_NXT1;
-SET &MSGX = 'Submission Failed (Error# &SQL_ERROR1.EVAL)';             
-SET &MSG1 = 'Please notify your WebFOCUS support.';                      
-INCLUDE ERROR_MSG.FEX                                                    
-EXIT                                                                   
-*                                                                         
-LBL_NXT1               
...continue processing.......                                                

The ERROR_MSG.FEX is a standard module we use inhouse for error trap and display.

Sandeep Mamidenna

Sandeep and Waz;

Thanks for your help.

I got the following error message:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 1/UPDATE
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC303) CONTROL LINE NOT RECOGNIZED IN FOCEXEC: -print &MSGX


My fex is:

SET SQLENGINE=SQLMSS

SQL SQLORA SET SERVER MYSERVER
SQL SQLORA
UPDATE table
SET
WHERE
END

-RUN
-SET &SQL_ERROR1 = &RETCODE;
-SET &MSGX = 'Update Successfully';
-SET &MSG1 = 'Please notify your WebFOCUS support.';
-IF (&SQL_ERROR1.EVAL EQ 1364) GOTO LBL_NXT1;
-print &MSGX
-LBL_NXT1
print &MSG1

The correct syntax for displaying the value of a variable is -TYPE, not -print.

-TYPE &MSGX

As Dan says, it should be -TYPE to just print back the return code on screen.

Confused a bit with your code however.
You start with SQLENGINE=SQLMSS, but have an Oracle table referenced with SQLORA ???

Are these not mutually exclusive?