This discussion is archived
13 Replies Latest reply: Nov 23, 2012 2:04 AM by 975734 RSS

Regarding Nclob

975734 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Dynamic SQL does not support NVARCHAR2/NCLOB. VARCHAR2/CLOB is as far as it gets.

    SY.
  • 2. Re: Regarding Nclob
    975734 Newbie
    Currently Being Moderated
    There is no way to run that nclob datatype queries dynamically. Can you please suggest me it's important
  • 3. Re: Regarding Nclob
    Hoek Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    why clob and nclob capacity is different for me is it because of pl sql version?
  • 11. Re: Regarding Nclob
    rp0428 Guru
    Currently Being Moderated
    Good bye. You clearly don't want to be helped so good luck with your problem.
  • 12. Re: Regarding Nclob
    975734 Newbie
    Currently Being Moderated
    I have given enough information. Can I post my query ?
  • 13. Re: Regarding Nclob
    975734 Newbie
    Currently Being Moderated
    I only did a mistake clob is working fine. thanks for your support.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points