Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle Error Running Script (Read 10366 times)
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
Oracle Error Running Script
29.03.10 at 19:08:04
 
I can run the following script without error in SQL Developer. When I run it in SQL*XL by copying the script into the SQL window, it will run fine. I set macro recording enabled, then when I try to run the macro, it errors out.
 
Edited:
Note that in SQL*XL 5.2 the syntax changed slightly. The ConnectionType parameter became read only and instead the ConnectionType is passed as a parameter to the Connect routine. For further information see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html

 
Sub Macro1()
 
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
    SQLXL.Database.Connect _
    UserName:="user", _
    Password:="secret", _
    DBAlias:="PROD", _
    ConnectionString:="Provider=OraOLEDB.Oracle.1;" & _
    "Persist Security Info=False;" & _
    "User ID=user;" & _
    "Data Source=PROD;", AllowTransactions:=True
    With SQLXL.Sql
       .Clear
       .appendText "ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:';"
       .appendText "ACCEPT CO_ID PROMPT 'Enter Company to Process - IMPR, NORM, or NRSH:'; "
       .appendText "SELECT p.emp_id, l.ded_num, d.descr, f.ss_num, f.ou_id, SUM(NVL( l.amt, 0 )), p.gross_pay,"
       .appendText "e.pct , f.last_name, f.first_name, f.middle_initial "
       .appendText "FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f "
       .appendText "WHERE (f.alpha_6 = '&CO_ID'"
       .appendText "OR f.alpha_6 = '&&CO_ID'"
       .appendText "OR f.alpha_6 = '&&&CO_ID')"
       .appendText "AND ((l.ded_num='553'"
       .appendText "AND e.pct IS NOT NULL)"
       .appendText "OR (l.ded_num='567'"
       .appendText "AND e.pct IS NOT NULL))"
       .appendText "AND l.ded_num = d.ded_num"
       .appendText "AND l.pr_chk_sa_num = p.pr_chk_sa_num"
       .appendText "AND ((p.pay_group_num IN ('200','250','325', '625', '750')"
       .appendText "AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD')) "
       .appendText "OR (p.prd_end_date = '&&sal_pr_pd_end_date'"
       .appendText "AND p.pay_group_num IN ('100', '110','300', '400', '410','600','610','700','710')))"
       .appendText "AND ((p.emp_id = e.emp_id AND e.ded_num = '553')"
       .appendText "OR (p.emp_id = e.emp_id AND e.ded_num = '567'))"
       .appendText "AND f.emp_id = p.emp_id "
       .appendText "GROUP BY p.emp_id, f.ss_num, f.ou_id, l.ded_num, d.descr, p.gross_pay, e.pct, f.last_name,"
       .appendText "f.first_name, f.middle_initial "
       .appendText "HAVING Sum(NVL(l.amt, 0)) <> 0  "
       .appendText "ORDER BY p.emp_id asc ;"
       .Parse
    End With
    SQLXL.Sql.Statements(1).Execute
    SQLXL.Sql.Statements(2).Execute
    Set SQLXL.Sql.Statements(3).Target = SQLXL.Targets(litExcel)
    SQLXL.Sql.Statements(3).OptimiseForLargeQuery = False
       With SQLXL.Targets(litExcel)
       .AutoFilter = False
       .AutoFit = True
       .Headings = True
       .Sort = False
       .StartFromCell = "$A$1"
       .Transpose = False
       .SQLInNote = False
       .ShowNote = False
       .FormatData = True
       .FreezePanes = False
       .PasteInsert = False
    End With
    SQLXL.Sql.Statements(3).SubstVariables.Add "CO_ID"
    SQLXL.Sql.Statements(3).SubstVariables.Add "SAL_PR_PD_END_DATE"
    With SQLXL.Sql.Statements(3)
       .ShowParametersDlg = False
       .ShowResultsetDlg = False
    End With
    SQLXL.Sql.statements(3).SubstVariables("CO_ID").Value = "NORM"
    SQLXL.Sql.statements(3).SubstVariables("SAL_PR_PD_END_DATE").Value = "2010/03/28"
    SQLXL.Sql.Statements(3).Execute
    SQLXL.Database.Disconnect
 
The two statements after the last 'End With' above are what the macro recorder included for the specific values I entered when I ran the script. When I ran this script in version 4.3.7, I changed those two lines to the following:
 
    SQLXL.Sql.statements(3).SubstVariables.Add "CO_ID"
    SQLXL.Sql.statements(3).SubstVariables.Add "SAL_PR_PD_END_DATE"
 
