Forum Home » Database » Application Express

Thread: oracle apex and extjs grid table

This question is answered.

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 15 - Pages: 2 [ 1 2 | Next ] - Last Post: Nov 17, 2009 5:48 AM Last Post By: Munky
george samaan

Posts: 47
Registered: 07/01/08
oracle apex and extjs grid table
Posted: Jun 16, 2009 4:15 AM 		 
Click to report abuse...   Click to reply to this thread Reply
hi

im new to extjs and i was wondering if someone can help me with this;
i want to create a report in oracle apex and put the output in extjs grid table
for example i want to view the output of select * from all_objects in grid table

thnx in advance
Munky

Posts: 1,465
Registered: 05/29/08
Re: oracle apex and extjs grid table
Posted: Jun 16, 2009 4:29 AM   in response to: george samaan in response to: george samaan
Correct
Click to report abuse...   Click to reply to this thread Reply
Okay

To get you started...

Create a new report template with named rows.

In row 1 template put this
["#1#","#2#","#3#","#4#","#5#","#6#","#7#","#8#"]

Assuming you have 8 rows say... and make it conditional based on a PL/SQL Expression like this
#ROWNUM# = 1

Then row 2 template like this 
,["#1#","#2#","#3#","#4#","#5#","#6#","#7#","#8#"]

Conditional on 
#ROWNUM# > 1

Then in before row put this
<script type="text/javascript">
 
Ext.onReady(function(){
 
var munkyData = [

And after rows something like this (based on the emp table)
];
 
var store = new Ext.data.SimpleStore({
      fields: [
               {name: 'empno', mapping: '0'},
               {name: 'ename', mapping: '1'},
               {name: 'job', mapping: '2'},
               {name: 'mgr', mapping: '3'},
               {name: 'hiredate', mapping: '4'},
               {name: 'sal', mapping: '5'},
               {name: 'comm', mapping: '6'},
               {name: 'deptno', mapping: '7'}
              ]
    });
    
store.loadData(myData);
 
var grid = new Ext.grid.GridPanel({
       store: store,
       columns: [
                 {id:'empno',header: "Employee",sortable:true, width:100,dataIndex:'empno'},
                 {header: "Name", sortable:true,width:75, dataIndex:'ename'},
                 {header: "Job", sortable:true, dataIndex:'job'},
                 {header: "Manager", sortable:true,width:75, dataIndex:'mgr'},
                 {header: "Hire Date", sortable:true,dataIndex:'hiredate'},
                 {header: "Salary", sortable:true,width:50,dataIndex:'sal'},
                 {header: "Commission", sortable:true,dataIndex:'comm'},
                 {header: "Department", dataIndex:'deptno'}
                ],
       stripeRows: true,
       width:700,
       autoHeight:true,
       title:'Array Grid',
       renderTo: 'munkyDiv'
    });
});
 
</script>

Then create a region to hold it with a source of 
<div id="munkyDiv>
</div>


This is pretty basic but it should get you going...

Cheers

Ben
http://www.munkyben.wordpress.com
Don't forget to mark replies helpful or correct ;)
BBA

Posts: 123
Registered: 01/19/04
Re: oracle apex and extjs grid table
Posted: Jun 16, 2009 4:36 AM   in response to: george samaan in response to: george samaan 		 
Click to report abuse...   Click to reply to this thread Reply
Hi George,

Hope that following links will help you in integrating the ExtJs:

Mark Lancaster's blogs on extJS:

# http://oracleinsights.blogspot.com/2008/02/integrating-ext-javascript-library-into.html
# http://oracleinsights.blogspot.com/2008/11/extjs-grids-in-oracle-apex.html
# http://oracleinsights.blogspot.com/2008/12/extjs-grid-in-apex-with-pagination.html

Tobias blog on extJS:
# http://apex.oracle.com/pls/otn/f?p=65555:31:3406318074310638::::

Thanks,
Bhavin
george samaan

Posts: 47
Registered: 07/01/08
Re: oracle apex and extjs grid table
Posted: Jun 20, 2009 11:03 PM   in response to: Munky in response to: Munky 		 
Click to report abuse...   Click to reply to this thread Reply
thnx for ur reply
ive done everything u said,and also i put the
before the body <body>#BOX_BODY#
i used the sql query(pl/sql function body returning sql query)
DECLARE
v_sql VARCHAR2 (30);
BEGIN
v_sql := 'SELECT * FROM emp';
apex_util.json_from_sql (v_sql);
END;

