Archive

Archive for the ‘PL/SQL’ Category

Table not listed under connections in SQLDeveloper

When I created a connection with an existing user name, i was able to connect to the sql server but in the Tables tree i cannot see any tables list.

Solution: Use another user who has access to the table list(i.e. user sa)the user which i tried previously is not having access to the table listing, thats why the table tree was empty

Advertisements
Categories: PL/SQL, SQL

Insert Special characters in Oracle table

July 4, 2012 Comments off

Ques: How to insert special characters like & in oracle table?

Ans: Before running the insert table command, execute “set define off” command

Categories: PL/SQL, Technical

Deleting a user in Oracle 11 g

July 4, 2012 Comments off

Syntax:

======

DROP USER username;

If user owns any other database objects then

DROP USER username CASCADE;

Example:

======

DROP USER karuvachi;

If karuvachi user owns any other database objects,

DROP USER karuvachi CASCADE;

Categories: PL/SQL, Technical

Alter user in Oracle 11g/Change password of user

July 4, 2012 Comments off

Syntax:

=====

ALTER USER username IDENTIFIED BY new_password;

 

Example

ALTER USER karuvachi IDENTIFIED BY nandhu;

 

Categories: PL/SQL, Technical

Creating User in Oracle 11g

July 4, 2012 Comments off

Syntax:

======

CREATE USER username IDENTIFIED BY password;

1.user name is a name you specify according to the naming rules of database object.

2.password follows the same rules, but password is case sensitive by default in 11g.

 

Example:

=======

CREATE USER karuvachi IDENTIFIED BY vellaiyan

 

 

Categories: PL/SQL, Technical

Create Global Temporary Table

March 29, 2012 Comments off

Today..i have used global temporary table to backup my system….

global temporary tables are very useful on session basis…

can have a session safe calculations using global temporary tables.

Two ways to create global temporary tables:

1.CREATE GLOBAL TEMPORARY TABLE TEMP_table( PRIMEID  NUMBER ) ON COMMIT PRESERVE ROWS;

2.CREATE GLOBAL TEMPORARY TABLE TEMP_table( PRIMEID  NUMBER ) ON COMMIT DELETE ROWS;

Categories: PL/SQL, SQL, Technical

PRAGMA Autonomous_transaction in Oracle

March 20, 2012 Comments off

Use of pragma autonomous_transaction in oracle PL/SQL is…the transactions performed in this procedure will be committed and does not bother about whether the calling procedures transactions are committed or rollback.

For Example: you are performing a banking transaction, whether the transaction is committed or rollback, you need to see the log of that transaction, So in your logging procedure, you can put PRAGMA Autonomous_transaction; statment to commit all the logs in log table.

Create or Replace procedure add_log( vlog_seq NUMBER,vmsg_type VARCHAR2,vmsg VARCHAR2)

is

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INERT INTO

Transaction_Log(log_seq,log_time,msg_type,msg)

VALUES (vlog_seq,systimestamp,vmsg_type,msg);

commit;

end add_log;

Categories: PL/SQL, Technical