Oracle Apex Signature pad

 Begin 

APEX_COLLECTION.DELETE_COLLECTION(p_collection_name=>'APEX_SIGNATURE');

exception when others then null;

End;

Begin 

APEX_COLLECTION.DELETE_COLLECTION(p_collection_name=>'APEX_SIGNATURE');

exception when others then null;

End;

begin 

for x in (select c001 as filename,

c002 as mine_type,

c001  as dae_created,

blob001 as img_content

from apex_collections

where collection_name='APEX_SIGNATURE');

loop

update HR_EMPLOYEES set 

sig_pic=x.img_content,

sig_file_name=x.filename,

sig_file_update_date=x.date_created,

sig_file_mine_type=x.mine_type

where pid=:p22_emp_id;

end loop;

end;

create table hr_employees

(

emp_id varchar2(30),

emp_name varchar2(120),

join_date date,

sig_pic  blob,

sig_file_name  varchar2(200),

sig_file_update_date date,

sig_file_mine_type varchar2(50)

)

/

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3200M, ORA-01078: failure in processing system parameters

 SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 24 09:49:39 2021

Version 19.3.0.0.0

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

Enter user-name: hr/hr@orcl

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3200M

ORA-01078: failure in processing system parameters

SQL> startup pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

ORACLE instance started.

Total System Global Area 2516579304 bytes

Fixed Size                  9053160 bytes

Variable Size             541065216 bytes

Database Buffers         1958739968 bytes

Redo Buffers                7720960 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile='c:\temp\init.ora';

File created.

SQL> startup force;

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3200M

ORA-01078: failure in processing system parameters

SQL> show parameter memory_target

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 496 Serial number: 49087

SQL> startup pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

ORACLE instance started.

Total System Global Area 2516579304 bytes

Fixed Size                  9053160 bytes

Variable Size             541065216 bytes

Database Buffers         1958739968 bytes

Redo Buffers                7720960 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

  2  /

create spfile from pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

                         *

ERROR at line 1:

ORA-02236: invalid file name

SQL> create spfile from pfile='D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044'

  2  /

File created.

SQL> startup force;

ORACLE instance started.

Total System Global Area 2516582224 bytes

Fixed Size                  9031504 bytes

Variable Size             553648128 bytes

Database Buffers         1946157056 bytes

Redo Buffers                7745536 bytes

Database mounted.

Database opened.

SQL>

How to startup database from pfile

SQL>  SHUTDOWN

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Process ID: 0

Session ID: 0 Serial number: 0

 SQL> startup pfile=D:\appdb19c\app\OracleHomeUser1\admin\orcl\pfile\init.ora.513202112044

ORACLE instance started.

Total System Global Area 2516579304 bytes

Fixed Size                  9053160 bytes

Variable Size             541065216 bytes

Database Buffers         1958739968 bytes

Redo Buffers                7720960 bytes

Database mounted.

Database opened.

Getting Cumulative Sum (Running Total) Using Analytical Functions in oralce

SELECT
    DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,ENAME) CUMDEPTTOT,
    SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTAL,
    SUM(SAL) OVER (ORDER BY DEPTNO, SAL) CUMTOT,
    SUM(SAL) OVER () TOTSAL
FROM
    SCOTT.EMP
ORDER BY
    DEPTNO

Sql Puzzle - Calendar of Current Year

BREAK ON MONTH

BREAK ON MONTH SKIP PAGE

COL MONTH FORMAT A15

 with curr_year as

   (

     select

       trunc(sysdate,'year') -1 + level dt

     from dual

       connect by level <=

         add_months(trunc(sysdate,'year'),12) - trunc(sysdate,'year')

   ),

   data as

   (

     select

       dt,

       to_char(dt,'d') d,

       sum(case when to_char(dt,'d') = 1 or to_char(dt,'dd') = 1 then 1 else 0 end) over (order by dt) week_no

     from

       curr_year

   )

   select

     to_char(min(dt),'Month') Month,

     max(case when d=1 then to_char(dt,'dd') end) sun,

     max(case when d=2 then to_char(dt,'dd') end) mon,

     max(case when d=3 then to_char(dt,'dd') end) tue,

     max(case when d=4 then to_char(dt,'dd') end) wed,

     max(case when d=5 then to_char(dt,'dd') end) thu,

     max(case when d=6 then to_char(dt,'dd') end) fri,

     max(case when d=7 then to_char(dt,'dd') end) sat

   from

     data

   group by week_no

   order by week_no;