and Use Generic Column Names (parse query at runtime only)
i get an output like this:

"row":[{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":"","HIREDATE":"17-NOV-81","SAL":5000,"COMM":"","DEPTNO":10},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01-MAY-81","SAL":2850,"COMM":"","DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09-JUN-81","SAL":2450,"COMM":"","DEPTNO":10},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02-APR-81","SAL":2975,"COMM":"","DEPTNO":20},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09-DEC-82","SAL":3000,"COMM":"","DEPTNO":20},{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03-DEC-81","SAL":3000,"COMM":"","DEPTNO":20},{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"17-DEC-80","SAL":800,"COMM":"","DEPTNO":20},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20-FEB-81","SAL":1600,"COMM":300,"DEPTNO":30},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22-FEB-81","SAL":1250,"COMM":500,"DEPTNO":30},{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28-SEP-81","SAL":1250,"COMM":1400,"DEPTNO":30},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08-SEP-81","SAL":1500,"COMM":0,"DEPTNO":30},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12-JAN-83","SAL":1100,"COMM":"","DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"03-DEC-81","SAL":950,"COMM":"","DEPTNO":30},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23-JAN-82","SAL":1300,"COMM":"","DEPTNO":10}]}{"row":[{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":"","HIREDATE":"17-NOV-81","SAL":5000,"COMM":"","DEPTNO":10},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01-MAY-81","SAL":2850,"COMM":"","DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09-JUN-81","SAL":2450,"COMM":"","DEPTNO":10},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02-APR-81","SAL":2975,"COMM":"","DEPTNO":20},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09-DEC-82","SAL":3000,"COMM":"","DEPTNO":20},{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03-DEC-81","SAL":3000,"COMM":"","DEPTNO":20},{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"17-DEC-80","SAL":800,"COMM":"","DEPTNO":20},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20-FEB-81","SAL":1600,"COMM":300,"DEPTNO":30},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22-FEB-81","SAL":1250,"COMM":500,"DEPTNO":30},{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28-SEP-81","SAL":1250,"COMM":1400,"DEPTNO":30},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08-SEP-81","SAL":1500,"COMM":0,"DEPTNO":30},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12-JAN-83","SAL":1100,"COMM":"","DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"03-DEC-81","SAL":950,"COMM":"","DEPTNO":30},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23-JAN-82","SAL":1300,"COMM":"","DEPTNO":10}]}

failed to parse SQL query:
The query can not be parsed, the cursor is not yet open or a function returning a SQL query returned without a value.

so i dont know where i went wrong so could u please help me out
thnx
Munky

Posts: 1,465
Registered: 05/29/08
Re: oracle apex and extjs grid table
Posted: Jun 21, 2009 4:17 AM   in response to: george samaan in response to: george samaan 		 
Click to report abuse...   Click to reply to this thread Reply
George

My example, is based on using the apex report template to create the JSON string so in the example the region source of the report would have been
SELECT *
FROM emp

and it would have been a straight SQL report, not PL/SQL funtion returning...

What you have done is passed the APEX reporting engine a JSON escaped string - not a SQL query - hence the error.

Cheers

Ben
http://www.munkyben.wordpress.com
Don't forget to mark replies helpful or correct ;)
george samaan

Posts: 47
Registered: 07/01/08
Re: oracle apex and extjs grid table
Posted: Jun 21, 2009 4:25 AM   in response to: Munky in response to: Munky 		 
Click to report abuse...   Click to reply to this thread Reply
ok i did a simple sql statement but this time nothing appears, the screen is empty
Munky

Posts: 1,465
Registered: 05/29/08
Re: oracle apex and extjs grid table
Posted: Jun 21, 2009 4:39 AM   in response to: george samaan in response to: george samaan 		 
Click to report abuse...   Click to reply to this thread Reply
Okay

You need to view that page source and see if the JSON string has been created correctly. You need to verify that it is inside the store declaration. You need to check the error console in Firefox if you're using it and ensure that you have included the relevant extjs libraries correctly and there are no missing resources. You need to make sure that you have no duplicate declarations of relevant div's etc. (if you have declared munkydiv twice then this will confuse extjs as to where to render to.).

Sorry if this seems like a lot but you need to have a reasonable understanding of HTML, the DOM and javascript to get this working in a custom solution. At the moment, you can't just plug extjs into APEX, it can take a little work.

