My Findings ...

13 October 2015

Setting the tnsnames.ora in Oracle SQL Developer

Setting the tnsnames.ora in Oracle SQL Developer

Tools --> Preferences --> Database --> Advanced --> TNS Names Directory

Oracle SQL MERGE statement

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

Example
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
WHERE (S.salary <= 8000);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
159 180
160 175
161 170
179 620
173 610
165 680
166 640
164 720
172 730
167 620
171 740

11 March 2015

How to purposefully open an Excel file as "Read Only"

Open a file as a copy

When you open a file as a copy, the program creates a duplicate of the file, and you look at the duplicate. Any changes that you make are saved to the copy. The program gives the copy a new name. The default is to add Copy (1) of to the beginning of the file name.

Click the Microsoft Office Button

, and then click Open.

IMPORTANT If you don't see the Microsoft Office Button

, click Open on the File menu.

Keyboard shortcut To display the Open dialog box, press CTRL+O.

In the Look in list or Navigation pane, click the folder, drive, removable media (such as flash drive, CD, or DVD), or Internet location that contains the file that you want to open.

Click the file that you want to open a copy of.

Click the arrow next to the Open button, and then click Open as Copy.

NOTE When you open a file as a copy, a new copy of the file is created in the folder that contains the original file.

Open a file as read-only

When you open a file as read-only, you are looking at the original file, but you cannot save changes to it.

Click the Microsoft Office Button

, and then click Open.

Keyboard shortcut To display the Open dialog box, press CTRL+O.

In the Look in list or Navigation pane, click the folder, drive, removable media (such as flash drive, CD, or DVD), or Internet location that contains the file that you want to open.

Click the file that you want to open as read-only.

Click the arrow next to the Open button, and then click Open Read-Only.

Source : https://support.office.com/en-nz/article/Open-a-file-ac57f07b-187f-45f2-a641-8a3d4ca28764#bm3

06 February 2015

Sample code for dbms_scheduler.create_job

Sample code for dbms_scheduler.create_job

ln_job_id NUMBER;

lv_job VARCHAR2(100);

lv_job_action VARCHAR2(150) := 'BEGIN XXPACKAGE_NAME.procedure(''' ||
pv_rice_id || ''',' || pn_run_id || '); END;';
BEGIN
ln_job_id := xxaud_soa_inbound_seq.nextval;
lv_job := pv_rice_id || '_' || pn_run_id || '_' || ln_job_id;

dbms_scheduler.create_job(job_name => lv_job,
job_type => 'PLSQL_BLOCK',
job_action => lv_job_action,
number_of_arguments => 0,
enabled => TRUE,
comments => 'SOA Invoke Program');

END;

30 January 2015

GOVERNMENT INTRODUCED ONLINE SERVICES 🔘

06 January 2015

SQL to fetch the SMTP server details from Workflow Setup

SQL to fetch the SMTP server details from Workflow Setup

select fscpv.parameter_value smtp_server_name,fscpt.display_name
from fnd_svc_comp_params_tl fscpt
, fnd_svc_comp_param_vals fscpv
, fnd_svc_components fsc
where fscpt.parameter_id = fscpv.parameter_id
and fscpv.component_id = fsc.component_id
and fscpt.display_name = 'Outbound Server Name'
and fsc.component_name = 'Workflow Notification Mailer'

09 October 2014

How to delete XML Templete in oracle R12

Use the following code to delete a Template Code

declare
l_templateCode varchar2 (100) := ; -- Template Code
begin
for r in (select t1.application_short_name template_app_name,
t1.data_source_code,
t1.application_short_name def_app_name
from xdo_templates_b t1
where t1.template_code = l_templateCode)
loop

xdo_templates_pkg.delete_row (r.template_app_name, l_templateCode);

delete from xdo_lobs
where lob_code = l_templateCode
and application_short_name = r.template_app_name
and lob_type in ('TEMPLATE_SOURCE', 'TEMPLATE');

delete from xdo_config_values
where application_short_name = r.template_app_name
and template_code = l_templateCode
and data_source_code = r.data_source_code
and config_level = 50;

end loop;
end;

My Findings ...

Search This Blog

13 October 2015

Setting the tnsnames.ora in Oracle SQL Developer

Oracle SQL MERGE statement

11 March 2015

How to purposefully open an Excel file as "Read Only"

06 February 2015

Sample code for dbms_scheduler.create_job

30 January 2015

GOVERNMENT INTRODUCED ONLINE SERVICES 🔘

06 January 2015

SQL to fetch the SMTP server details from Workflow Setup

09 October 2014

How to delete XML Templete in oracle R12

About Me

Labels

Blog Archive

Friends Blogs

Telugu News

Followers