Home » SQL & PL/SQL » SQL & PL/SQL » default type of null column in explicit cursor (Oracle 11g)
default type of null column in explicit cursor [message #606339] Wed, 22 January 2014 09:03 Go to next message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
Hi there

I was wondering, just out of curiosity, what would be the default type of the "null_field" as shown in this snippet:

DECLARE

   CURSOR smart_cursor IS
      SELECT 1    AS useless_field,
             NULL AS null_field
        FROM dual;

   TYPE ty_smart_cursor IS TABLE OF smart_cursor%ROWTYPE;
   tab_cursor ty_smart_cursor;

BEGIN

   OPEN smart_cursor;
   FETCH smart_cursor BULK COLLECT
      INTO tab_cursor;
   FOR i IN 1 .. tab_cursor.count
   LOOP
   
      tab_cursor(i).null_field := 1;
      tab_cursor(i).null_field := 'string';
      tab_cursor(i).null_field := SYSDATE;
      
      SELECT LPAD('0',327676,'0')
      INTO tab_cursor(i).null_field FROM dual;
      
     -- dbms_output.put_line(tab_cursor(i).null_field);
   
   END LOOP;

   CLOSE smart_cursor;

END;


can you help me out? I couldn't find an answer but perhaps I was searching for the wrong things

thanks

Report message to a moderator

Re: default type of null column in explicit cursor [message #606340 is a reply to message #606339] Wed, 22 January 2014 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is VARCHAR2.

SQL> create view v as select null a from dual;

View created.

SQL> desc v
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 A                                         VARCHAR2

Report message to a moderator

Re: default type of null column in explicit cursor [message #606341 is a reply to message #606339] Wed, 22 January 2014 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
varchar2. You can check by creating a view:
SQL> CREATE VIEW test_view AS
  2  SELECT 1 AS useless_field,
  3         NULL AS null_field
  4  FROM dual;
 
View created
 
SQL> select column_name, data_type, data_length from user_tab_columns where table_name = 'TEST_VIEW';
 
COLUMN_NAME                    DATA_TYPE              DATA_LENGTH
------------------------------ ---------------------  -----------
USELESS_FIELD                  NUMBER                          22
NULL_FIELD                     VARCHAR2                         0
 
SQL>

Alternatively you can tell oracle what datatype to use with the cast function.

Report message to a moderator

Re: default type of null column in explicit cursor [message #606342 is a reply to message #606341] Wed, 22 January 2014 09:32 Go to previous messageGo to next message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
I didn't thought of that. I was wondering about the length, though, because in sql it's 4000, in pl/sql 32.767, I used 327.676 and it worked so..

Report message to a moderator

Re: default type of null column in explicit cursor [message #606344 is a reply to message #606342] Wed, 22 January 2014 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I used 327.676 and it worked so..
32767 is OK until error gets thrown.

Report message to a moderator

Re: default type of null column in explicit cursor [message #606346 is a reply to message #606342] Wed, 22 January 2014 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

LPAD just silently truncates the result to 4000 bytes, its maximum length in SQL statement:
SQL> set lines 100
SQL> DECLARE
  2  
  3     CURSOR smart_cursor IS
  4        SELECT 1    AS useless_field,
  5               NULL AS null_field
  6          FROM dual;
  7  
  8     TYPE ty_smart_cursor IS TABLE OF smart_cursor%ROWTYPE;
  9     tab_cursor ty_smart_cursor;
 10  
 11  BEGIN
 12  
 13     OPEN smart_cursor;
 14     FETCH smart_cursor BULK COLLECT
 15        INTO tab_cursor;
 16     FOR i IN 1 .. tab_cursor.count
 17     LOOP
 18     
 19        tab_cursor(i).null_field := 1;
 20        tab_cursor(i).null_field := 'string';
 21        tab_cursor(i).null_field := SYSDATE;
 22        
 23        SELECT LPAD('0',327676,'0')
 24        INTO tab_cursor(i).null_field FROM dual;
 25  
 26   dbms_output.put_line(tab_cursor(i).null_field);
 27        
 28       -- dbms_output.put_line(tab_cursor(i).null_field);
 29     
 30     END LOOP;
 31  
 32     CLOSE smart_cursor;
 33  
 34  END;
 35  /
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

PL/SQL procedure successfully completed.

SQL>  SELECT LPAD('0',327676,'0')
  2  FROM dual;
LPAD('0',327676,'0')
----------------------------------------------------------------------------------------------------
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

1 row selected.

Report message to a moderator

Re: default type of null column in explicit cursor [message #606347 is a reply to message #606346] Wed, 22 January 2014 10:13 Go to previous message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
Thanks Michel, that was a tricky one Smile

Report message to a moderator

Previous Topic: Explain how these errors are coming? is it due to raise_application_error??
Next Topic: how to improve sql & Skills
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 08:34:02 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.