And why have you put 
<body>#BOX_BODY#

before the body?

Cheers

Ben
http://www.munkyben.wordpress.com
Don't forget to mark replies helpful or correct ;)
george samaan

Posts: 47
Registered: 07/01/08
Re: oracle apex and extjs grid table
Posted: Jun 21, 2009 6:03 AM   in response to: Munky in response to: Munky 		 
Click to report abuse...   Click to reply to this thread Reply
thank u very much for ur help, finally i have managed to get it to work
there was something wrong in my page template
Tobias Arnhold

Posts: 220
Registered: 05/23/07
Re: oracle apex and extjs grid table
Posted: Jul 1, 2009 11:31 PM   in response to: george samaan in response to: george samaan 		 
Click to report abuse...   Click to reply to this thread Reply
Hi Ben,

you forgot a little detail which made me crazy finding out! ;) Need to use the following report template: "One Column Unordered List"

In all other reports you can't use #1#,#2# for your columns! If you don't know this little difference to a normal Standard report you get mad.

Anyway it's great example you pointed.

Tobias
Munky

Posts: 1,465
Registered: 05/29/08
Re: oracle apex and extjs grid table
Posted: Jul 2, 2009 2:39 AM   in response to: Tobias Arnhold in response to: Tobias Arnhold 		 
Click to report abuse...   Click to reply to this thread Reply
Hi Tobias

I was referring to creating a new template from scratch using the type: 'Named Column (row template)', sorry should have made that clearer!

Cheers

Ben
http://www.munkyben.wordpress.com
Don't forget to mark replies helpful or correct ;)
pjturley

Posts: 330
Registered: 09/07/05
Re: oracle apex and extjs grid table
Posted: Sep 9, 2009 1:19 AM   in response to: george samaan in response to: george samaan 		 
Click to report abuse...   Click to reply to this thread Reply
Hi George,

Any chance you can post your example on apex.oracle.com.

Regards

Paul
Omar M Sawalhah

Posts: 109
Registered: 05/30/99
Re: oracle apex and extjs grid table
Posted: Nov 17, 2009 4:28 AM   in response to: george samaan in response to: george samaan 		 
Click to report abuse...   Click to reply to this thread Reply
Hi George,
could you please provide us with the template, I followed the steps and did not work for me.

thanks

omarsawalhah@yahoo.com
Munky

Posts: 1,465
Registered: 05/29/08
Re: oracle apex and extjs grid table
Posted: Nov 17, 2009 4:43 AM   in response to: Omar M Sawalhah in response to: Omar M Sawalhah 		 
Click to report abuse...   Click to reply to this thread Reply
Omar

What problems are you having?

I've talking this quite a bit further now and dynamically get all the column info etc. from the APEX meta data. I also create the JSON in PL/SQL so it's quite different.

Having said that - the basic example should still work...

Cheers

Ben
Munky

Posts: 1,465
Registered: 05/29/08
Re: oracle apex and extjs grid table
Posted: Nov 17, 2009 4:46 AM   in response to: Omar M Sawalhah in response to: Omar M Sawalhah 		 
Click to report abuse...   Click to reply to this thread Reply
Here's a quick example I've just knocked up...

http://apex.oracle.com/pls/otn/f?p=49805:5
Omar M Sawalhah

Posts: 109
Registered: 05/30/99
Re: oracle apex and extjs grid table
Posted: Nov 17, 2009 5:36 AM   in response to: Munky in response to: Munky 		 
Click to report abuse...   Click to reply to this thread Reply
Thanks Munky,
Actually I followed the steps and checked the JSON string it is ok, but nothing appears and I have no errors in the page, and I've ExtJs configured properly.

to get this very quick and shorten the time, please give the access to this page and I will continue.

thanks in advance
Pages: 2 [ 1 2 | Next ]
Back to Thread List Back to Thread List
Legend
Guru Guru : 2500 - 1000000 pts
Expert Expert : 1000 - 2499 pts
Pro Pro : 500 - 999 pts
Journeyman Journeyman : 200 - 499 pts
Newbie Newbie : 0 - 199 pts
Oracle ACE Director
Oracle ACE Member
Oracle Employee ACE
Helpful Answer (5 pts)
Correct Answer (10 pts)

Point your RSS reader here for a feed of the latest messages in all forums