Your SAS programs, embedded in web apps and elsewhere

Issue while using StoredProcess -Writing date to Oracle

Reply
Frequent Contributor
Posts: 83

Issue while using StoredProcess -Writing date to Oracle

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 ?
SAS Super FREQ
Posts: 306

Re: Issue while using StoredProcess -Writing date to Oracle

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
Frequent Contributor
Posts: 83

Re: Issue while using StoredProcess -Writing date to Oracle

Posted in reply to Vince_SAS
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
SAS Super FREQ
Posts: 306

Re: Issue while using StoredProcess -Writing date to Oracle

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

Vince DelGobbo
SAS R&D
Super Contributor
Super Contributor
Posts: 3,174

Re: Issue while using StoredProcess -Writing date to Oracle

Posted in reply to Vince_SAS
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.
Ask a Question
Discussion stats
  • 4 replies
  • 318 views
  • 0 likes
  • 3 in conversation