3 Replies Latest reply: Jan 10, 2013 11:56 PM by Billy~Verreynne RSS

    Passing json string into plsql varchar2 variable

    Johnny98
      Hello,
      I'm trying to pass a JSON string into a stored procedure that has a single VARCHAR2 variable.

      begin
      SAVE_JSON('{"app_id":"800414","merch_id":"84657156","user_id":"6985","P25_DBA_NAME":"TEST"}');
      end;
      /

      I'm getting the ORA-20001: ORA-06502: PL/SQL: numeric or value error

      Can anyone tell me what i'm doing wrong?

      Thank you,
      John

      Edited by: Johnny98 on Jan 10, 2013 5:05 PM
        • 1. Re: Passing json string into plsql varchar2 variable
          sb92075
          Johnny98 wrote:
          Hello,
          I'm trying to pass a JSON string into a stored procedure that has a single VARCHAR2 variable.
          what is a JSON string?

          create or replace
          PROCEDURE "SAVE_JSON" (VIN_JSON VARCHAR2) IS


          begin
          SAVE_JSON('{"app_id":"800414","merch_id":"84657156","user_id":"6985","P25_DBA_NAME":"TEST"}');
          end;
          /
          why does procedure "SAVE_JSON" also invoke "SAVE_JSON"?

          >
          I'm getting the ORA-20001: ORA-06502: PL/SQL: numeric or value error

          Can anyone tell me what i'm doing wrong?

          Thank you,
          John
          what exactly is supposed to be done with the string?
          one cause of the ORA-06502 is when string is longer than the size of the variable.
          • 2. Re: Passing json string into plsql varchar2 variable
            rp0428
            >
            I'm trying to pass a JSON string into a stored procedure that has a single VARCHAR2 variable.

            create or replace
            PROCEDURE "SAVE_JSON" (VIN_JSON VARCHAR2) IS

            begin
            SAVE_JSON('{"app_id":"800414","merch_id":"84657156","user_id":"6985","P25_DBA_NAME":"TEST"}');
            end;
            /

            I'm getting the ORA-20001: ORA-06502: PL/SQL: numeric or value error

            Can anyone tell me what i'm doing wrong?
            >
            Post the actual DDL for the code that is giving the error. What you posted doesn't show VIN_JSON being used at all.
            • 3. Re: Passing json string into plsql varchar2 variable
              Billy~Verreynne
              Johnny98 wrote:

              I'm trying to pass a JSON string into a stored procedure that has a single VARCHAR2 variable.
              Usually JSON is output from a stored proc (as part of generating dynamic web content or responding to Ajax calls). Strange to see it being passed as input.. and parsed?

              The error you get is from the Save_JSON() procedure itself. The error stack (that you did not post), will include the call stack, unit code names, and source code line numbers. This will pinpoint just where the exception was raised.

              I asked, "+parsed?+" - as that would explain the error. A bug in parsing text into structured data typically results in a "+ORA-06502: PL/SQL: numeric or value error+".

              Also, the actual exception text you posted says:
              ORA-20001: ORA-06502: PL/SQL: numeric or value error

              And this points to yet another fubar exception handler implementation.

              The exception handler very likely looks as follows:
              exception when OTHERS then
                raise_application_error( -20001, SQLERRM(SQLCODE) );
              end;
              Pardon my blunt response, but this is idiotic. Exception handlers should NOT change a meaningful exception (like -6502) into a generic catchall error (like -20001). NOT IN ANY LANGUAGE. NOT IN PL/SQL. This is not how robust and well designed software is engineered.