BookmarkSubscribeRSS Feed
tommy81
Obsidian | Level 7
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 ?
4 REPLIES 4
Vince_SAS
Rhodochrosite | Level 12
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
tommy81
Obsidian | Level 7
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
Vince_SAS
Rhodochrosite | Level 12
You might want to open a track with our technical support department.

Vince DelGobbo
SAS R&D
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 896 views
  • 0 likes
  • 3 in conversation