SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

set define off.with ; or with out semicolen .

947771Apr 18 2013 — edited Apr 18 2013

hi,

SET DEFINE OFF

CREATE OR REPLACE PROCEDURE myproc
IS
v_txt VARCHAR (10);
BEGIN
v_txt := '&abc';
END;

1)i want to keep this in file, please tel me should i use "SET DEFINE OFF;" OR "SET DEFINE OFF".
that is with out ";".

2) and should i put / after SET DEFINE OFF or not.

3) should i put / in last of stored proc. i keep only one stored proc in one file.

yours sincerely

Edited by: 944768 on Apr 17, 2013 10:58 PM

Edited by: 944768 on Apr 17, 2013 11:03 PM

Edited by: 944768 on Apr 17, 2013 11:11 PM

mdtaylor

Please review the following note:

ORA-12514 When Applying Patch in the Patch Edition Using Adpatch (Doc ID 1472560.1)

1 person found this helpful

Noname123

Hi Thanks for your reply. However, I am able to connect via sqlplus after sourcing patch file system. See below:

$ . EBSapps.env

E-Business Suite Environment Information

----------------------------------------

RUN File System : /u01/EBS_HOME/fs1/EBSapps/appl

PATCH File System : /u01/EBS_HOME/fs2/EBSapps/appl

Non-Editioned File System : /u01/EBS_HOME/fs_ne

DB Host: <hostname> Service/SID: TEST

E-Business Suite Environment Setting

------------------------------------

- Enter [R/r] for sourcing Run File System Environment file, or

- Enter [P/p] for sourcing Patch File System Environment file, or

- Enter anything else to exit

Please choose the environment file you wish to source [R/P]:p

Sourcing the PATCH File System ...

$ sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Apr 30 13:10:28 2020

Enter user-name: system

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Furthermore, the shared note by you is related to adpatch. I don't know if it makes any difference. However, I am using online patching cycle via (adop) utilily.

Note that the parameter (service_name) already has the value ( <ORACLE_SID>, ebs_patch). See the output below:

SQL> show parameter service_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

service_names string TEST, ebs_patch

mdtaylor

It depends on your AD/TXK level. If you are AD/TXK C Delta 10 or higher, the patch service name needs the sid before it.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter service_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

service_names string prd122, prd122_ebs_patch

SQL>

Noname123

Mine is AD/TXK C Delta 7. How different it is here? See below

$ sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Mon May 4 15:25:00 2020

Enter user-name: apps

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter service

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

service_names string TEST, ebs_patch

mdtaylor

What is your local_listener database paraneter? I recently had a similar issue because local_listener was not TEST_LOCAL.

adop phase=prepare fails with: Error Message : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (Doc ID 2007225.1)

What do you get from sqlplus apps@TEST_patch? It should connect successfully from run and patch FS if adop session is open.

Noname123

SQL> show parameter listener

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

listener_networks string

local_listener string TEST_LOCAL

remote_listener string

I was able to run adop phase=prepare successfully. The error in the worker log occurred at the time of running adop phase=apply

mdtaylor

What do you get from sqlplus apps@TEST_patch? It should connect successfully from run and patch FS if adop session is open.

Noname123

From Run file system:

$ . EBSapps.env

E-Business Suite Environment Information

----------------------------------------

RUN File System : /u01/EBS_HOME/fs1/EBSapps/appl

PATCH File System : /u01/EBS_HOME/fs2/EBSapps/appl

Non-Editioned File System : /u01/EBS_HOME/fs_ne

DB Host: <host_name> Service/SID: TEST

E-Business Suite Environment Setting

------------------------------------

- Enter [R/r] for sourcing Run File System Environment file, or

- Enter [P/p] for sourcing Patch File System Environment file, or

- Enter anything else to exit

Please choose the environment file you wish to source [R/P]:r

Sourcing the RUN File System ...

$ sqlplus apps@TEST_PATCH

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 5 16:09:49 2020

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

===============================================================

From patch file system

$ . EBSapps.env

E-Business Suite Environment Information

----------------------------------------

RUN File System : /u01/EBS_HOME/fs1/EBSapps/appl

PATCH File System : /u01/EBS_HOME/fs2/EBSapps/appl

Non-Editioned File System : /u01/EBS_HOME/fs_ne

DB Host: <hostname> Service/SID: TEST

E-Business Suite Environment Setting

------------------------------------

- Enter [R/r] for sourcing Run File System Environment file, or

- Enter [P/p] for sourcing Patch File System Environment file, or

- Enter anything else to exit

Please choose the environment file you wish to source [R/P]:p

Sourcing the PATCH File System ...

$ sqlplus apps@TEST_PATCH

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 5 16:12:02 2020

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

mdtaylor

Can you please attach the entire worker1 log, and the adpatch log. Which patch are you trying to apply?

Noname123

I couldn't find attachment upload button in this page. Therefore, I have uploaded it to onedrive and shared the link below:

https://1drv.ms/u/s!AlMyQ-25HX-Gjx2e5MOdyRo1eNme?e=VrTt2p

The patch I am trying to apply is OW related patch (29158460)

Noname123

Any update?

mdtaylor

It appears due to your TXK level, it is looking for TEST_ebs_patch in your service_name. Like in my entry for prd122_ebs_patch, update your service_name init.ora parameter to show TEST_ebs_patch, restart the database and try again.

Time when worker completed job: Fri Apr 17 2020 09:10:56

JDBC connect string from AD_APPS_JDBC_URL is

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ritpedbl1.bahri.sa)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TEST_ebs_patch)(INSTANCE_NAME=TEST)))

Time when worker started job: Fri Apr 17 2020 09:10:56

Also type tnsping '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ritpedbl1.bahri.sa)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TEST_ebs_patch)(INSTANCE_NAME=TEST)))'

It should respond OK.

Noname123

Hi. Thanks for the reply. I will try the offered solution then I am going to update this thread.

1 - 13

Locked Post

New comments cannot be posted to this locked post.

Locked on May 16 2013

Added on Apr 18 2013

#sql-developer

3 comments

6,059 views

SQL Developer

set define off.with ; or with out semicolen .

Comments

Post Details