Home » SQL & PL/SQL » SQL & PL/SQL » passing parameters to pl/sql tables
passing parameters to pl/sql tables [message #192022] Sat, 09 September 2006 19:13 Go to next message
anil.rdbms
Messages: 11
Registered: August 2006
Location: NEW YORK
Junior Member

hi all,

i have a problem passing parameter values to pl/sql tables and executing it.


in my requirement my procedure gets values from webapplication
as a list . i need to capture and use those values to update a table in the procedure .

i have writen the procedure sp_editsets(a in varchar , b in varchar , in_loaners in sp_loaners);

in_loaners is the variable of table type sp_loaners.

i have used this parameter to capture the values and tried with sample data like this .

an example of my situation.


exec sp_editsets('x','y', 'ite','dhhh','dhhdh','dddd');

here after x and y parameters all the values are pl/sql table values which should be used inside the procedure

it gives message invalid arguments and i tried to execute like this also

exec sp_editsets('x','y',sp_loaners('dhhh','dhhdh','dddd');

where sp_loaners is the table type .

but again it gave in valid arguments .


how to do this ? how to give parameter values to pl/sql table when trying to execute from sqlpropmt?

if any body nows please come back..

thanks
anil
Re: passing parameters to pl/sql tables [message #192023 is a reply to message #192022] Sat, 09 September 2006 19:24 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
>pl/sql table
IMO, you have a basic "design" problem.
PL/SQL Table data type ONLY exists within a PL/SQL procedure; not within SQL*Plus & not within a "web application". RIGHT?
Modify sp_editsets such that it accepts data types available to the web application.
Re: passing parameters to pl/sql tables [message #192024 is a reply to message #192023] Sat, 09 September 2006 21:37 Go to previous messageGo to next message
rleishman
Messages: 3651
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If SP_LOANERS is a type declared in PL/SQL, then you have a problem. However, if it is a TYPE decared on the database
CREATE OR REPLACE TYPE sp_loaners AS TABLE OF VARCHAR2(60)
then you should be able to call it with an object constructor using the syntax you used.
exec sp_editsets('x','y',sp_loaners('dhhh','dhhdh','dddd');


Ross Leishman
Re: passing parameters to pl/sql tables [message #192041 is a reply to message #192024] Sun, 10 September 2006 07:48 Go to previous messageGo to next message
anil.rdbms
Messages: 11
Registered: August 2006
Location: NEW YORK
Junior Member

hi,
i have declared in the package body . i am not getting you clearly.

u want me to declare the type on the database server and then execute not in the package

and in the syntax u have written we have to

close one more bracket at the end after sp-loaners(dnjkkllll))
like this i hope .

i am working in one of the user on the database . shall
i create the type outside the package any

way i am executing the procedure
with packagename.procedurename .

can u please come bck quickly.

regards
anil
Re: passing parameters to pl/sql tables [message #192042 is a reply to message #192023] Sun, 10 September 2006 07:59 Go to previous messageGo to next message
anil.rdbms
Messages: 11
Registered: August 2006
Location: NEW YORK
Junior Member

hi,

actually i will get input to the parameter as a list
like '123','234','456','678','890'

i need to use each of the value to insert into a table in the procedure .

so i have taken pl/sql table of varchar as in parameter to capture that .

i just want to excute in the sql prompt the packagename.procedure to check that procedure with some parameters in sql prompt

i hve tried in two ways

like pk_sets._editsets('x','y','wsss','sss','dddd');
and like sp_editsets('x','y',sp_loaners('wsss','sss','dddd');

where pk_sets. sp_loaners is a table type declared in the package.

u mean to say since we declared a pl/sql table we cannot execute the procedure from sql prompt since the pl/sql table will be available only in pl/sql or what .

make me clear on this please.


regards
anil





why
Re: passing parameters to pl/sql tables [message #192550 is a reply to message #192042] Tue, 12 September 2006 18:29 Go to previous message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
See the attached sample code (designed to work from MOD_PLSQL)...
  • Attachment: DEMO_PKG.PKS
    (Size: 1.87KB, Downloaded 131 times)
  • Attachment: DEMO_PKG.PKB
    (Size: 7.75KB, Downloaded 184 times)
Previous Topic: Inserting the values in Internal BLOB
Next Topic: Shell and PLSQL
Goto Forum:
  


Current Time: Thu Apr 17 23:22:42 CDT 2014

Total time taken to generate the page: 0.15196 seconds