Archive for the ‘Oracle’ Category

ProcessMaker: trigger to insert into Oracle

$db = '9324682434f8896adf05bc2078458052';//database connection UID
$idarea = 44;
$depid=@@DEPID;
$monto=@@MONTO;
$fecha=@@FECHA;

$result = executeQuery(“INSERT INTO PM_REEMBOLSO_CAJA_CHICA (ID_AREA,id_pmaker,monto,fecha)
VALUES (‘$idarea’,’$depid’,’$monto’,’$fecha’) “, $db);

Error when insert chinese charactor into oracle in a trigger

Database Connections

 

Oracle TO_CHAR function

“In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.”

Oracle/PLSQL: To_Char Function

Week of Year and Day to DATE (Oracle)

Jeni — Thanks for the question regarding “Date range per week”, version 8.1.7

select dt, to_char(dt+1,'iw') from(
select to_date('14-mar-2012') + rownum dt from all_objects where rownum < 45);

SQL query build calendar

CREATE      procedure [dbo].[CalMonthByYearMonth] (@YEAR INT, @MONTH INT) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=@MONTH
SET @INPUTDATE=DATEADD(MM,@MONTH - 1,@INPUTDATE)

SET @COUNT=1
SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)
SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
SET @STARTWEEK=DATEPART(WEEK,@INPUTDATE)
DECLARE @CURRWEEK INT
DECLARE @CUR INT
CREATE TABLE #TEMP(
WEEK VARCHAR(10),
SUNDAY VARCHAR(10),
MONDAY VARCHAR(10),
TUESDAY VARCHAR(10),
WEDNESDAY VARCHAR(10),
THURSDAY VARCHAR(10),
FRIDAY VARCHAR(10),
SATURDAY VARCHAR(10),
YEARWEEK VARCHAR(10))
DECLARE @wkcount int
DECLARE @weeksinmonth int
DECLARE @EXEC NVARCHAR(2000)
SET @WKCOUNT=1
SET @weeksinmonth=datediff(week, @date, @lastdate) + 1
WHILE @wkcount<= @weeksinmonth
begin
INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', @STARTWEEK + @WKCOUNT - 1)
SET @WKCOUNT=@WKCOUNT + 1
end
WHILE @COUNT<=@MONTHDAYCOUNT
BEGIN
SET @DAY=DATENAME(WEEKDAY,@DATE)
IF @STARTWEEK=DATENAME(WEEK,@DATE)
SET @CURRWEEK=1
ELSE
BEGIN
SET @CUR=DATENAME(WEEK,@DATE)
SET @CURRWEEK=(@CUR-@STARTWEEK)+1
END

SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
EXEC SP_EXECUTESQL @EXEC
SET @DATE=DATEADD(DD,1,@DATE)
SET @COUNT=@COUNT + 1
END
UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'
SELECT Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday --, YEARWEEK
--SELECT Monday, Tuesday, Wednesday, Thursday, Friday, YEARWEEK
FROM #TEMP order by week
DROP TABLE #TEMP

 

References:

 

What is a good (cheap/free) calendar/schedule control for asp.net?

Creating a Calendar in a single SQL statement

T-SQL: Generate Sequences in a Loop Query

T-SQL Query with calendar table

How to create a Calender table for 100 years in Sql

Oracle analytic functions: lead and lag

Oracle “LEAD” function, according to LAG and LEAD Analytic Functions, is defined as:

“The LEAD function is used to return data from the next row.”

LAG funtion is defined as:

“The LAG function is used to access data from a previous row.”

The following example loops through a payroll table, to get the oldest continuous date for each employee, comparing the current date with the next, and stopping if there’s a difference of more than one day between them. For this, the best solution I found was to use the LEAD function.

create or replace
PROCEDURE PA_PROCESAR_INGRESOS
IS
CURSOR CURSOR1 IS
SELECT DISTINCT IDPERSONAL FROM MAESTROPERSONAL WHERE NVL(ANULADO,0)=1 AND NVL(FECHAINGRESO,'01/01/9000')<>NVL(FECHACESE,'01/01/9000')
ORDER BY IDPERSONAL;

