Thursday, April 1, 2010

coldfusion - how to output hatch # character

sometimes we need a dynamic anchor id that use a hatch # as the first character for anchor id.

so you just simply use this as a hatch character in cfoutput #chr(35)#

it works!!...


Sunday, March 21, 2010

cfchart - produce chart using more that one data in a single bar chart

Need to create a chart using more that one data in a single bar chart?
use cfchart.... nice and easy...


<cfchart format="png" show3d="yes" seriesplacement="stacked">
<cfchartseries type="bar">
<cfchartdata item="Col1" value="30">
<cfchartdata item="Col2" value="60">
</cfchartseries>

<cfchartseries type="bar">
<cfchartdata item="Col1" value="20">
<cfchartdata item="Col2" value="110">
</cfchartseries>

<cfchartseries type="bar">
<cfchartdata item="Col1" value="80">
<cfchartdata item="Col2" value="25">
</cfchartseries>
</cfchart>


Tuesday, March 16, 2010

Configure the JDBC driver for Oracle on ColdFusion 8 Standard Edition

This articel is from http://blog.sixsigns.com/2008/01/25/configure-the-jdbc-driver-for-oracle-on-coldfusion-8-standard-edition/
Thank you.

We have had a lot of people asking us if they could use the Oracle database with their ColdFusion Standard Edition. Of course, Adobe would prefer to sell you the Enterprise Edition for using Oracle, but fortunately ColdFusion is based on Java and thus we can use the free and existing Oracle JDBC Driver.

Note: If you do go with the Oracle JDBC “Thin Client” option you will NOT be able to return result sets from stored procedures. So, if you depend upon PL/SQL stored procedures for returning queries, then you will be out of luck without the ColdFusion Enterprise Edition. On the other hand one could also buy the same JDBC drivers as Adobe is using from DataDirect.

Now, before you will be able to use the JDBC Driver you will need to download the appropriate JDBC Oracle Driver. Make sure, that you download the correct driver for the correct version of your database. They are not all the same! Also, download the “ojdbc14.jar” file, since this is for the JDK 1.4 and JDK 1.5.

Grab the “ojdbc14.jar” file and copy/move it to “ColdFusion8/wwwroot/WEB-INF/lib”. Once done, you will need to restart the ColdFusion Server.

Go the the ColdFusion Administration and go to your “data sources”. Add a new “data source” and choose “Other” from the Database driver options.

On the following page enter “jdbc:oracle:thin:@10.10.10.11:1521:ORCL” in the “JDBC URL” Textarea. In the “Driver Class” textfield enter “oracle.jdbc.OracleDriver”. Of course, enter your username and password. Once done you should connect to your Oracle database without fail.

Here is a screenshot to visualize the above.




ORACLE - FAQs on user account, schema and privileges

This article is from http://dev.fyicenter.com/faq/oracle/oracle_managing_user.php

collection of 23 FAQs on Oracle user account, schema and privileges. Clear answers are provided with tutorial exercises on creating user accounts, granting privileges for session connections, granting privileges for creating tables and inserting rows. Topics included in this FAQ are:

  1. What Is a User Account?
  2. What Is the Relation of a User Account and a Schema?
  3. What Is a User Role?
  4. What Are the System Predefined User Roles?
  5. What Are Internal User Account?
  6. How To Connect to the Server with User Account: SYS?
  7. How To Use Windows User to Connect to the Server?
  8. How To List All User Accounts?
  9. How To Create a New User Account?
  10. How To Change User Password?
  11. How To Delete a User Account?
  12. What Privilege Is Needed for a User to Connect to Oracle Server?
  13. How To Grant CREATE SESSION Privilege to a User?
  14. How To Revoke CREATE SESSION Privilege from a User?
  15. How To Lock and Unlock a User Account?
  16. What Privilege Is Needed for a User to Creat Tables?
  17. How To Assign a Tablespace to a Users?
  18. What Privilege Is Needed for a User to Creat Views?
  19. What Privilege Is Needed for a User to Creat Indexes?
  20. What Privilege Is Needed for a User to Query Tables in Another Schema?
  21. What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
  22. What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
  23. How To Find Out What Privileges a User Currently Has?

