13 Replies Latest reply: Nov 14, 2008 8:29 AM by 551930 RSS

    Snapshots with LONG datatype

    551930
      Hi,

      I have a table with a LONG datatype field.
      While creating a materialized view for the same it gives the following error -
      ORA-00997: illegal use of LONG datatype

      Is there any way to use LONG datatype?
      I am using Oracle 10g database.
      Plz help.


      Thanks
      PantherHawk
        • 1. Re: Snapshots with LONG datatype
          BluShadow
          PantherHawk wrote:
          Hi,

          I have a table with a LONG datatype field.
          While creating a materialized view for the same it gives the following error -
          ORA-00997: illegal use of LONG datatype

          Is there any way to use LONG datatype?
          I am using Oracle 10g database.
          Well really you shouldn't be using LONG datatypes any more, they were deprecated a long time ago and replaced with CLOBs.

          How can we tell what "illegal use" you are doing if you don't show us your materialized view and table structure?
          • 2. Re: Snapshots with LONG datatype
            551930
            I am creating Materialized View as below-

            create snapshot MSTB_DLY_MSG_OUT refresh complete with rowid as SELECT /*+ PARALLEL ( "MSTB_DLY_MSG_OUT" , 8) */
            "MSTB_DLY_MSG_OUT"."BRANCH",
            "MSTB_DLY_MSG_OUT"."DCN",
            "MSTB_DLY_MSG_OUT"."REFERENCE_NO",
            "MSTB_DLY_MSG_OUT"."MESSAGE",
            "MSTB_DLY_MSG_OUT"."FORM_ID"
            FROM "MSTB_DLY_MSG_OUT"@CRPFCC.WORLD "MSTB_DLY_MSG_OUT";

            here field MESSAGE is of LONG datatype.


            Thanks
            PantherHawk
            • 3. Re: Snapshots with LONG datatype
              Karthick_Arp
              LONG cannot be used with Materialized view. Stop using LONG and start using LOB
              SQL> create table t(no number primary key, lng long, lb clob)
                2  /
              
              Table created.
              
              SQL> CREATE MATERIALIZED VIEW t_mv BUILD IMMEDIATE REFRESH ON COMMIT
                2  AS
                3  SELECT no, lng
                4    FROM t;
                FROM t
                     *
              ERROR at line 4:
              ORA-00997: illegal use of LONG datatype
              
              
              SQL> CREATE MATERIALIZED VIEW t_mv BUILD IMMEDIATE REFRESH ON COMMIT
                2  AS
                3  SELECT no, lb
                4    FROM t;
              
              Materialized view created.
              • 4. Re: Snapshots with LONG datatype
                94799
                Depending on what the snapshot/materialized view will be used for, you may be able to use the TO_LOB function to convert the LONG on the fly.
                Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                
                SQL> CREATE TA BLE table_name (
                  2     column_name_number NUMBER PRIMARY KEY,
                  3     column_name_long LONG);
                
                Table created.
                
                SQL> INSERT INTO table_name VALUES (1, 'long value');
                
                1 row created.
                
                SQL> CREATE MATERIALIZED VIEW materialized_view_name
                  2     BUILD IMMEDIATE
                  3     REFRESH COMPLETE
                  4  AS
                  5     SELECT column_name_number, TO_LOB (column_name_long) column_name_lob
                  6     FROM   table_name;
                
                Materialized view created.
                
                SQL>
                • 5. Re: Snapshots with LONG datatype
                  551930
                  Thanks padders

                  this method works..

                  but my problem is slightly different, as my materliazied view is not in the same instance as table, I am using DB link to create it in other instance.

                  In this case it gives following error -
                  ORA-22992: cannot use LOB locators selected from remote tables

                  Please let me know if there is a way to solve this.


                  Thanks
                  PantherHawk
                  • 6. Re: Snapshots with LONG datatype
                    BluShadow
                    PantherHawk wrote:
                    Thanks padders

                    this method works..

                    but my problem is slightly different, as my materliazied view is not in the same instance as table, I am using DB link to create it in other instance.
                    Would have been a good idea to tell us that at the beginning don't you think? ?:|

                    In this case it gives following error -
                    ORA-22992: cannot use LOB locators selected from remote tables
                    It will, as you cannot select LOB datatypes from remote databases.
                    • 7. Re: Snapshots with LONG datatype
                      94799
                      FROM "MSTB_DLY_MSG_OUT"@CRPFCC.WORLD "MSTB_DLY_MSG_OUT";
                      Actually it is evident from his script that it is remote, I did not notice, apologies.
                      • 8. Re: Snapshots with LONG datatype
                        551930
                        that means there is no solution to this problem..
                        • 9. Re: Snapshots with LONG datatype
                          94799
                          Well yes. Or that the two people answering this thread can't think of one just yet, or they were having a cup of tea or something.
                          • 10. Re: Snapshots with LONG datatype
                            551930
                            I found the solution of my problem -

                            create snapshot MSTB_DLY_MSG_OUT refresh complete with rowid as SELECT /*+ PARALLEL ( "MSTB_DLY_MSG_OUT" , 8) */
                            "MSTB_DLY_MSG_OUT"."BRANCH",
                            "MSTB_DLY_MSG_OUT"."DCN",
                            "MSTB_DLY_MSG_OUT"."REFERENCE_NO",
                            sys.dbms_metadata_util.long2varchar(4000,'MSTB_DLY_MSG_OUT','MESSAGE',rowid) MESSAGE,
                            "MSTB_DLY_MSG_OUT"."FORM_ID"
                            FROM "MSTB_DLY_MSG_OUT"@CRPFCC.WORLD "MSTB_DLY_MSG_OUT";

                            Thanks anyways for replying my query.

                            Regards
                            PantherHawk
                            • 11. Re: Snapshots with LONG datatype
                              BluShadow
                              padders wrote:
                              Well yes. Or that the two people answering this thread can't think of one just yet, or they were having a cup of tea or something.
                              I just had to pop out and pick up my new car. :)
                              • 12. Re: Snapshots with LONG datatype
                                94799
                                sys.dbms_metadata_util.long2varchar
                                Well that seems reasonable given that you are prepared to risk truncating the LONG. You could also have considered writing your own PL/SQL function call to do similarly.
                                • 13. Re: Snapshots with LONG datatype
                                  551930
                                  Well I can use the following also
                                  sys.dbms_metadata_util.long2clob(40000,'MSTB_DLY_MSG_OUT','MESSAGE',rowid) MESSAGE

                                  in place of
                                  sys.dbms_metadata_util.long2varchar(4000,'MSTB_DLY_MSG_OUT','MESSAGE',rowid) MESSAGE


                                  Regards
                                  PantherHawk