OraFAQ Forum: SQL & PL/SQL » ORA-01008 error for cursor

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01008 error for cursor

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

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

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 00:07:15 CST 2024