UB7.NET

Hi all,

I was just wondering about the fact that how to create an auto increment column in oracle. It is quite easy to create an auto increment column in sql server but in oracle’s old version that is 11, it is bit of an overwork for you. Well it is not that difficult if you are using SQL developer but in case you are creating the table from CLI on Unix or other CLI based operating system than you need to know that to create an auto increment column in oracle you need to first declare a sequence using the below structured query:

create sequence name_of_sequence

Once you have executed the above query you will have to create a trigger of type before on the newly created table where you want the auto incremented column. The structured query for the same is mentioned below:

create or replace trigger name_of_trigger before insert on new_tbl_name
for each row
begin
select name_of_seq.nextval into :new.auto_incremented_col_name from dual
end

Congrats you have just created the column with auto incremented values

Well this is not a difficult answer at all but i was facing some weird issues with Oracle .Net provider when i had tried to login with the newly created user. Before you try to login with a newly created user in Oracle .NET provider, please first give that user some basic set pf permissions.

 I don’t know which is the key permission below, which had successfully helped me in login into the .NET provider interface but here are the permission

 

GRANT create session TO demo_user;

GRANT create table TO demo_user;

GRANT create view TO demo_user;

GRANT create any trigger TO demo_user;

GRANT create any procedure TO demo_user;

GRANT create sequence TO demo_user;

GRANT create synonym TO demo_user;

Well i was stuck at this question from past 1 hour but now i have finally found it.

In order to specify the server name for the .net provider of Oracle please use the EX Connect format as specidified below:

//localhost:1521/db_name

General syntax is:

//Name of the server:port number/Db

User Creation

In order to create a new user just run the below query

CREATE USER DemoUser

IDENTIFIED BY DemoPassword

DEFAULT TABLESPACE DemoSpace

TEMPORARY TABLESPACE temp

QUOTA 20M on DemoSpace;

The query is really self-explanatory and you can modify it as you wish.

Identified is used for creating the password.

Tablespace is basically a space on the disk only where all your objects like tables will be stored.

20M indicates the size of the space allotted to your tablespace.

The above query will refuse to run incase if there is no tablespace in the system. So you need to create the tablespace before executing the above query. Here is the query for the same. It is not a complete query. For that please consult official documentation.

Create tablespace spacename datafile ‘datafilename.dbf’ size 60m reuse extent management local

Also I was confused with the fact that how to store binary data in the oracle table. In the official help file it is written we can use varbinary and binary as the data types for storing binary data but they are not available in the express edition of the oracle. So in order to store the binary data in this edition we can use RAW type. The RAW type allow the storage of bytes of size up-to 4000 bytes.

How to know the version of oracle?
Select * from v$version where banner like ‘oracle%’

Hi all of you,

Here I am going to share some of the Oracle based queries which can be helpful to someone who is just starting with Oracle.

Well, first question most of the times is that how to connect a user as a sysdba. Normally we try to login with the System user but you should know that System user does not have this kind of Privilege from the start. So in order to start using sysdba privileges login with the sys user and connect it as sysdba. Password is normally same for sys also.

Once you have login with this user you can assign sysdba role to another user also using the following query.

Grant SYSDBA to username