What Is a User Account?

A user account is identified by a user name and defines the user's attributes, including the following:

  • Password for database authentication
  • Privileges and roles
  • Default tablespace for database objects
  • Default temporary tablespace for query processing work space

What Is the Relation of a User Account and a Schema?

User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.


What Is a User Role?

A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.

What Are the System Predefined User Roles?

Oracle 10g XE comes with 3 predefined roles:

  • CONNECT - Enables a user to connect to the database. Grant this role to any user or application that needs database access.
  • RESOURCE - Enables a user to create certain types of schema objects in his own schema. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges.
  • DBA - Enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating and dropping schema objects in other users' schemas; and more. It grants all system privileges, but does not include the privileges to start up or shut down the database. It is by default granted to user SYSTEM.

What Are Internal User Account?

An internal user account is a system predefined user account. Oracle 10g XE comes with a number of internal accounts:

  • SYSTEM - This is the user account that you log in with to perform all administrative functions other than starting up and shutting down the database. SYSTEM is automatically created when you install the server. It's password is the one you specified during the installation process.
  • SYS - This is another user account automatically created when you install the server. It's password is the one you specified during the installation process. All base tables and views for the database data dictionary are stored in the SYS schema. So avoid log in as user SYS as much as possible to reduce the risk of damaging those important data objects. User SYSTEM is preferred for all administrative tasks except starting up and shutting down.
  • Other internal user accounts - Other special user accounts are predefined for special purposes. For example, CTXSYS is a special user account used by the Oracle Text product.

How To Connect to the Server with User Account: SYS?

SYS is a very special user account. It has been associated with the highest privilege call SYSDBA. Normally, you should not connect to the server with SYS. But if you want to use it, you need to use a special connect command:

>cd (OracleXE home directory)

>.\bin\sqlplus /nolog

SQL> connect SYS/fyicenter AS SYSDBA

Connected.

SQL> quit

Note that the "/nolog" option is used to start SQL*Plus without login immediately. A special form of the "connect" command is used to include the user name, password, and the privilege in the same line.

You can not log in with SYS without SYSDBA privilege.

How To Use Windows User to Connect to the Server?

During the installation process, 10g XE will create a special Windows user group called ORA_DBA, and put your Windows user into this group. Any Windows users in this group can be connected to Oracle server with SYSDBA privilege without any Oracle server user account. This process is called connecting the server as SYSDBA with OS Authentication. Here is how to do this with a special form of the "connect" command:

(Log in with the same user you used to install 10g XE)
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus /nolog
 
SQL> connect / AS SYSDBA
Connected.
 
SQL> quit

So if "connect" is used without user name and password, the current Windows user will be trusted if he/she is in the ORA_DBA user group on the Windows system.

How To List All User Accounts?

User accounts can be accessed through a system view called ALL_USERS. A simple SELECT statement can be used to get a list of all user accounts. Try the following script:

>.\bin\sqlplus /nolog
 
SQL> connect SYSTEM/fyicenter
Connected.
 
SQL> SELECT * FROM ALL_USERS;
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
FLOWS_020100                           35 07-FEB-06
FLOWS_FILES                            34 07-FEB-06
HR                                     33 07-FEB-06
MDSYS                                  32 07-FEB-06
ANONYMOUS                              28 07-FEB-06
XDB                                    27 07-FEB-06
CTXSYS                                 25 07-FEB-06
DBSNMP                                 23 07-FEB-06
TSMSYS                                 20 07-FEB-06
DIP                                    18 07-FEB-06
OUTLN                                  11 07-FEB-06
SYSTEM                                  5 07-FEB-06
SYS                                     0 07-FEB-06

