Browse: Home » PL/SQL

PL/SQL

  • UTF_FILE and Directories

    November 9, 2011 Respond

    In case you are maintaining a pl/sql program developed by someone else and that program writes into a file, then basically that file is written to a folder/directory. And since the directories are saved in codes, you have to find the meaning (or location) of those codes. The code below will help you display the…

  • Oracle WITH Clause (Subquery Refactoring)

    December 17, 2009 Respond

    WITH clause was introduced in Oracle 9i to provide powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries. WITH Clause Syntax WITH alias_name — alias for the aggregate_query AS (aggregate_query_here) SELECT… — Beginning of the query main body It should be noted that multiple aggregate queries…

  • Oracle CASE WHEN Statement

    December 8, 2009 Respond

    Another oracle sql statement that you may need in order to simplify and optimize your code is the use of CASE statement. Here’s how it is being used. Syntax #1 CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 … WHEN condition_n THEN result_n ELSE result END Example SELECT last_name, commission_pct, (CASE…

  • Rename Table in Oracle

    September 2, 2009 Respond

    SYNTAX alter table table_name rename to new_table_name; Example alter table employee rename to employee_bak;

  • Long to Varchar2

    July 30, 2009 Respond

    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…

  • Explain Plan in Oracle SQL*Plus

    March 23, 2009 Respond

    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…