Solved

oracle bind variable

Posted on 2008-06-09
2
1,099 Views
Last Modified: 2013-12-07
I am new to oracle, and I wanted to know how to initialize a bind variable.  I tried doing the example below but I keep getting an error at line exec :basic_percent  := 45;  Once I remove the line it runs fine.  How do I set 45 to basic_percent within the executable section?
VARIABLE basic_percent number

VARIABLE pf_percent number
 

declare

outputHello varchar2(11):= 'Hello World';

today DATE:=SYSDATE;

tomorrow today%type:= today+1;
 

begin
 
 

exec :basic_percent  := 45;

DBMS_OUTPUT.PUT_LINE(outputHello);

DBMS_OUTPUT.PUT_LINE(today);

DBMS_OUTPUT.PUT_LINE(tomorrow);

end;

/

print basic_percent 

print pf_percent

Open in new window

0
Comment
Question by:yanci1179
  • 2
2 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 21748488
try the below :

VARIABLE basic_percent number
VARIABLE pf_percent number
 
declare
outputHello varchar2(11):= 'Hello World';
today DATE:=SYSDATE;
tomorrow today%type:= today+1;
 
begin
 
:basic_percent  := 45;
DBMS_OUTPUT.PUT_LINE(outputHello);
DBMS_OUTPUT.PUT_LINE(today);
DBMS_OUTPUT.PUT_LINE(tomorrow);
end;
/
print basic_percent
print pf_percent
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 21748498
exec should not be used in begin .. end; section. It can be used in the sql prompt to assign values like as shown below :

12:30:04 SQL> exec :basic_percent := 50

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
12:31:24 SQL> print

BASIC_PERCENT
-------------
           50

12:31:27 SQL>
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
compre toata in where clue oracle 4 55
Oracle - Stored Procedure Privilge access 3 38
PL/SQL Search for multiple strings 5 39
PL SQL Search Across Columns 4 18
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now