CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
Julio
Julio

Reputation: 551

Plsql - How return a json from a function and use it, into another variable or print as a json structure

i want to create a package with a function that return a json structure, this is the code from the package:

Package Specification

create or replace PACKAGE my_utils IS

    FUNCTION get_json (id_row IN NUMBER, table_name IN VARCHAR2) RETURN CLOB;

END my_utils;

Package Body

create or replace PACKAGE BODY my_utils IS

    FUNCTION get_json (id_row IN NUMBER, table_name IN VARCHAR2) RETURN CLOB
IS 
    --json_clob CLOB;

    v_stmt VARCHAR2(1000);
    l_sep varchar2(1) := chr(10);
    l_tab varchar2(1) := chr(9);
    up_column VARCHAR2(50);
    low_column VARCHAR2(50);

    cur_asc SYS_REFCURSOR;
    Vcdogcur SYS_REFCURSOR;

    id_ccd number;
    first boolean := true;
begin
    id_ccd := id_row;
    v_stmt := 'select ';

    open cur_asc for 
        SELECT column_name as up_column
              ,lower(column_name) as low_column
        FROM USER_TAB_COLUMNS
        WHERE table_name = upper(table_name);
    loop 
        FETCH cur_asc
        INTO  up_column, low_column;
        EXIT WHEN cur_asc%NOTFOUND;
        if not first then
            v_stmt := v_stmt || ',';
        end if;
        first := false;
        v_stmt := v_stmt || l_sep || l_tab ||up_column || ' as "' || low_column || '"';
    END LOOP;
    v_stmt := v_stmt || l_sep || 'from ' || table_name || l_sep ||'where id = ' || id_ccd;
    DBMS_OUTPUT.PUT_LINE(v_stmt);
    CLOSE cur_asc;

    open Vcdogcur for v_stmt;
        APEX_JSON.initialize_clob_output;
        APEX_JSON.open_object;
        APEX_JSON.write(table_name, Vcdogcur);
        APEX_JSON.close_object;
        DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);

        --json_clob := APEX_JSON.get_clob_output;

        --APEX_JSON.free_output;
    --CLOSE Vcdogcur;

    --return json_clob;
    return APEX_JSON.get_clob_output;
end;

END my_utils;

I test this code and it print at the end the json structure (is commented (**) )

Now i want to use this function from another plsql, and i want to see the result, using this code:

Case - 1

DECLARE
    SALIDA CLOB;
BEGIN

    SALIDA := mibdes.homeserver_utils.get_json(174, 'my_table_name');
    DBMS_OUTPUT.put_line(SALIDA);

END;
/

But i can't see the result that i want. I see this error:

Error --> Case 1:

Informe de error -
ORA-06502: PL/SQL: error : buffer de cadenas de caracteres demasiado pequeño numérico o de valor
ORA-06512: en "my_schema.my_utils", línea 48
ORA-06512: en línea 10
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

Case - 2

Step - 1:

create table temp_json (
  json_data blob not null
);
alter table temp_json
  add constraint temp_data_json 
  check ( json_data is json );

Step - 2:

DECLARE
    SALIDA CLOB;
BEGIN

    insert into temp_json (JSON_DATA) values (mibdes.homeserver_utils.get_json(174, 'my_table_name'));

END;
/

Error --> Case 2:

Informe de error -
ORA-06502: PL/SQL: error : buffer de cadenas de caracteres demasiado pequeño numérico o de valor
ORA-06512: en "my_schema.my_utils", línea 48
ORA-06512: en línea 14
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

Function as stand alone

CREATE OR REPLACE FUNCTION otro_get_json (id_row     IN NUMBER, 
                                     table_name IN VARCHAR2) RETURN CLOB 
IS 
  --json_clob CLOB; 
  v_stmt     VARCHAR2(1000); 
  l_sep      VARCHAR2(1) := Chr(10); 
  l_tab      VARCHAR2(1) := Chr(9); 
  up_column  VARCHAR2(50); 
  low_column VARCHAR2(50); 
  cur_asc    SYS_REFCURSOR; 
  vcdogcur   SYS_REFCURSOR; 
  --id_ccd     NUMBER; 
  first      BOOLEAN := TRUE; 
