Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VTOS-4845 CANNOT RETRIEVE DATA FROM THE TABLE

Posted on 2010-11-22
12
1,108 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
ID: 34192282
do you have the oracle error?

i guess you are having:

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

Author Comment

by:HITCHINER
ID: 34192414
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
ID: 34192473
what is the ora error you have with 10g client?
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
ID: 34192581
The ORA-0313 is from Oracle10g and the VTOS-4845 from 8i
0
 
LVL 5

Expert Comment

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

Author Comment

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

0
 

Author Comment

by:HITCHINER
ID: 34202044
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
ID: 34202155
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
ID: 34364567
It was all about the size of the TEMP files; after I increased them the error stopped.
0
 

Author Comment

by:HITCHINER
ID: 34364586
Thanks
0
 

Author Closing Comment

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

Expert Comment

by:Zopilote
ID: 34392951
Happy you found the solution!! :-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

789 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