Solved

VTOS-4845 CANNOT RETRIEVE DATA FROM THE TABLE

Posted on 2010-11-22
12
1,094 Views
Last Modified: 2013-12-19
Have a kind of complex query, that when run on DBASTUDIO it detects a certain amount of records, but when prompted if I want to  apply a query, regardless I select all the records or by building a query, a window messages pops up with the message:
"VTOS-4845 CANNOT RETRIEVE DATA FROM THE TABLE",
I am  runing an ORACLE 8i client and accesing an Oracle 10g db(if run from Oracle10g client got another generic error, and still unable to access the data). I can run other queries just fine, and not this one:

Select
A.ORDER_REF,
A.ORDER_PRD,
A.SUPP_CODE,
B.STOCK_QTY,
DECODE(B.COST_VAL,'01',B.VALUE_1,'02',B.VALUE_2,'03',B.VALUE_3 ,'04',B.VALUE_4 ,'05',B.VALUE_5 ,'06',B.VALUE_6,'07',B.VALUE_7,'08',B.VALUE_8,'09',B.VALUE_9,'10',B.VALUE_10,'11',B.VALUE_11,'12',B.VALUE_12,'13',B.VALUE_13,'14',B.VALUE_14,'15',B.VALUE_15,'16',B.VALUE_16,'17',B.VALUE_17) CV1J2,
B.GRN_REF,
DECODE(B.INV_STATUS,'01',B.HIGH_REF,'02',B.HIGH_REF,'03',B.HIGH_REF,'10',B.HIGH_REF,'11',B.HIGH_REF,'12',B.HIGH_REF,'13',B.HIGH_REF,'20',B.HIGH_REF,'21',B.HIGH_REF,'22',B.HIGH_REF,'23',B.HIGH_REF,'40',B.HIGH_REF,'41',B.HIGH_REF,'42',B.HIGH_REF,'43',B.HIGH_REF,'50',B.HIGH_REF ,'51',B.HIGH_REF,'52',B.HIGH_REF,'53',B.HIGH_REF,'00',' ') HRJ2,
B.INV_PRD,
B.STOCK_PRD_POSTED,
B.RECEIPT_REF,
B.ACCNT_CODE,
B.PURCH_QTY,
B.VALUE_1,
B.VALUE_2,
B.VALUE_3,
B.VALUE_4,
B.VALUE_5,
B.VALUE_6,
B.VALUE_7,
B.VALUE_8,
B.VALUE_9,
B.VALUE_10,
B.VALUE_11,
B.ORD_STATUS,
B.DEL_DATE,
B.GRN_DATE,
DECODE (C.REC_TYPE,'M', C.PERIOD,(SUBSTRB(C.MVMNT_REF,1,7))) AS PERIOD1,
C.ITEM_CODE,
C.RI_IND,
C.ID_ENTERED,
C.ID_ALLOC,
C.STATUS,
C.AUDIT_DATE,
D.ITEM_PROC,
D.ALLOC_METH,
E.ITEM_CODE AS ITEM_CODE1,
E.DESCRIPTN,
E.UNIT_WGHT,
E.STD_COST,
E.UNIT_STOCK
from
SPMFORD2010 A,
SPMFDET2010 B,
SMMFMOV2010 C,
SSRFITM D,
SSRFITM E,
SSRFADD F,
las_LANG G
WHERE
(B.TRANS_REF(+)=A.ORDER_REF)
AND (C.MVMNT_REF=B.TRANS_REF AND C.ORIG_LINE_NO = B.TRANS_LINE AND C.ITEM_CODE=B.ITEM_CODE AND C.MVMNT_TYPE=B.TRANS_TYPE)
AND (D.SUN_DB (+)='HM2' AND D.ITEM_CODE(+)=C.ITEM_CODE)
AND (E.SUN_DB (+)='HM2' AND E.ITEM_CODE(+)=B.ITEM_CODE)
AND (F.SUN_DB (+)='HM2' AND F.ADD_CODE(+)=A.SUPP_CODE)
AND (G.DBASE(+)='HM2' and G.CODE(+)=F.ADDRESS_1 and G.CONTROL (+)='0AD')
AND DECODE (C.REC_TYPE,'M', C.PERIOD,(SUBSTRB(C.MVMNT_REF,1,7)))
BETWEEN 2010001 AND 2010001
ORDER BY A.ORDER_REF desc
0
Comment
Question by:HITCHINER
  • 7
  • 5
12 Comments
 
LVL 5

Expert Comment

by:Zopilote
Comment Utility
do you have the oracle error?

i guess you are having:

ORA-03113: end-of-file on communication channel
0
 

Author Comment

by:HITCHINER
Comment Utility
Hello, yes, that is the error ; I  had to cread a TEMP db in ACCESS 2007 and created a link to the respective view ( as I do not have DBAStudio or its equivalent in Oracle 10g), any advice?
Regards
0
 
LVL 5

Expert Comment

by:Zopilote
Comment Utility
what is the ora error you have with 10g client?
0
 

Author Comment

by:HITCHINER
Comment Utility
The ORA-0313 is from Oracle10g and the VTOS-4845 from 8i
0
 
LVL 5

Expert Comment

by:Zopilote
Comment Utility
do you have sql*plus with client O10G?
can you run your query there?
0
 

Author Comment

by:HITCHINER
Comment Utility
Let me find out how to run it from there, and I will let you know of the results.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:HITCHINER
Comment Utility
hELLO : I've got new error messages, now when run or call the View fromCrystal Reportsx 8.5 :
"ora-01652 unable to extend temp segment by in tablespace temp"

Guess this is the root cause.......any advice?
0
 
LVL 5

Assisted Solution

by:Zopilote
Zopilote earned 500 total points
Comment Utility
good, you got the real problem now.
you can read about that error here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:374218170986

I am quite sure the real problem is your query, you need to optimize it

do you know how to do an explain plan? in sql*PLus replacing with your query

EXPLAIN PLAN FOR
   SELECT empno, ename
   FROM emp
   WHERE ename LIKE 'S%';

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

http://articles.techrepublic.com.com/5100-10878_11-6070884.html
http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/ex_plan.htm#19259

post the results.
0
 

Accepted Solution

by:
HITCHINER earned 0 total points
Comment Utility
It was all about the size of the TEMP files; after I increased them the error stopped.
0
 

Author Comment

by:HITCHINER
Comment Utility
Thanks
0
 

Author Closing Comment

by:HITCHINER
Comment Utility
I was lead to find myself the solution, and probably otherwayt wouldn't have worked.
0
 
LVL 5

Expert Comment

by:Zopilote
Comment Utility
Happy you found the solution!! :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now