Home » SQL & PL/SQL » SQL & PL/SQL » Substitution variable into a procedure (merged)
Substitution variable into a procedure (merged) [message #302296] Mon, 25 February 2008 03:53 Go to next message
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 !
Re: ACCEPT PROMPT command with a Procedure or function [message #302298 is a reply to message #302296] Mon, 25 February 2008 03:55 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Prompting for values is always closely related to the client/application your are using.

So, prompting for values within a procedure won't work at all.
Why not use an additional argument to your procedure? That's what they are for.
Re: ACCEPT PROMPT command with a Procedure or function [message #302306 is a reply to message #302298] Mon, 25 February 2008 04:08 Go to previous messageGo to next message
batitou81
Messages: 11
Registered: February 2008
Junior Member
MarcS wrote on Mon, 25 February 2008 03:55
Why not use an additional argument to your procedure? That's what they are for.

Thanks for your answer. How can I use an additional argument?
Re: ACCEPT PROMPT command with a Procedure or function [message #302310 is a reply to message #302306] Mon, 25 February 2008 04:10 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
batitou81 wrote on Mon, 25 February 2008 11:08
MarcS wrote on Mon, 25 February 2008 03:55
Why not use an additional argument to your procedure? That's what they are for.

Thanks for your answer. How can I use an additional argument?


Simply like this:
CREATE OR REPLACE PROCEDURE proc_number (table_name VARCHAR2 , another_argument NUMBER ) IS


Re: ACCEPT PROMPT command with a Procedure or function [message #302320 is a reply to message #302310] Mon, 25 February 2008 04:20 Go to previous messageGo to next message
batitou81
Messages: 11
Registered: February 2008
Junior Member
Yes, but I want to use a substitution variable, so, instead of the classic "Enter value for <substitution variable", I want my own message.
You knwo ?
Re: ACCEPT PROMPT command with a Procedure or function [message #302321 is a reply to message #302320] Mon, 25 February 2008 04:24 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Which client/application are you going to use to call the procedure?
Prompting for values is something done by the client/application and not from within the database itself.
Re: ACCEPT PROMPT command with a Procedure or function [message #302322 is a reply to message #302321] Mon, 25 February 2008 04:26 Go to previous messageGo to next message
batitou81
Messages: 11
Registered: February 2008
Junior Member
I use iSQL*Plus
Re: ACCEPT PROMPT command with a Procedure or function [message #302323 is a reply to message #302322] Mon, 25 February 2008 04:29 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
batitou81 wrote on Mon, 25 February 2008 11:26
I use iSQL*Plus


I don't know nor use iSQL*Plus, but I'm sure there's plenty of documentation on it, maybe you should have a read Wink
Re: ACCEPT PROMPT command with a Procedure or function [message #302325 is a reply to message #302323] Mon, 25 February 2008 04:33 Go to previous messageGo to next message
batitou81
Messages: 11
Registered: February 2008
Junior Member
Ok, maybe I miss somethings, I'm going to read it again.
Thanks!
Substitution variable into a procedure [message #302339 is a reply to message #302296] Mon, 25 February 2008 05:03 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: help needed
Next Topic: TRIGGER FOR CREATE VIEW
Goto Forum:
  


Current Time: Sun Feb 09 09:51:04 CST 2025