Network Automation Software
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: **Big Conversion issue** SQL statement not working - was working in Automate 6
Topic Summary:
Created On: 11/18/2009 01:54 PM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
 11/18/2009 01:54 PM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hi

We are trying to see if we can migrate some of our tasks in Automate 6 to Automate 7, but we are running into several issues with some DB related tasks. We are using Oracle. For ex - the task copied below fails. This used to work perfectly fine in Automate 6. If these don't work in 7, then it is a huge issue for us to convert. Please let me know why the error occurs and if it can be fixed

<STEPS>
<AMVARIABLE NAME="v_dbenvironment">DEVELOP</AMVARIABLE>
<AMVARIABLE NAME="v_sql"></AMVARIABLE>
<AMVARIABLE NAME="v_dbconnectstring">Provider=MSDAORA.1;Password=*****;User ID=****;Data Source=*****;Persist Security Info=True</AMVARIABLE>
<AMSET VARIABLENAME="v_sql">DECLARE
  CURSOR lCurGSSchedule
  IS
  SELECT sch.schedule_id,shf.shift_start_time
    FROM abc sch, def shf
   WHERE sch.shift_id = shf.shift_id
     AND sch.schedule_id = 2;
BEGIN
  FOR lRecGSSchedule IN lCurGSSchedule
  LOOP
 
  UPDATE abc
         SET next_run_time = SYSDATE
     WHERE schedule_id = 2;
 
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001, SQLERRM);
END;</AMSET>
<AMSQLQUERY CONNECTIONSTRING="AM1XV9vbqWWQ1uF/rANxS0sG8dyeCc2Pr09aME">%v_sql%</AMSQLQUERY>
</STEPS>

Error that I am getting is

(Step 5) Connecting to the database...

(Step 5) Executing SQL statement...

(Step 5) Microsoft OLE DB Provider for OracleRA-06550: line 8, column 0:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;

(Step 5) Microsoft OLE DB Provider for OracleRA-06550: line 9, column 0:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;

(Step 5) Microsoft OLE DB Provider for OracleRA-00900: invalid SQL statement

(Step 5) Microsoft OLE DB Provider for OracleRA-00900: invalid SQL statement

(Step 5) Microsoft OLE DB Provider for OracleRA-00900: invalid SQL statement

 

 



 Category Survey
 11/19/2009 06:44 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hello SupremeGurus - Any thoughts on this?



 Category Survey
 11/19/2009 11:12 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Hi Arun,

I have forwarded this to our QA team to see if they can offer any assistance.

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 11/20/2009 01:57 PM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Thanks Leonard. Please keep me posted.



 Category Survey
 11/24/2009 09:27 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hi

Do we have any updates on this? We are right now stuck right in the middle. We are having issues with Automate 6 (that I have taken up in the other thread) and we are not able to move to Automate 7 because of the issue in this thread. Even though we have both and spent considerable money to buy, we are in the middle of nowhere.

Can this be resolved or answered ASAP?

-Arun

 

 

 



 Category Survey
 11/24/2009 09:31 AM
User is offline View Users Profile Print this message

Author Icon
Nathalie Nahmani
AUTOMATE EMPLOYEE

Posts: 120
Joined: 03/31/2005

Hi ArunG,

Can you try removing the semicolons from your query and let us know if the task still fails?

Thanks

Nathalie Nahmani

 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Nathalie Nahmani
Network Automation, Inc. – No Code, No Limits Business Process Automation
 11/24/2009 10:41 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hi Nathalie

Semicolon is required for Oracle statements and we cannot try without them. But nevertheless I still went ahead and tried as you suggested and got the error as expected

(Step 6) Executing SQL statement...

(Step 6) Microsoft OLE DB Provider for OracleRA-06550: line 7, column 31:

PL/SQL: ORA-00933: SQL command not properly ended

ORA-06550: line 4, column 3:

PL/SQL: SQL Statement ignored

ORA-06550: line 21, column 0:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

-Arun

 



 Category Survey
 11/25/2009 10:53 AM
User is offline View Users Profile Print this message

Author Icon
Nathalie Nahmani
AUTOMATE EMPLOYEE

Posts: 120
Joined: 03/31/2005

Hi Arun,

Thank you for testing this.
We are working on a fix and we will have something for you to test next week.

Happy Thanksgiving!

 Category Survey


-------------------------
Nathalie Nahmani
Network Automation, Inc. – No Code, No Limits Business Process Automation
 12/01/2009 07:00 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Thanks Nathalie. Hope for some solution this week. This has been holding us for too long now

-Arun

 



 Category Survey
 12/07/2009 08:37 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hi Nathalie

Do you have an update? We are really lagging behind big time in our upgrade process and hope to have a resolution ASAP

Thanks

Arun

 

 



 Category Survey
 12/07/2009 01:12 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Hi Arun,

If possible we would like to send you an updated file that you will need to test against all of you SQL related tasks to ensure that they all are still functioning properly. This will not be included in the upcoming maintenance release and will only apply to your issues. Is this something that you would be willing to do?

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 12/08/2009 12:13 PM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Yes we can test.



 Category Survey
 12/09/2009 02:42 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Hi Arun,

I apologize for the delay. We have to release v7.1.2.0 first which you must update to. Then we will be providing an updated .dll file for your testing. This should be within the next week or so.

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 12/11/2009 10:27 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Thanks. Would be very grateful if this is available ASAP.



 Category Survey
 12/17/2009 08:37 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hi Leonard

It has been quite sometime now. Can you let me know when this is going to be available?