because I don't want the macro to always run for a specific variable - I want it to run using the two variables from the ACCEPT statements each time I run it. I have 4.3.7 installed on another machine I no longer use (but keep for reference) and the script runs fine...but in 4.3.20, it doesn't. I get two errors:
 
ORA-01841: (full) year must be between -4713 and +9999 and not be 0   line 40
       and then
litADO
error - 2147221501 field 0 coud not be found in the recordset line 560
 
I have no problem with any of the connection strings. Connecting to the Oracle database works like it should.
 
The only difference between the two machines is that the one that does not work (Win7 64-bit) is running the Oracle Wire Protocol 11g and the reference machine (Win7 32-bit) is running the Oracle Wire Protocol 9g.
 
Is there an error in this script - as recorded by the macro recorder inside SQL*XL - that I need to fix in order to get this script to run?
 
 
Back to top
 
« Last Edit: 30.07.14 at 13:46:51 by Gerrit-Jan Linker »  
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle Error Running Script
Reply #1 - 29.03.10 at 21:33:19
 
Thank you for providing soo much detail.
 
It looks like the date value is not correctly recognised. A string is provided (as it should with substitution variables) as a date and we rely on implicit conversions. Clearly Oracle interprets the date not the way we would do. Perhaps the database client settings use a different way of formatting dates. I think you can see this when running this query: select to_char(sysdate) from dual. You can use the same date format in your code.
 
But, would it not be easier to remove the explicit substitution variable handling? In your macro you still execute the accept statements and therefore you can interactively enter the values. If you want to continue to specify the values programmatically I suggest you remove the accept statements from the SQL (also renumber the statements in the statements collection).
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
Re: Oracle Error Running Script
Reply #2 - 30.03.10 at 14:19:58
 
Thanks, Gerrit...
 
For this program to run like I need it to, I have to use the ACCEPT statements. I run this once a week and each time I run it, I run it with more than one date and more than one company ID, hence the ACCEPT statements so as to let me enter those two variables.
 
Running select to_char(sysdate) from dual returns 30-MAR-10.  I tried changing the first ACCEPT statement to DD-MMM-YY and also changing it in line 15, then running the script in SQL Developer.  It returns an error ORA-01821: Date format not recognized.
 
Can you tell me how to modify the code as you describe in your second paragraph in the respone - "In your macro you still execute the accept statements and therefore you can interactively enter the variables."  I'm not sure I know how to do what you are suggesting.
 
Thanks!
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle Error Running Script
Reply #3 - 30.03.10 at 14:37:45
 
It is a good idea to keep the accept statements in the SQL so you are prompted for values. However when you do that you do not need the statements to handle the parameters again in the code. When you run the macro the accept statements will define the variables and values. Then your command 3 will run and all the variables are already initialised.
 
Looking at your macro code I suggest you comment out all lines that refer to the SubstVariables colletion and run your code again.
 
This should work fine!
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
Re: Oracle Error Running Script
Reply #4 - 30.03.10 at 22:08:14
 
You're right...taking out the substitution variable lines still runs the program with the right result set.  I also ran the select to_char(sysdate) from dual statement in Oracle SQL Developer and it returned the date as DD-MMM-YY (the Oracle default), so I added the ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD' line just after the two ACCEPT statements. Then I I put the select to_char(sysdate) from dual statement at the end of the script and ran it again in Oracle SQL Developer and verified that the SET NLS_DATE_FORMAT statement was read and executed. This time, it returned the date correctly as YYYY/MM/DD, so I know the date formats are matching.  
 
Then I created a new worksheet in Excel, started the macro recorder, clicked on SQL*XL and connected to Oracle, and then took the entire SQL script that ran correctly in Oracle SQL Developer and copied it into the SQL*XL script block, ran it, and stopped the macro recorder.  Then I opened the recorded macro in the Excel VBA module window. When I ran the macro from Excel VBA, it errored out with the same error: "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". I don't understand why the script will run in Oracle SQL Developer but won't run in SQL*XL.  Below is the full VBA script from Excel. The lines of the actual script that runs correctly in Oracle SQL Developer appear in the .appendText lines of the Excel VBA macro. I guess there's something about the way SQL*XL is interpreting those lines that differs from the way Oracle SQL Developer interprets them, or one or more of the other lines in the script (before the .Clear line and/or after the .Parse line) that could be causing the problem. Maybe there is another "SQLXL." command I need to include. However, since I know the script will execute properly in Oracle SQL Developer, it seems it should execute properly in SQL*XL - I just can't seem to make it work. Any insight you have into this would be most appreciated!
 
