Home » SQL & PL/SQL » SQL & PL/SQL » Query on PL/SQL table using CAST (OS: Windows xp; Oracle 10g)
Query on PL/SQL table using CAST [message #386121] Thu, 12 February 2009 16:16 Go to next message
prasenjit.dutta
Messages: 14
Registered: July 2007
Junior Member

Hi,

I am using CAST function to query on a PL/SQL table. The part of code is like following:

DECLARE
TYPE g_xx_inv_tab_name_rec_typ IS RECORD (
tab_name VARCHAR2 (30)
);

TYPE g_xx_inv_tab_name_tab_typ IS TABLE OF g_xx_inv_tab_name_rec_typ
INDEX BY BINARY_INTEGER;

x_tab_list g_xx_inv_tab_name_tab_typ; --:= g_xx_inv_tab_name_tab_typ ();

CURSOR c_from
IS
SELECT *
FROM TABLE (CAST (x_tab_list AS g_xx_inv_tab_name_rec_typ));
BEGIN
NULL;
END;
/

While compiling this, I am continuously getting following error:

ORA-06550: line 14, column 41:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 13, column 7:
PL/SQL: SQL Statement ignored



Can any body help me out in this regard to fix this issue?
Re: Query on PL/SQL table using CAST [message #386137 is a reply to message #386121] Thu, 12 February 2009 18:43 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

It would help greatly if you used sqlplus along with CUT & PASTE so we can see what is at line 13 & 14.

[Updated on: Thu, 12 February 2009 18:43]

Report message to a moderator

Re: Query on PL/SQL table using CAST [message #386157 is a reply to message #386137] Thu, 12 February 2009 22:14 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Use SQL TYPE.

Thanks
Trivendra
Re: Query on PL/SQL table using CAST [message #386161 is a reply to message #386121] Thu, 12 February 2009 22:42 Go to previous messageGo to next message
prasenjit.dutta
Messages: 14
Registered: July 2007
Junior Member

Reposting the code along with error message from DQL prompt:

1 DECLARE
2 TYPE g_xx_inv_tab_name_rec_typ IS RECORD (
3 tab_name VARCHAR2 (30)
4 );
5 TYPE g_xx_inv_tab_name_tab_typ IS TABLE OF g_xx_inv_tab_name_rec_typ
6 INDEX BY BINARY_INTEGER;
7 x_tab_list g_xx_inv_tab_name_tab_typ; --:= g_xx_inv_tab_name_tab_typ ();
8 CURSOR c_from
9 IS
10 SELECT *
11 FROM TABLE (CAST (x_tab_list AS g_xx_inv_tab_name_rec_typ));
12 BEGIN
13 NULL;
14* END;
15 /
FROM TABLE (CAST (x_tab_list AS g_xx_inv_tab_name_rec_typ));
*
ERROR at line 11:
ORA-06550: line 11, column 33:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored


I am using this code in program units of form 10g and getting same error while compiling.

Fixing this issue is extremely urgent, as this is done against another issue in UAT.
Re: Query on PL/SQL table using CAST [message #386165 is a reply to message #386121] Thu, 12 February 2009 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
below from Posting Guidelines previously provided...

Before you decide to open a new topic, you should ask yourself:

Did I search the board properly? Have I also tried the Site Search Engine?



Re: Query on PL/SQL table using CAST [message #386167 is a reply to message #386161] Thu, 12 February 2009 23:08 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Go to SQLPLUS and create TYPE as you have done in PLSQL block. And then try.

Thanks
Trivnedra
Previous Topic: Gererate Undo Query Logs corresponding to DDL/DML logs
Next Topic: LAG Function Logic
Goto Forum:
  


Current Time: Mon Dec 05 19:14:10 CST 2016

Total time taken to generate the page: 0.10474 seconds