CURSOR CURSOR2 (V_PERSONA IN NUMBER) IS
SELECT CODIGOEMPLEADO,IDPERSONAL,IDEMPRESA,FECHAINGRESO,LEAD(FECHACESE, 1,'01/01/9000') OVER (ORDER BY FECHAINGRESO DESC) AS NEXT_FEC_CESE,
abs((FECHAINGRESO - (LEAD(FECHACESE, 1,'01/01/9000') OVER (ORDER BY FECHAINGRESO DESC)))) as dias,
FECHACESE,NVL(FLAGACTIVO,'N') AS FLAGACTIVO
FROM MAESTROPERSONAL
WHERE NVL(ANULADO,0)=1 AND IDPERSONAL=V_PERSONA AND NVL(FECHAINGRESO,'01/01/9000')<>NVL(FECHACESE,'01/01/9000')
ORDER BY FECHAINGRESO DESC;

VALOR NUMBER;
FECHA_INGRESO DATE;
IDPERSONAL NUMBER;
BEGIN

--DELETE FROM ingresosconsolidados;

FOR C_1 IN CURSOR1
LOOP
FOR C_2 IN CURSOR2(C_1.IDPERSONAL)
LOOP
if c_2.dias = 1 THEN
FECHA_INGRESO:=c_2.FECHAINGRESO;
IDPERSONAL:=c_2.IDPERSONAL;
else
FECHA_INGRESO:=c_2.FECHAINGRESO;
IDPERSONAL:=c_2.IDPERSONAL;
exit;
end if;
END LOOP;
INSERT INTO INGRESOSCONSOLIDADOS (IDPERSONAL,FECHAPRIMERINGRESO)
VALUES (IDPERSONAL,FECHA_INGRESO);
END LOOP;

--SELECT IDPERSONAL, FECHAPRIMERINGRESO FROM USER_RRHH.INGRESOSCONSOLIDADOS ;

COMMIT;
END;

Consume SSRS 2008R2 x64 report from ASP.NET app using Oracle ODAC11.2.0.1.0 on IIS 7.5 (Enabled 32 bit apps) on Win7 x64

On my development machine (Win7 x64, IIS 7.5, SSRS 2008R2 x64), I have an ASP.Net app (application pool with “Enable 32-bit apps” set to “True”) connecting to an Oracle database server, using Oracle Data Provider for .Net 32 bits (ODAC 11.2.0.1.0, Oracle.DataAccess 2.112.1.0.). IIS 7.5 and SSRS 2008 R2 run on the same development machine. Oracle runs on a test server.

It works fine, except when I embed a report from SSRS 2008 R2, using ReportViewer, like this:

    reportViewer.ProcessingMode = ProcessingMode.Remote     reportViewer.ServerReport.ReportServerCredentials = New ReportServerCredentials()     Dim serverReport As ServerReport     serverReport = reportViewer.ServerReport     serverReport.ReportPath = "/" + ReportServerCredentials.ReportFolder + "/rptParteIngreso"     serverReport.ReportServerUrl = New Uri(ReportServerCredentials.urlServerReporting)     Dim parameters() As ReportParameter = {P_IDPAIS, P_FECHAINICIO, P_FECHAFIN, P_NROPARTE, P_FLAGAPROBING}     Try         serverReport.SetParameters(parameters)     Catch ex As Exception         MessageBoxs.Show(ex.InnerException.ToString())     End Try 

It throws the following error:

