What is ASP.NET Core 1.0 and .NET Core 1.0 and what is future of ASP.NET 5 and 4.6!

ASP.NET 5 is now ASP.NET Core 1.0.

.NET Core 5 is now .NET Core 1.0.

The name is has changed, nothing more than this!

Why new name 1.0?

Because the concept is new, its not having same architecture as previous version of ASP.net till 4.6.

However till now, the .NET Core 1.0 is not mature as earlier .NET Frameworks were, this is in testing and development phase. Earlier ASP.NET version are more mature and very well tested for developing a new project.

ASP.NET Core 1.0 is a 1.0 release which has Web API and MVC but not SignalR and Web Pages. It does not have VB and F# till now, they will be added in near future.

ASP.NET Core 1.0 a new framework, but earlier ASP.NET 4.6 version will remain there, and will be fully supported, but ASP.NET Core 1.0 is new, very new…

SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Some errors when encountered take most of us for a spin. In this category the error related to “Timeout” surely falls. If you are a web developer and receive the same there are a hundred combinations why this can possibly happen. The web results can sometimes lead us in completely opposite direction because we have…

Source: SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Pl/SQL Concepts: How to with an example?

Import Data/file using a sql script:

Big files: sqlloader or external tables & Small files: own parsing using UTL_FILE

Import: sqlloader Example

  1. Creating a control file:
LOAD DATA
 INFILE '<path and file name of csv file>'
 INTO TABLE <your table name>
 FIELDS TERMINATED BY ','
 (feature_name CHAR OPTIONALLY ENCLOSED BY '"',
 feature_type CHAR, county CHAR,
 latitude CHAR,
 longitude CHAR,
 elevation INTEGER EXTERNAL,
 update_time DATE "YYYYMMDDHH24MI")

Remember that you need to use char instead of varchar 2 and integer for number columns save your control file as name.ctl
2. Start sql loader from the command like this: This can also be made a procedure to create a job for this to make the import process automatic.

sqlldr username/password@connect_string control=ctl_file.ctl log=log.log
you should see the rows inserted and commited as they load

Import: External tables Example

Oracle9i Database introduced external tables, which allow a formatted plain text file to be visible to the database as a table that can be selected by regular SQL.Create a directory object named dump_dir as:

create directory dump_dir as '/home/oracle/dump_dir';
Create an external table:
create table trans_ext
(   ... <columns of the table> ...)
organization external
(
 type oracle_loader 
default directory admin  
access parameters
   (
      records delimited by newline
      badfile 'trans_ext.bad'
      discardfile 'trans_ext.dis'
      logfile 'trans_ext.log'
      fields terminated by ","  optionally enclosed by '"'
      (... <columns> ...)
   )
   location ('trans_flat.txt')
) reject limit unlimited;

Now load the external table into the regular tables using any common method such as direct load insert and merge.

Import: Read/Import file by using UTL_FILE

CREATE OR REPLACE PROCEDURE my_app2 IS
   infile              utl_file.file_type;
   outfile             utl_file.file_type;
   buffer              VARCHAR2(30000);
   b_paragraph_started BOOLEAN := FALSE; -- flag to indicate that required paragraph is started
BEGIN
   infile := utl_file.fopen('TEST_DIR', 'mytst.txt', 'r');    -- open a file to read
   outfile := utl_file.fopen('TEST_DIR', 'out.txt', 'w');    -- open a file to write
   IF utl_file.is_open(infile)    -- check file is opened
   THEN       -- loop lines in the file
      LOOP
         BEGIN
            utl_file.get_line(infile, buffer);
            IF buffer LIKE 'foo%' OR b_paragraph_started
            THEN
               utl_file.put_line(outfile, buffer, FALSE);                --write to out.txt
               b_paragraph_started := TRUE;
            END IF;
            IF buffer LIKE '%ZEN'
            THEN
               b_paragraph_started := FALSE;
            END IF;
            utl_file.fflush(outfile);
         EXCEPTION
            WHEN no_data_found THEN EXIT;
         END; END LOOP;
   END IF;
   utl_file.fclose(infile);
   utl_file.fclose(outfile);
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20099, 'Unknown UTL_FILE Error');
END my_app2l;

Export: Write a file using a sql script: Example text file creation

