OraFAQ Forum: Application Express, ORDS & MOD_PLSQL

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » running pl/sql in apex.oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

running pl/sql in apex.oracle [message #627326]

Mon, 10 November 2014 22:00

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

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627329 is a reply to message #627326]

Mon, 10 November 2014 22:26

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627331 is a reply to message #627329]

Mon, 10 November 2014 22:57

Lalit Kumar B
Messages: 3174
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>

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627344 is a reply to message #627326]

Tue, 11 November 2014 02:17

John Watson
Messages: 8961
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.

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627420 is a reply to message #627344]

Tue, 11 November 2014 15:32

Littlefoot
Messages: 21823
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) ...?

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627585 is a reply to message #627331]

Wed, 12 November 2014 18:57

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

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627586 is a reply to message #627420]

Wed, 12 November 2014 18:59

ppatel87
Messages: 5
Registered: November 2014

Junior Member

Yes i am trying to run that in sql workshop in apex oracle

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627601 is a reply to message #627586]

Thu, 13 November 2014 00:15

Littlefoot
Messages: 21823
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

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Sorry, my bad. I didn't pay attention to the topic title.

Report message to a moderator

Re: running pl/sql in apex.oracle [message #627765 is a reply to message #627601]

Sat, 15 November 2014 20:15

ppatel87
Messages: 5
Registered: November 2014

Junior Member

Hi
thanks a lot for your help
It worked Smile

Report message to a moderator

Previous Topic:	Filter not working
Next Topic:	ORA-06502: PL/SQL: numeric or value error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 20:10:44 CST 2024