Home » SQL & PL/SQL » SQL & PL/SQL » Avoid & in plsql (oracle 9i, 10g)
Avoid & in plsql [message #444720] Tue, 23 February 2010 13:51 Go to next message
sajidenam
Messages: 1
Registered: February 2010
Location: Mumbai
Junior Member
Following is a piece of my procedure.

CREATE OR REPLACE PROCEDURE SP_GET_TABLES_PROMPT
AS
l_no_rec NUMBER;
l_ratio NUMBER;
l_table_str VARCHAR2(200);

BEGIN
DBMS_OUTPUT.PUT_LINE('A) Populate All Tables');
DBMS_OUTPUT.PUT_LINE('B) Populate Selected Tables');
DBMS_OUTPUT.PUT_LINE('Select your Choice (A/B) :');
l_no_rec:='&No_RECORD';
l_ratio:='&RATIO';
l_table_str:='&Comma_Separeted_list';
SP_PROMPT_TABLE(l_table_str,l_no_rec,l_ratio);
END SP_GET_TABLES_PROMPT;
/

While running the code compiler prompts as Enter value for no_record, ratio and Comm_Sepereted_list due to & notation. I want to ignore this and use the values as input parameter to sp_prompt_table procedure.
Please help.
Re: Avoid & in plsql [message #444721 is a reply to message #444720] Tue, 23 February 2010 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to ignore this

set define off

Quote:
and use the values as input parameter to sp_prompt_table procedure.

You can't.
&var is a SQL*Plus variable.
The parameters of the procedure are in the procedure definition.


See:
SQL Reference, CREATE PROCEDURE
PL/SQL User's Guide and Reference, Chapter 1 Overview of PL/SQL, Section Subprograms: Procedures and Functions
SQL*PlusĀ® User's Guide and Reference, Chapter 5 Using Scripts in SQL*Plus, Section Using Substitution Variables

Regards
Michel


[Updated on: Tue, 23 February 2010 14:12]

Report message to a moderator

Re: Avoid & in plsql [message #444813 is a reply to message #444720] Wed, 24 February 2010 05:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to write pl/sql, you have to learn the syntax - you can't just write code from a different language and assume it will work.

You need to defined the parameters for your procedure explicitly:
CREATE OR REPLACE PROCEDURE SP_GET_TABLES_PROMPT
(p_no_record  in  number
,p_ratio      in  number
,p_list       in  varchar2) AS
l_no_rec NUMBER;
l_ratio NUMBER;
l_table_str VARCHAR2(200);

BEGIN
DBMS_OUTPUT.PUT_LINE('A) Populate All Tables');
DBMS_OUTPUT.PUT_LINE('B) Populate Selected Tables');
DBMS_OUTPUT.PUT_LINE('Select your Choice (A/B) :');
l_no_rec    := p_no_record;
l_ratio     := p_ration; 
l_table_str := p_list;
SP_PROMPT_TABLE(l_table_str,l_no_rec,l_ratio);
END SP_GET_TABLES_PROMPT;
/
Previous Topic: Mutating tables and row level triggers
Next Topic: count for null year
Goto Forum:
  


Current Time: Wed Dec 07 08:40:30 CST 2016

Total time taken to generate the page: 0.17138 seconds