Microsoft.Reporting.WebForms.ReportServerException was unhandled by user code   ErrorCode=rsProcessingAborted   Message=Error al procesar el informe. (rsProcessingAborted)   Source=Microsoft.ReportViewer.WebForms   StackTrace:        en Microsoft.Reporting.WebForms.ServerReportSoapProxy.OnSoapException(SoapException e)        en Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)        en Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.SetExecutionParameters(ParameterValue[] Parameters, String ParameterLanguage)        en Microsoft.Reporting.WebForms.ServerReport.SetParameters(IEnumerable`1 parameters) .....        en System.Web.UI.Control.OnLoad(EventArgs e)        en System.Web.UI.Control.LoadRecursive()        en System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)        ErrorCode=rsErrorOpeningConnection        Message=Cannot create a connection to data source 'DSRRHH'. (rsErrorOpeningConnection)        InnerException: Microsoft.Reporting.WebForms.ReportServerException             Message=El intento de cargar las bibliotecas de clientes de Oracle lanzó BadImageFormatException. **Este problema ocurrirá al ejecutar el modo 64 bits con los componente cliente de Oracle de 32 bits instalados**.             InnerException: Microsoft.Reporting.WebForms.ReportServerException                  Message=Se ha intentado cargar un programa con un formato incorrecto. (Excepción de HRESULT: 0x8007000B) 

The relevant message I think is “This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

Also, on the Task Manager, I’ve checked Reporting Services is running as a 64-bit process(ReportingServicesService.exe) and my app is running on 32 bits (w3wp.exe *32).

However, when designing the report, it works OK, using “.Net Framework Data Provider for Oracle”, which I think is provided by Microsoft.

So, my conclusion is there’s a conflict between my ASP.Net app (running as 32-bit) and my reports published on a SSRS 2008 R2 x64 server.

So, my question is, how can I embed those SSRS reports in my ASP.Net app?

Some alternatives I’ve thought:

  1. Diable “Enable 32-bit apps” on application pool on IIS, so it will run as 64-bits.
  2. Change SSRS process to run as 32-bits. (Is this even posible?)

Also, I’ve published the reports to another server (SSRS 2008 R2 x64 on Win 2008 R2 x64) and I can embed them without any problem from my ASP.Net 32-bits app on my development machine.

Any help would be greatly appreciated.

 

UPDATE:

Solved. Installed Oracle Data Provider for .Net 64 bits (ODAC 11.2.0.1.0, Oracle.DataAccess 2.112.1.0.) and disabled “Enable 32-bit apps” on application pool on IIS, so it will run as 64-bits. This way, it can connects to SSRS 2008 R2 64 bits.

Oracle Update Multiple Columns

If you want to update multiple columns from multiple tables on a single query, the following syntax works OK:

update
(SELECT cata.idcatalogo, pro.idproducto, pro.descripcion,cate.descripcion,cate.factor,cata.score,round(cata.precioultimacompra * cate.factor) as newscore,cata.precioultimacompra,loc.nombre_local
FROM MC_CATALOGO CATA
INNER JOIN mc_producto PRO ON pro.idproducto=cata.idproducto
INNER JOIN mc_categoria CATE ON cate.idcategoria=pro.idcategoria
INNER JOIN LOCALES LOC ON LOC.ID_LOCAL=cata.idlocal
WHERE loc.idpais_mc=1 AND cate.factor>0 AND CATE.ESTADO=1)
set score= newscore;

I’m using a little bit complex query with 3 INNER JOINS relating 4 tables, but updating one column in 1 table. This will work for several columns also.

References:

Update table with multiple columns from another table

Get Oracle database tables columns

You can use the following system tables to get metadata about your database. Take this query, for example, which I run using Oracle SQL Developer:

SELECT t.table_name, c.column_name,c.data_type,c.char_used,c.data_precision, c.data_scale, c.owner
FROM all_tables t , all_tab_columns c
WHERE t.TABLE_NAME = c.TABLE_NAME
AND t.OWNER      = c.OWNER
AND t.OWNER      = 'db_username'
ORDER BY t.TABLE_NAME, c.COLUMN_NAME;

Query above returns table name, column name, column data type, column data type unit, column precision and scale (for numbers) and schema owner.

For more information, you can query the DICTIONARY and DICT_COLUMNS views to check what each column means.

SELECT table_name, comments FROM dictionary ORDER BY table_name;

SELECT column_name, comments FROM dict_columns WHERE table_name='ALL_TABLES';

References

Oracle metadata
The Master Key to Oracle’s Data Dictionary
The Self-Documenting Dictionary

Oracle: get list of all tables?
How to get column info from oracle table you don’t own (without using describe)?

Oracle – How to create a readonly user