Automation: Unable to Call Procedure

Toad Data Point
1

DECLARE

BEGIN

HLEO_SYBASE_WEBSTAT_STG_SP;

/

select to_char(sysdate-7,‘MM/DD/YYYY’) from dual

Hi I am trying to call oracle Procedure using automation but its not working.
Its a valid procedure.
Please find the attached scripts which might will help you to provide the solution.

I am facing two issue.

  1. Unable to Call Procedure.
    I used Execute script activity to give a call to the procedure.

    Used follwoing SQL code to call the procedure.

END;
2. Varible is not set to correct value.
I am trying to assign value using SQL query to a Variable VAR_DATE.
But the value is not correct.

Query used to assign value to variable is
I am expecting the varible value as 12/08/2010
It will be great if you are able to provide any suggestion/help.
If you find some information missing please write me.

Regards
Pravin

AutmateProcedure.zip (156 KB)

2

Hi Debbie,
I am able to fix Issue#2 by re-writting the SQL query as below.

select ‘"’||to_char(sysdate-7,‘MM/DD/YYYY’)||’"’ dt from dual;
But issue #1 still not resolved, I cant call oracle procedure using Execute
scripts activity.

Could you please help in how to call oracle procedure in automation.

Regards

Pravin

3

I just got back to my desk. I will take a look in between meetings.

4

Your fix for the date is correct.

I created my own execute proc script and have no issues. I would suggest taking out the DECLARE and SQL*Plus run ‘/’ as this is not needed. (At least in your sample)

What kind of Oracle connection do you have? Are you using Direct Connect or Oracle Client? I would not suggest Direct Connect.

Debbie

5

Thanks Debbie,

After using the Oracle Client instead of Direct Connect I am able to call

Procedure.

Regards

Pravin

6

Very interesting. We are planning to replace that section of code in TDA 3.0. Lots of people want to connect without the hassle of client but, as you found, it is limiting.

Debbie