ORA-01001 [message #346750] |
Tue, 09 September 2008 10:55  |
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 #346756 is a reply to message #346750] |
Tue, 09 September 2008 11:43   |
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 #346759 is a reply to message #346757] |
Tue, 09 September 2008 11:59   |
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 #346765 is a reply to message #346759] |
Tue, 09 September 2008 12:10   |
 |
Michel Cadot
Messages: 68749 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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 #346813 is a reply to message #346812] |
Tue, 09 September 2008 14:30   |
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 #346823 is a reply to message #346815] |
Tue, 09 September 2008 15:52   |
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 #406688 is a reply to message #406663] |
Fri, 05 June 2009 06:45  |
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?
|
|
|