topic Issue while using StoredProcess -Writing date to Oracle in Developers

Issue while using StoredProcess -Writing date to Oracle

tommy81 — Tue, 05 Oct 2010 11:34:17 GMT

I`m able to insert a row to oracle with the following script
proc sql;

insert into MART.detail_extra(period_key,report_name,audit_time,comments)

values(252,'Commission Detail',&curdate,'REP-Generation of report ');

run;

But when i run using stored process it s giving the following error.

Error:

ERROR: ERROR: ERROR: ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected. With the occurrence of the above ERROR, the error limit of 1 set by

the ERRLIMIT= option has been reached. ROLLBACK has been issued(Any Rows processed after the last COMMIT are lost).

Total rows processed: 1

Rows failed : 1

ERROR: ROLLBACK issued due to errors for data set

Has anyone encountered this before ?

Re: Issue while using StoredProcess -Writing date to Oracle

Vince_SAS — Tue, 05 Oct 2010 15:08:54 GMT

Have you checked the value of CURDATE when run through the stored process to make sure it is valid? I'm guessing that the error could be encountered if CURDATE is not defined or contains illegal characters.

Vince DelGobbo
SAS R&D

Re: Issue while using StoredProcess -Writing date to Oracle

tommy81 — Tue, 05 Oct 2010 17:31:25 GMT

Hi Vince Curdate is defined as a datetime variable ....
Reg Q1 , how xcatly do u think the value would change once we run in a stored process , and no that vvalue is not checked hen run thru a storedprocess

Re: Issue while using StoredProcess -Writing date to Oracle

Vince_SAS — Tue, 05 Oct 2010 17:59:13 GMT

You might want to open a track with our technical support department.

Vince DelGobbo
SAS R&D

Re: Issue while using StoredProcess -Writing date to Oracle

sbb — Wed, 06 Oct 2010 04:48:27 GMT

What is mentioned as "Curdate is defined as a datetime variable" may be misunderstood, given you are using a SAS macro variable. Somewhere in your SAS code the macro variable &CURDATE must be assigned, possibly with a CALL SYMPUT or a %LET statement. But do consider that this macro variable is a character-string, even if it is a number -- although it may appear formatted as DATETIME, it is still a character-string of information. Or if you see a SAS message "WARNING: Apparent symbolic reference CURDATE not resolved."
-- that would lead one to believe that your program is not setting the macro variable used in the code piece you revealed in your post. Suggest some desk-checking of your SAS log output to determine where &CURDATE is assigned and also consider executing the code below to display all user-defined macro variables:

%PUT _USER_;

Scott Barry
SBBWorks, Inc.