Software Applications Programming Errors and solutions.

This example will tell you How to use cursor in stored procedure in MS SQL. Cursor is complex terms in sql but when you start to use it its become simple. You can understand cursor well by using following example. DECLARE @ testcols1 AS varchar(30) DECLARE @ testcols2 AS varchar(30) DECLARE c_Cursor CURSOR FOR SELECT Col1, Col2 from Customer OPEN c_ Cursor FETCH NEXT FROM c_ Cursor INTO @ testcols1, @ testcols2 WHILE @@FETCH_STATUS=0 BEGIN Exec ('select ' + @ testcols1 + ' , ' + @vcol2 + ' from CustE) FETCH NEXT FROM c_ Cursor INTO @ testcols1, @ testcols2 END CLOSE c_ Cursor DEALLOCATE c_ Cursor

Code to send email using PLSQL You can use UTL_SMTP and UTL_TCP to send email attachment from Oracle Version 8i onwards. Sample Code: DECLARE v_From VARCHAR2(80) := 'oracle@mycompany.com'; v_Recipient VARCHAR2(80) := 'test@mycompany.com'; v_Subject VARCHAR2(80) := 'test subject'; v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com'; v_Mail_Conn utl_smtp.Connection; crlf VARCHAR2(2) := chr(13)||chr(10); BEGIN v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25); utl_smtp.Helo(v_Mail_Conn, v_Mail_Host); utl_smtp.Mail(v_Mail_Conn, v_From); utl_smtp.Rcpt(v_Mail_Conn, v_Recipient); utl_smtp.Data(v_Mail_Conn, 'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf || 'From: ' || v_From || crlf || 'Subject: '|| v_Subject || crlf || 'To: ' || v_Recipient || crlf || 'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard 'Content-Type: multi...

declare start_date date; end_date date; begin select min(sales_date),max(sales_date) into start_date, end_date from t1; for start_date <= end_date loop Insert Into t1 (ShopName , sales_Date, Sales) Select t2.ShopName , nvl(t1.sales_Date,start_date), nvl(t1.Sales,0) From t1, t2 Where t1.sales_Date=start_date; start_date:=start_date+1; end loop; end;