Home » SQL & PL/SQL » SQL & PL/SQL » User created datatype as OUT param in procedure - how do we read it?
User created datatype as OUT param in procedure - how do we read it? [message #249645] Thu, 05 July 2007 07:43 Go to next message
smora
Messages: 59
Registered: May 2006
Member
Im using 10g on Windows 2003.

I have created the following datatypes:

CREATE OR REPLACE TYPE selectbanner_obj AS OBJECT(
sb_ban_id NUMBER(32),
sb_ban_filename VARCHAR2(160),
sb_ban_resize NUMBER(1),
sb_trans_id NUMBER(32)
);
/
CREATE OR REPLACE TYPE selectbanner_table AS TABLE OF selectbanner_obj;
/


And i have a procedure that returns the above type as an OUT parameter:

create or replace procedure select_banner
(p_sub_id 	IN subscribers.sub_id%TYPE,
p_game_id 	IN games.game_id%TYPE,
p_ingame 	IN banners.banner_ingame_placement%TYPE,
p_ip		IN VARCHAR2,
p_uniq		IN NUMBER,
p_sel_bans	OUT selectbanner_table
)

AS


How do i read this datatype in
1) SQLPLus
2) In an ASP script

I tried this:
SQL>var aa refcursor
SQL>exec select_banner(520061115053206,120060727021315,'np','0.0.0.0',3,:aa);
BEGIN select_banner(520061115053206,120060727021315,'np','0.0.0.0',3,:aa); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_BANNER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Elapsed: 00:00:02.14


The procedure makes calls to other procs which execute DML statements, so i cant make it into a function and do a select off of the returned table. Any leads would be greatly appreciated.

Thanks
SM
Re: User created datatype as OUT param in procedure - how do we read it? [message #249655 is a reply to message #249645] Thu, 05 July 2007 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00306: wrong number or types of arguments in call to 'SELECT_BANNER'

Quote:
CREATE OR REPLACE TYPE selectbanner_table AS TABLE OF selectbanner_obj;
p_sel_bans OUT selectbanner_table

Quote:
var aa refcursor

Seems quite clear to me.

Regards
Michel
Re: User created datatype as OUT param in procedure - how do we read it? [message #249660 is a reply to message #249645] Thu, 05 July 2007 08:10 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Well if i try to declare variable aa as type selectbanner_table i get an error:

SQL>var aa selectbanner_table
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
                    BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL>


Thats why i tried var aa refcursor. What type should i declare var aa as? Or am i approaching this the wrong way?

[Updated on: Thu, 05 July 2007 08:11]

Report message to a moderator

Re: User created datatype as OUT param in procedure - how do we read it? [message #249665 is a reply to message #249660] Thu, 05 July 2007 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Or am i approaching this the wrong way?

Correct.
Return a REF CURSOR
Or use a PIPELINED function.

Regards
Michel
Re: User created datatype as OUT param in procedure - how do we read it? [message #249675 is a reply to message #249665] Thu, 05 July 2007 09:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
or ask yourself "why would I call this procedure, with an out parameter, from top-level sqlplus? Would I not call this only from other procedures?
If yes, why not test it using a test procedure instead of a variable?
Re: User created datatype as OUT param in procedure - how do we read it? [message #250481 is a reply to message #249645] Tue, 10 July 2007 04:12 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
I could call it from another procedure, but ultimately it will be called by an .asp script, so I'm trying to figure out what the best return type is that can be read in in asp.

Will try a few ideas and post results here. Any suggestions are appreciated.
Re: User created datatype as OUT param in procedure - how do we read it? [message #250486 is a reply to message #250481] Tue, 10 July 2007 04:26 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REF CURSOR is the best.

Regards
Michel
Previous Topic: Problem in Date - Group by
Next Topic: XMLTYPE Column
Goto Forum:
  


Current Time: Mon Dec 05 10:38:25 CST 2016

Total time taken to generate the page: 0.10455 seconds