BEGIN 
    --id_ccd := id_row; 

    v_stmt := 'select '; 

    OPEN cur_asc FOR 
      SELECT column_name        AS up_column, 
             Lower(column_name) AS low_column 
      FROM   user_tab_columns 
      WHERE  table_name = Upper(table_name); 

    LOOP 
        FETCH cur_asc INTO up_column, low_column; 
        EXIT WHEN cur_asc%NOTFOUND; 
        IF NOT first THEN 
          v_stmt := v_stmt 
                    || ','; 
        END IF; 

        first := FALSE; 
        v_stmt := v_stmt 
                  || l_sep 
                  || l_tab 
                  ||up_column 
                  || ' as "' 
                  || low_column 
                  || '"'; 
    END LOOP; 

    v_stmt := v_stmt 
              || l_sep 
              || 'from ' 
              || table_name 
              || l_sep 
              ||'where id = ' 
              || id_row; 

    --dbms_output.Put_line(v_stmt); 
    CLOSE cur_asc; 
    OPEN vcdogcur FOR v_stmt; 
    apex_json.initialize_clob_output; 
    apex_json.open_object; 
    apex_json.WRITE(table_name, vcdogcur); 
    apex_json.close_object; 
    --dbms_output.Put_line(apex_json.get_clob_output); 

    --json_clob := APEX_JSON.get_clob_output; 
    --APEX_JSON.free_output; 
    --CLOSE Vcdogcur; 
    --return json_clob; 
    RETURN apex_json.get_clob_output; 
END; 
/

Function compile correctly, no errors But when i want to use it:

DECLARE
    SALIDA CLOB;
BEGIN
    SALIDA := otro_get_json(174, 'my_table_name');
    DBMS_OUTPUT.put_line(SALIDA);

END;
/

Error --> Function Stand Alone:

Informe de error -
ORA-06502: PL/SQL: error : buffer de cadenas de caracteres demasiado pequeño numérico o de valor
ORA-06512: en "my_schema.OTRO_GET_JSON", línea 34
ORA-06512: en línea 10
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

What is wrong , and how can i resolve this?

My function returns something like this :

{
  "my_table_name": [
    {
      "created": "2020-06-04T08:10:09.000000000Z",
      "created_by": "APEX_APP",
      "updated": "2020-06-04T10:10:09.295632000Z",
      "updated_by": "APEX_APP",
      "id": 174,
      "text": "ALL",
      "part": 2770,
      "code": 2212,
      "date": "2017-01-01T00:00:00.000000000Z",
      "percentage": -4.98
    }
  ]
}

But i need to see or print on SALIDA variable.

Thanks so much

Upvotes: 1

Views: 3349

Answers (1)

user12766509
user12766509

Reputation:

To replicate your error I reduced varchar2 size to 100 for v_stmt 

     create or replace function get_emp(p_no in number,p_table_name in varchar2) return clob is 
        --declare
            v_stmt VARCHAR2(100);
            l_sep varchar2(1) := chr(10);
            l_tab varchar2(1) := chr(9);
            up_column VARCHAR2(50);
            low_column VARCHAR2(50);
            cur_asc SYS_REFCURSOR;
            Vcdogcur SYS_REFCURSOR;
            id_ccd number:=p_no;
            --id_ccd number:= 7369;
            first boolean := true;
            -- json_clob clob;
        begin

            v_stmt := 'select *';

            open cur_asc for 
                SELECT column_name as up_column
                      ,lower(column_name) as low_column
                FROM USER_TAB_COLUMNS
                WHERE table_name = upper('p_table_name');
            loop 
                FETCH cur_asc
                INTO  up_column, low_column;
                EXIT WHEN cur_asc%NOTFOUND;
                if not first then
                    v_stmt := v_stmt || ',';
                end if;
                first := false;
                v_stmt := v_stmt || l_sep || l_tab ||up_column || ' as ' || low_column;
            END LOOP;
            --v_stmt := v_stmt || l_sep || 'from my_table' || l_sep ||'where id = ' || id_ccd || ';';
            v_stmt := v_stmt || l_sep || 'from '||p_table_name|| l_sep ||'where empno = ' || id_ccd;


            --v_stmt := v_stmt || l_sep || 'from my_table' || l_sep ||'where id = ' || id_ccd;
CLOSE cur_asc;             
DBMS_OUTPUT.PUT_LINE('salida: ' || v_stmt || 'length of v_stmt: ' || to_char(length(v_stmt)));

            open Vcdogcur for v_stmt;
                APEX_JSON.initialize_clob_output;
                APEX_JSON.open_object;
                APEX_JSON.write(p_table_name, Vcdogcur);
                APEX_JSON.close_object;
              --  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);

              return APEX_JSON.get_clob_output;
               APEX_JSON.free_output;  

        end;


SQL> select get_emp(7369,'emp') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.GET_EMP", line 34



no rows selected

Now after increasing varchar2 size

SQL> select get_emp(7369,'emp') from dual;

GET_EMP(7369,'EMP')
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
"emp":[
{
"EMPNO":7369
,"ENAME":"SMITH"
,"JOB":"CLERK"
,"MGR":7902
,"HIREDATE":"1980-12-17T00:00:00Z"
,"SAL":800
,"DEPTNO":20
}
]
}

P.S:-Add length(v_stmt) after CLOSE cur_asc;to find out of length of v_stmt if you are curious .

Upvotes: 2

Related Questions