Problem with variable.

[EM1107]

Good morning everyone!

I am new to Oracle scripting, I am from the SQL environment and now I have to create a script but I would like to assign the default value to a variable and I keep getting the error Bind variable "DOCKET_NO1" not declared.

Here is the query I am trying to run. Why is this not working!

DECLARE
DOCKET_NO1 NVARCHAR2 (14) := '2005-001-00001';

SELECT H.CREATOR AS PRTCP, M.NAME AS PRTCPNAME
FROM MECTS_HISTORY H
INNER JOIN MEMBER M ON M.MEMBERID = H.CREATOR
WHERE H.DOCKET_NO = OCKET_NO1
ORDER BY M.NAME;

 

[TheEntertainer]

replace
WHERE H.DOCKET_NO = OCKET_NO1

with

WHERE H.DOCKET_NO = DOCKET_NO1

if you really what to use bind variable then in sqlplus you need to put in something like

variable DOCKET_NO1 NVARCHAR2(14) [press return]

begin
select '2005-001-00001'
into OCKET_NO1
from dual;
end;
[type / then press return]

then type

print OCKET_NO1

 

[Beilstwh]

In sql$plus you can also do the following.


SELECT H.CREATOR AS PRTCP, M.NAME AS PRTCPNAME
FROM MECTS_HISTORY H
INNER JOIN MEMBER M ON M.MEMBERID = H.CREATOR
WHERE H.DOCKET_NO = &DOCKET_NO1
ORDER BY M.NAME;

When you run the select, you will be asked to supply a value for the variable "&DOCKET_NO1".

Bill
Oracle DBA/Developer
New York State, USA