Browse: Home » Blog » PL/SQL

PL/SQL

  • Enable/Disable Bind Variable in SQL*Plus

    January 18, 2010 Respond

    As state in the title, this post is about turning off the bind variable in SQL*Plus. Actually, this is also applicable in PL/SQL Developer and Toad but I’ll be discussing more on SQL*Plus. You see, I have a problem wherein whenever I load my backup data (saved as insert statements) and there are ampersand (&)…

  • 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…

  • How to Get Oracle DB Version

    September 17, 2009 Respond

    I only know two ways of getting the Oracle DB version you are currently connected to. They are as follows: SELECT * FROM product_component_version SELECT * FROM v$version This is quite handy if your program has SQL commands that are not applicable for earlier versions of Oracle Database.

  • 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…