Disabling PL/SQL Warnings

Hi all!
So, the DBA keep insisting that the Procedure need to compile without warnings? Easy!
This is actually a nice option if you are compiling a code in a client and don’t want to show that your code has warnings, which is kind of ok, once it’s almost impossible to code without warnings.

And this is not even new. Have a look on this documentation from 10.2.

Ok, so how to do it?

ALTER SESSION SET plsql_warnings = 'disable:all';

Have a look in the example below:

SQL> CREATE OR REPLACE PROCEDURE plw5001
  2  IS
  3     a   BOOLEAN;
  4     a   PLS_INTEGER;
  5  BEGIN
  6     a := 1;
  7     DBMS_OUTPUT.put_line ('Will not compile?');
  8  END plw5001;
  9  /
Warning: Procedure created with compilation errors.

SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE PLW5001:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PLW-05001: previous use of 'A' (at line 3) conflicts with this
use

6/4 PL/SQL: Statement ignored
6/4 PLS-00371: at most one declaration for 'A' is permitted
SQL>
SQL> ALTER SESSION SET plsql_warnings = 'disable:all';

Session altered.

SQL>
SQL> CREATE OR REPLACE PROCEDURE plw5001
2 IS
3 a BOOLEAN;
4 a PLS_INTEGER;
5 BEGIN
6 DBMS_OUTPUT.put_line ('Will not compile?');
7 END plw5001;
8 /

Procedure created.

 

Oracle SQL: Aggregate List – LISTAGG

Know this command?

I think it’s very useful, but not so often I see that in use… More about it:

It was introduced in Oracle 11g Release 2, as an analytic function to make the life easier fot the PLSQL Dev, specially when working to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

In case WM_CONCAT is in use for this end, be aware that WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). Also, WM_CONCAT has been removed from 12c onward, once it was completely replaced by LISTAGG.

More info and examples can be found here: https://oracle-base.com/articles/12c/listagg-function-enhancements-12cr2

Hope you enjoy it. Cheers!

Automating your Github Project’s build and deploy with Heroku and Travis

Hey fellas!
My name is Jéferson Sartor Decker, I’m a Software Developer in Thoughtworks Brasil and live in Porto Alegre! Matheus invited me to make a post here, so I hope you enjoy it!

What we’re going to use?

Github: A web-based service for version control, that uses git and is free to open source projects <3 !

Heroku: As they said in their site:

Heroku is a cloud platform that lets companies build, deliver, monitor and scale apps — we’re the fastest way to go from idea to URL, bypassing all those infrastructure headaches.

And is also free with some restrictions. Remember that Heroku support for default the languages below:

  • Javascript
  • Ruby
  • PHP
  • Python
  • Go
  • Scala
  • Clojure

Heroku CLI: Heroku Command Line Interface.

Travis-CI: A continuous integration service, that builds and tests code in Github and also is free to open source projects <3 !

Travis-CI CLI: Travis CI Command Line Interface.

Let’s get started!

So, first of all we need to have a project on Github, I’ll create a sample Java + Spring project, just to have something to build and deploy. You can do this to any project that you already have in your Github! My project is the ‘sample-java-project’, as you can see in the next image:
Continue reading

Application Named Variables Returning ORA-01008: not all variables bound

Hi all,
I saw it a long time ago and last week I was involved in a discussion for this same error. So, this deserve a post for future reference. 🙂

Imagine the situation: You are building an application or a module to perform queries on database. You want to use variables in the query and fill the values using text fields on application. Sounds easy and works fine for SQLServer and others, but Oracle database is returning:

ORA-01008: not all variables bound

What to do?

Fist let’s clear the issue: this is not related to database layer or oracle interpreter/parser. This error happens when a bind variable being used on SQL have no value. The official reasoning is:

Cause: A SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.

In my case ODP.Net with C# (but apply to other languages). Interesting fact:
“ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name, set property BindByName to true.”

This means Oracle may be waiting for “:1”, “:2” as bind variables and this can also not being set correctly by application.
In this case, please try to set BindByName to true in application code for Oracle command. Example below:

using(OracleCommand cmd = con.CreateCommand()) {
    ...
    cmd.BindByName = true;
    ...
}

Now, try again. 🙂

Hope this helps you!
Cheers!