ORA-01460: unimplemented or unreasonable conversion requested - PL/SQL

Hi:

   I' getting the error (ORA-01460: unimplemented or unreasonable conversion requested), when the number of requested characters exceeds 4000 (Oracle 10g CLOB limit).

   I understand there is a workaround, but am not clear on how to implement it.  I searched through the knowledge base, but was unable to determine which may be the solution.

   My stored procedure is as follows:

CREATE OR REPLACE
PROCEDURE "ADDIDLISTS" (id_no IN VARCHAR2) IS
  id_actual_val VARCHAR2(8000);
  id_insert_val VARCHAR2(8000);
BEGIN
  id_actual_val := id_no;
  DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
 
  IF instr(id_actual_val, ',') = 0 THEN
    EXECUTE IMMEDIATE ' insert into tempIdlists(vals) values(' ||
                      id_actual_val || ')';
    DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
  ELSE
    WHILE (instr(id_actual_val, ',') > 0) LOOP
      id_insert_val := substr(id_actual_val,
                              1,
                              instr(id_actual_val, ',') - 1);
      id_actual_val := substr(id_actual_val, instr(id_actual_val, ',') + 1);
      EXECUTE IMMEDIATE ' insert into tempIdlists(vals) values(' ||
                        id_insert_val || ')';
      IF instr(id_actual_val, ',') = 0 THEN
        id_actual_val := id_actual_val || ',';
      END IF;
      DBMS_OUTPUT.PUT_LINE('ins val---' || id_insert_val);
      DBMS_OUTPUT.PUT_LINE('val---' || id_actual_val);
    END LOOP;
  END IF;
  COMMITT;
END ADDIDLISTS;

   Thanks for any assistance you can provide.

   Regards:

henderxx
henderxeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ishandoCommented:
you could do it by changing your parameter (id_no) to a clob

this could be written as:

CREATE OR REPLACE procedure AddIdLists (id_no in clob) is
  id_insert_val varchar2(8000);
  id_pos int := 1;
  id_pos1 int := 1;
begin
  if dbms_lob.instr(id_no, ',', id_pos) = 0 then
    execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
  else
    while id_pos < dbms_lob.getlength(id_no) loop
      id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
      if id_pos1 = 0 then -- last value in list
        id_pos1 := dbms_lob.getlength(id_no) + 1;
      end if;
      id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
      id_pos := id_pos1 + 1;
      execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
    end loop;
  end if;
  commit;
end AddIdLists;
/

Open in new window

0
sumit2906Commented:
This error could be misleading, because you will hit the variable size limit if you exceed 4000 values in your in parameter.
try increasing the size of following two variables:
 id_actual_val VARCHAR2(20000);
 id_insert_val VARCHAR2(20000);
0
sumit2906Commented:
also paster the complete error message, which may include line numbers etc.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

riazpkCommented:
when i try try the code provided by you, i get:
SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE
  2  PROCEDURE "ADDIDLISTS" (id_no IN VARCHAR2) IS
  3    id_actual_val VARCHAR2(8000);
  4    id_insert_val VARCHAR2(8000);
  5  BEGIN
  6    id_actual_val := id_no;
  7    DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
  8    IF instr(id_actual_val, ',') = 0 THEN
  9      EXECUTE IMMEDIATE ' insert into tempIdlists(vals) values(' ||
 10                        id_actual_val || ')';
 11      DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
 12    ELSE
 13      WHILE (instr(id_actual_val, ',') > 0) LOOP
 14        id_insert_val := substr(id_actual_val,
 15                                1,
 16                                instr(id_actual_val, ',') - 1);
 17        id_actual_val := substr(id_actual_val, instr(id_actual_val, ',') + 1);
 18        EXECUTE IMMEDIATE ' insert into tempIdlists(vals) values(' ||
 19                          id_insert_val || ')';
 20        IF instr(id_actual_val, ',') = 0 THEN
 21          id_actual_val := id_actual_val || ',';
 22        END IF;
 23        DBMS_OUTPUT.PUT_LINE('ins val---' || id_insert_val);
 24        DBMS_OUTPUT.PUT_LINE('val---' || id_actual_val);
 25      END LOOP;
 26    END IF;
 27    COMMIT;
 28* END ADDIDLISTS;
SQL> /

Procedure created.

SQL> create table tempIdlists (vals varchar2(4000));

Table created.

SQL> exec "ADDIDLISTS"('asdas');
BEGIN "ADDIDLISTS"('asdas'); END;

*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "RIAZ.ADDIDLISTS", line 8
ORA-06512: at line 1

Open in new window

0
riazpkCommented:
So please pos the right code for which you are getting the error. Or maybe you are using different version than mine. Mine is 10.2.0.1.
0
riazpkCommented:
To correct above error, use bind variable:
SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE
  2  PROCEDURE "ADDIDLISTS" (id_no IN VARCHAR2) IS
  3    id_actual_val VARCHAR2(8000);
  4    id_insert_val VARCHAR2(8000);
  5  BEGIN
  6    id_actual_val := id_no;
  7    DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
  8    IF instr(id_actual_val, ',') = 0 THEN
  9      EXECUTE IMMEDIATE ' insert into tempIdlists(vals) values(:a)' USING id_actual_val;
 10      DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
 11    ELSE
 12      WHILE (instr(id_actual_val, ',') > 0) LOOP
 13        id_insert_val := substr(id_actual_val,
 14                                1,
 15                                instr(id_actual_val, ',') - 1);
 16        id_actual_val := substr(id_actual_val, instr(id_actual_val, ',') + 1);
 17        EXECUTE IMMEDIATE ' insert into tempIdlists(vals) values(:a)' USING id_actual_val;
 18        IF instr(id_actual_val, ',') = 0 THEN
 19          id_actual_val := id_actual_val || ',';
 20        END IF;
 21        DBMS_OUTPUT.PUT_LINE('ins val---' || id_insert_val);
 22        DBMS_OUTPUT.PUT_LINE('val---' || id_actual_val);
 23      END LOOP;
 24    END IF;
 25    COMMIT;
 26* END ADDIDLISTS;
SQL> /

Procedure created.

SQL> exec "ADDIDLISTS"('asd');

PL/SQL procedure successfully completed.

SQL> desc tempIdlists 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 VALS                                               VARCHAR2(4000)


SQL> ed
Wrote file afiedt.buf

  1* SELECT length(rpad('asd',4000,'*')) from dual
SQL> /

LENGTH(RPAD('ASD',4000,'*'))
----------------------------
                        4000

SQL> exec "ADDIDLISTS"(rpad('asd',4000,'*'));

PL/SQL procedure successfully completed.

Open in new window

0
henderxeAuthor Commented:
Hi All:

  Sorry for the delay -- team retreat . .  . and thank you for the rapid responses.
 
  A few things:
 
       1.  The complete error follows:
 
           Message        Error Executing Database Query.
           Detail       [Macromedia][Oracle JDBC Driver][Oracle]ORA-01460: unimplemented or unreasonable conversion requested
           Extended Info       
           Tag Context       R:\art\views\cfm\exportReports\generateCustomReports.cfm (340)
           R:\art\views\cfm\exportReports\generateCustomReports.cfm (328)
           C:\inetpub\wwwroot\MachII\framework\ViewContext.cfc (107)
           C:\inetpub\wwwroot\MachII\framework\EventContext.cfc (468)
           C:\inetpub\wwwroot\MachII\framework\commands\ViewPageCommand.cfc (85)
           C:\inetpub\wwwroot\MachII\framework\EventHandler.cfc (81)
           C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (379)
           C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (327)
           C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (271)
           C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (201)
           C:\inetpub\wwwroot\MachII\mach-ii.cfm (123)
           R:\art\index.cfm (9)
          
          
      2.  I'm running a Coldfusion web server, with the Mach-II framework.
      Other than the limitation on the number of characters, my code works fine.
      
      
      3.  The ID's are passed as a string (e.g. 0000039392,0000493847,0009483749), and then converted to another format:  0000039392
                                                                                              0000493847
                                                                                              0009483749
      
      4.  We're running Oracle 10g Enterprise Version, Release 10.2.0.4.0
      
      
  ishando:
 
     Your solution appears to work in terms of eliminating the above error on one feature in our application (create CSV file), but didn't work on others.  Also, when I upload 40,000 IDs (10 character string), I should have that many in the final result.  Instead,I'm getting results like 5,808, or if I upload 4,000, I get 9,500.  Other than the limitation on the number of characters, my stored procedure works fine.
     
     
   riazpk:
 
     The code I posted is correct -- double checked.  The version of Oracle I'm using is in 4. above.
     
     Also tried using your code modifications, and it compiles fine, but it resulted in the same error above.
     
  Hope there's a Plab B :]!
 
  Thanks again!
 
  henderxe
0
riazpkCommented:
Can you try to execute the procedure in SQL*PLUS (just copy/paste here the whole test as i did)?
0
henderxeAuthor Commented:
Once again, I apologize for the delay!

riazpk:

    I ran the procedure as you did in SQL Plus, and got the exact same results.  However, I rarely, if at all, use SQL Plus, (usually TOAD, PLSQL Developer or Oracle SQL Developer), therefore I was unable to determine how to post the results as you did.  Cut and paste does not work within SQL Plus.

    Not sure if that's good or bad that the results are the same.

   Any other  thoughts on how I can resolve this?

   Thanks!
0
riazpkCommented:
No worries. I just wanted to see if we can reproduce the error at database level (more easier to fix since i don't knowledge of cold fusion).
Ok; lets give it one more try: if you create procedure as in my post and call it from the your code (cold fusion), what result you get?
If not sucessfull, create the below procedure and then try calling it from your code and let us know about result.

CREATE OR REPLACE
    PROCEDURE ADDIDLISTS (id_no IN VARCHAR2) IS
      id_actual_val VARCHAR2(8000);
      id_insert_val VARCHAR2(8000);
    BEGIN
      id_actual_val := id_no;
      DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
      IF instr(id_actual_val, ',') = 0 THEN
        insert into tempIdlists(vals) values(id_actual_val);
       DBMS_OUTPUT.PUT_LINE('Actual val---' || id_actual_val);
     ELSE
       WHILE (instr(id_actual_val, ',') > 0) LOOP
         id_insert_val := substr(id_actual_val,
                                 1,
                                 instr(id_actual_val, ',') - 1);
         id_actual_val := substr(id_actual_val, instr(id_actual_val, ',') + 1);
         insert into tempIdlists(vals) values(id_actual_val);
         IF instr(id_actual_val, ',') = 0 THEN
           id_actual_val := id_actual_val || ',';
         END IF;
         DBMS_OUTPUT.PUT_LINE('ins val---' || id_insert_val);
         DBMS_OUTPUT.PUT_LINE('val---' || id_actual_val);
       END LOOP;
     END IF;
     COMMIT;
  END ADDIDLISTS;

Open in new window

0
henderxeAuthor Commented:
Greetings:

    Tested both statements, and each resulted in the same error, which I hadn't seen before.

    Message        Error Executing Database Query.
     Detail       [Macromedia][Oracle JDBC Driver][Oracle]ORA-01722: invalid number

    Extended Info       
    Tag Context       R:\art\views\cfm\exportReports\generateCustomReports.cfm (360)
                    R:\art\views\cfm\exportReports\generateCustomReports.cfm (328)
                   C:\inetpub\wwwroot\MachII\framework\ViewContext.cfc (107)
                  C:\inetpub\wwwroot\MachII\framework\EventContext.cfc (468)
                 C:\inetpub\wwwroot\MachII\framework\commands\ViewPageCommand.cfc (85)
                 C:\inetpub\wwwroot\MachII\framework\EventHandler.cfc (81)
                 C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (379)
                C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (327)
                 C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (271)
                 C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (201)
                 C:\inetpub\wwwroot\MachII\mach-ii.cfm (123)
                R:\art\index.cfm (9)

    Thought it also made sense to show you the code that's calling the stored procedure, which follows.

 339  <cfstoredproc procedure="addIdLists"   datasource="#dbname#" username="#dbUserName#"       password="#dbPassword#" >
340               <cfprocparam type="In" cfsqltype="cf_sql_varchar"  variable="id_no"  value="#listID#">
341        </cfstoredproc>
342        <cfquery name="execIDListQuery" datasource="#dbName#" username="#dbUserName#" password="#dbPassword#">
343            SELECT
344                #ToString(columnList)#
345            FROM
346                ART_MANUSCRIPT AM
347                #xtraADE#
348            WHERE
349                AM.ID_Number IN (
350                      <cfif trim(URL.86)>
351                        SELECT ID_NUMBER FROM ART_MANUSCRIPT WHERE ID_Number IN  (
352                              
353                              SELECT  to_number(vals) as ID_Number FROM tempIdlists
354                        )
355                        UNION
356                        SELECT SPOUSE_ID AS ID_NUMBER FROM ART_MANUSCRIPT WHERE ID_Number IN  (
357                              
358                              SELECT  to_number(vals) as ID_Number FROM tempIdlists
359                        )
360                    <cfelse>
361                          
362                        SELECT  to_number(vals) as ID_Number FROM tempIdlists
363                    </cfif>
364                )
365        </cfquery>

    Perhaps you'll see something that I'm not seeing.

   Thanks!

   
0
henderxeAuthor Commented:
A quick update:

   I discovered that the error message in y previous post (ORA-01722: invalid number) was for less than 4000 records.  

   Anything over 4000 again results in the initial error(ORA-01460: unimplemented or unreasonable conversion requested).

  Man, this is totally frustrating!
0
sumit2906Commented:
did you tried increasing the variable sizes declared in your proc? if you have 4000 delimited values, that need a variable size of 8000 or more.your declaration is 8k, so that may be less then what is required.
0
henderxeAuthor Commented:
Hi sumit2906:

      Sorry  .  .  . failed to mention it previously, but it was one of the first things I tried (was the easiest :]), but the error remained the same -- nothing different.

     Just tried it again after the many changes I've made to the stored procedure, and still the same errors.

     Thank you for your suggestion!
0
henderxeAuthor Commented:
Ishando:

Experienced much frustration since mylast post, including an error on my part which generated the last error (e.g. invalid number).

But I wound up re-v-s-ting your previous post, as follows, because nothing else was coming close to working.

CREATE OR REPLACE procedure AddIdLists (id_no in clob) is
  id_insert_val varchar2(8000);
  id_pos int := 1;
  id_pos1 int := 1;
begin
  EXECUTE IMMEDIATE ' truncate table tempIdlists';
  if dbms_lob.instr(id_no, ',', id_pos) = 0 then
    execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
  else
    while id_pos < dbms_lob.getlength(id_no) loop
      id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
      if id_pos1 = 0 then -- last value in list
        id_pos1 := dbms_lob.getlength(id_no) + 1;
      end if;
      id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
      id_pos := id_pos1 + 1;
      execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
    end loop;
  end if;
  commit;
end AddIdLists;

  As I mentioned previously, it didn't produce the right results, but didn't give any errors either, so thought trying to modify it would be a good starting point.

   Resolved the issue with the strange numbers (counts) by truncating the table.
 
   What remains (the problem) is that no matter how many records it processes (e.g. 50,000), it will only actually process 5,818, but without an error.

   Are you able to see in the code why it won't process all records. ?  I'm wondering if an absence of the statement e.g. (dbms_lob.substr(id_no, 4000, 1), or something like "id_pos := id_pos + 4000).

Thanks!
 
   
0
ishandoCommented:
I have tested the procedure using the below code, and it inserts as many records as are in the clob.

Is it possible that there is some size limit is being imposed when generating the list of values sent to the procedure, so that it is not receiving the full set of data?

Perhaps you can check that the size of the received parameter is correct by putting something like

insert into tempIdlists(vals) values(dbms_lob.getlength(id_no))
at the start of the procedure.

btw the "execute immediate"s are not necessary and can be replace with simple insert statements.

declare
  iBase int := 1000000000;
  pClob clob;
  vTemp varchar2(20);
begin
  dbms_lob.createtemporary(pClob, false, dbms_lob.call);

  for i in 1 .. 10000 loop
    vTemp := case when i > 1 then ',' else '' end || to_char(iBase + i);
    dbms_lob.WRITEAPPEND(pClob, length(vTemp), vTemp);
  end loop;
  
  AddIdLists(pClob);

  dbms_lob.FREETEMPORARY(pClob);
end;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
henderxeAuthor Commented:
First, let me say thank you so much for your efforts in assisting me in resolving my problem.

  It now works due to a combination of solutions by riazpk, and ishando (which ultimately solve the issue).

  So, in an effort to be fair to both, I've awarded 200, and 300 points, respectively.

  Hope you consider it fair as well.

  Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.