1

Facing very strange issue regarding PL/SQL function call from VBA and Output of same on Oracle SQL Developer. Both seems to vary by a lot. Please go through the attached screen shots. 1st Image shows the VBA output.

VBA Output

Here is the output which I get on ORACLE SQL Developer

Oracle SQL Developer Output

More strange thing here is, This error I am facing only for few FSYM_IDs not all. For others its coming very much precise.

I did see the following question Similar Question

There user had mentioned about hash joins but I haven't used any sort of hash joins at all.

I have used ADO and Range.CopyFromRecordSet to fetch data into excel. Executed the same exact query which is

select FSYM_ID, CURRENT_FY, ROUND(VALUATION_PB_HIGH,0), ROUND(VALUATION_PB_LOW,0) from table(PRICE2BV('07BR8Q-R'))

into excel and oracle sql developer.

I have updated the SQL Developer screenshot. Difference is in the particular years high and low value. I have highlighted that in the SQL developer output.

Additional things about Oracle PL/SQL functions are

  1. Function returns data from global temporary table.
  2. Variables used are of type number or varchar only.
  3. Input to the function is identifier for the company only.
  4. I have tried to retrieve output using table also but no use.

I have even tried the approach of GetRows mentioned on following link. SQL Query output in VBA is different than in SQL Oracle

One thing I observed regarding recordset is that==> Values are different when I query from VBA and Oracle SQL Developer.

Please help me regarding same.

Improve this question
6
  • So many unclear points: How do you read the data with VBA? ADO? How do you write them into Excel? Range.CopyFromRecordSet? Or do you loop manually? Can you show the relevant code? Are you sure you are using the same data source? Are you sure you executed the exact same query with the same parameters? What parameter do you use at all? It seems that in your 2nd picture you are not showing a PrintScreen from SQL developer? What exactly shows your picture? Is that also Excel? How did you get the data into it? Commented Aug 4, 2021 at 8:05
  • @ShekharNalawade, please add the statements from your comment to the main post. Commented Aug 4, 2021 at 10:40
  • If you want the community to compare your data then post it as formatted text not images. See Senseful for a method of creating formatted text. Paste the results between lines containing just ``` Also Post you query function and the call patterns. Commented Aug 4, 2021 at 17:02
  • @FunThomas I have made the required changes. Thanks for highlighting the shortcomings in the question raised. Sam Thanks for highlighting the mistake. I have updated everything in the query. Belayer I have posted data from the destination where I am pasting it or retrieving it. I have already posted my query which is being used in EXCEL VBA and Oracle SQL Developer. Commented Aug 5, 2021 at 7:19
  • I am almost sure that either your data source or the query is different. Note that not only the rows with values are different, there is at least one row (2008) where one query shows data and the other not. I doubt that this related to the linked question. What you could do is to execute the query with another tool (SQL-Plus, Toad, PLSQL-Developer) to check if this is a SQL Developer (or Excel) problem. What I don't understand is your point 4. If I where you, I would try to query the underlying tables to get "the truth". Commented Aug 5, 2021 at 7:42
Related questions
2
PL/SQL Function returned without value
0
oracle pl sql function having errors
1
Oracle - Function not working
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.