Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » running pl/sql in apex.oracle
running pl/sql in apex.oracle [message #627326] Mon, 10 November 2014 22:00 Go to next message
ppatel87
Messages: 5
Registered: November 2014
Junior Member
Hi,
I am trying to run below pl/sql block in apex.oracle

DECLARE
v_itemname VARCHAR2(30);
BEGIN
v_itemname := '&v_item';
DBMS_OUTPUT.PUT_LINE(v_itemname);
END;

but the output i am getting is '&v_item'
while It should be prompting to ask for a value to be given by user
for v_itemname

Please can anyone help me to know that how can I make it work it in such a way that,
the user given value is assigned to v_itemname and hence the same to be printed
Re: running pl/sql in apex.oracle [message #627329 is a reply to message #627326] Mon, 10 November 2014 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 26708
Registered: January 2009
Location: SoCal
Senior Member
WELCOME to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

SQL> SET DEFINE OFF
Re: running pl/sql in apex.oracle [message #627331 is a reply to message #627329] Mon, 10 November 2014 22:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
BlackSwan wrote on Tue, 11 November 2014 09:56

SQL> SET DEFINE OFF


OP wants the opposite of it. SET DEFINE ON

ppatel87 wrote on Tue, 11 November 2014 09:30

but the output i am getting is '&v_item'
while It should be prompting to ask for a value to be given by user
for v_itemname



SQL> set serveroutput on;
SQL> DECLARE
  2  v_itemname VARCHAR2(30);
  3  BEGIN
  4  v_itemname := '&v_item';
  5  DBMS_OUTPUT.PUT_LINE(v_itemname);
  6  END;
  7  /
&v_item

PL/SQL procedure successfully completed.

SQL> set define on
SQL> DECLARE
  2  v_itemname VARCHAR2(30);
  3  BEGIN
  4  v_itemname := '&v_item';
  5  DBMS_OUTPUT.PUT_LINE(v_itemname);
  6  END;
  7  /
Enter value for v_item: hi
old   4: v_itemname := '&v_item';
new   4: v_itemname := 'hi';
hi

PL/SQL procedure successfully completed.

SQL>
Re: running pl/sql in apex.oracle [message #627344 is a reply to message #627326] Tue, 11 November 2014 02:17 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
Are you trying to use a SQL*Plus substitution variable in an Apex programme? That is not going to work.
Re: running pl/sql in apex.oracle [message #627420 is a reply to message #627344] Tue, 11 November 2014 15:32 Go to previous messageGo to next message
Littlefoot
Messages: 21562
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where exactly do you want to run that PL/SQL code? What do you call "apex.oracle"? I'm not familiar with this term; so, a few suggestions:
a) is it a workspace you got on apex.oracle.com?
b) is it Apex' SQL Workshop (either on apex.oracle.com or your local Apex installation)?
c) Page process?
d) ...?
Re: running pl/sql in apex.oracle [message #627585 is a reply to message #627331] Wed, 12 November 2014 18:57 Go to previous messageGo to next message
ppatel87
Messages: 5
Registered: November 2014
Junior Member
Hi,
But I am trying to run above code in apex.oracle
and there set define on is showing error
Please help
Re: running pl/sql in apex.oracle [message #627586 is a reply to message #627420] Wed, 12 November 2014 18:59 Go to previous messageGo to next message
ppatel87
Messages: 5
Registered: November 2014
Junior Member
Yes i am trying to run that in sql workshop in apex oracle
Re: running pl/sql in apex.oracle [message #627601 is a reply to message #627586] Thu, 13 November 2014 00:15 Go to previous messageGo to next message
Littlefoot
Messages: 21562
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In that case, substitute ampersand (&) with a colon (:):
DECLARE
  v_itemname VARCHAR2(30);
BEGIN
  v_itemname := :v_item;
  DBMS_OUTPUT.PUT_LINE(v_itemname);
END;
/


P.S. You don't need any SET command (as it is relevant for SQL*Plus, but - you don't use it anyway).

[Updated on: Thu, 13 November 2014 00:16]

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627626 is a reply to message #627601] Thu, 13 November 2014 07:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sorry, my bad. I didn't pay attention to the topic title.
Re: running pl/sql in apex.oracle [message #627765 is a reply to message #627601] Sat, 15 November 2014 20:15 Go to previous message
ppatel87
Messages: 5
Registered: November 2014
Junior Member
Hi
thanks a lot for your help
It worked Smile
Previous Topic: Filter not working
Next Topic: ORA-06502: PL/SQL: numeric or value error
Goto Forum:
  


Current Time: Tue Jun 02 19:41:32 CDT 2020