The APEX$_WS_ROWS table is owned by your workspace schema and not the APEX_040000 schema. We have a similar table in APEX so we can compare your schema table with the APEX table to ensure the table has the proper column names (and if not we can adjust).
If from within APEX you click on the "SQL Workshop" tab then the "Object Browser" icon, you can browse the various APEX$ tables APEX creates in your workspace schema to support websheets. You can manage these tables from "Home > Administration", then click "Websheet Database Objects" in the tasks region on the right. Websheet data is a combination of data stored within the APEX data dictionary (owned by APEX e.g. APEX_040000) and tables owned by schemas. The tables owned by the schemas contain the "data" and the APEX dictionary tables contain the application definition.
The following APEX data dictionary views are available:
The following SQL query will show data grid ID's
select application_name, data_grid_name, data_grid_id from apex_ws_data_grid
Edited by: mhichwa on Jul 22, 2010 12:42 PM
Hate to jump on the bandwagon, but websheets hit with a resounding thud for me. Maybe it was all the hype about them before they were released that contributed to this but as I see them I am not terribly motivated to use them. I can tell a lot of time and thought was put into them, but I have to agree I believe that direction was misguided and could have been redirected onto the core product in a much more effective manner.
Companies already running Apex or a heavy Oracle shop that does not already have a solution in place may be interested in websheets. But since Apex is such a niche market anyway, it seems that adoption of this feature would be even lower. Why? For example we use Sharepoint which has all the features plus locking of files, direct integration with MS Office..etc. - many other larger companies already have solutions in place. The really small shops that would actually benefit from this feature likely would not have the opportunity to use it due to the prohibitive cost of an Oracle database. Yes I know there is XE, but that is pretty old, not being patched and just not as attractive as say..mysql or sql server.
As far as being able to cut and paste in a dataset from Excel, the only way I could figure out how to do that was to create a new one. Once you had created one, I did not see a cut and paste feature. After 3 rows of 10 columns I gave up. It was pretty tedious to say the least. Maybe I missed something?
Although the intent was good, I personally think this is a niche feature in what is already a niche product. Maybe future releases will blow me away, but put me in the "no thanks" column on this feature.
I agree with Scott. While Mike's passion for the websheet vision is compelling, one can't help but get the feeling that it is too little too late. Looking at MS Sharepoint feature set, how ubiquitous Microsoft products are, it is hard to imagine websheets gaining any traction at all. With dynamic actions and plugins in 4.0, Apex has finally caught up with the established IDEs like ASP/.NET, PHP, etc so it one possible path was to release websheets as a open source Packaged Application and let the community drive enhancements as opposed to the core Apex team spending valuable time/effort on it. Again, I don't mean to disparage the effort that the Apex team has put into it, for Version 1, it is fantastic. Thanks.
(and all the other kind people here as well),
this is working in APEX only (SWL Workshop).
From outside I found no way, well surely because of the inner logic used to transform the BLOB into the “virtual” Table APEX$_WS_ROWS.
However that’s a restriction I would be willing to bear- at least until version 1.5…
Or is there any way accomplish it anyway?
What I find very pretty is the simple way to manipulate date directly in the grid.
Well, that’s definitely an advantage.
What I’m missing - and that is really a big issue - is the opportunity to "recycle" a given grid.
What I mean is something like an TRUNCATE against the grid and the ability to refill it with new (structurally identical) data just the same way like it was filled during the creation process. The same is true für just APPEND (Insert) new (structurally identical) data to such a given grid.
You can't expect, that someone will agree with the idea to enter a lot of “records” by hand and one by one.
The way it works now is less useful und indeed impractical.
I think this should be a prio zero issue for the devs.
Sorry, this is in first line a response on Sharon.
I just (to late) saw the afterward posts. Will read it an see what to say in addition.
Edited by: andreml on Jul 22, 2010 12:12 PM
Hmm..., no nothing to say in addition.
I only can repeat and affirm what I wrote on
Jul 21, 2010 1:07 PM
"...make web sheets an easy to use helper(!)-tool for data manipulation by end users..."
And you will make it best you can - I'm sure.
Edited by: andreml on Jul 22, 2010 12:35 PM
- Appending rows using data that conforms to the same structure is a top priority for the next release; it would be really useful
- To delete rows in a data grid you navigate to the data grid, click "Manage", then "Rows", then "Delete Rows". You have the option to delete all rows, checked rows, or null rows.
- I don't understand your question "From outside I found no way, well surely because of the inner logic used to transform the BLOB into the “virtual” Table APEX$_WS_ROWS"
- You should be able to insert into the APEX$_WS_ROWS
insert into APEX$_WS_ROWS (OWNER, C001, C002, DATA_GRID_ID, WS_APP_ID) values ('MIKE','hello','world',856922317632864269,2336);
insert into APEX$_WS_ROWS (OWNER, C001, C002, DATA_GRID_ID, WS_APP_ID) select 'MIKE' o, ename, job, 856922317632864269 did, 2336 aid from emp;
update and deletes will work as well.
- Here is how you can locate your ID's used in the SQL statements above:
Navigate to datagrids from within websheets by clicking on the "Data / Data Grids" tab. Then click on the websheet in question. Next in you URL you will see three items set WS_APP_ID,P2_ID,P2_WEBSHEET_ID, the first is your WS_APP_ID and the last is your DATA_GRID_ID.
I'm in a fairly large (government, scientific) organization, with thousands of users scattered across all 50 states and several US territories. Many users create their own little spreadsheets for whatever project they are working on, and then send them around to all the other folks working on the same project.
This in itself causes me extreme headaches, since these spreadsheets usually contain some very good data that I could definately use to improve the data in my database. However, these spreadsheets never come to my attention, and only rarely ever get to see one, usually only when a user has a problem with one of them.
For websheets to be useful to me (and the various users), it would be great if the 'spreadsheet upload' capability was built around the same routines as in Data Workshop within SQL Workshop, so the user uploads their spreadsheet, and follows all the prompts to create a 'real' table within Oracle. Then, allow the same functionality as currently in Websheets, to change occurences of a value in a column to something else, etc., and then store those corrections back in the table. Then, after the users have decided the data is cleaned up to thier standards, I could quite easily insert that data into my 'real' tables that the application uses. If there was also a way (other than constraints and triggers) to flag records that still didn't meet the criteria for my app, without blocking the current round of changes from being made, that would be a huge bonus. I personally like some of the other aspects of Websheets, like the 'collaborative' features of notes, tagging, mini-wiki, etc. as that would allow those users to collaborate better than they do now, with just a bunch of scattered emails back and forth, dropping and adding different recipients as they see fit (perhaps something like Google Wave would be useful here as well?).
We also have several different 'groups' that have setup Sharepoint servers to try and share data, but those are rather awkward and cumbersome and don't have the same capabilities. They also wouldn't benefit me at all, as I don't have logins on most of those servers, so there is no way I can look over the data to see which pieces could be brought in to improve the data in my database.
I'm looking at Websheets as possibly something I can take advantage of to improve the data collection efforts my database requires. Right now, we lose (from people never supplying me with the data) far more information each year than we actually capture and save in this 'National Database'. Websheets are the best offering I've seen so far that the users MAY be enticed to use so that I can capture this data before it gets lost.
If others have the same kind of situation in their organizations, I'd love to hear how you handle this kind of problem and what, if any, solutions you've come up with. Our management doesn't have the backbone to tell everybody they have to use the database, so most simply continue with their old way of doing things.
"From outside I found no way, ....
Using TOAD or SQL Developer:
WhenI execute this query as SYS I see data.
When I execute it (for examlple) as Schema/User AAA (my application data schema) I dont see anything.
However at least I dont get any exception regarding privs.
So the question is how can one under which conditons see what data from this query; and how can one query the correspondig data (values)?
You create your websheet tables within the schema associated with your workspace (each workspace has their own set of tables). If you go into SQL Developer, you will see the APEX$_WS... tables just listed as normal tables. I believe you are in the wrong schema if you are not able to access them.
I logged into SQL Developer as the "first schema provisioned" for the workspace and ran the following SQL statements:
select count(*) from APEX$_WS_ROWS;
insert into APEX$_WS_ROWS (OWNER, C001, C002, DATA_GRID_ID, WS_APP_ID) values ('MIKE','hello','world',856922317632864269,2336);
select count(*) from APEX$_WS_ROWS;
select count(*) from APEX$_WS_ROWS; Results:
1 rows inserted
Edited by: mhichwa on Jul 22, 2010 5:07 PM
I am also of the opinion that time could have been better spent improving the core product.
The new features in apex 4.0 are very impressive, in particular the dynamic actions and plug-ins components.
Having spent some time on Websheets, i'm impressed by some of the new functionality such as data grids that are currently not available within the main application builder. I'm also glad to see that the 'create table from spreadsheet' functionality has been included but suprised you can't add spreadhseet data to existing tables.
My issue with websheets is its scalability.
From experience developing customer applications, the core product is powerful and flexible enough to meet the more advanced customer requirements. This is one of the major selling points of Apex, that the investment in an application won't be wasted.
At the moment Websheets is been positioned as a wiki/data capture/analyses tool. Thats all well and good for customers with simple requirements, but what happens when those requirements change and they want more advanced features. From what i understand theres no way of 'upgrading' the app to a standard apex application to access the more advanced features. It's also difficult to access the underlying data within a separate app.
I will be asked by our customers what are the benefits and risks of building a Web Sheet app, and unfortunatley at the moment i'm struggling to think of any serious justifications for websheets.
I'd like to know what the vision is for Websheets. Is it just a one off additional feature for apex or are we going to see better integration with the standard apex component and BI Publisher? What should we expect to see in future releases?
The main concern appears to be that the time going into WebSheets benefits a very small market and time could be better spent elsewhere.
I really appreciate your responses as I understand better why Oracle have included WebSheets. However, it appears that everyone has a different view why they are not beneficial at this stage. My concern is that endless time will be spent on WebSheets trying to get it to a stage that is of benefit to wide audience, at the expense of the main development tool.
I fear that WebSheets is a dangerous route to take. Without devoting considerable more development time adding functionality, there will not be a wide enough audience to make them worthwhile. But by doing this the core development environment will suffer and WebSheets could become advanced enough to limit the use of "normal APEX" which would be harmful to everyone using this forum.
I would prefer to see some of the functionality in WebSheets integrated into normal APEX functionality and for APEX to be made more robust with great features using current web standards and best practices.
user2293646 hit the nail on the head, and I couldn't have said it better. APEX is a great development tool, which should have a higher profile. Please don't divert resources to WebSheets; instead devote the time to enhancing the normal APEX development environment.
Many have expressed the concern that the APEX development team has invested too much in [websheets |http://www.oracle.com/technology/products/database/application_express/html/more_websheets.html] at the expense of the core application builder, and others have expressed that websheets has nice features but lacks critical mass to make it an effective tool for mass deployment. If you look at APEX 4.0 and the breadth and depth of new functionality it brings (here) you will see 10 marquee features and 32 additional features. From a development resource perspective websheets represented a small percentage of the available time and effort, we did consider boosting websheet efforts; but we wanted to deliver as best we could on the other 41 features. I know many (including myself) very much wanted update capability in interactive reports. This was reasonably easy in websheets because it was going against a table specifically developed to support AJAX style updates; for example the APEX$_WS_ROWS table has a trigger maintained CHANGE_COUNT column used to prevent lost updates. Building the ability to declaratively update an arbitrary query; with full proper controls for text, textarea, select list, radio group, date picker etc is non trivial. With websheets we are able to develop the functionality in steps; first in websheets, then moving the feature function to the core apex. In fact many features in websheets, async IR data updates, ACL, APEX$ tables, SQL tags, annotations, tag clouds, are paving the ground work for enhanced web 2.0 out-of-the-box functionality in core builder.
The websheet vision includes the capability of converting your "websheet" to a "proper apex application" when you "hit the wall". However this only elegantly accomplished when the core apex builder can deal more declaratively with functionality such as tagging, files etc. I guess the high level point is that websheets are pioneering cool and productive functionality in future APEX releases, and laying foundation for enlarging our 300,000 strong community. Could we have done more with websheets in its first release (yes), could we have held back the 4.0 release to add more functionality (yes); but the decision was made to do what we have always done, provide incremental advancement of the platform. Having written the first line of APEX code on August 4th, 1999 I can say with confidence that this 4.0 release of APEX is our best, our most feature rich, and our most innovative (I am obviously biased). I hope readers of this long thread will take away that we are passionate about websheets, we see value in their development, we see websheet innovations helping core apex, and that most importantly; that the APEX team is fully committed to spending the vast majority of our resources (as we have done in 4.0) on core application builder in support of our core user community.
Thanks for the reply
Its good to know that some of my concerns have already been taken on board by your team.
I appreciate that version 4.0 has only just been released.
When will these enhancements be listed in the new statement of direction for Apex?
It would be useful to see a high level overview of future releases (4.1) similar to apex 4.0 when apex 3.0 was released.
Our team is focused on getting the 4.0.1 release out; having said that reading and responding to this forum helps crystalize our top 4.1 goals. At this time it's just too early to publish an SOD, as we would be real likely to change our minds. A few features on my short list are:
1. Ease of integrating spreadsheets with core apex apps, and websheets (e.g. append with update capability). I have deployed an APEX 4.0 application for internal that does exactly this (e.g. permits updates online or via spreadsheet). I hope to turn it into a package app once it proves itself in the real world.
2. Extending Interactive reports to support updates (similar to websheet data grids)
3. Potentially exposing a websheet datagrid as a page type in core apex
4. Further improvements refinements in classic reports
5. Refined themes
6. Making it easier to add tagging to any APEX application
7. APEX listener enhancements, potential bundling with glassfish for even easier deployment (apex listener was developed by the SQL Dev team so its on a different schedule)
8. Potential application generation from SQL Developer data modeling tool
9. Better PDF reporting
10. Better email integration; ingest; easier attachment generation etc; so just like with this forum; we would make it easy to tie updates to data within an application with email "alerts". This type of integration also helps better integrate database apps with smart phones as they typically deal very well with html email attachments.
But it is really too early; our #1, #2, and #3 priority is fix issues identified in the 4.0 release to ensure 4.0 does "what it says on the box" and with a minimum number of bugs. Reference: http://www.oracle.com/technology/products/database/application_express/html/4.0_known_issues.html
I am sure the list will be extended, changed, etc.
Edited by: mhichwa on Jul 23, 2010 1:01 PM