Message-Id: <10719.125301@fatcity.com> From: "Cyril Thankappan" Date: 23 Dec 2000 13:26:38 -0000 Subject: consistent gets /current block Hi gurus.. can some one please tell me what is 'exactly' a current block.. and when 'exactly' the consistent gets is incremented and wnen the db block gets is incremented? I am sorry if this is a 'trivial' question.. Thanks a lot.. _____________________________________________________ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.com ------------------------------ From: larry elkins Date: Sat, 23 Dec 2000 14:27:36 -0600 Subject: RE: Dynamic PL/SQL Viktor, I am assuming that you are running into an ORA-01756 error, "quoted string not properly terminated". Is this correct? It looks like you are trying to get around this by using the REPLACE function in your dynamically built SELECT statement to replace a single quote in the AUTHOR_LASTNAME with a space. But, if you inspect your SELECT statement that is built, you will see that the REPLACE portion of the SELECT statement ends up looking like: REPLACE(MS.AUTHOR_LASTNAME,'',' ') So, when the SELECT is executed, it is *not* replacing the single quote with a space and you end up with the error on the INSERT. I think a lot of people myself included, who have used DBMS_SQL to dynamically insert data have been bitten by the ORA-01756 error. I think the *easiest* fix with regards to understanding what the code is doing is to use the replace function against the local variable holding the value. So, you would rip out the REPLACE part in your dynamically built SELECT and simply say MS.AUTHOR_LASTNAME. Then, after your DBMS_SQL.COLUMN_VALUE, you could use the replace against the variable: DBMS_SQL.COLUMN_VALUE(li_cursor_id, 9, v_author_lastname); v_author_lastname := replace(v_author_lastname,'''',' '); For me, that is the easiest to understand. I take it a step further and use a function: Function FIX_QUOTE (p_string VARCHAR2) RETURN VARCHAR2 IS Begin RETURN(REPLACE(p_string,'''',' ')); End; You could define this within a PL/SQL block, as an internal function within a package, or, as an exposed generic routine. Whatever floats your boat. Then, do something like: v_author_lastname := FIX_QUOTE(v_author_lastname); That's the way I like to do it. Actually, I do it a little bit differently in that I like to preserve the quote. As can be seen from your code, you already know that 2 quotes gives 1 quote. So, my function actually says: RETURN(REPLACE(p_string,'''','''''')); Now, when the insert statement is created, the value would look something like 'O''Dell'. And there are so many variations -- some people find it easier to understand if they use chr(39) instead of all the tickies, maybe even defining a variable to hold it and using the variable. If you want to continue using the REPLACE directly within the dynamically built SELECT statement, you could do it something like REPLACE(MS.AUTHOR_LASTNAME,'''''''','' '') or