13 Replies Latest reply: Nov 23, 2012 4:04 AM by 975734 RSS

    Regarding Nclob

    975734
      I have declared a nclob variable and stored large oracle query. Now I need to execute that stored query and move the result to cursor . Any one can help me please.
      Same can be possible with clob data type but my query is larger than clob capacity.
        • 1. Re: Regarding Nclob
          Solomon Yakobson
          Dynamic SQL does not support NVARCHAR2/NCLOB. VARCHAR2/CLOB is as far as it gets.

          SY.
          • 2. Re: Regarding Nclob
            975734
            There is no way to run that nclob datatype queries dynamically. Can you please suggest me it's important
            • 3. Re: Regarding Nclob
              Hoek
              Why are you storing queries in the first place?
              This approach is not a recommended one.
              See the follow-up here, for example: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1943344500346351703

              "CLOB objects can store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data."
              http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#SQLRF20041
              What kind of queries are you trying to run, if you manage to exceed that limit?

              Rethink your entire approach to your unknown requirement and make sure to read the SQL and PL/SQL FAQ, and add necessary details like database version and a small testcase.
              • 4. Re: Regarding Nclob
                975734
                I have a table with query column, in stored procedure I have to read that query from table and execute it and send the result to calling stored procedure. Clob capacity is 4 gigabytes but when I have declared clob variable in stored procedure it's not accepting more than 4 kb of data. That's why I am planning to use nclob but execution of nclob query is the problem. My query length is not that much big but why it's not stored in clob data type ?
                • 5. Re: Regarding Nclob
                  sb92075
                  972731 wrote:
                  I have a table with query column, in stored procedure I have to read that query from table and execute it and send the result to calling stored procedure. Clob capacity is 4 gigabytes but when I have declared clob variable in stored procedure it's not accepting more than 4 kb of data. That's why I am planning to use nclob but execution of nclob query is the problem. My query length is not that much big but why it's not stored in clob data type ?
                  When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

                  storing query as data violates Best Practices & is amateurish.

                  query should be stored in Procedures & executed as required.
                  • 6. Re: Regarding Nclob
                    975734
                    Query will be changed in table so we cann't hard code the query in stored procedure. We have to read from table and then we have to execute it.
                    • 7. Re: Regarding Nclob
                      rp0428
                      >
                      I have a table with query column, in stored procedure I have to read that query from table and execute it and send the result to calling stored procedure. Clob capacity is 4 gigabytes but when I have declared clob variable in stored procedure it's not accepting more than 4 kb of data.
                      >
                      Then you are doing it wrong. Post the code you are using that you say does not accept more than 4kb of data into the CLOB.

                      Besides, both NCLOB and CLOB have the same max size so that isn't any reason to try to use NCLOB.

                      What is your 4 digit Oracle version? If you are using 10g or earlier you can't need more than 32KB for your queries so in PL/SQL a VARCHAR2 would work anyway.

                      In 11g EXECUTE IMMEDIATE will work with a CLOB.
                      • 8. Re: Regarding Nclob
                        975734
                        PL/SQL DEVELOPER VERSION 7.1.4.1390 AND ORACLE DATABASE IS 11g. Nclob variable is storing my entire query but clob is storing half of the query . If both capacity is same then why this problem is coming?
                        • 9. Re: Regarding Nclob
                          rp0428
                          >
                          Nclob variable is storing my entire query but clob is storing half of the query . If both capacity is same then why this problem is coming?
                          >
                          You're the only one that can see the code you are using. As I said above
                          >
                          Post the code you are using that you say does not accept more than 4kb of data into the CLOB.
                          • 10. Re: Regarding Nclob
                            975734
                            why clob and nclob capacity is different for me is it because of pl sql version?
                            • 11. Re: Regarding Nclob
                              rp0428
                              Good bye. You clearly don't want to be helped so good luck with your problem.
                              • 12. Re: Regarding Nclob
                                975734
                                I have given enough information. Can I post my query ?
                                • 13. Re: Regarding Nclob
                                  975734
                                  I only did a mistake clob is working fine. thanks for your support.