Home » SQL & PL/SQL » SQL & PL/SQL » "ORA-01405" returned with .NET driver on Oracle server 9.2
"ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202866] Mon, 13 November 2006 02:16 Go to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
Hi.
I am developing a web application (with ASP.NET, framework version 1.1)
which has to run some queries on a Oracle server (version 9.2), through
System.Data.OracleClient.OracleDataAdapter.

When I run a particular query, I retreive the following error through
the .NET driver
"ORA-01405: fetched column value is NULL".


The SQL query that raises the problem is something like that
"SELECT [...],
RIGHE_OFF.IS_CANONE As Canone,
[...]"


Moreover, if I run the same query with TOAD (sql client for Oracle), no
exception is raisen, even if some NULL data is effectively returned.


After several hours wihtout any idea, I found a "dirty" solution to fix
the problem, and now my query is
"SELECT [...],
CASE WHEN RIGHE_OFF.IS_CANONE IS NULL THEN
NULL
ELSE
RIGHE_OFF.IS_CANONE
END As Canone,
[...]"


The query finally returns what I need, but my solution is just a
workaround on the problem, because, even looking for informations on
the net, I coudn't understand the problem.
And, above all, I don't understand why, with such a fool artifice, I
could solve the problem!


Can someone tell me how to avoid that stupid trick, or at least why I
have to do it?
Thanx in advance!
Alessio
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202869 is a reply to message #202866] Mon, 13 November 2006 02:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you google for the error ORA-01405?
There are some hits that look like they explain the problem. eg. this one
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202877 is a reply to message #202869] Mon, 13 November 2006 02:36 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
Frank wrote on Mon, 13 November 2006 09:23
Did you google for the error ORA-01405?
There are some hits that look like they explain the problem. eg. this one


Yes, of course I searched on the net, but without any useful result.

I found tips like that one on the forum to which you linked me, but:
1) I can't use the NVL function because I have to keep my SQL code as much server-independent as possible (so I should not use Oracle-specific functions)
2) I still can't understand how to use the "USAFE_NULL=TRUE" command
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202879 is a reply to message #202877] Mon, 13 November 2006 02:42 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of NVL, you could use CASE instead (which is, I believe, ANSI standard).
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202881 is a reply to message #202879] Mon, 13 November 2006 02:51 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
Littlefoot wrote on Mon, 13 November 2006 09:42
As of NVL, you could use CASE instead (which is, I believe, ANSI standard).


In fact I used that clause, as I wrote in my first post, but it's strange the fact that it works...
I'd like, if someone can explain it to me, to understand why such a workaround is working and, possibly, how to avoid it.

Thanx anyway Smile
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202883 is a reply to message #202881] Mon, 13 November 2006 02:59 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, right, you've said so, but that would require me to read the whole topic. Sorry.

It seems that query you used was NOT exactly the one you wrote as an example - regarding the error description, there should be a FETCH operation with an INTO clause which contained a NULL value. Did you use a cursor, perhaps?

However, never mind that - you got an error, and Oracle suggests you to solve it using the NVL function (or CASE, which you already did).
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202885 is a reply to message #202883] Mon, 13 November 2006 03:04 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

"2) I still can't understand how to use the "USAFE_NULL=TRUE" command"

I reckon you mean UNSAFE_NULL

UNSAFE_NULL
Purpose
Specifying UNSAFE_NULL=YES prevents generation of ORA-01405 messages when fetching NULLs without using indicator variables.

Syntax
UNSAFE_NULL={YES | NO}

Default
NO

Usage Notes
Cannot be entered inline.

The UNSAFE_NULL=YES is allowed only when MODE=ORACLE.

The UNSAFE_NULL option has no effect on host variables in an embedded PL/SQL block. You must use indicator variables to avoid ORA-01405 errors.

[Updated on: Mon, 13 November 2006 03:06]

