how to calling oracle procedure and get SAS result

Reply
Occasional Contributor
Posts: 6

how to calling oracle procedure and get SAS result

Team,

 

I am trying to call SA_THIRDPARTYVALIDATELOAD.VALIDATEANDLOADMETADATA . Please find the block below, I am not able to write appropriate steps. Please help.     Kindly note that PO_VALIDMETADATA is output parameter, how to get the result from this output parameter.          
    DECLARE
   PO_VALIDMETADATA NVARCHAR2(200):=NULL;
 BEGIN
 SA_THIRDPARTYVALIDATELOAD.VALIDATEANDLOADMETADATA(
    PI_COMPANYID => 1,
    PI_STUDYNUMBER => 'study1',
    PI_TABLENAME => 'DTTM',
    PI_VENDORID => 'vendor1',
    PI_CONFIGSTATUS => 'T',
    PO_VALIDMETADATA => PO_VALIDMETADATA
   );
 PO_VALIDMETADATA => PO_VALIDMETADATA
 END; 

 

 

The proc sql steps:

%macro ab;

%let PO_VALIDMETADATA= ab;

proc sql;

connect to odbc (datasrc=PDRDEV USER=PDR_CONTROL PASSWORD=PDR_CONTROL);

create table sasresults as select * from connection to odbc (

call (SA_THIRDPARTYVALIDATELOAD.VALIDATEANDLOADMETADATA (1, 'study1', 'DTTM', 'vendor1', 'T',Smiley TongueO_VALIDMETADATA))

);

quit;

%mend;

%ab;

 

I am getting below error:

 

ERROR: CLI prepare error: [Oracle][ODBC][Ora]ORA-01008: not all variables bound

SQL statement: call (SA_THIRDPARTYVALIDATELOAD.VALIDATEANDLOADMETADATA (1, 'study1', 'DTTM',

'vendor1', 'T',Smiley TongueO_VALIDMETADATA)).

 

 

Super User
Posts: 7,405

Re: how to calling oracle procedure and get SAS result

I recommend contacting one of your local Oracle experts, as the error message clearly comes from Oracle.

 

But I see that there seems to be something funny with your macro variable reference. It should be &PO_VALIDMETADATA.

 

BTW, you don't need the %macro definition, as all you do is substitute a macro variable, which can be done anywhere in SAS code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 1 reply
  • 226 views
  • 0 likes
  • 2 in conversation