FILEHANDLE        := UTL_FILE.FOPEN('\\10.15.1.197\d\orahome\bin','test.txt','w');
 WRITEMESSAGE  := 'This is created for testing purpose \n' || ' \n This is the second line'; 
 UTL_FILE.PUTF(filehandle,writemessage); 
 UTL_FILE.FFLUSH(filehandle);
 UTL_FILE.FCLOSE(filehandle);
 END;

Export: a file using External files

From the database, create a plain text file with the contents of the table TRANS. The file can be called trans_flat.txt in the directory /home/oracle/dump_dir. Usually this file is created with this SQL:

spool trans_flat.txt
select <column_1> ||','|| <column_2> ||','|| ...
from trans;
spool off

Automating a Process

DBMS_SCHEDULER is an internal Oracle package (since Version 10g) which provides database driven jobs.
It’s divided into 3 parts:

  1. Time schedule part – dbms_scheduler.create_schedule
  2. Program declaration part – dbms_scheduler.create_program
  3. Job (conflation) part -dbms_scheduler.create_job

Examples of the dbms_scheduler.create_schedule part:

 begin -- daily from Monday to Sunday at 22:00 (10:00 p.m.)
 dbms_scheduler.create_schedule
 (schedule_name => 'INTERVAL_DAILY_2200',
  start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
  comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');
 
-- run every hour, every day
 dbms_scheduler.create_schedule(  
 schedule_name  => 'INTERVAL_EVERY_HOUR',  
   start_date    => trunc(sysdate)+18/24,  
   repeat_interval => 'freq=HOURLY;interval=1',  
   comments     => 'Runtime: Every day every hour');  
 -- run every Sunday at 18:00 (06:00 p.m.)
dbms_scheduler.create_schedule
 (schedule_name => 'INTERVAL_EVERY_SUN_1800',
  start_date=> trunc(sysdate)+18/24,
  repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',
  comments=>'Runtime: Run at 6pm every Sunday');
 end;

 Example of the dbms_scheduler.create_program part:

Begin -- Call a procedure of a database package
 dbms_scheduler.create_program
 (program_name=> 'PROG_COLLECT_SESS_DATA',
  program_type=> 'STORED_PROCEDURE',
  program_action=> 'pkg_collect_data.prc_session_data',
  enabled=>true,
  comments=>'Procedure to collect session information'
  );
 end;

 Example of the dbms_scheduler.create_job part:

begin
 -- Connect both dbms_scheduler parts by creating the final job
 dbms_scheduler.create_job
  (job_name => 'JOB_COLLECT_SESS_DATA',
   program_name=> 'PROG_COLLECT_SESS_DATA',
   schedule_name=>'INTERVAL_EVERY_5_MINUTES',
   enabled=>true,
   auto_drop=>false,
   comments=>'Job to collect data about session values every 5 minutes');
 end;

Example to run job immediate:

dbms_scheduler.run_job(‘JOB_COLLECT_SESS_DATA’,TRUE);

 

 

 UTL_FILE

Export/write a text file from pl sql block using UTL_FILE and then create a procedure for this and then create a job and then schedule the job to make this automatic.

UTL_FILE: With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

Subprogram Description
FCLOSE Closes a file
FCOPY Copies a contiguous portion of a file to a newly created file
FFLUSH Physically writes all pending output to a file
FGETATTR Reads and returns the attributes of a disk file
FOPEN Function Opens a file for input or output
FREMOVE Deletes a disk file, assuming that you have sufficient privileges
FRENAME Renames an existing file to a new name, similar to the UNIX mv function
FSEEK Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE Reads text from an open file
GET_LINE_NCHAR Reads text in Unicode from an open file
GET_RAW Function Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
NEW_LINE Writes one or more operating system-specific line terminators to a file
PUT Writes a string to a file
PUT_LINE Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR Writes a Unicode line to a file
PUT_NCHAR Writes a Unicode string to a file
PUTF PUT with formatting
PUT_RAW Function Accepts as input a RAW data value and writes the value to the output buffer

Collections:

Collections are Oracle's version of arrays; collections are single-dimensioned lists. To create a collection or record variable, you first define a collection or record type, and then you declare a variable of that type. In a collection, the internal components are always of the same data type, and are called elements.
Nested Tables, they are the most common form of collection and so represent a useful basis of comparison. A nested table is a variable which can hold more than one instance of something, often a record from a database table. They might be declared like this:

type emp_nt is table of emp%rowtype;

emp_rec_nt emp_nt;

Use: to store multiple instances of data against which we want to do the same thing. The classic example is using BULK COLLECT to store multiple records:

select * bulk collect into emp_rec_nt from employees;

 An Index By table / Associative Array: These are simple collections of single attributes with an index. Nested tables also have indexes but their indexes are just row counts. With an associative array the index can be meaningful, i.e. sourced from a data value. So they are useful for caching data values for later use. The index can be a number, or (since 9iR2) a string which can be very useful. For instance, here is an associative array of salaries which is indexed by the employee identifier.

type emp_sal_aa is table of emp.sql%type      index by emp.empno%type;

l_emp_sales emp_sal_aa;

Elements of an array can identified by an index value, in this case EMPNO:

l_emp_sals(l_emp_no) := l_emp_sal;

Other than caching reference tables or similar look-up values there aren't many use cases for associative arrays.
Variable arrays are just nested tables with a pre-defined limit on the number of elements. So perhaps the name is misleading: they are actually fixed arrays. They are declared like this:
type emp_va is varray(14) of emp%rowtype;
emp_rec_va emp_va;
We can use bulk collect to populate a VArray ...
select * bulk collect into emp_rec_va from employees;
The query will return the no of elements specified in the VArray's declaration otherwise ORA-22165: given index [string] must be in the range of [string] to [string]  will be thrown.
Use: same as Nested Table, here we can set the limit and another one big advantage of VArrays over nested tables is that they guarantee the order of the elements. So if you must get elements out in the same order as you inserted them use a VArray.

Autonomous Transaction

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.
Routine can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions. Top-level (not nested) anonymous PL/SQL block. The routine can be Standalone, packaged, or nested subprogram Method of a SQL object type or Database trigger

Example

CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON tab1
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
  COMMIT; -- only allowed in autonomous triggers
 END; / 

Use: that with the above example will insert and commit log entries – even if the main transaction is rolled-back! One situation in which autonomous transactions can prove extremely useful is with auditing and debugging PL/SQL.

Materialized View

Materialized views are disk based and are updated periodically based upon the query definition. Normal Views are virtual only and run the query definition each time they are accessed.
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;
The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.
Use: Performance, on queries performing aggregations and transformations of the data. This allows the work to be done once and used repeatedly by multiple sessions, reducing the total load on the server
Queries to large tables using joins. These operations are very expensive in terms of time and processing power. The type of materialized view that is created determines how it can be refreshed and used by query rewrite.
Limitations: must define column names explicitly; you cannot include a SELECT * .
Do not include columns defined as TIMESTAMP WITH TIME ZONE in the materialized view. The value of the time_zone_adjustment option varies between connections based on their location and the time of year, resulting in incorrect results and unexpected behavior.
When creating a materialized view, the definition for the materialized view cannot contain, references to other views, materialized or not , references to remote or temporary tables, variables such as CURRENT USER; all expressions must be deterministic, calls to stored procedures, user-defined functions, or external functions

Mutating Exception

ORA-04091 (table xxx is mutating. Trigger/function might not see it): The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating.
CREATE OR REPLACE TRIGGER TUA_TEST
 AFTER UPDATE OF STATUS ON TEST
 FOR EACH ROW – This is the culprit here
 DECLARE
 v_Count NUMBER;
 BEGIN
SELECT count(*) INTO v_count FROM TEST WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
 END;
 /
Throwing the exception by updating the status to ‘INVALID’
update test set status = 'INVALID'  where object_name = 'TEST1'; 
ERROR at line 1:
 ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
 
 Different ways to handle mutating table errors
  1. First one is to create statement level trigger instead of row level. If we omit the ‘for each row’ clause from above trigger, it will become statement level trigger. Let us create a new statement level trigger.
CREATE OR REPLACE TRIGGER TUA_TEST
 AFTER UPDATE OF STATUS ON TEST
 DECLARE
v_Count NUMBER;
 BEGIN
SELECT count(*) INTO v_count FROM TEST WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
 END;
 2. Second way of dealing with the mutating table issue is to declare row level trigger as an autonomous transaction so that it is not in the same scope of the session issuing DML statement. Following is the row level trigger defined as pragma autonomous transaction. By defining row level trigger as an autonomous transaction, we got rid of mutating table error but result is not correct. The latest updates are not getting reflected in our result set as oppose to statement level trigger. So one has to be very careful when using this approach.
  1. In version 11g, Oracle made it much easier with introduction of compound triggers. Let us see in this case how a compound trigger can resolve mutating table error. Let’s create a compound trigger first:
CREATE OR REPLACE TRIGGER TEST_TRIG_COMPOUND
FOR UPDATE ON TEST
COMPOUND TRIGGER
/* Declaration Section*/
v_count NUMBER;
AFTER EACH ROW IS
BEGIN
dbms_output.put_line(‘Update is done’);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
SELECT count(*) INTO v_count FROM TEST WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END AFTER STATEMENT;
END TEST_TRIG_COMPOUND;
/
SET Operator:  UNION, INTERSECT, MINUS, UNION ALL, used on complex queries
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports). The implication of this, is that union is much less performant as it must scan the result for duplicates

Triggers

A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
A database definition (DDL) statement (CREATE, ALTER, or DROP)
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
Use:Automatically generate derived column values, Enforce referential integrity across nodes in a distributed database, Enforce complex business rules, Provide transparent event logging, Provide auditing, Maintain synchronous table replicates, Gather statistics on table access, Modify table data when DML statements are issued against views, Publish information about database events, user events, and SQL statements to subscribing applications, Restrict DML operations against a table to those issued during regular business hours, Enforce security authorizations and Prevent invalid transactions

Limitations/points to be considered when creating trigger

  1. A SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers,
  2. It should not be generate mutating behavior.
  3. Analyze to using the autonomous transaction?
  4. Trigger Size Restriction, The size of the trigger cannot exceed 32K. In this case create an procedure.
  5. A trigger cannot declare a variable of the LONG or LONG RAW data type. A SQL statement in a trigger can reference a LONG or LONG RAW column only if the column data can be converted to the data type CHAR or VARCHAR2. A trigger cannot use the correlation name NEW or PARENT with a LONG or LONG RAW column.
  6. One of the most dangerous attributes of a database trigger is its hidden behavior, we do not know it was fired or not, if it was disabled due to change in a table.

Cursors

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc.
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement
Using the SELECT-INTO statement: implicit cursor
Fetching from an explicit cursor: A SELECT-INTO is also referred to as an implicit query, because Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that (or when an exception is raised). You can, alternatively, explicitly declare a cursor and then perform the open, fetch, and close operations yourself.
DECLARE
 l_total       INTEGER := 10000;
      CURSOR employee_id_cur
       IS
           SELECT employee_id FROM plch_employees ORDER BY salary ASC;
      l_employee_id   employee_id_cur%ROWTYPE;
  BEGIN
     OPEN employee_id_cur;
     LOOP
        FETCH employee_id_cur INTO l_employee_id;
        EXIT WHEN employee_id_cur%NOTFOUND;
        assign_bonus (l_employee_id, l_total);
        EXIT WHEN l_total <= 0;
     END LOOP;
     CLOSE employees_cur;
  END; 
Using a cursor FOR loop: the cursor FOR loop is an elegant and natural extension of the numeric FOR loop in PL/SQL. With a numeric FOR loop, the body of the loop executes once for every integer value between the low and high values specified in the range. With an implicit cursor FOR loop, the body of the loop is executed for each row returned by the query.
The following block uses a cursor FOR loop to display the last names of all employees in department 10:
BEGIN
   FOR employee_rec IN (SELECT * FROM employeesWHERE department_id = 10)
   LOOP       DBMS_OUTPUT.put_line (employee_rec.last_name);
 END LOOP; 
END;

You can also use a cursor FOR loop with an explicitly declared cursor:
DECLARE
   CURSOR employees_in_10_cur 
   IS
      SELECT * FROM employees WHERE department_id = 10;
BEGIN
   FOR employee_rec 
   IN employees_in_10_cur
   LOOP
      DBMS_OUTPUT.put_line (employee_rec.last_name);
   END LOOP;
END;
The nice thing about the cursor FOR loop is that Oracle Database opens the cursor, declares a record by using %ROWTYPE against the cursor, fetches each row into a record, and then closes the loop when all the rows have been fetched (or the loop terminates for any other reason).
Using cursor variables: A cursor variable is, as you might guess from its name, a variable that points to a cursor or a result set. Unlike with an explicit cursor, you can pass a cursor variable as an argument to a procedure or a function. There are several excellent use cases for cursor variables, including the following:
Construct a result set inside a function, and return a cursor variable to that set. This is especially handy when you need to use PL/SQL, in addition to SQL, to build the result set.
Pass a cursor variable to a pipelined table function—a powerful but quite advanced optimization technique. A full explanation of cursor variables, including the differences between strong and weak REF CURSOR types, is beyond the scope of this article.
Cursor variables can be used with either embedded (static) or dynamic SQL.
CREATE OR REPLACE FUNCTION names_for (
         name_type_in IN VARCHAR2)
      RETURN SYS_REFCURSOR
   IS
      l_return   SYS_REFCURSOR;
   BEGIN
      CASE name_type_in
         WHEN 'EMP'
         THEN
           OPEN l_return FOR
                SELECT last_name FROM employees ORDER BY employee_id;
        WHEN 'DEPT'
        THEN
           OPEN l_return FOR
                SELECT department_name FROM departments ORDER BY department_id;
     END CASE;
     RETURN l_return;
  END names_for;

Strong and weak REF CURSOR types

Ref cursor is a cursor variable which acts as a pointer to the sql memory area. Ref cursor can be asssociated with multiple sql statements where as a cursor can be associated with only one sql statement. Refcursor is dynamic where as cursor is static. Ref cursors can be typed/strong and untyped/weak:
A strongly typed ref cursor always returns a known type, usually from a declared TYPE object. The compiler can find problems in a PL/SQL block by comparing the types returned to how they are used.
A weakly typed ref cursor has a return type that is dependent on the SQL statement it executes, i.e. only once the cursor is opened is the type known (at runtime). The compiler cannot determine the types until it is ran, so care must be taken to ensure that the cursor result set is handled properly to avoid runtime errors.

User Defined Exception

Is it possible to create user-defined exceptions and be able to change the SQLERRM? Yes, You could use RAISE_APPLICATION_ERROR like this:
DECLARE
    ex_custom       EXCEPTION;
BEGIN
    RAISE ex_custom;
EXCEPTION
    WHEN ex_custom THEN
        RAISE_APPLICATION_ERROR(-20001,'My exception was raised');
END;
That will raise an exception that looks like: ORA-20001: My exception was raised, The error number can be anything between -20001 and -20999.
How to catch and handle only specific Oracle exceptions? Refer to the exception directly by number:
BEGIN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
        NULL; -- suppresses ORA-00955 exception
      ELSE
         RAISE;
      END IF;
END;

BULK COLLECT

BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine. Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch. Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values. The UPDATE statement in the increase_salary procedure fits this scenario; the only thing that changes with each new execution of the statement is the employee ID. PL/SQL collections are essentially arrays in memory, so massive collections can have a detrimental effect on system performance due to the amount of memory they require.  In some situations, it may be necessary to split the data being processed into chunks to make the code more memory-friendly.   This “chunking” can be achieved using the LIMIT clause of the BULK COLLECT syntax.
The bulk_collect_limit.sql script uses the LIMIT clause to split the collection into chunks of 10,000; processing each chunk in turn.  Notice the use of the explicit cursor for this operation.
FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly
CREATE OR REPLACE PROCEDURE increase_salary (
      department_id_in   IN employees.department_id%TYPE,
      increase_pct_in    IN NUMBER)
   IS
      TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE
              INDEX BY PLS_INTEGER; 
      l_employee_ids   employee_ids_t;
      l_eligible_ids   employee_ids_t;
     l_eligible       BOOLEAN;
  BEGIN
     SELECT employee_id
       BULK COLLECT INTO l_employee_ids
       FROM employees
      WHERE department_id = increase_salary.department_id_in;
     FOR indx IN 1 .. l_employee_ids.COUNT
     LOOP
        check_eligibility (l_employee_ids (indx),
                           increase_pct_in,
                           l_eligible);
        IF l_eligible
        THEN
           l_eligible_ids (l_eligible_ids.COUNT + 1) :=
              l_employee_ids (indx);
        END IF;
     END LOOP;
     FORALL indx IN 1 .. l_eligible_ids.COUNT
        UPDATE employees emp
           SET emp.salary =
                    emp.salary
                  + emp.salary * increase_salary.increase_pct_in
         WHERE emp.employee_id = l_eligible_ids (indx);
  END increase_salary;
Steps to generate Excel sheet output
  1. Create a custom DAD if required using Enterprise Manager Console of MidTier for HTTP Server or use Portal DAD itself to implement the solution.
     Create a new procedure (a webdb solution) to stream the HTML for the excel sheet report which will be downloaded.
     CREATE OR REPLACE  PROCEDURE generateCsv as
     cursor p_emp is select * from PORTAL_DEMO.EMP;
     begin
     owa_util.mime_header( 'application/vnd.ms-excel', False ); -- Here change the content-type to PDF Format accordingly
     htp.print('Content-Disposition: attachment;filename="pvasista.csv"');
     owa_Util.Http_Header_Close;
     for v_emp_cur in p_emp
     LOOP
     htp.p (v_emp_cur.ename || ',' || v_emp_cur.deptno || chr(13));
     END LOOP;
     end;
Probably we can apply appropriate content-type for PDF in the PL/SQL written below, to get the same in PDF. i suppose it should be application/pdf
Sending email: UTL_MAIL: The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt.
SEND Procedure
Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients
SEND_ATTACH_RAW Procedure
Represents the SEND Procedure overloaded for RAW attachments
SEND_ATTACH_VARCHAR2 Procedure
Represents the SEND Procedure overloaded for VARCHAR2 attachments
Most used functions: Merge, NVL, Decode

What is REST and when it becomes RESTfull?

REST is the underlying architectural principle of the web. The amazing thing about the web is the fact that clients (browsers) and servers can interact in complex ways without the client knowing anything beforehand about the server and the resources it hosts. The key constraint is that the server and client must both agree on the media used, which in the case of the web is HTML.

An API that adheres to the principles of REST does not require the client to know anything about the structure of the API. Rather, the server needs to provide whatever information the client needs to interact with the service. An HTML form is an example of this: The server specifies the location of the resource, and the required fields. The browser doesn’t know in advance where to submit the information, and it doesn’t know in advance what information to submit. Both forms of information are entirely supplied by the server. (This principle is called HATEOAS.)

So, how does this apply to HTTP, and how can it be implemented in practice? HTTP is oriented around verbs and resources. The two verbs in mainstream usage are GET and POST, which I think everyone will recognize. However, the HTTP standard defines several others such as PUT and DELETE. These verbs are then applied to resources, according to the instructions provided by the server.

For example, Let’s imagine that we have a user database that is managed by a web service. Our service uses a custom hypermedia based on JSON, for which we assign the mimetype application/json+userdb(There might also be an application/xml+userdb and application/whatever+userdb – many media types may be supported). The client and the server has both been programmed to understand this format, but they don’t know anything about each other. As Roy Fielding points out:

A REST API should spend almost all of its descriptive effort in defining the media type(s) used for representing resources and driving application state, or in defining extended relation names and/or hypertext-enabled mark-up for existing standard media types.

A request for the base resource / might return something like this:

Request

GET /
Accept: application/json+userdb

Response

200 OK
Content-Type: application/json+userdb

{
    "version": "1.0",
    "links": [
        {
            "href": "/user",
            "rel": "list",
            "method": "GET"
        },
        {
            "href": "/user",
            "rel": "create",
            "method": "POST"
        }
    ]
}

We know from the description of our media that we can find information about related resources from sections called “links”. This is called Hypermedia controls. In this case, we can tell from such a section that we can find a user list by making another request for /user:

Request

GET /user
Accept: application/json+userdb

Response

200 OK
Content-Type: application/json+userdb

{
    "users": [
        {
            "id": 1,
            "name": "Emil",
            "country: "Sweden",
            "links": [
                {
                    "href": "/user/1",
                    "rel": "self",
                    "method": "GET"
                },
                {
                    "href": "/user/1",
                    "rel": "edit",
                    "method": "PUT"
                },
                {
                    "href": "/user/1",
                    "rel": "delete",
                    "method": "DELETE"
                }
            ]
        },
        {
            "id": 2,
            "name": "Adam",
            "country: "Scotland",
            "links": [
                {
                    "href": "/user/2",
                    "rel": "self",
                    "method": "GET"
                },
                {
                    "href": "/user/2",
                    "rel": "edit",
                    "method": "PUT"
                },
                {
                    "href": "/user/2",
                    "rel": "delete",
                    "method": "DELETE"
                }
            ]
        }
    ],
    "links": [
        {
            "href": "/user",
            "rel": "create",
            "method": "POST"
        }
    ]
}

We can tell a lot from this response. For instance, we now know we can create a new user by POSTing to /user:

Request

POST /user
Accept: application/json+userdb
Content-Type: application/json+userdb

{
    "name": "Karl",
    "country": "Austria"
}

Response

201 Created
Content-Type: application/json+userdb

{
    "user": {
        "id": 3,
        "name": "Karl",
        "country": "Austria",
        "links": [
            {
                "href": "/user/3",
                "rel": "self",
                "method": "GET"
            },
            {
                "href": "/user/3",
                "rel": "edit",
                "method": "PUT"
            },
            {
                "href": "/user/3",
                "rel": "delete",
                "method": "DELETE"
            }
        ]
    },
    "links": {
       "href": "/user",
       "rel": "list",
       "method": "GET"
    }
}

We also know that we can change existing data:

Request

PUT /user/1
Accept: application/json+userdb
Content-Type: application/json+userdb

{
    "name": "Emil",
    "country": "Bhutan"
}

Response

200 OK
Content-Type: application/json+userdb

{
    "user": {
        "id": 1,
        "name": "Emil",
        "country": "Bhutan",
        "links": [
            {
                "href": "/user/1",
                "rel": "self",
                "method": "GET"
            },
            {
                "href": "/user/1",
                "rel": "edit",
                "method": "PUT"
            },
            {
                "href": "/user/1",
                "rel": "delete",
                "method": "DELETE"
            }
        ]
    },
    "links": {
       "href": "/user",
       "rel": "list",
       "method": "GET"
    }
}

Notice that we are using different HTTP verbs (GET, PUT, POST, DELETE etc.) to manipulate these resources, and that the only knowledge we presume on the clients part is our media definition.

Further reading:

(This answer has been subject of a fair amount of criticism for missing the point. For the most part, that has been a fair critique. What I originally described was more in line with how REST was usually implemented a few years ago when I first wrote this, rather than its true meaning. I’ve revised the answer to better represent the real meaning.)

Guid vs Identity columns (Ints)

SQL Studies

I came across an interesting question on SE last week. Guid vs INT – Which is better as a primary key? In addition to the quite good accepted answer I thought I would throw in my own take.

  • Size
    • GUIDs are 16 bytes and hold more values you then could ever use.
    • With an identity column you can choose a data type dependent on your need.
      • tinyint 1 byte 0-255
      • smallint 2 bytes -2^15 (-32,768) to 2^15-1 (32,767)
      • int 4 bytes -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
      • bigint 8 bytes -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

    Remember that the size of your column affects not just how much space the table takes up but how many pages (both index and data) need to be read to perform a given operation. Bigger the column the less you can fit in a page, the more pages need to be read, the slower your queries…

View original post 458 more words

Calling an oData Batch using .net HttpClient class

You have full code here, no theory!

Private Async Function SaveEntityAsBatch() As Threading.Tasks.Task
Using Client As New HttpClient()
Dim APIhostUri As String = “http://localhost:2025/&#8221;
Dim APIResponse As HttpResponseMessage
Client.BaseAddress = New Uri(APIhostUri)
Dim content As HttpContent

‘Required if you need dates serialization

Dim microsoftDateFormatSettings As JsonSerializerSettings = New JsonSerializerSettings With {
.DateFormatHandling = DateFormatHandling.IsoDateFormat,
.DateParseHandling = DateParseHandling.DateTimeOffset, .DateTimeZoneHandling = DateTimeZoneHandling.Utc
}

‘ Batch Process
Dim boundary As String = “–changeset_Data—”
Dim batchContent As New MultipartContent(“mixed”, “–batch” & boundary)

‘Project
Dim RqP As New HttpRequestMessage(HttpMethod.Put, “URL(id)/”)
RqP.Content = New StringContent(Newtonsoft.Json.JsonConvert.SerializeObject(projectRequest, microsoftDateFormatSettings))
If RqP.Content.Headers.Contains(“Content-Type”) Then RqP.Content.Headers.Remove(“Content-Type”)
RqP.Content.Headers.Add(“Content-Type”, “application/json”)
Dim ReqContent As New HttpMessageContent(RqP)
If ReqContent.Headers.Contains(“Content-Type”) Then ProjectContent.Headers.Remove(“Content-Type”)
ReqContent.Headers.Add(“Content-Type”, “application/http”)
ReqContent.Headers.Add(“Content-Transfer-Encoding”, “binary”)
ReqContent.Headers.Add(“client-request-id”, “12345678”)
ReqContent.Headers.Add(“return-client-request-id”, “True”)
ReqContent.Headers.Add(“Content-ID”, “1”)
ReqContent.Headers.Add(“DataServiceVersion”, “3.0”)
If ReqContent.Headers.Contains(“contentTypeMime-Part”) Then ReqContent.Headers.Remove(“contentTypeMime-Part”)
ReqContent.Headers.Add(“contentTypeMime-Part”, “Content-Type:application/http;”)

batchContent.Add(ReqContent)

‘Report1

Dim RqR As New HttpRequestMessage(HttpMethod.Put, “URL2/”, PtsAPIhostUri)
RqR.Content = New StringContent(Newtonsoft.Json.JsonConvert.SerializeObject(report, microsoftDateFormatSettings))
If RqR.Content.Headers.Contains(“Content-Type”) Then RqR.Content.Headers.Remove(“Content-Type”)
RqR.Content.Headers.Add(“Content-Type”, “application/json”)
Dim ReqContent2 As New HttpMessageContent(RqR)

If ReqContent2.Headers.Contains(“Content-Type”) Then ReportContent.Headers.Remove(“Content-Type”)
ReqContent2.Headers.Add(“Content-Type”, “application/http”)
ReqContent2.Headers.Add(“Content-Transfer-Encoding”, “binary”)
ReqContent2.Headers.Add(“client-request-id”, “99999”)
ReqContent2.Headers.Add(“return-client-request-id”, “True”)
ReqContent2.Headers.Add(“Content-ID”, “2”)
ReqContent2.Headers.Add(“DataServiceVersion”, “3.0”)

If ReqContent2.Headers.Contains(“contentTypeMime-Part”) Then ReqContent2.Headers.Remove(“contentTypeMime-Part”)
ReqContent2.Headers.Add(“contentTypeMime-Part”, “Content-Type:application/http;”)
batchContent.Add(ReqContent2)
Dim batchRequest As HttpRequestMessage = New HttpRequestMessage(HttpMethod.Post, APIhostUri & “/$batch/”)
batchRequest.Content = batchContent
Dim APIResponseBatch = Await Client.SendAsync(batchRequest)
Dim streamProvider = APIResponseBatch.Content.ReadAsMultipartAsync().Result()

For Each cnt As HttpContent In streamProvider.Contents
Console.WriteLine(cnt.ReadAsStringAsync().Result)
Next

End Using
End Function

I got following errors in given sequences and I continued to add the header and finally I was able to two PUT request in Batch.

Error #1

The ‘Content-Type’ header value ‘application/http; msgtype=request’ is invalid. When this is the start of the change set, the value must be ‘multipart/mixed’; otherwise it must be ‘application/http’.

Error #2

The content type ‘multipart/mixed’ specifies a batch payload; however, the payload either does not include a batch boundary or includes more than one boundary. In OData, batch payload content types must specify exactly one batch boundary in the ‘boundary’ parameter of the content type.

Error #3

Nested change sets in a batch payload are not supported.

Error #3 took most of the time to find out this URL https://msdn.microsoft.com/en-us/library/azure/dn878159.aspx from MSDN, which speaks about adding following Header Content Types

ReqContent2.Headers.Add(“client-request-id”, “99999”)
ReqContent2.Headers.Add(“return-client-request-id”, “True”)
ReqContent2.Headers.Add(“Content-ID”, “2”)
ReqContent2.Headers.Add(“DataServiceVersion”, “3.0”)

After adding above four types to header Error #3 was gone and my Batch was working.

The URL from MSDN says;

Add Tasks requests in Batch service support the following concepts:

  • Each request must contain a unique Content-ID MIME part header. This header is returned with the result of the operation in the response, and can be used to match an individual request with its response.
  • The Batch service returns a HTTP Status Code 400 (Bad Request) if any request contains an invalid set of headers or contains operations which are not supported in the batch.
  • The Batch service returns a HTTP Status Code 202 (Accepted) for a valid Add Tasks request. The server will then stream the results of individual operations.
  • The Batch service can re-order the responses of these Add Task requests. The Content-Id MIME part header needs to be used by the Client to match the request corresponding to the response. The response contains the results and error information for each operation.If the server times out or the connection is closed during an Add Tasks request, the request may have been partially or fully processed, or not at all. In such cases, the user should re-issue the request. Note that it is up to the users to correctly deal with failures when re-issuing a request. For example, the users should use the same task names during retry, so that if the prior operation succeeded, the retry will not create extra tasks unexpectedly.
  • An Add Tasks request can include at most 100 operations.
  • All the tasks in an Add Tasks request must belong to the same workitem and job.