Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01008 error for cursor
ORA-01008 error for cursor [message #347132] Wed, 10 September 2008 18:14 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Analyze Table
Next Topic: Inserting Data For a long time??
Goto Forum:
  


Current Time: Sat Dec 03 08:07:44 CST 2016

Total time taken to generate the page: 0.05611 seconds