Substitution variable into a procedure (merged) [message #302296] |
Mon, 25 February 2008 03:53  |
batitou81
Messages: 11 Registered: February 2008
|
Junior Member |
|
|
Hi everybody!
I'm trying to put " ACCEPT variable_column PROMPT 'number of columns'" into a procedure or function like:
CREATE OR REPLAC EPROCEDURE proc_number (table_name VARCHAR2) IS
ACCEPT variable_column PROMPT 'number of columns?
variable_column NUMBER(10) := &variable_column;
...
But this action doesn't works...
Do you know what I'm trying to do ?
Thanks !
|
|
|
|
|
|
|
|
|
|
|
Substitution variable into a procedure [message #302339 is a reply to message #302296] |
Mon, 25 February 2008 05:03   |
batitou81
Messages: 11 Registered: February 2008
|
Junior Member |
|
|
Hi everybody,
this is an example of my request:
create or replace procedure add_column(table_name VARCHAR2) IS
column_name VARCHAR2(20) := '&column_name';
column_type VARCHAR2(20) := '&column_type';
request VARCHAR2(40) := 'alter table ' || table_name || ' ADD (';
tmp VARCHAR2(15);
BEGIN
tmp :=
case
when column_type = 'Caracteres' THEN
'VARCHAR2(40)'
when column_type = 'Nombres' THEN
'NUMBER(15)'
END;
request := request || column_name || ' ' || tmp || ' )';
EXECUTE IMMEDIATE request;
END add_column;
/
But the subsitution variable are asked during the procedure creation, and no when I call my procedure.
How can I do that? Is it possible ?
Thank
|
|
|
Re: Substitution variable into a procedure [message #302341 is a reply to message #302339] |
Mon, 25 February 2008 05:09  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You can't.
As already said, procedure executes on SERVER, substitution variables are CLIENT objects.
This is the same question as the previous one, don't start a new topic for that.
Regards
Michel
|
|
|