Solved

VTOS-4845 CANNOT RETRIEVE DATA FROM THE TABLE

Posted on 2010-11-22
12
1,121 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
[X]
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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

696 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