Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01001 (Oracle 10.2)
icon5.gif  ORA-01001 [message #346750] Tue, 09 September 2008 10:55 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,
I'm experiencing strange error:
ORA-01001: Invalid cursor

And this is why it is strange:

PROCEDURE proc(msg IN NUMBER, details IN VARCHAR2 DEFAULT NULL)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   moduleName VARCHAR2(48);
   actionName VARCHAR2(32);
   currentTimestamp LogTable.EventTimestamp%TYPE;
   osUser     VARCHAR2(30 BYTE);
BEGIN
DBMS_APPLICATION_INFO.READ_MODULE(module_name => moduleName, action_name => actionName);
INSERT /*+ APPEND */
INTO EpnpaLog 
VALUES
(logLevel  /* package-level variable of type NUMBER */
,CURRENT_TIMESTAMP
,moduleName 
,actionName 
,msg        
,details    
,sys_context('USERENV', 'OS_USER')
);
END proc;


The code above is inside a logging package.
I am not using DBMS_SQL package here. When I try to debug it from Oracle SQL Developer I'm getting ORA-01001 exactly on that INSERT...

Re: ORA-01001 [message #346755 is a reply to message #346750] Tue, 09 September 2008 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did.
Did you get the error at compilation time or at execution one?
In the latter, post your call statement.

By the way, APPEND hint on "INSERT VALUES" is meaningless.
Don't use hint if you don't know what they are for.

Regards
Michel
Re: ORA-01001 [message #346756 is a reply to message #346750] Tue, 09 September 2008 11:43 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks Michel!

As for my strange incident with oracle I found the cause.
DECLARE
var VARCHAR2(4001);
BEGIN
var := 'text long for 4001 characters [COLOR=red]...[/COLOR]';
CREATE TABLE t (col VARCHAR2(4000));
INSERT INTO t VALUES var;
END;


I haven't checked this but it should cause 'Invalid cursor' exception. Strange, isn't it?
Re: ORA-01001 [message #346757 is a reply to message #346756] Tue, 09 September 2008 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It is not a valid PL/SQL block (CREATE TABLE is not a PL/SQL statement
2/ It depends on your character set and what's inside the string
3/ What is the relation with the procedure?

Regards
Michel
Re: ORA-01001 [message #346759 is a reply to message #346757] Tue, 09 September 2008 11:59 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Sorry if this is not described enough.

If
1) you create a table that has column of type VARCHAR2(4000)
2) try to insert into it a value that is larger than 4000 characters (this "can be" done from PL/SQL * )
then you would get "Invalid cursor" exception.

* In PL/SQL you can use VARCHAR2 variables of size 32768 (if I remember correctly) while SQL and database itself allows only 4000 characters.
Re: ORA-01001 [message #346762 is a reply to message #346750] Tue, 09 September 2008 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: ORA-01001 [message #346765 is a reply to message #346759] Tue, 09 September 2008 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again copy and paste what you did.
I didn't get what you said:
SQL> CREATE TABLE t (col VARCHAR2(4000));

Table created.

SQL> declare var VARCHAR2(4001) := rpad('X',4001,'X');
  2  begin
  3    INSERT INTO t VALUES (var);
  4  end;
  5  /
declare var VARCHAR2(4001) := rpad('X',4001,'X');
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 3

SQL> @v

Version Oracle : 10.2.0.4.0

Regards
Michel

[Updated on: Tue, 09 September 2008 12:10]

Report message to a moderator

Re: ORA-01001 [message #346810 is a reply to message #346755] Tue, 09 September 2008 14:09 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Michel Cadot wrote on Tue, 09 September 2008 12:38


By the way, APPEND hint on "INSERT VALUES" is meaningless.
Don't use hint if you don't know what they are for.




What do you mean by meaningless? do you mean we should only used APPEND hint on insert..select? thanks!
Re: ORA-01001 [message #346812 is a reply to message #346810] Tue, 09 September 2008 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this is what I meant.

Regards
Michel
Re: ORA-01001 [message #346813 is a reply to message #346812] Tue, 09 September 2008 14:30 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks, as from what I've read the APPEND hint just tells the insert to place the rows after the HWM, what would be the difference in effect of using "insert...select" over "insert...values". Thanks again.
Re: ORA-01001 [message #346815 is a reply to message #346813] Tue, 09 September 2008 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
APPEND Hint
Quote:
The APPEND hint instructs the optimizer to use direct-path INSERT


and

Database Administrator's Guide
Chapter 15 Managing Tables
Section Inserting Data Into Tables Using Direct-Path INSERT

Regards
Michel
Re: ORA-01001 [message #346823 is a reply to message #346815] Tue, 09 September 2008 15:52 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
got it =)

Notes:

*Direct-path INSERT supports only the subquery syntax of the INSERT statement, not the VALUES clause.

thank you very much!

it seems like there's a lot more *great* things to learn in the DBA guide than in the application dev guide.

[Updated on: Tue, 09 September 2008 15:54]

Report message to a moderator

Re: ORA-01001 [message #406663 is a reply to message #346750] Fri, 05 June 2009 04:43 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member

Are you using hints in your SQL?[ 3 votes ]
1. NO - I trust that Oracle is choosing correct decisions 1 / 33%
2. YES - Oracle does not know how to choose the best way of query execution 0 / 0%
3. YES - and it is dynamically added to my queries 1 / 33%
4. Why would I need hints for? If the query plan is incorrect then its work for DBA. 0 / 0%
5. Only to test whether different approach would be better. 1 / 33%

Although this post is rather quite old I feel that one thing should be added to its sub-discussion:

APPEND hint could be used for INSERT's and that would allow data compression, while normal INSERT would not (?) compress such data.
Reference: http://www.oracle.com/technology/oramag/oracle/04-mar/o24tech_data.html
Re: ORA-01001 [message #406666 is a reply to message #406663] Fri, 05 June 2009 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
APPEND hint could be used for INSERT's and that would allow data compression, while normal INSERT would not (?) compress such data.

It does in 11g.

Regards
Michel
Re: ORA-01001 [message #406688 is a reply to message #406663] Fri, 05 June 2009 06:45 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
wakula wrote on Fri, 05 June 2009 11:43
YES - and it is dynamically added to my queries

WTF is that? Blindly adding hints?
Why not get rid of Oracle altogether and get a cheaper database, if you are planning to do the work for it anyway?
Previous Topic: WILL BFILE FETECH FILES FROM SHARED FOLDER
Next Topic: EMAIL WITH ATTACHMENT FROM ORACLE 9i (merged 2)
Goto Forum:
  


Current Time: Tue Mar 19 05:52:10 CDT 2024