Archive for July, 2013

Create tablespace and user using SQLPLUS in Oracle XE 11g

Create TableSpace in Oracle XE 11g using SQLPLUS

To create a user first you need to create a tablespace to create a tablespace use the following query

create tablespace <tablespace_name> datafile '<path>' size 32m autoextend on next 32m maxsize 2048m extent management local;

<tablespace_name> : Your new tablespace name

<path> : Path to the dbf file (database file)

Example

create tablespace MYTABLESPACE datafile '/data/oracle/oradata/tpol/tools01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;


Create User  in Oracle XE 11g using SQLPLUS

If you have no privilege for create .dbf (database) file then you can skip the above query and you can create the user using the system tablespace.

Create user

SQL> CREATE USER jijokjose IDENTIFIED BY jijokjose DEFAULT TABLESPACE SYSAUX TEMPORARY TABLESPACE "TEMP";

User created.

SQL>

Read more »

Execute DDL commands in Oracle PL/SQL

PL/SQL Dynamic SQL

To execute DDL commands we need Dynamic SQL. Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.

PL/SQL provides two ways to write dynamic SQL:

  •     Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements
  •     DBMS_SQL package, an API for building, running, and describing dynamic SQL statements

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package.


When You Need Dynamic SQL

In PL/SQL, you need dynamic SQL to run:

  •     SQL whose text is unknown at compile time

    For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.

Read more »

How to solve Error loading stylesheet: An unknown error has occurred (805303f4) in wordpress

Google-XML-Sitemaps-plugin-error

If you are facing this error after installing Google XML Sitemaps plugin, then its not your fault because the stylesheet linked loads without problems and the sitemap doesn't present any issues. The problem was your website is accessible at http://www.sample.com but your sitemap is browsable both at

  • http://www.sample.com/sitemap.xml and
  • http://sample.com/sitemap.xml

Thus, the stylesheet fails to load if you visit the sitemap without having www

So you should load your sitemap by using the following URL : http://www.sample.com/sitemap.xml

Read more »