[ oracle ] solution to `cannot perform a DML operation inside a query`

use PRAGMA AUTONOMOUS_TRANSACTION;, and COMMIT;

CREATE OR REPLACE FUNCTION supercomplex(datainput IN VARCHAR2)
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO dumtab VALUES (datainput);
COMMIT;
RETURN 'done';
END supercomplex;
/

ref : Vincent Malgrat

Advertisements

python connect oracle

Install

cx_Oracle binary install for windows

Example code

import cx_Oracle
conn_str = u'user/password@host:port/service'
conn = cx_Oracle.connect(conn_str)
c = conn.cursor()
c.execute(u'select your_col_1, your_col_2 from your_table')
for row in c:
   print row[0], "-", row[1]
conn.close()

To set encoding

 import os
os.environ["NLS_LANG"] = "AMERICAN_AMERICA.AL32UTF8"

The database encoding can be found using the below query

SELECT USERENV ('language') FROM DUAL

ref : oracleDecimusXIV

[ oracle ] export sql oracle command line

use DBMS_METADATA package

Syntax

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

object_type

 ASSOCIATION
 AUDIT
 AUDIT_OBJ
 CLUSTER
 COMMENT
 CONSTRAINT
 CONTEXT
 DB_LINK
 DEFAULT_ROLE
 DIMENSION
 DIRECTORY
 FUNCTION
 INDEX
 INDEXTYPE
JAVA_SOURCE
LIBRARY
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
OBJECT_GRANT
OPERATOR
OUTLINE
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
PROCEDURE
PROFILE
PROXY
REF_CONSTRAINT
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
TRUSTED_DB_LINK
TYPE
TYPE_SPEC
TYPE_BODY
USER
VIEW 

NOTE:

– PACKAGE will export both PACKAGE_SPEC and PACKAGE_BODY

– TYPE will export both TYPE_SPEC and TYPE_BODY

– to exclude the table detail, i.e. tablespace storage, etc., use the below anonymous block

BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
END;

– to extract w/o schema, use the add the below command to the anonymous block

 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EMIT_SCHEMA', false);

ref : oracle, yanfeng

[ C#] MVC 2 101

Create New Project

In visual studio, File > New Project
`Visual C# Projects` and in the Templates pane, select `ASP.NET MVC 2 Web Application`.

The files will be generated.

SFA.sln
SFA\App_Data
SFA\bin
SFA\Content\Site.css
SFA\Controllers\AccountController.cs
SFA\Controllers\HomeController.cs
SFA\Global.asax
SFA\Global.asax.cs
SFA\Models\AccountModels.cs
SFA\obj\Debug
SFA\obj\Debug\DesignTimeResolveAssemblyReferencesInput.cache
SFA\obj\Debug\Package
SFA\obj\Debug\TempPE
SFA\Properties\AssemblyInfo.cs
SFA\Scripts\jquery-1.4.1-vsdoc.js
SFA\Scripts\jquery-1.4.1.js
SFA\Scripts\jquery-1.4.1.min.js
SFA\Scripts\jquery.validate-vsdoc.js
SFA\Scripts\jquery.validate.js
SFA\Scripts\jquery.validate.min.js
SFA\Scripts\MicrosoftAjax.debug.js
SFA\Scripts\MicrosoftAjax.js
SFA\Scripts\MicrosoftMvcAjax.debug.js
SFA\Scripts\MicrosoftMvcAjax.js
SFA\Scripts\MicrosoftMvcValidation.debug.js
SFA\Scripts\MicrosoftMvcValidation.js
SFA\SFA.csproj
SFA\SFA.csproj.user
SFA\SFA.Publish.xml
SFA\Views\Account
SFA\Views\Home
SFA\Views\Shared
SFA\Views\Web.config
SFA\Views\Account\ChangePassword.aspx
SFA\Views\Account\ChangePasswordSuccess.aspx
SFA\Views\Account\LogOn.aspx
SFA\Views\Account\Register.aspx
SFA\Views\Home\About.aspx
SFA\Views\Home\Index.aspx
SFA\Views\Shared\Error.aspx
SFA\Views\Shared\LogOnUserControl.ascx
SFA\Views\Shared\Site.Master
SFA\Web.config
SFA\Web.Debug.config
SFA\Web.Release.config
----------------------------------------------------------------- UnitTest
SFA.Tests\App.config
SFA.Tests\bin
SFA.Tests\Controllers
SFA.Tests\obj
SFA.Tests\Properties
SFA.Tests\SFA.Tests.csproj
SFA.Tests\bin\Debug
SFA.Tests\Controllers\AccountControllerTest.cs
SFA.Tests\Controllers\HomeControllerTest.cs
SFA.Tests\obj\Debug
SFA.Tests\obj\Debug\DesignTimeResolveAssemblyReferencesInput.cache
SFA.Tests\obj\Debug\TempPE
SFA.Tests\Properties\AssemblyInfo.cs

Build and Deploy

In Solution Manager, Right Click at the project > Build

Click `Start Debugging (F5)` icon

The default route

Global.asax.cs

routes.MapRoute(
"Default", // Route name
"{controller}/{action}/{id}", // URL with parameters
new { controller = "Home", action = "Index", id = UrlParameter.Optional } // Parameter defaults
);

i.e. http://localhost:52386/Home/About calls method About()

in SFA\Controllers\HomeController.cs

 

Passing The data from controller to view

Controller

ViewData["Message"] = "Welcome to ASP.NET MVC!";

View

<%: ViewData["Message"] %>

Action Anchor

View

<%: Html.ActionLink("Register", "Register") %>

Form

View : Account/LogOn.aspx

NOTE : submit to Account/LogOn.aspx

<% using (Html.BeginForm()) { %>
<%: Html.ValidationSummary(true, "Login was unsuccessful. Please correct the errors and try again.") %>
<div>
<fieldset>
<legend>Account Information</legend>

<div>
<%: Html.LabelFor(m => m.UserName) %>
</div>
<div>
<%: Html.TextBoxFor(m => m.UserName) %>
<%: Html.ValidationMessageFor(m => m.UserName) %>
</div>

<div>
<%: Html.LabelFor(m => m.Password) %>
</div>
<div>
<%: Html.PasswordFor(m => m.Password) %>
<%: Html.ValidationMessageFor(m => m.Password) %>
</div>

<div>
<%: Html.CheckBoxFor(m => m.RememberMe) %>
<%: Html.LabelFor(m => m.RememberMe) %>
</div>

<p>
<input type="submit" value="Log On" />
</p>
</fieldset>
</div>
<% } %>

Model : AccountModels.cs

#region Models

public class LogOnModel
{
[Required]
[DisplayName("User name")]
public string UserName { get; set; }

[Required]
[DataType(DataType.Password)]
[DisplayName("Password")]
public string Password { get; set; }

[DisplayName("Remember me?")]
public bool RememberMe { get; set; }
}

#endregion

Properties Message

Web.config

i.e. minRequiredPasswordLength

<membership>
<providers>
<clear/>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"
enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
applicationName="/" />
</providers>
</membership>

 

 

[ oracle ] rows into a column

Decode syntax

DECODE( expression , search , result [, search , result]... [, default] )

Example

table

emp_num  person_name  organization  earned_date  sum(hours)
-------  -----------  ------------  -----------  ----------
36372    Name1        Test1         23-MAR-11      3.17
36372    Name1        Test1         15-MAR-11      6.70
40208    Name2        Test2         08-APR-11     13.50
40208    Name2        Test2         06-APR-11     12.07

result

emp_num  person_name  organization  23-MAR-11  15-MAR-11  08-APR-11  06-APR-11
-------  -----------  ------------  ---------  ---------  ---------  ---------
36372     Name1       Test1           3.17        6.70
40208     Name2       Test2                                 13.50      12.70    

query

select
emp_num,
person_name,
organization,
sum(decode(earned_date,to_date('23/03/2011','dd/mm/yyyy'),hours,0)) 23mar11,
sum(decode(earned_date,to_date('15/03/2011','dd/mm/yyyy'),hours,0)) 15mar11,
sum(decode(earned_date,to_date('08/04/2011','dd/mm/yyyy'),hours,0)) 08apr11,
sum(decode(earned_date,to_date('06/04/2011','dd/mm/yyyy'),hours,0)) 06apr11
from
the_table //don't know the name
group by
emp_num,
person_name,
organization

ref : Andrew Wood

SQL Join Cheat Sheet

useful naja

sqljoins_cheatsheetNatural Join คือ ไม่ต้องระบุว่าจะเอา column อะไรมาเป็นตัวเทียบเท่ากัน Database จะไปดูว่า column ใดที่ชื่อเหมือนกันในทั้ง 2 ตาราง  มันก็จะอนุมานเอา column นั้น มาเป็นใช้เป็นตัวเท่ากับ ในการ join ( ref )