Solved

VTOS-4845 CANNOT RETRIEVE DATA FROM THE TABLE

Posted on 2010-11-22
12
1,114 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
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!

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Title # Comments Views Activity
SQL Developer 6 75
Oracle function to insert records? 15 65
error in my cursor 5 50
Cannot connect to Oracle database, python not recognizing cx_Oracle 2 31
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

733 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