How To Create a New User Account?

If you want to create a new user account, you can log in as SYSTEM and use the CREATE USER command as shown in the following example:

>.\bin\sqlplus /nolog
 
SQL> connect SYSTEM/fyicenter
Connected.
 
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.

Note that CREATE is a SQL statement, so you need to terminate it with ";". This command creates a user called "DEV", with a password of "developer". You can test this account by log in with DEV from SQL*Plus.

How To Change User Password?

If you want to change a user's password, you can log in as SYSTEM and use the ALTER USER command as shown in the following example:

>.\bin\sqlplus /nolog
 
SQL> connect SYSTEM/fyicenter
Connected.
 
SQL> ALTER USER DEV IDENTIFIED BY beginner;
User altered.

Note that ALTER is SQL statement, so you need to terminate it with ";". This command resets DEV's password to "beginner".

How To Delete a User Account?

If you want to delete a user account and its associated schema, you can log in as SYSTEM and use the DROP USER command as shown in the following example:

>.\bin\sqlplus /nolog
 
SQL> connect SYSTEM/fyicenter
Connected.
 
SQL> DROP USER DEV CASCADE;
User dropped.
 
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.

Note that CASCADE tells the server drop the associated schema.


What Privilege Is Needed for a User to Connect to Oracle Server?

Oracle deny connection to users who has no CREATE SESSION privilege. Try the following tutorial exercise, you will find out how Oracle denies connection:

>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
 
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
 
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-01045: user DEV lacks CREATE SESSION privilege;
  logon denied

Oracle error message is pretty clear.

How To Grant CREATE SESSION Privilege to a User?

If you want give a user the CREATE SESSION privilege, you can use the GRANT command. The following tutorial exercise shows you how to grant DEV the privilege to connect to the server:

>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.
 
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.

How To Revoke CREATE SESSION Privilege from a User?

If you take away the CREATE SESSION privilege from a user, you can use the REVOKE command as shown in the following example script:

>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
 
SQL> REVOKE CREATE SESSION FROM dev;
Revoke succeeded.
 
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.

This script restored the CREATE SESSION privilege to user "dev", so you can continue other example scripts below.


How To Lock and Unlock a User Account?

If you want to lock a user account for a short period of time, and unlock it later, you can use the ALTER USER ... ACCOUNT command. The following sample script shows how to use this command:

>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
 
SQL> ALTER USER dev ACCOUNT LOCK;
User altered.
 
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-28000: the account is locked
 
SQL> disconnect
SQL> connect SYSTEM/fyicenter
 
SQL> ALTER USER dev ACCOUNT UNLOCK;
User altered.
 
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.

What Privilege Is Needed for a User to Create Tables?

To be able to create tables in a user's own schema, the user needs to have the CREATE TABLE privilege, or the CREATE ANY TABLE privilege, which is more powerful, and allows the user to create tables in other user's schema. The following tutorial exercise gives you a good example on CREATE TABLE privilege:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
 
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01031: insufficient privileges
 
SQL> disconnect
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
 
SQL> disconnect
SQL> CONNECT DEV/developer
 
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01950: no privileges on tablespace 'SYSTEM'

The above error message tells that user "dev" is not allowed to use the tablespace "SYSTEM". See the next question for answers.

How To Assign a Tablespace to a Users?

When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's default tablespace, and assigns 4MB of space to dev:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
 
SQL> ALTER USER dev DEFAULT TABLESPACE USERS;
User altered.
 
SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.
 
SQL> disconnect
SQL> CONNECT DEV/developer
 
SQL> CREATE TABLE fyi (id NUMBER);
Table created.
 
SQL> DROP TABLE fyi;
Table dropped.
 
SQL> CREATE TABLE fyi (id NUMBER);
Table created.

As you can see, "dev" can create and drop tables now. You can also let "dev" to create tables in any tablespace without any restriction by granting him the UNLIMITED TABLESPACE system privilege.

