Systems Engineering and RDBMS

Archive for August 6th, 2007

SQL 2008 July CTP

Posted by decipherinfosys on August 6, 2007

Microsoft has included a very nice chart for the feature improvements in SQL Server 2008. You can access it here:

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5470

One can click on any of the improvements listed on this chart to see what they are all about. The addition of the TVP (same as Oracle), better support for displaying object dependencies, the support for the date and time data-types, better hierarchical data support and database mirroring enhancements are all very good improvements. As we play with these new features in the days to come, we will start posting code snippets for them.

Posted in SQL Server | Leave a Comment »

Oracle – Returning a recordset from a stored procedure

Posted by decipherinfosys on August 6, 2007

In Oracle, returning a record set from a stored procedure to the calling client application is not as straight forward as simply doing a select from a table in the stored procedure code.  One needs to make use of a reference cursor.  In this blog post, we will cover how one can achieve this in Oracle.  First, let us create a table and populate it with small set of data.

CREATE TABLE Invoice
(
INVOICE_NUMBER      NUMBER(9) NOT NULL,
INVOICE_DATE        DATE NOT NULL,
CLIENT_ID           NUMBER(9)     NOT NULL,
INVOICE_AMT         NUMBER(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG           NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
— TABLESPACE Clause
/

CREATE SEQUENCE INVOICE_SEQ
START WITH 1
CACHE 100
/

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,101,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,102,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,103,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,104,1100.00);

Oracle uses cursor variables to pass query result sets between PL/SQL sub programs and to the client application. A cursor variable has data type REF CURSOR and that is what is normally known as a ref cursor (we will cover more on the ref cursors in one of our future posts). Following is the code snippet to return a result set from the stored procedure.

To create a cursor variable first we need to define a REF CURSOR type and then declare the cursor variable of that type.  To avoid declaring the same REF CURSOR type in each program, we will create one global type in a package specification and declare cursor of that type in our procedure.

Run following scripts to create package and procedure from SQL*Plus prompt. Testing script is to test the execution of the procedure.

/* Create package */
CREATE OR REPLACE PACKAGE types
AS
type cursorType is ref cursor;
END;
/

— Here we have declared cursor variable of type cursorType as an output variable.
CREATE OR REPLACE PROCEDURE DEC_RTN_RECORDSET
(
p_InvoiceDate      IN   DATE,
p_ResultSet        OUT  TYPES.cursorType
)
AS
BEGIN
OPEN p_ResultSet FOR
SELECT Invoice_Number, Invoice_Date, Client_ID, Invoice_Amt
FROM Invoice
WHERE Invoice_date <= p_InvoiceDate
ORDER BY Invoice_number;

END DEC_RTN_RECORDSET;
/

— Testing
VARIABLE resultSet  REFCURSOR
EXEC DEC_RTN_RECORDSET(sysdate, :resultSet);
PRINT :resultSet

Following is the output. It is formatted for more readability.

Invoice_Number       Invoice_Date  Client_ID     Invoice_Amt
————–       ————  ———     ———–
1       25-JAN-07           101            1100
2       25-JAN-07           102            1100
3       25-JAN-07           103            1100
4       25-JAN-07           104            1100

Posted in Oracle | 2 Comments »