oracle inserted row OUT SYS_REFCURSOR by execution procedure

CREATE TABLE "BOROO"."CMS_TEMP"
   (    "ID" VARCHAR2(36 BYTE) DEFAULT SYS_GUID() NOT NULL ENABLE,
    "NAME" NVARCHAR2(150),
    "RCDATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE
   )
 
procedure

create or replace PROCEDURE INSEL_TEMP (p_name NVARCHAR2, v_cur OUT SYS_REFCURSOR)
AS
v_id NVARCHAR2(36);
BEGIN
INSERT INTO CMS_TEMP (NAME) VALUES (p_name) RETURNING ID INTO v_id;
COMMIT;
OPEN v_cur FOR SELECT ID,NAME,RCDATE FROM CMS_TEMP WHERE ID=v_id;
END;



call






DECLARE
  l_cursor  SYS_REFCURSOR;
  l_id   cms_temp.ID%TYPE;
  l_name   cms_temp.NAME%TYPE;
  l_rcdate  cms_temp.RCDATE%TYPE;
BEGIN
  INSEL_TEMP('name 1', l_cursor);
 
  LOOP FETCH l_cursor
    INTO l_id, l_name, l_rcdate;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_id || ' | ' || l_name || ' | ' || l_rcdate);
  END LOOP;
  CLOSE l_cursor;
END;

oracle SYS_EXTRACT_UTC(SYSTIMESTAMP), mssql GETUTCDATE(), mysql CURRENT_TIMESTAMP

inORACLE

DROP TABLE "CMS_TEMP";

CREATE TABLE "CMS_TEMP" (  
"ID" VARCHAR2(36) DEFAULT SYS_GUID() NOT NULL,
"NAME" NVARCHAR2(150),
"RCDATE" TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
);

insert into "BOROO"."CMS_TEMP" (NAME)
values ('new name 1');

select TO_CHAR(RCDATE, 'YYYY-MM-DD HH24:MI:SS.FF') from CMS_TEMP;


inMSSQL


