ORA-01008 error for cursor [message #347132] |
Wed, 10 September 2008 18:14 |
casttree
Messages: 83 Registered: August 2008
|
Member |
|
|
I saw some code like below (the code is called by other program). I am not sure how the code works as ":1 := data_cursor; " I mean it may not work directly (could be embedded with other function) , but I am more interetsed in understaning the procedure and how the data_cursor is assigned to :1 ?
When I try to replace the original code with simple sql "select 2 from dual; " and run it, it shows the error "ORA 01008 not all variable bound"
Any comments for how the cursor works in this way,
DECLARE
TYPE data_cursor_type IS REF CURSOR;
data_cursor data_cursor_type;
BEGIN
OPEN data_cursor FOR
-- SELECT to_char(NVL(MAX(s.snap_time), sysdate), 'YYYY-MM-DD HH24:MI:SS')
-- FROM perfstat.stats$snapshot s, v$database d, v$instance i
-- WHERE s.dbid = d.dbid and s.instance_number = i.instance_number;
select 2 from dual;
:1 := data_cursor;
END;
[Edit MC: add code tags]
[Updated on: Thu, 11 September 2008 00:55] by Moderator Report message to a moderator
|
|
|
Re: ORA-01008 error for cursor [message #347197 is a reply to message #347132] |
Thu, 11 September 2008 00:58 |
|
Michel Cadot
Messages: 68726 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
This PL/SQL code is called from a language (Pro*C, JDBC, OCI...), :1 is (in this case) the first (and only) bind variable.
It is not intended to be called from SQL*Plus.
If you change :1 to a named variable, you can use it in SQL*Plus:
SQL> var foo refcursor
SQL> DECLARE
2 TYPE data_cursor_type IS REF CURSOR;
3 data_cursor data_cursor_type;
4 BEGIN
5 OPEN data_cursor FOR
6 -- SELECT to_char(NVL(MAX(s.snap_time), sysdate), 'YYYY-MM-DD HH24:MI:SS')
7 -- FROM perfstat.stats$snapshot s, v$database d, v$instance i
8 -- WHERE s.dbid = d.dbid and s.instance_number = i.instance_number;
9
10 select 2 from dual;
11
12 :foo := data_cursor;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL> print foo
2
----------
2
1 row selected.
Regards
Michel
[Updated on: Thu, 11 September 2008 01:03] Report message to a moderator
|
|
|