Edited:
Note that in SQL*XL 5.2 the syntax changed slightly. The ConnectionType parameter became read only and instead the ConnectionType is passed as a parameter to the Connect routine. For further information see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html

 
---------------------------------------
Sub Macro1()
 
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
    SQLXL.Database.Connect UserName:="user", Password:="secret", DBAlias:="PROD", ConnectionString:="Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=secret;Data Source=PROD;", AllowTransactions:=True
    With SQLXL.Sql
       .Clear
       .appendText "ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:'; "
       .appendText "ACCEPT CO_ID PROMPT 'Enter Company to Process - IMPR, NORM, or NRSH:';  "
       .appendText "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD' ; "
       .appendText "SELECT p.emp_id, l.ded_num, d.descr, f.ss_num, f.ou_id, SUM(NVL( l.amt, 0 )), p.gross_pay, e.pct, f.last_name, f.first_name, f.middle_initial "
       .appendText "FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f "
       .appendText "WHERE (f.alpha_6 = '&CO_ID'  OR f.alpha_6 = '&&CO_ID' OR f.alpha_6 = '&&&CO_ID')  "
       .appendText "AND ((l.ded_num='553'   AND e.pct IS NOT NULL)   OR (l.ded_num='567'   AND e.pct IS NOT NULL)) "
       .appendText "AND l.ded_num = d.ded_num AND l.pr_chk_sa_num = p.pr_chk_sa_num "
       .appendText "AND ((p.pay_group_num IN ('200','250','325', '625', '750')  "
       .appendText "AND p.d_prd_end_date = to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD') -7) "
       .appendText "OR (p.d_prd_end_date = to_date('&&sal_pr_pd_end_date', 'YYYY/MM/DD') "
       .appendText "AND p.pay_group_num IN ('100', '110','300', '400', '410','600', '610', '700', '710'))) "
       .appendText "AND ((p.emp_id = e.emp_id AND e.ded_num = '553') "
       .appendText "OR (p.emp_id = e.emp_id AND e.ded_num = '567')) "
       .appendText "AND f.emp_id = p.emp_id "
       .appendText "GROUP BY p.emp_id, f.ss_num, f.ou_id, l.ded_num, d.descr, p.gross_pay, e.pct, f.last_name, f.first_name, f.middle_initial "
       .appendText "HAVING SUM(NVL( l.amt, 0 )) <> 0 "
       .appendText "ORDER BY p.emp_id "
       .Parse
    End With
    SQLXL.Sql.Statements(1).Execute
    SQLXL.Sql.Statements(2).Execute
    SQLXL.Sql.Statements(3).Execute
    Set SQLXL.Sql.Statements(4).Target = SQLXL.Targets(litExcel)
    SQLXL.Sql.Statements(4).OptimiseForLargeQuery = False
    With SQLXL.Targets(litExcel)
       .AutoFilter = False
       .AutoFit = True
       .Headings = True
       .Sort = False
       .StartFromCell = "$A$1"
       .Transpose = False
       .SQLInNote = False
       .ShowNote = False
       .FormatData = True
       .FreezePanes = False
       .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(4)
       .ShowParametersDlg = False
       .ShowResultsetDlg = False
    End With
       SQLXL.Sql.Statements(4).Execute
       SQLXL.Database.Disconnect
End Sub
Back to top
 
« Last Edit: 30.07.14 at 13:47:19 by Gerrit-Jan Linker »  
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle Error Running Script
Reply #5 - 30.03.10 at 22:20:38
 
I just noticed that you use &parameter_name and next &&parameter_name with two & characters. Is there the error perhaps? Please try the second instance just with one & character to see what happens. I will review the way SQL*XL deals with the & characters tomorrow.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle Error Running Script
Reply #6 - 31.03.10 at 10:25:02
 
There is no problem with parameters and using multiple & symbols. SQL*XL will not prompt twice for variables anyways. To check I ran the following command and gave 01-01-1980 as the value for the parameter:
 
select * from emp  
where hiredate > to_date('&myhiredate','dd-mm-yyyy') - 1000  
and    hiredate < to_date('&&myhiredate','dd-mm-yyyy') + 1000
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle Error Running Script
Reply #7 - 31.03.10 at 11:25:59
 
I found the cause and the solution to your problems.
 
