2 Replies Latest reply: Feb 23, 2014 1:24 PM by Nicolette RSS

how i get data from table using json in oracle pl/sql

user1_apex Newbie
Currently Being Moderated

My need is to get data from oracle table using JSON and show output at html table. I'm very new to JSON (got some experience in PL/SQL and basic html knowledge). Could someone direct me to manual or give me simple example?

  • 1. Re: how i get data from table using json in oracle pl/sql
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated

    This example shows how to get a json string in a background on demand process and how to insert the data into a select list:

     

    https://apex.oracle.com/pls/apex/f?p=31517:316

     

    You requirement is a bit strange. An interactive report is what you are looking at. Are there any special requirements there for using a HTML table?

     

    Denes Kubicek

     

    -------------------------------------------------------------------

     

     

     

    -------------------------------------------------------------------

  • 2. Re: how i get data from table using json in oracle pl/sql
    Nicolette Expert
    Currently Being Moderated

    User1_apex

     

    JSON isn't a programming language but rather an standard to transmit data objects consisting of attribute value pairs. For a better explenation see wikipedia or Introducing JSON from Douglas Crockford.

     

    In Denes Kubicek's example the pl/sql function apex_util.json_from_sql returns the following string:

    {"row":[{"EMPNO":7876,"ENAME":"ADAMS"},{"EMPNO":7499,"ENAME":"ALLEN"},{"EMPNO":7698,"ENAME":"BLAKE"},{"EMPNO":7782,"ENAME":"CLARK"},{"EMPNO":7902,"ENAME":"FORD"},{"EMPNO":7900,"ENAME":"JAMES"},{"EMPNO":7566,"ENAME":"JONES"},{"EMPNO":7839,"ENAME":"KING"},{"EMPNO":7654,"ENAME":"MARTIN"},{"EMPNO":7934,"ENAME":"MILLER"},{"EMPNO":7788,"ENAME":"SCOTT"},{"EMPNO":7369,"ENAME":"SMITH"},{"EMPNO":7844,"ENAME":"TURNER"},{"EMPNO":7521,"ENAME":"WARD"}]}

    The JavaScript that receives the string parses this string and turns it in an object. Which in turn is conferted into a list of select list options. With empno as that value and ename as the taxt.

     

    Now if you can use this function depends on how your JSON object should look like.

     

    Your question sounds a bit like your asking about a solution you found without explaining what you are trying to accomplice.

    Could you explain what you're trying to accomplice?

    Are you using a (jQuery) plugin that is expecting a json object as input?

    How does the JSON object you are expecting look like?

    Could you set up a demonstration of what you have so far on apex.oracle.com.

    That is a great resource to visualize the problem and actual work with data.

     

    Nicolette

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points