Report message to a moderator

Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202887 is a reply to message #202883] Mon, 13 November 2006 03:17 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
Littlefoot wrote on Mon, 13 November 2006 09:59
Huh, right, you've said so, but that would require me to read the whole topic. Sorry.

It seems that query you used was NOT exactly the one you wrote as an example - regarding the error description, there should be a FETCH operation with an INTO clause which contained a NULL value. Did you use a cursor, perhaps?

However, never mind that - you got an error, and Oracle suggests you to solve it using the NVL function (or CASE, which you already did).


I used THAT query, but I guess oracle driver for .NET internally transforms it in a stored procedure using cursors...

I can't use the NVL function because is Oracle-dependent, and, as I said, I have to keep my SQL code platform-independent.

Thanx anyway!
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202889 is a reply to message #202885] Mon, 13 November 2006 03:29 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
tahpush wrote on Mon, 13 November 2006 10:04
"2) I still can't understand how to use the "USAFE_NULL=TRUE" command"

I reckon you mean UNSAFE_NULL

UNSAFE_NULL
Purpose
Specifying UNSAFE_NULL=YES prevents generation of ORA-01405 messages when fetching NULLs without using indicator variables.

Syntax
UNSAFE_NULL={YES | NO}

Default
NO

Usage Notes
Cannot be entered inline.

The UNSAFE_NULL=YES is allowed only when MODE=ORACLE.

The UNSAFE_NULL option has no effect on host variables in an embedded PL/SQL block. You must use indicator variables to avoid ORA-01405 errors.


Sorry, I still don't know how tu use it...
I typed "help SET" on Oracle Plus (Oracle command-line tool) 'cause I thought I had to use that, but "UNSAFE_NULL" is not mentioned in possible parameters for the SET command:

SQL> help SET

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session, for example:
     -   display width for data
     -   turn on HTML formatting
     -   enabling or disabling printing of column headings
     -   number of lines per page

 In iSQL*Plus, you can also use the System Variables screen to set
 system variables.

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                  *NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n}
   AUTORECOVERY {ON|OFF}                    PAGES[IZE] {24|n}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}        *PAU[SE] {OFF|ON|text}
     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED] |
   BLO[CKTERMINATOR] {.|c}                    EA[CH]|OFF}
   CMDS[EP] {;|c|OFF|ON}                    RECSEPCHAR {_|c}
   COLSEP {_|text}                          SERVEROUT[PUT] {OFF|ON}
   COM[PATIBILITY] {V7|V8|NATIVE}             [SIZE n] [FOR[MAT]
   CON[CAT] {.|c|OFF|ON}                      {WRA[PPED] |
   COPYC[OMMIT] {0|n}                         WOR[D_WRAPPED] |
   COPYTYPECHECK {OFF|ON}                     TRU[NCATED]}]
   DEF[INE] {&|c|OFF|ON}                   *SHIFT[INOUT] {VIS[IBLE] |
   DESCRIBE [DEPTH {1|n|ALL}]                 INV[ISIBLE]}
     [LINENUM {ON|OFF}] [INDENT {ON|OFF}]  *SHOW[MODE] {OFF|ON}
   ECHO {OFF|ON}                           *SQLBL[ANKLINES] {ON|OFF}
  *EDITF[ILE] file_name[.ext]               SQLC[ASE] {MIX[ED] |
   EMB[EDDED] {OFF|ON}                        LO[WER] | UP[PER]}
   ESC[APE] {\|c|OFF|ON}                   *SQLCO[NTINUE] {> | text}
   FEED[BACK] {6|n|OFF|ON}                 *SQLN[UMBER] {OFF|ON}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
  *FLU[SH] {OFF|ON}                        *SQLPRE[FIX] {#|c}
   HEA[DING] {OFF|ON}                      *SQLP[ROMPT] {SQL>|text}
   HEADS[EP] {||c|OFF|ON}                   SQLT[ERMINATOR]
   INSTANCE [instance_path|LOCAL]             {;|c|OFF|ON}
   LIN[ESIZE] {80|n} ({150|n} iSQL*Plus)   *SUF[FIX] {SQL|text}
   LOBOF[FSET] {n|1}                        TAB {OFF|ON}
   LOGSOURCE [pathname]                     TERM[OUT] {OFF|ON}
   LONG {80|n}                              TI[ME] {OFF|ON}
   LONGC[HUNKSIZE] {80|n}                   TIMI[NG] {OFF|ON}
   MARK[UP] HTML [ON|OFF]                   TRIM[OUT] {OFF|ON}
     [HEAD text] [BODY text] [TABLE text]   TRIMS[POOL] {ON|OFF}
     [ENTMAP {ON|OFF}]                      UND[ERLINE] {-|c|ON|OFF}
     [SPOOL {ON|OFF}]                       VER[IFY] {OFF|ON}
     [PRE[FORMAT] {ON|OFF}]                 WRA[P] {OFF|ON}

 An asterisk (*) indicates the SET option is not supported in iSQL*Plus.

SQL>


I tried anyway to set it like that:

SQL> SET UNSAFE_NULL YES
SP2-0735: opzione SET con inizio "UNSAFE_NUL..." sconosciuta
SQL>


But, as you can see, it returns me the "unknown option" error message (the right translation from Italian is "SP2-0375: SET option starting with 'UNSAFE_NUL...' is unknown").
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202890 is a reply to message #202889] Mon, 13 November 2006 03:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look at the PreCompilers guide

Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202893 is a reply to message #202890] Mon, 13 November 2006 03:47 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
JRowbottom wrote on Mon, 13 November 2006 10:34
Have a look at the PreCompilers guide




Sorry, I have few experience with command-line scripting.
I tried to set that option with SQLPlus (command-line tool for Oracle) that way:

SQL> UNSAFE_NULL=YES
SP2-0734: comando con inizio "UNSAFE_NUL..." sconosciuto. Il resto della riga  stato ignorato.
SQL>


As you can see, I got an error: "SP2-0734: command starting with 'UNSAFE_NUL...' is unknown. The rest of the line has been ignored."

I've also tried with "SET" command, but unsuccessfully, as I wrote in a previous post.

[Updated on: Mon, 13 November 2006 07:13]

Report message to a moderator

Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202906 is a reply to message #202893] Mon, 13 November 2006 04:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another possibility is to add an indicator variable

As for why your query works with the case, I don't have a clue!

[Updated on: Mon, 13 November 2006 04:35]

Report message to a moderator

Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202910 is a reply to message #202906] Mon, 13 November 2006 04:48 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
Frank wrote on Mon, 13 November 2006 11:35
Another possibility is to add an indicator variable

As for why your query works with the case, I don't have a clue!


I guess indicator variables are used un functions/stored procedures.
My problem is with a normal query.

Anyway, I also think indicator variables are Oracle-specific.
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202942 is a reply to message #202910] Mon, 13 November 2006 06:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why pay tons of dollars for a database and not use its functionality??
Database-independent code is a pain!
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202943 is a reply to message #202942] Mon, 13 November 2006 07:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True - tends to work poorly on the Db you develop against, and badly on everything else.

You're using .NET I notice - why is Database independence so much more important than OS independance?
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202947 is a reply to message #202943] Mon, 13 November 2006 07:11 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
JRowbottom wrote on Mon, 13 November 2006 14:00
True - tends to work poorly on the Db you develop against, and badly on everything else.

You're using .NET I notice - why is Database independence so much more important than OS independance?



I'm using ASP.NET, so I have OS independency, you just need a web browser to interface my application, no matter which OS you use.
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202948 is a reply to message #202942] Mon, 13 November 2006 07:12 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
Frank wrote on Mon, 13 November 2006 13:58
Why pay tons of dollars for a database and not use its functionality??
Database-independent code is a pain!


I know :(
But my company's specifications are these ones, I'm payed to do what they need.
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202951 is a reply to message #202866] Mon, 13 November 2006 07:16 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Well UNSAFE_NULL=YES is not a SQLPLUS command in anyway.

Im might on the wrong side of the road here.......
Anyway
It's used for when you manually precompile code on for example the unix prompt, with the proc "command"

Example for c++ pre-compile
proc UNSAFE_NULL=YES MODE=ORACLE DBMS=V8 CODE=CPP SQLCHECK=FULL LINES=YES CPP_SUFFIX=cc testclass.pc



"Why pay tons of dollars for a database and not use its functionality??"
Agree, strange indeed
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202956 is a reply to message #202951] Mon, 13 November 2006 07:24 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
tahpush wrote on Mon, 13 November 2006 14:16
Well UNSAFE_NULL=YES is not a SQLPLUS command in anyway.

Im might on the wrong side of the road here.......
Anyway
It's used for when you manually precompile code on for example the unix prompt, with the proc "command"

Example for c++ pre-compile
proc UNSAFE_NULL=YES MODE=ORACLE DBMS=V8 CODE=CPP SQLCHECK=FULL LINES=YES CPP_SUFFIX=cc testclass.pc



So I can't use it in an ASP.NET application?
Sorry, but I didn't know, I told I'm programming in .NET since my first post, and many replies to my question are saying I can use this option to solve the problem...
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202972 is a reply to message #202956] Mon, 13 November 2006 08:04 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Im not a java/net nor c programmer

I think you have to pinpoint the error more specific.
Exactly when does this occure, when you do Execute ? Or when you read back the result into "something".
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #202985 is a reply to message #202972] Mon, 13 November 2006 08:16 Go to previous messageGo to next message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
That's the piece of code launching the query and getting the error:
            Select Case DBType
                [...]
                Case DBTYPE_ORACLE
                    Dim loc_osqlDA As New OracleDataAdapter
                    loc_osqlDA.SelectCommand = BuildQueryCommandOracle(par_sQuery, m_QueryParameters)
                    loc_osqlDA.Fill(loc_oDataSet, par_sTableName)
                [...]
            End Select


The function "BuildQueryCommandOracle" is ok: given the String containing the SQL code (par_sQuery) and its parameters (m_QueryParameters), it builds correctly the data adapter SelectCommand.

The exception I talked about is thrown on the "loc_osqlDA.Fill".
"loc_oDataSet" is a DataSet object to fill and par_sTableName is the name of the table in the DataSet.

The query passed is the one I mentioned in my first post. I didn't post all the query becase it's very complex and I could find that the problem was just on the line I reported.
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #203006 is a reply to message #202866] Mon, 13 November 2006 09:03 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

So the dataset is created in the BuildQueryCommandOracle function ? instead of ?
Dim loc_oDataSet As DataSet = New DataSet("loc_oDataSet")
loc_osqlDA.Fill(loc_oDataSet, par_sTableName)

As I said --> .NET not my thing
Re: "ORA-01405" returned with .NET driver on Oracle server 9.2 [message #203008 is a reply to message #203006] Mon, 13 November 2006 09:21 Go to previous message
hashintosh
Messages: 12
Registered: November 2006
Junior Member
The empty dataset is instanced before, then filled by "loc_osqlDA.Fill" instruction, which runs the query and raises the error:
            [...]
            Dim loc_oDataSet As New DataSet
            Select Case DBType
                [...]
                Case DBTYPE_ORACLE
                    Dim loc_osqlDA As New OracleDataAdapter
                    loc_osqlDA.SelectCommand = BuildQueryCommandOracle(par_sQuery, m_QueryParameters)
                    loc_osqlDA.Fill(loc_oDataSet, par_sTableName)
                [...]
            End Select
            [...]

Previous Topic: Select case when
Next Topic: how to split a full name seperate names
Goto Forum:
  


Current Time: Sat Dec 03 01:24:19 CST 2016

Total time taken to generate the page: 0.10327 seconds