Thanks

Arun

 

 



 Category Survey
 12/21/2009 02:27 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Hi Arun,

We apologize for the delay in releasing 7.1.2. It now looks like the release will be sometime in January 2010.

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 01/06/2010 08:05 AM
User is offline View Users Profile Print this message

Author Icon
nmolinos
Apprentice (20-49)

Posts: 41
Joined: 12/18/2008

Originally posted by: Leonard Amabile (N.A) Hi Arun, We apologize for the delay in releasing 7.1.2. It now looks like the release will be sometime in January 2010.

 

Hi Leonard,

 

Do you guys have a date yet? Waiting for this release to upgrade from 7.1.0.0 (hotfixes applied).

 

Thanks



 Category Survey


-------------------------
 
 01/11/2010 03:31 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Originally posted by: nmolinos

Originally posted by: Leonard Amabile (N.A) Hi Arun, We apologize for the delay in releasing 7.1.2. It now looks like the release will be sometime in January 2010.




 




Hi Leonard,




 




Do you guys have a date yet? Waiting for this release to upgrade from 7.1.0.0 (hotfixes applied).




 




Thanks



This was released on Friday 01/08/10.

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 01/12/2010 02:12 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Originally posted by: ArunG

Hi Leonard




It has been quite sometime now. Can you let me know when this is going to be available?




Thanks




Arun




 




 



Hi Arun,

I will be sending you an email shortly with a .dll file. You will need to shutdown your AM7 service and rename the original file to .old and put the new file in its place. Then restart the service and let us know your testing results.

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 04/28/2010 12:41 PM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hi Leonard

Sorry for the late response. The dll you gave worked and was able to meet some of our requirements. I tested it.

When will this be available as an official downloadable version?

Thanks

Arun

 



 Category Survey
 05/05/2010 09:12 AM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Leonard?



 Category Survey
 05/05/2010 11:58 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Hi Arun,

Sorry for the delayed response. The fix will be included in the next major version of Automate. For now, this correction applies to v7.1.2.0. When v7.1.3.0 is released, we will provide another update for you.

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 05/14/2010 12:40 PM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Will v7.1.3.0 already have this fix incorporated? Thanks!



 Category Survey
 05/14/2010 12:40 PM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Also what is the timeline that we are expecting v7.1.3.0 to be available?



 Category Survey
 05/18/2010 02:28 PM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Hi Arun,

Version 7.1.3.0 will not have the fix incorporated. We will provide an updated .dll file for you once you upgrade to v7.1.3.0. We are looking to release v7.1.3.0 within the next week or so. Our A-Team members are currently reviewing our RC2 build.

 Category Survey


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 05/21/2010 12:08 PM
User is offline View Users Profile Print this message

Author Icon
ArunG
Expert (100-199)

Posts: 158
Joined: 10/29/2009

Hi Leonard

 

We are trying to first use Automate 7 before moving onto BPA server (since that might be a faster upgrade). This issue persists in Automate 7 as well. What is the fix? Should I use the same dll you gave me?

Let me know

Thanks

Arun

 

 



 Category Survey
 05/21/2010 12:18 PM
User is offline View Users Profile Print this message

Author Icon
Rick Johnson
AUTOMATE EMPLOYEE

Posts: 2757
Joined: 07/14/2008

Originally posted by: ArunG

Hi Leonard




 




We are trying to first use Automate 7 before moving onto BPA server (since that might be a faster upgrade). This issue persists in Automate 7 as well. What is the fix? Should I use the same dll you gave me?




Let me know




Thanks




Arun




 




 



Hi Arun,

The DLL will be the same for both the BPA v7.1.3 and AutoMate v7.1.3 release.

 Category Survey


-------------------------
- Rick Johnson
Network Automation
 09/03/2013 04:23 PM
User is offline View Users Profile Print this message

Author Icon
StephanieWatanabe
n00b (>6 posts)

Posts: 2
Joined: 07/18/2013

Hi, I know this post is a few years old now, but I am having the same problem. I can run my sql query in SQL DEVELOPER just fine, but when I try to run it in Automate 9, it does not work. I can, however, run it in Automate 6. I am getting the same error "ORAOLEDB: ORA-06550; line 3, column 0: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following :=.(@%; not null range default character.



 Category Survey


AIS - Pers.aml
AIS - Pers.aml  (4 KB)

 09/05/2013 02:28 PM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

Hi Stephanie,

As a test can you try putting the SQL statement into a variable and reference the variable within the SQL action? If that does not work you will need to replace the single quotes with double.

We look forward to hear your results.

 Category Survey
AutoMate BPA Server 7 version:
Windows version:


-------------------------

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
 09/05/2013 04:16 PM
User is offline View Users Profile Print this message

Author Icon
StephanieWatanabe
n00b (>6 posts)

Posts: 2
Joined: 07/18/2013

I tried both of these suggestions and I still can't seem to get it to work. the sql is written in pl/sql. is there a way to get pl/sql to work?



 Category Survey
 09/09/2013 01:08 PM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

Hi Stephanie,

Would it be possible to setup a GoToAssist session where we can take a closer look at this issue? If this is possible please send an email over to support@networkautomation.com with a list of your availability.

Note: Please reference this forum thread in your email.


 Category Survey
AutoMate BPA Server 7 version:
Windows version:


-------------------------

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
Statistics
18257 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 6686 on 11/01/2020 at 01:24 AM.
There are currently 927 guests browsing this forum, which makes a total of 927 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2021 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog