• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1139
  • Last Modified:

VTOS-4845 CANNOT RETRIEVE DATA FROM THE TABLE

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
HITCHINER
Asked:
HITCHINER
  • 7
  • 5
2 Solutions
 
ZopiloteCommented:
do you have the oracle error?

i guess you are having:

ORA-03113: end-of-file on communication channel
0
 
HITCHINERAuthor Commented:
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
 
ZopiloteCommented:
what is the ora error you have with 10g client?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HITCHINERAuthor Commented:
The ORA-0313 is from Oracle10g and the VTOS-4845 from 8i
0
 
ZopiloteCommented:
do you have sql*plus with client O10G?
can you run your query there?
0
 
HITCHINERAuthor Commented:
Let me find out how to run it from there, and I will let you know of the results.

0
 
HITCHINERAuthor Commented:
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
 
ZopiloteCommented:
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
 
HITCHINERAuthor Commented:
It was all about the size of the TEMP files; after I increased them the error stopped.
0
 
HITCHINERAuthor Commented:
Thanks
0
 
HITCHINERAuthor Commented:
I was lead to find myself the solution, and probably otherwayt wouldn't have worked.
0
 
ZopiloteCommented:
Happy you found the solution!! :-)
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now