Long to Varchar2

July 30, 2009 By Leave a Comment

I got tangled with a problem on the datatype LONG in Oracle which is still being used in a number of applications. Siebel, is actually one of them. When I was tasked to extract some data from one of the tables in EIM using C++, I run into this problem.

I googled for answer and here’s what I got.

CREATE OR REPLACE FUNCTION LONG_TO_CHAR( in_rowid rowid,in_owner
varchar,in_table_name varchar,in_column varchar2)
RETURN varchar AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--
begin
sql_cur := 'select '||in_column||' from
'||in_owner||'.'||in_table_name||' where rowid =
'||chr(39)||in_rowid||chr(39);
dbms_output.put_line (sql_cur);
execute immediate sql_cur into text_c1;

text_c1 := substr(text_c1, 1, 5000);
RETURN TEXT_C1;
END;

It’s pretty simple but unfortunately, we can’t do this without using function.

Example use:

SELECT MI_F_LONG_TO_CHAR(ROWID, 'SIEBEL','EIM_ORDER','ORD_X_COMMENTS')
FROM SIEBEL.EIM_ORDER

credit to Oracle Mag for this entry.

Incoming search terms:

  • oracle long to varchar
  • oracle long to varchar2
  • LONG to VARCHAR2
  • long to varchar
  • oracle long to char
  • ORACLE long varchar2
  • varchar2 long
  • LONG_TO_CHAR( in_rowid
  • sql long to varchar2
  • pl sql long to varchar2
Filed Under: Oracle, PL/SQL Tagged With: , , , ,

Explain Plan in Oracle SQL*Plus

March 23, 2009 By Leave a Comment

In case you only have SQL*Plus as your editor, you can use the following codes to show the execution plan of your query.

First Step : create plan
EXPLAIN PLAN FOR
SELECT field1, field2
FROM table;

Second Step: show results

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

You will need the necessary privileges to execute this. You’ll also need to have the PLAN table. If not, you’ll have to create it as that’s the placeholder where execution plan is written.

This technique is important especially when you don’t have a GUI-based SQL editor like PL/SQL Developer or TOAD.

Incoming search terms:

  • oracle explain plan sqlplus
  • explain in sql*plus
  • explain plan pl sql developer
  • how to generate the explain plan from sqlplus
  • oracle explain plan using SQL*PLUS
  • oracle plsql explain plan privileges
  • oracle sql developer execution plan
  • sqlplus explain plan
Filed Under: Oracle, PL/SQL Tagged With: , ,

Monitor Oracle DB Server Processes

March 20, 2009 By Leave a Comment

To see DB processes running on your Oracle DB, the code below can be used. Specially handy if you want to check which part of your package or stored procedure is taking time to execute. Indispensable for performance tuning.

SELECT sess.sid, sess.serial#, sess.sql_child_number, sess.sql_exec_id,
sess.process, sess.status, sess.username, sess.osuser, sess.program,
sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = 'USER'
--AND sess.osuser = 'yourwindowslogin';

Equivalent in SQL Server/Sybase

The equivalent command in Sybase or SQL Server is SP_WHO. However, the Oracle version provides more information.

For now, just this. I’ll provide additional details later. For additional fields, try to check the schema of v$session.

Incoming search terms:

  • sp_who in oracle
  • oracle sp_who
  • what is equivalent of sp_who2 in oracle
  • sp_who2 on siebel
  • sp_who pour oracle
  • sp_who oracle equivalent
  • sp_who oracle
  • equivalent of sp_who in oracle
  • oracle sp_who equivalent
  • oracle pl/sql sp_who
Filed Under: Blog, Oracle Tagged With: , ,
Newer Posts »