Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: Open Ref Cursor For CLOB datatype?

    All,

    I'm trying to open a REF CURSOR for a dynamically generated query. Due to it's side I've had to switch from VARCHAR2 to CLOB.

    Of course when I switched the datatypes, the other procedures croaked due to the change in datatype.

    Here's a snippet of the code:
    Code:
    IF NOT customer_cur%ISOPEN THEN
         subQ:=getSubSelect(userid,'CUSTOMER');--this is a CLOB
         OPEN customer_cur FOR 'SELECT customer_id FROM ('||subQ||') WHERE customer_acct='''|| UPPER(v_customer_acct)||'''';
    END IF;
    My question is:

    Is there anyway to force that type of call to accept a CLOB?

    I've successfully wrapped subQ in a TO_CHAR, and that worked (for a small sub query (i.e. les that 32k chars), but I haven't tried it with a generated query that is longer than the max size of a varchar2. Is this the only way to make it work?

    Am I missing something simple here?


    If there is any other way to go about this, please let me know.


    Thanks in advance!

    --james

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    A PL/SQL VARCHAR2 can hold up to 32,767 characters.
    If you can't hold a single SQL statement in that length, then I am impressed with your SQL coding skills.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2006
    Posts
    47

    :)Heh

    Wish I could take the credit for it... And yes on occasion, they can exceed 32k characters in length. Its a user generated query, and due to some wonky DECODE commands, I'm forced to list every column in the table individually (some 60 or so) and then order them afterwords. So what I end up with is a pretty long string, but not to bad. Then you let your users start mucking around and you end up with this same query being joined 15 or 16 times, and the size hits 35k characters

    Hence my question. I was thinking maybe of writing a function to break out the clok every 32k characters into an array of varchar2's. and then concatenating the array into the query. But I thought I'd ask for advice before doing that.

    I wish I could do this to save some room:
    SELECT CUSTOMER.*, DECODE() FROM CUSTOMER
    --james

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    If the 'DECODE()' statements are being used to translate code's to text descriptions you may want to create function(s) to do this -- or if they are used as 'IF' statements, try replacing the DECODE() with CASE statement.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by LKBrwn_DBA


    If the 'DECODE()' statements are being used to translate code's to text descriptions you may want to create function(s) to do this -- or if they are used as 'IF' statements, try replacing the DECODE() with CASE statement.

    I'm not 100% sure I'd know how to use those. Let me give you an example.

    SELECT blah1,blah2,....,blah65,DECODE(max(decode(permissi on,''change'',10,''request'',9,''view'',8,''none'' ,7,0)),10,''change'',9,''request'',8,''view'',7,'' none'') as permission
    FROM some_table GROUP BY blah1,blah2,....,blah65
    As you can see, I'm trying to rank the "permissions" that are assigned to each of these records and only return the max permission.

    The issue at hand is two fold. For one of these tables there are 60+ columns. So what I end up doing is generating a query that has to list all of the field names, and then list them again for the group by clause. (I think this is correct, but maybe there's a better way). What happens is that if the generated query is 2000 characters long (btw, this represents one "permission group"), its possible the user might have been granted multiple "permissions groups", this could lead to even longer queries.

    The second issue is that the criteria that sets the permission is user generated. Its unlikely, though possible, that regardless of how i wirte the query the sheer number of where criteria might exceed the maximum VARCHAR2 limit.


    So I'm down to two options that I can see:

    1.) Find someway to make a variable length field (like a CLOB for maximum size protection) work as part of a dynamic OPEN FOR ref cursor. (Splitting it into multiple varchars and concatting/using to_char/etc)

    2) Compact the generated code so much that it becomes really difficult to exceed that limit.

    If I'm making part of this overly bulky, please let me know. Although, honestly, since the generated query could be as big as the user makes it, i think using a clob is a better answer.

    Does anyone know what would happen if you had a 50k character CLOB, and ran TO_CHAR on it?

    You guys are a great help as always.

    --james

  6. #6
    Join Date
    Feb 2006
    Posts
    46

    You can use string functions on type CLOB and convert them to varchar

    Quote Originally Posted by jholder
    I'm not 100% sure I'd know how to use those. Let me give you an example.



    As you can see, I'm trying to rank the "permissions" that are assigned to each of these records and only return the max permission.

    The issue at hand is two fold. For one of these tables there are 60+ columns. So what I end up doing is generating a query that has to list all of the field names, and then list them again for the group by clause. (I think this is correct, but maybe there's a better way). What happens is that if the generated query is 2000 characters long (btw, this represents one "permission group"), its possible the user might have been granted multiple "permissions groups", this could lead to even longer queries.

    The second issue is that the criteria that sets the permission is user generated. Its unlikely, though possible, that regardless of how i wirte the query the sheer number of where criteria might exceed the maximum VARCHAR2 limit.


    So I'm down to two options that I can see:

    1.) Find someway to make a variable length field (like a CLOB for maximum size protection) work as part of a dynamic OPEN FOR ref cursor. (Splitting it into multiple varchars and concatting/using to_char/etc)

    2) Compact the generated code so much that it becomes really difficult to exceed that limit.

    If I'm making part of this overly bulky, please let me know. Although, honestly, since the generated query could be as big as the user makes it, i think using a clob is a better answer.

    Does anyone know what would happen if you had a 50k character CLOB, and ran TO_CHAR on it?

    You guys are a great help as always.

    --james
    You can use string functions on type CLOB and convert them to varchar

  7. #7
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by shiva65
    You can use string functions on type CLOB and convert them to varchar
    Even if the size of the CLOB you're converting exceeds the max length of a VARCHAR2?

    --james

  8. #8
    Join Date
    Mar 2006
    Posts
    47

    hmmm

    My testing leads me to believe that you cannot perform a TO_CHAR on a CLOB that is longer than 32,727 characters.

    --james

  9. #9
    Join Date
    Feb 2006
    Posts
    46
    You will have to break up your CLOB into serveral varchar variables. I do not know if this is what you want. I have had to use CLOBS when writing them to files and there is a 65,000 byte limit on the buffer size. I worked around this by using substring functions.

  10. #10
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by shiva65
    You will have to break up your CLOB into serveral varchar variables. I do not know if this is what you want. I have had to use CLOBS when writing them to files and there is a 65,000 byte limit on the buffer size. I worked around this by using substring functions.
    That's one of hte options I was looking at. I ran across an old ASKTOM article that said you could open a cursor for a datatype CLOB by using the DBMS_SQL functions. I'm not familiar with those, so I'm going to look into that tommorrow and see if thats what I need to use here. According to Tom's response, it is possible to do what I'm looking for, I just need to figure out exactly how to do it.

    I'll post how that works out.

    --james

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I will have to second anacedent's comments on this, for _so_ many reasons.

    james, it is possible for you to post a sample of your data and the expected output you want ? perhaps there's a better way of doing this alltogether!

  12. #12
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by JMartinez
    I will have to second anacedent's comments on this, for _so_ many reasons.

    james, it is possible for you to post a sample of your data and the expected output you want ? perhaps there's a better way of doing this alltogether!
    Whose comments?

    I can't post the data for security reasons, however the basic premise is pretty simple. We have a group of tables that hold criteria for certain data elements. These criteria are grouped into, uh, user groups

    Then we apply these groups to individual users, that then filters out all of the data elements that their particular user groups can/can't see.

    I can't use Oracles built in functionality because its a web app, and all users share the same oracle user to access it.

    The groups permission criteria is changed by the customer in real time. So we have very little control over how simple or complex they get.

    A user can belong to multiple groups, and their "permissions" are a tiered no permission, view permission, request for change, and direct change. Where request and direct change implies view.

    I don't know of anyway to achieve the end results that we wanted differently than this.

    (although thats probably more htan you wanted to know

    Basically my problem is two fold:

    1) I have to list every row in the table twice because of the DECODE bit, and since I'm max'ing on the decode, I have to list almost all of them again for GROUP BY.

    2) Since we don't control the number of permissions, the queries will be of unknown size. Since we dynamically create a query for each assigned group, and then UNION when a user has multiple groups.

    The problem comes up in some of our stored procedures when we try to return a cursor that has been "pre-filtered" for lack of a better term. Since we generate the "filter query", and then query it... something like:

    Code:
    fq:='some really long query string';
    
    
    OPEN customer_cur FOR 'SELECT field1,field2 FROM '||fq||'WHERE somevalue=445';
    
    blah blah
    The problem shows up when 'fq' is a CLOB.

    Hope this helps some.
    --james

  13. #13
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    With that I meant that I agree when anacedent says:
    ...
    If you can't hold a single SQL statement in that length (32,767 characters), then I am impressed with your SQL coding skills.
    ...
    1) I am not understanding this at all. You have to list every ROW in the table twice ?? !! ??
    2) Not following you there either (obviously -- dont know the data, the requirements, etc).

    Anyway, I see something like this
    Code:
    DECODE(max(decode(permissi on,''change'',10,''request'',9,''view'',8,''none'' ,7,0)),10,''change'',9,''request'',8,''view'',7,'' none'') as permission
    and all I can say is: perhaps by normalizing the data you can cut the query length ? that is, if you put those permissions and description in a table (you know, a simple table, called perhaps permission_descripcion with columns ID and DESCRIPTION) and you join this table to your parent table, what happens then ? have you thought about that ?

  14. #14
    Join Date
    Mar 2006
    Posts
    47

    hmm

    Sorry its taken me so long to get back, its been busy couple days.

    Ok, here we go:

    1) I am not understanding this at all. You have to list every ROW in the table twice ?? !! ??
    This maybe a failure in my understanding of sql. Let me describe what I'm doing and we'll see if there is a shorter way to go about it.


    Here's a short table to help me describe whats going on:

    Code:
    CREATE TABLE "example" ( 
    	"field1"	VARCHAR2(25) NULL,
    	"field2"	VARCHAR2(25) NULL,
    	"field3"	VARCHAR2(25) NULL 
    	)
    (Ok, my real tables are much larger than this, but this will do for now)

    For these permissions, we have a table (example), and each one of the records in there will have a permission associated with it. The permission criteria is a query that we generate from several tables. Basically the generated query could look something like this (its possible to have overlapping permissions, and I'm making sure to have that in this example)

    Role 1: Grant "Change" permission to record where Field1 LIKE '%slapd%'
    Role 2: Grant "Request" permission to record where field2 is 'ARCH'

    We go through a bunch of rigamorole to assign roles to our users (I'll spare you that), but what we end up is a source query that might look like this.

    Code:
    SELECT field1,field2,field3,'change' as permission from example where field1 LIKE '%slapd%'
    UNION
    SELECT field1,field2,field3,'request' as permission from example where field2='ARCH'
    I don't know of any other way to add that permission field (easily, without creating a view), without listing the fieldnames individually. I hoped I could do:
    Code:
    SELECT example.*,'request' as permission from example where example.field2='ARCH'
    But I don't believe that will work in oracle.

    So anyway, now lets say I've got the overlapping roles I mentioned earlier. We have a record that is the equivalent of ('joe_slapd_value','ARCH','GERG#$@#$').

    Generally what happens is we craft the subquery, and when a script needs to access the info, it calls a procedure that returns the generated query as a string. Then the script merely forms a subselect using it.

    For example:
    Code:
    SELECT *
    FROM
    (
    SELECT field1,field2,field3,'change' as permission from example where field1 LIKE '%slapd%'
    UNION
    SELECT field1,field2,field3,'request' as permission from example where field2='ARCH'
    )
    Since one user has both of those roles applied, and we union the queries, we'd see two records returned, one with Change and one with Request permissions.

    What I want to do now is just return the max value of the two (or <i>n</i> depending). (This is where we get into the group by clause, which is what I meany by saying I had to list them twice). So that we only get one record back.


    This is where that long ass DECODE string comes in (and due to the MAX, the group by):
    Code:
    SELECT
    field1,field2,field3,
    DECODE(max(decode(permission,''change'',10,''request'',9,''view'',8,''none'',7,0)),10,''change'',9,''request'',8,''view'',7,''none'') as permission
    FROM
    (
    SELECT field1,field2,field3,'change' as permission from example where field1 LIKE '%slapd%'
    UNION
    SELECT field1,field2,field3,'request' as permission from example where field2='ARCH'
    )
    GROUP BY field1,field2,field3
    We store the permissions as a string, so its easier to just refer to it by name, and since they're not in alphabetical order, MAX'ing the strings won't work.

    You mentioned normalizing these. I'm not sure how that would work here in this case. The permissions returned are wholly dependant on the user, and the roles they have been assigned. (Which is stored in another (well 6) other tables.)

    Does this make it more clear?

    I tried to come up with a relatively simple (and fast) solution that would not require us to rewrite large amount of code to take the changes into account. (In fact, I grep'ed on table name, and replaced it with a function that just returns a subselect)


    Any thoughts?

    --james

  15. #15
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If you call creating a view not easily, then I dont know what to say. But yes, you can use something like
    Code:
    SELECT example.*,'request' as permission from example where example.field2='ARCH'
    in Oracle. The reason I was actually telling you to normalize this is that in the event that you want to add (or delete/change) permissions, it would imply you to change every place where you have this piece of code. At least, try to attach to some modular solution as LKBrwn_DBA suggested.

    My solution to you would imply you change almost every bit of the code where you use this, and since you dont want that, use what you have know and expect the best.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •