Solved

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

Posted on 2010-08-29
17
2,983 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:henderxe
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 15

Expert Comment

by:ishando
ID: 33555243
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
 
LVL 7

Expert Comment

by:sumit2906
ID: 33555679
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
 
LVL 7

Expert Comment

by:sumit2906
ID: 33555680
also paster the complete error message, which may include line numbers etc.
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 200 total points
ID: 33565427
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
 
LVL 13

Expert Comment

by:riazpk
ID: 33565430
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
 
LVL 13

Expert Comment

by:riazpk
ID: 33565462
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
 

Author Comment

by:henderxe
ID: 33566619
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
 
LVL 13

Expert Comment

by:riazpk
ID: 33573849
Can you try to execute the procedure in SQL*PLUS (just copy/paste here the whole test as i did)?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:henderxe
ID: 33599932
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
 
LVL 13

Expert Comment

by:riazpk
ID: 33605704
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
 

Author Comment

by:henderxe
ID: 33614065
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
 

Author Comment

by:henderxe
ID: 33614517
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
 
LVL 7

Expert Comment

by:sumit2906
ID: 33614690
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
 

Author Comment

by:henderxe
ID: 33614745
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
 

Author Comment

by:henderxe
ID: 33676276
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
 
LVL 15

Accepted Solution

by:
ishando earned 300 total points
ID: 33678531
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
 

Author Closing Comment

by:henderxe
ID: 33685494
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now