DROP TABLE [dbo].[table1]
GO
/****** Object:  Table [dbo].[table1]    Script Date: 3/21/2017 5:50:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_table1_ID]  DEFAULT (newid()),
    [Name] [nvarchar](50) NOT NULL,
    [rcdate] datetime NOT NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO
insert into table1(name)
values ('name 1');
GO
select rcdate from table1;


inMySQL

CREATE TABLE `table1` (
  `id` char(36) DEFAULT NULL,
  `name` varchar(250) NOT NULL,
  `rcdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE OR REPLACE PROCEDURE test for REF CURSOR

CREATE OR REPLACE PROCEDURE test
IS
  TYPE ref_cursor_type IS REF CURSOR;
  c_ref_cursor ref_cursor_type;
  v_sql VARCHAR2(1000) := 'SELECT 1 FROM some_object';
  v_dummy NUMBER;
BEGIN
  OPEN c_ref_cursor FOR v_sql;
  FETCH c_ref_cursor INTO v_dummy;
  CLOSE c_ref_cursor;
END;

oracle sys_guid(), mssql newid(), mysql trigger all for default uniqueidentifier ID

inORACLE

DROP TABLE "CMS_TEMP";

CREATE TABLE "CMS_TEMP" (   
"ID" VARCHAR2(36) NOT NULL,
"NAME" NVARCHAR2(150),
"RCTIME" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
);

create or replace FUNCTION NEWID RETURN VARCHAR2 IS guid VARCHAR2(36);
BEGIN
    SELECT SYS_GUID() INTO guid FROM DUAL;
guid := regexp_replace(rawtohex(sys_guid())
       , '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
       , '\1-\2-\3-\4-\5');
--OR
guid := SUBSTR(guid,  1, 8) ||
        '-' || SUBSTR(guid,  9, 4) ||
        '-' || SUBSTR(guid, 13, 4) ||
        '-' || SUBSTR(guid, 17, 4) ||
        '-' || SUBSTR(guid, 21);
    RETURN guid;
END NEWID;

create or replace TRIGGER SetGUIDforCMS_TEMP BEFORE INSERT ON CMS_TEMP
FOR EACH ROW
BEGIN
    :new.ID := NEWID();
END;

insert into "CMS_TEMP" (NAME)
values ('new name 1');

select * from cms_temp;

DECLARE
  v_Return VARCHAR2(36);
BEGIN
  v_Return := NEWID;
  DBMS_OUTPUT.PUT_LINE(v_Return);
END;

select NEWID from dual;

inMSSQL

Data Type: uniqueidentifier
Default Value or Binding: (newid())

CREATE TABLE [dbo].[table1](
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_table1_ID]  DEFAULT (newid()),
    [Name] [nvarchar](50) NOT NULL,
    [rcdate] datetime NOT NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO

inMYSQL

CREATE TRIGGER `before_insert_table1` BEFORE INSERT ON `table1`
FOR EACH ROW BEGIN
    SET new.id = UPPER(uuid());
END

oracle CREATE OR REPLACE Function with cursor, if clause

CREATE OR REPLACE Function IncomeLevel
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   monthly_value number(6);
   ILevel varchar2(20);

   cursor c1 is
     SELECT monthly_income
     FROM employees
     WHERE name = name_in;

BEGIN

   open c1;
   fetch c1 into monthly_value;
   close c1;

   IF monthly_value <= 4000 THEN
      ILevel := 'Low Income';

   ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
      ILevel := 'Avg Income';

   ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
      ILevel := 'Moderate Income';

   ELSE
      ILevel := 'High Income';

   END IF;

   RETURN ILevel;

END;

npm install oracledb using python27,node-gyp,bcrypt,build-tools,oracle-instantclient in windows

1.
install python 2.7
>npm config set python python2.7

2.
download and extract Oracle Instant Client to C:\oracle\instantclient
C:\oracle\instantclient; add to PATH of system variables

3. may be, not use that
>npm install --global --production windows-build-tools
>npm config set msvs_version 2013 --global
>npm install bcrypt
>npm install -g node-gyp
>node-gyp rebuild

4.
http://landinghub.visualstudio.com/visual-cpp-build-tools
or
visual studio 2015 install
>npm config set msvs_version 2015

5.
>set OCI_LIB_DIR=C:\oracle\instantclient\sdk\lib\msvc
>set OCI_INC_DIR=C:\oracle\instantclient\sdk\include
or
>set OCI_LIB_DIR=C:\oracle\BOR\product\11.2.0\dbhome_1\OCI\lib\msvc
>set OCI_INC_DIR=C:\oracle\BOR\product\11.2.0\dbhome_1\OCI\include

6.
>npm install oracledb --save

oracle database date, timestamp, utc (SYS_EXTRACT_UTC(SYSTIMESTAMP)) field convert to local time example

  CREATE TABLE "BOROO"."CMS_TEMP"
   (    "ID" VARCHAR2(32 BYTE) DEFAULT SYS_GUID() NOT NULL ENABLE,
    "NAME" NVARCHAR2(150) NOT NULL ENABLE,
    "DESCR" NVARCHAR2(500),
    "ISDELETED" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
    "RCDATE" DATE NOT NULL ENABLE,
    "RCTIME" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


  CREATE UNIQUE INDEX "BOROO"."CMS_TEMP_INDEX1" ON "BOROO"."CMS_TEMP" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


  CREATE INDEX "BOROO"."CMS_TEMP_INDEX2" ON "BOROO"."CMS_TEMP" ("NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


  CREATE INDEX "BOROO"."CMS_TEMP_INDEX3" ON "BOROO"."CMS_TEMP" ("RCDATE" DESC)
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;


INSERT INTO CMS_TEMP (NAME,DESCR,ISDELETED,RCDATE)
VALUES ('my name', 'миний тайлбар', 0, TO_DATE('2017-03-07 15:17:19', 'YYYY-MM-DD HH24:MI:SS'));

/*UTC timestamp field view as local date*/
SELECT ID,NAME,TO_CHAR(RCDATE, 'YYYY-MM-DD HH24:MI:SS') as DATETIME,RCTIME as UTC
,TO_CHAR(FROM_TZ(RCTIME, 'UTC') at LOCAL, 'YYYY-MM-DD HH24:MI:SSxFF6') as local
FROM CMS_TEMP;

/*UTC without TZH:TZM*/
SELECT SYSTIMESTAMP as local, SYS_EXTRACT_UTC(SYSTIMESTAMP) as utc, TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'YYYY-MM-DD HH24:MI:SSxFF6') as str from dual;

how to create a user in oracle 11g and grant permissions, account unlock

CREATE USER username IDENTIFIED BY password;
GRANT CONNECT TO username;
GRANT SELECT,INSERT,UPDATE,DELETE on schema.table TO username;
 esvel
GRANT EXECUTE on schema.procedure TO username;
esvel
GRANT ALL privileges TO username;

CREATE USER <<username>> IDENTIFIED BY <<password>>; -- create user with password
GRANT CONNECT,RESOURCE,DBA TO <<username>>; -- grant DBA,Connect and 

Resource permission to this user(not sure this is necessary 

if you give admin option)
GRANT CREATE SESSION TO <<username>> WITH ADMIN OPTION; --Give admin option to user
GRANT UNLIMITED TABLESPACE TO <<username>>; -- give unlimited tablespace grant

http://stackoverflow.com/questions/9447492/how-to-create-a-user-in-oracle-11g-and-grant-permissions

Resolving ORACLE ERROR:ORA-28000: the account is locked

By thekkadath on Mar 20, 2006

After installation of Oracle10g, there was a problem ..couldnt login using SQL+. None of the accounts(scott/tiger) worked . At last a quick web search gave the solution . Here is what it is:

From your command prompt, type
sqlplus "/ as sysdba"
Once logged in as SYSDBA, you need to unlock the SCOTT [or maybe SYSTEM] account
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;


example in my PC: 

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\BOR>sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 08:56:49 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user SYSTEM unlock;
alter user SYSTEM unlock
                  *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> ALTER USER SYSTEM ACCOUNT UNLOCK;

User altered.

SQL> ALTER USER SYSTEM IDENTIFIED BY password;

User altered.

SQL>