What Privilege Is Needed for a User to Create Views?

To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the user to create views in other user's schema. The following tutorial exercise gives you a good example on CREATE VIEW privilege:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
 
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
ORA-01031: insufficient privileges
 
SQL> disconnect
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT CREATE VIEW TO dev;
Grant succeeded.
 
SQL> disconnect
SQL> CONNECT DEV/developer
 
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.
 
SQL> DROP VIEW fyi_view;
View dropped.
 
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.

As you can see, "dev" can create and drop views now.


What Privilege Is Needed for a User to Create Indexes?

For a user to create indexes, he/she needs the same privilege as the creating tables. Just make sure he/she has the CREATE TABLE privilege. The following tutorial exercise gives you a good example on creating view privilege:

>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
 
SQL> disconnect
SQL> CONNECT DEV/developer
 
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.
 
SQL> DROP INDEX fyi_index;
Index dropped.
 
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.

This exercise assumes that you followed previous exercises in the FAQ collection.

What Privilege Is Needed for a User to Query Tables in Another Schema?

For a user to run queries (SELECT statements) on tables of someone else's schema, he/she needs the SELECT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to query tables in "hr" schema:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
 
SQL> SELECT COUNT(*) FROM hr.employees;
ORA-01031: insufficient privileges
 
SQL> disconnect
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT SELECT ANY TABLE TO dev;
Grant succeeded.
 
SQL> disconnect
SQL> CONNECT DEV/developer
 
SQL> SELECT COUNT(*) FROM hr.employees;
  COUNT(*)
----------
       107

As you can see, "dev" can query tables in any schema now.

You also need to remember that table name must be prefixed with the schema name (same as owner user name).

What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?

For a user to insert rows into tables of someone else's schema, he/she needs the INSERT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to insert rows in "hr" schema:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
 
SQL> INSERT INTO hr.jobs 
  VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
ORA-01031: insufficient privileges
 
SQL> disconnect
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT INSERT ANY TABLE TO dev;
Grant succeeded.
 
SQL> disconnect
SQL> CONNECT DEV/developer
 
SQL> INSERT INTO hr.jobs 
  VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
1 row created.

As you can see, "dev" can insert rows in any schema now. But you should be careful when giving this privilege to a regular developer.


What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?

For a user to delete rows from tables of someone else's schema, he/she needs the DELETE ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to delete rows in "hr" schema:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
 
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
ORA-01031: insufficient privileges
 
SQL> disconnect
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
 
SQL> disconnect
SQL> CONNECT DEV/developer
 
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
1 row deleted.

As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.


How To Find Out What Privileges a User Currently Has?

Privileges granted to users are listed in two system views: DBA_SYS_PRIVS, and USER_SYS_PRIVS. You can find out what privileges a user currently has by running a query on those views as shown in the tutorial exercise below:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
 
SQL> SELECT username, privilege FROM USER_SYS_PRIVS;
USERNAME                       PRIVILEGE
------------------------------ ----------------------
DEV                            SELECT ANY TABLE
DEV                            INSERT ANY TABLE
DEV                            CREATE SESSION
DEV                            CREATE VIEW
DEV                            DELETE ANY TABLE
DEV                            CREATE ANY TABLE
 
SQL> disconnect
SQL> connect SYSTEM/fyicenter
 
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
 
SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS 
  WHERE GRANTEE = 'HR';
GRANTEE                        PRIVILEGE
------------------------------ -----------------------
HR                             CREATE VIEW
HR                             UNLIMITED TABLESPACE
HR                             DEBUG CONNECT SESSION
HR                             CREATE DATABASE LINK
HR                             CREATE SEQUENCE
HR                             CREATE SESSION
HR                             DEBUG ANY PROCEDURE
HR                             ALTER SESSION
HR                             CREATE SYNONYM

Looks like "hr" has move privileges than "dev".