Customize input message - PL/SQL Substitution variable

[Kasi Viswan]

Hi,

Am trying to make user input URL through substitution variable, am not able to use accept command since i am using pl/sql anonymous block and this statement is one of many in that block.
I want this message be displayed to the user, Enter value for

serverurl  :-P	  ortno

:
am not able to use a substitution variable like this

&serverurl  :-P	  ortno

Has anyone come across such issue?, your help is greatly appreciated.

i don't know why i get the smileys, its serverurl followed by : followed by portno

Thanks,
Kasi

[ November 02, 2008: Message edited by: Kasi Viswan ]

[ November 02, 2008: Message edited by: Kasi Viswan ]
[ November 02, 2008: Message edited by: Kasi Viswan ]

[Avi Abrami]

Kasi,
You said:

i don't know why i get the smileys

When posting to the forum, look for the "Disable smilies in this post" check box and make sure it is checked.

Good Luck,
Avi.

[Paul Campbell]

Originally posted by Kasi Viswan:
[QB]Hi,

Am trying to make user input URL through substitution variable, am not able to use accept command since i am using pl/sql anonymous block and this statement is one of many in that block.
I want this message be displayed to the user, Enter value for

serverurl   :-P	   ortno

:
am not able to use a substitution variable like this

&serverurl   :-P	   ortno

Has anyone come across such issue?, your help is greatly appreciated.

i don't know why i get the smileys, its serverurl followed by : followed by portno

Thanks,
Kasi
QB]

Substitution variables are a feature of SQLPlus... is that what you're using? Could you post your pl/sql (between code tags)?

[Kasi Viswan]

Hi, here is the part of my script, here instead of using a substitution variable &URL_AND_PORTNUMBER, i want some thing like &serverurl:portnumber

so a user will be prompted to, Enter the value for serverurl:portnumber.

Thanks for the inputs on disabling the smiley.

 
SET SERVEROUTPUT ON;
SET VERIFY OFF;
 
DECLARE
 
app_server_name VARCHAR2(20);
serverurl_portno VARCHAR2(20);
 
BEGIN
 
--- Some code here deleted
 
app_server_name := '&APP_SERVER_NAME';
--accept myv char prompt 'SERVER_URL :p ORT_NO: ';
serverurl_portno := '&URL_AND_PORTNUMBER';
 
DBMS_OUTPUT.PUT_LINE(' APP_SERVER_NAME: ' || app_server_name);
DBMS_OUTPUT.PUT_LINE(' SERVER_URL :p ORT_NO: ' || serverurl_portno);
 
UPDATE NT_APPSERVER SET URL = serverurl_portno WHERE APPSERVER = app_server_name;
DBMS_OUTPUT.PUT_LINE(' NT_APPSERVER update complete ');
 
commit;

END;
/   

[ November 03, 2008: Message edited by: Kasi Viswan ]

[Paul Campbell]

Kasi,

Accept & Prompt are SQLPlus commands. Annonymous blocks do support variable substitution via these commands (your substitution code looks correct and you did set severoutput on ). Are the Accept and Prompt part of the same Batch Script you are running in SQLPlus? They need to be part of the same session and you can't do this substitution outside of SQLPlus... if you need to do it external from SQLPlus you can do things like read a table that captures the information or something similar.

Paul