Home » SQL & PL/SQL » SQL & PL/SQL » WHY IT'S NOT
WHY IT'S NOT [message #242494] Sat, 02 June 2007 15:53 Go to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

Hi everyone,
see the following query ,it's in oracle 9.2.0.1.0

DECLARE
TYPE C_REC IS RECORD(ID DEICMAIN.DCAN%TYPE,INC DEICMAIN.INC%TYPE);
TYPE C_TABLE IS TABLE OF C_REC
INDEX BY BINARY_INTEGER;
C_TAB C_TABLE;
BEGIN
SELECT DCAN,INC BULK COLLECT INTO C_TAB FROM DEICMAIN;
FOR I IN 1..C_TAB.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(C_TAB(I).ID);
END LOOP;
END;

but if the same i am firing in oracle 9.1 then it gives an error at the following highlighted porion

DECLARE
TYPE C_REC IS RECORD(ID DEICMAIN.DCAN%TYPE,INC DEICMAIN.INC%TYPE);
TYPE C_TABLE IS TABLE OF C_REC
INDEX BY BINARY_INTEGER;
C_TAB C_TABL;
BEGIN
SELECT DCAN,INC BULK COLLECT INTO C_TAB FROM DEICMAIN;
END;

is it like that this query is new introduced in oracle 9.2 version.

RAJAT CHAUDHARY

[Updated on: Sat, 02 June 2007 15:58]

Report message to a moderator

Re: WHY IT'S NOT [message #242495 is a reply to message #242494] Sat, 02 June 2007 16:12 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>is it like that this query is new introduced in oracle 9.2 version.
Or Oracle fixed a bug.
So what exactly is your question?
Re: WHY IT'S NOT [message #242526 is a reply to message #242495] Sun, 03 June 2007 10:41 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

hi anacedent,
my question is .
if this is first time introduced in oracle 9.2 then it's fine otherwise where i am mistaken in writing the code.......
Re: WHY IT'S NOT [message #242537 is a reply to message #242526] Sun, 03 June 2007 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you posted the error message we can say you more.
Better copy and paste your screen with the failed execution.
Please read and apply How to format your posts

Regards
Michel


Re: WHY IT'S NOT [message #242693 is a reply to message #242537] Mon, 04 June 2007 13:01 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

HI Michel Cadot,

CODE IS GIVEN BELOW

DECLARE
 TYPE REC_TAB IS RECORD (VAR_EMP EMPLOYEES.EMPLOYEE_ID%TYPE,VAR_DEPT DEPARTMENTS.DEPARTMENT_ID%TYPE);
 TYPE TABLE_TYPE IS TABLE OF REC_TAB
 INDEX BY BINARY_INTEGER;
 TAB_TYPE TABLE_TYPE;
BEGIN
 SELECT EMPLOYEE_ID,DEPARTMENT_ID BULK COLLECT INTO TAB_TYPE FROM EMPLOYEES;
 FOR I IN 1..TAB_TYPE.COUNT LOOP
 DBMS_OUTPUT.PUT_LINE(TAB_TYPE(I).VAR_EMP||'-----'||TAB_TYPE(I).VAR_DEPT);
 END LOOP;
END;
/


THE SAME CODE IS RUNNING PERFECTLY IN ORACLE 9.2.0.1.0 BUT IT GIVES AN ERROR IN ORACLE 9.0 VERSION,
NOT GETING WHY ?

CODE IS GIVEN BELOW

DECLARE
 TYPE REC_TAB IS RECORD (VAR_EMP EMPLOYEES.EMPLOYEE_ID%TYPE,VAR_DEPT DEPARTMENTS.DEPARTMENT_ID%TYPE);
 TYPE TABLE_TYPE IS TABLE OF REC_TAB
 INDEX BY BINARY_INTEGER;
 TAB_TYPE TABLE_TYPE;
BEGIN
 *SELECT EMPLOYEE_ID,DEPARTMENT_ID BULK COLLECT INTO TAB_TYPE FROM EMPLOYEES;
 FOR I IN 1..TAB_TYPE.COUNT LOOP
 DBMS_OUTPUT.PUT_LINE(TAB_TYPE(I).VAR_EMP||'-----'||TAB_TYPE(I).VAR_DEPT);
 END LOOP;
END;
/


* SELECT EMPLOYEE_ID,DEPARTMENT_ID BULK COLLECT INTO TAB_TYPE FROM EMPLOYEES;

ERROR IS SOMETHING LIKE THAT (THE COLOR PORTION ):=
INTO CLAUSE CANN'T BE USE AT HERE.
Re: WHY IT'S NOT [message #242697 is a reply to message #242693] Mon, 04 June 2007 13:22 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each version has its limitations, each next version remove some limitations.
In your case, you have some limitations in 9.0 that were removed in 9.2.
Conclusion: use 9.2.

Btw, 9.0 was the most buggy version that Oracle ever released.

Regards
Michel
Previous Topic: Inner Join example
Next Topic: date compare
Goto Forum:
  


Current Time: Wed Dec 07 05:04:53 CST 2016

Total time taken to generate the page: 0.05739 seconds