This content has been marked as final. Show 12 replies
Hi and welcome to the forums.
Your issue isn't really related to Oracle's SQL or PL/SQL funcationality, but rather to the use of the Microsoft Visual Studio product.
You would probably be better asking on the Visual Studio forums over at Microsoft:
If you are using Oracle Developer Tools for Visual Studio you can have a look here: 5 Using Oracle Developer Tools for Visual Studio.
At step 7 you should add the schema where your tables reside.
If you are not using Oracle Developer Tools for Visual Studio then in this case I have to stick to what has been already told you by BluShadow.
Any specific reason why you are using Visual Studio? That introduces another layer of complexities. And another language that you need to know, understand and use, in addition to the database SQL and PL/SQL.
And PL/SQL and SQL are mandatory to know and use if you expect to develop a half decent performing and scalable system.
My suggestion is to drop Visual Studio all together. That removes a very significant complexity. Instead, use Apex (Application Express).
Apex has 2 components:
- web application framework and run-time system (for running Apex apps)
- a RAD GUI that only needs a web browser (IE, Firefox or Chrome) to create Apex apps
Apex itself is written in PL/SQL and SQL. It resides in the database. If you have a default 11g database, you have Apex right now, already installed in the database.
An Apex (web) application resides in the Oracle database. Is executed inside the database. Right next to where the application's data is. This means quick and fast access to database data, a lot faster than Visual Studio app (or any other app) can ever hope for.
To use an Apex application requires a web server. You have 3 choices:
- Oracle's Apache 2.x web server (also called OHS or Oracle Http Server)
- Apex Listener (jar container that you can run stand-alone, or in a Java app server)
- use the Oracle database's XDB servlet called EPG
The easiest to get this to work with a couple of clicks - download and install Oracle XE (Express Edition). This is a free 11g database, that includes Apex, and comes with EPG configured for you. All you need to do after the install is click the Apex URL shortcut on your desktop to launch your default web browser and start using your XE database and Apex.
Any other method requires a far steeper learning curve, and has significantly more moving parts, and more complexity. Apex is easy enough for beginners to use (with little to no web application and Oracle experience). Apex is flexible and powerful enough to be used for corporate systems (Oracle's cloud.oracle.com servers run Apex applications for cloud access and management - and many Oracle customers use Apex extensively).
Not considering Apex would be a major mistake on your part IMO. Developing a proper .Net app using Visual Studio and Oracle, is not as easy and as simplistic as you may think. And locks you into proprietary framework that must run on Windows. Apex runs in the database - and the database runs on Windows, Linux, Solaris, AIX, HP-UX and so on.
All of your suggestions are massively appreciated so Thankyou!! It's certainly given me a lot more to work with and the suggestion of Apex in particular sounds very appealing, I never even considered that! There's no restrictions on the front end I just chose (probably stupidly haha) to choose Visual Basic.
Sorry if I posted in the wrong place too, ill be sure to have a look at all you've said over today and let you all know how I end up and I ever get a front end going! Your certainly right about VB not being as simple as you think, I have to admit I thought this would be the easier bit once the database was done!
Ok mini update now that I'm home.. I've decided to go with the APEX route, and because I already had the Express Edition of Oracle installed I was able to find it within my desktop shortcuts without any trouble.
Once in there is where I'm a bit lost, I've created a workspace and I have played around with some of the tools within the actual application developer. However at this stage I am unable to access my tables that were created in SQL Developer, is there something I need to do within SQL Developer in order to make my tables show up? I assumed that because I was connecting to the localhost connection I used with SQL Developer, that I would be able to access the tables from APEX too.
I notice there's an APEX listener in SQL Developer under the View tab, is this something I need to look at?
EDIT: Wait a minute, am I even meant to use SQL Developer here? I've just found a link in the SQL Workshop part of APEX that allowed me to upload the script file I used to create the tables.. once I uploaded and ran it, the tables all appear as wanted... is this what I was supposed to do in the first place, or should it have worked anyway given that I'd already ran the scripts in SQL Developer? Either way I appear to be OK with this at the minute but I'd appreciate an answer anyway because it would be nice to understand it! :)
Edited by: 986500 on Feb 6, 2013 12:11 PM
In Oracle, all objects are stored in schemas (which you can think of as the Oracle user that owns the object).
In APEX, a workspace has a default schema.
If the default schema of your APEX workspace is a different schema than the one you connected to in SQL Developer (and assuming that you didn't grant the APEX workspace's default schema privileges on the tables you created in SQL Developer), your APEX app won't be able to access them. You would either need to create the objects in the APEX workspace's default schema (which it sounds like you did) or you would need to modify the APEX workspace's default schema to be the schema that you used originally to create the objects.
Generally, when you are building a new application, you would create a new schema
create a workspace in APEX that has FOOTBALL_MGMT as the default schema, and then create your objects in that schema either using SQL Developer or APEX (or SQL*Plus or whatever other tool you prefer). I find SQL Developer much easier to work with for creating tables, packages, etc. but that's a matter of personal preference.
CREATE USER football_mgmt ...
You can have an APEX application in a workspace whose default schema is different than the one that actually owns the objects. But in order to do that, you would need the owner of the objects to grant the default schema user access to the objects and you would either need to use fully qualified names in your code
or you would need to create synonyms (either local or global) for the objects that are owned by the other user.
SELECT * FROM some_other_user.some_table_name
Thanks for that Justin, yes that makes sense now!
I'm flying ahead with this now, I simply cannot believe I even considered Visual Studio when I see how much simpler this Application Express is to use!! One final question, when I have created all my forms and reports, how would a given 'user' be able to use my application? I.e. with Visual Studio when you are done, you create a final solution which is a runnable application, is there anything similar with this APEX that will achieve a similar outcome?
Thanks again everyone!
APEX creates a web-based application. In theory, you could just give someone else the URL to the application running on your machine. In practice, though, you would probably want to deploy the application to a real server (running XE or some other edition of Oracle) and let the users access the application via a URL to that server.
Potentially, you could register for a workspace on http://apex.oracle.com as well which is a free hosted APEX environment.
I suggest that you keep development and production separate.
You can for example create your Apex dev app as application id 100. When a milestone is reached and the dev app is ready for production, export application 100 and import it as application 500 for example.
Application 500 is the production app and you provide its URL to users.
Interactive updates are also possible - while application 500 is in use, you can import a new dev version and overwrite it. As long as the changes in the new dev app are minor, the users on application 500 will not even know that you just ripped out the app and replaced it with an updated version.
Also a good idea to keep backup copies of these export scripts - as they serve as backups when you loose the entire database and need to recreate your Apex app from scratch. Also provides you with the ability to roll production back to a previous version in case of problems with new functionality deployed.
Thats great, thanks for your help with that! I'm amazed at how brilliant APEX is, I've created pages for everything now I've actually only got one problem left.. I'm obviously needing pages to insert data into each of my tables, e.g. for clubs, players, teams, etc.
I've followed the guide here http://docs.oracle.com/cd/E14373_01/appdev.32/e13363/frm_tabular.htm but when clicking the 'Add Row' in the tabular forms that I create, my primary key is setting as null (which essentially means I am, at this stage, unable to create any more records for any of my tables using this application). Is there a way of instructing my forms to generate a random number for my primary keys? I thought this would be automatic!