First, your SQL is not correct in the sense that when you append lines to the SQL you do not everywhere code a space between the fragments. I have corrected that in the code below.
 
The second problem is that I moved the parameters parsing recently to allow an escape character to be defined and used. I will fix try to fix that in the code. For now you can explictly call ParseParameters on the statement.
 
Finally, you use &&& somewhere and SQL*XL only handles situations where two repetitions are used. I will make an attempt to correct that too.
 
I have corrected your macro accordingly:
 
Sub Macro1()
    With SQLXL.Sql
      .Clear
      .appendText "ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:'; "
      .appendText "ACCEPT CO_ID PROMPT 'Enter Company to Process - IMPR, NORM, or NRSH:'; "
      .appendText "SELECT p.emp_id, l.ded_num, d.descr, f.ss_num, f.ou_id, SUM(NVL( l.amt, 0 )), p.gross_pay, "
      .appendText "e.pct , f.last_name, f.first_name, f.middle_initial "
      .appendText "FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f "
      .appendText "WHERE (f.alpha_6 = '&CO_ID' "
      .appendText "OR f.alpha_6 = '&&CO_ID' "
      .appendText "OR f.alpha_6 = '&&CO_ID') "
      .appendText "AND ((l.ded_num='553' "
      .appendText "AND e.pct IS NOT NULL) "
      .appendText "OR (l.ded_num='567' "
      .appendText "AND e.pct IS NOT NULL)) "
      .appendText "AND l.ded_num = d.ded_num "
      .appendText "AND l.pr_chk_sa_num = p.pr_chk_sa_num "
      .appendText "AND ((p.pay_group_num IN ('200','250','325', '625', '750') "
      .appendText "AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD')) "
      .appendText "OR (p.prd_end_date = '&&sal_pr_pd_end_date' "
      .appendText "AND p.pay_group_num IN ('100', '110','300', '400', '410','600','610','700','710'))) "
      .appendText "AND ((p.emp_id = e.emp_id AND e.ded_num = '553') "
      .appendText "OR (p.emp_id = e.emp_id AND e.ded_num = '567')) "
      .appendText "AND f.emp_id = p.emp_id "
      .appendText "GROUP BY p.emp_id, f.ss_num, f.ou_id, l.ded_num, d.descr, p.gross_pay, e.pct, f.last_name, "
      .appendText "f.first_name, f.middle_initial "
      .appendText "HAVING Sum(NVL(l.amt, 0)) <> 0  "
      .appendText "ORDER BY p.emp_id asc ;"
      .Parse
    End With
    SQLXL.Sql.Statements(1).Execute
    SQLXL.Sql.Statements(2).Execute
    Set SQLXL.Sql.Statements(3).Target = SQLXL.Targets(litExcel)
    SQLXL.Sql.Statements(3).OptimiseForLargeQuery = False
      With SQLXL.Targets(litExcel)
      .AutoFilter = False
      .AutoFit = True
      .Headings = True
      .Sort = False
      .StartFromCell = "$A$1"
      .Transpose = False
      .SQLInNote = False
      .ShowNote = False
      .FormatData = True
      .FreezePanes = False
      .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(3)
      .ShowParametersDlg = False
      .ShowResultsetDlg = False
    End With
    SQLXL.Sql.Statements(3).ParseParameters
    SQLXL.Sql.Statements(3).Execute
End Sub
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
Re: Oracle Error Running Script
Reply #8 - 31.03.10 at 13:29:50
 
Thanks, Gerrit...that script executes with no errors.  I did not know about the requirement to code a space between appendText fragments. Thanks for pointing that out! What does the command "SQLXL.Sql.Statements(3).ParseParameters" do? I'm just curious to know how that command works, what it does, and where to use it (or if it's necessary to use it) in other VBA scripts with SQL*XL.  Thanks again for your great help!
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle Error Running Script
Reply #9 - 31.03.10 at 20:57:24
 
After my fix the ParseParameters routine should not have to be used actually.  The function scans the SQL statement and determines whether there are any bind or substitution variables. This can be done automatically and that is that I have reinstated.
 
About adding spaces at the end of the AppendText method is logical. The strings are added, concatenated, and if you don't leave a space between fragments the keywords will not be saparated and you will get a faulty SQL statement.  
 
E.g.
.appendtext "select *"
.appendtext "from table"
 
This will result in the SQL: "select *from table". A invalid SQL statement. We need a space between the * and the from. The following statements will work ok:
.appendtext "select * "
.appendtext "from table"
 
I hope this explanation helps.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1