10

When you process a script in SQL Developer using the "Run Script" button (not the "Run Statement" button), it uses SQL*Plus internally to execute your script. Therefore using a SQL*Plus control setting which stops processing on errors should work. Try adding to the top of your script line like this: WHENEVER SQLERROR EXIT FAILURE


8

SQL80001 is a SQL Server error message. SQL Server doesn't support the old Oracle (+) syntax for outer joins. Re-write your SQL using LEFT/RIGHT OUTER JOIN .... ON .....


5

The preferences dictate when the code assist features kick in. In 4.0.3 we filter the advice for the automatic bits when there are more than 10 suggested identifiers. So, typing: select * from ...on a large schema, you'd likely not see any suggested table or views names, unless you invoked the helper directly via ctrl+spacebar. As you type more, the more ...


5

Alter the session: declare a number; begin EXECUTE IMMEDIATE 'ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE'; select count(*) into a from item w where w.Item_num='MOH601' ; dbms_output.put_line(a); end ;


4

Several errors: varstocks_id varchar2; The VARCHAR2() datatype requires a data length. .. as indicated by the error message Error(7,15): PLS-00215: String length constraints must be in range (1 .. 32767), which tells you the error is on line 7. The 3rd from last line: AND b.orders_id = a.orders_id requires a semi-colon ...


3

You can create it like this: CREATE OR REPLACE PROCEDURE procedurename(param1 NUMBER, param2 varchar(20), returnvalue OUT NUMBER); IS BEGIN ... your code END; And then use it like this: returnvalue NUMBER; procedurename(0, 'xxx', returnvalue); dbms_output.putline(returnvalue); You can look at CREATE PROCEDURE documentation at Oracle website.


3

The problem is that the variable a that you declare in your declaration section is not the same as the variable a that is declared in the context of your loop. You've declared two different variables named a with different scope. If you run your block without a declaration section, you'll notice that it works even though you aren't declaring a variable a ...


3

The DROPTIME and DROPSCN columns in the DBA_RECYCLEBIN view clearly show when the object was dropped. Stop relying on a simplified GUI.


3

It seems your question contains the answer. By default PL/SQL Developer gathers statistical data per session for every query. In the SQL Window and in the debugger's Test Window you can use the Statistics page to view the resources used by the last executed SQL statement or program unit. This information can be a big help when optimizing your SQL or PL/SQL ...


3

SQL Developer the client application is free of charge. Of course, the Oracle database would need to be licensed in a way that allowed you to connect with any tool. PL/SQL Developer is a completely different tool produced by a different company that you would need to license. Define "secure". Assuming a default database install, using any tool to connect ...


2

You have to loop over the results from the first query. You're just fetching once from your cursor and then close it. So you only process the first line of the result. Try something like: CREATE OR REPLACE PROCEDURE a_proc IS BEGIN FOR email IN (SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id) email FROM ...


2

Oracle's wrap program doesn't wrap triggers. The way to do this is to move the code into a package or a stand-alone procedure, and make the trigger a one line call to invoke this code. See the documentation on the limitations of wrapping: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm#LNPLS01602 In your case, something like the following (...


1

I checked the impdb log and saw that this object was not imported due to an OID conflict. At the beginning of the log I found this message. ORA-39083: Object type TYPE:"VERZANI"."DAYS_INTERVAL" failed to create with error: ORA-02304: invalid object identifier literal Failing sql is: CREATE EDITIONABLE TYPE "VERZANI"."DAYS_INTERVAL" OID '...


1

If you are the one who writes the procedure, you might want to make it a function instead. While "procedure" and "function" are often used as synonyms, in PL/SQL they are quite different and should be used accordingly. If you need a program that changes something (delete content, create new structures and so on), it should be a procedure. However, if the ...


1

If I understand correctly, you are trying to consume an existing procedure that has a return value. This can be done in another procedure, package, or function, but the simplest method is using a block. In the declare section you define the variables that will receive the values and then use those in the call to the procedure. DECLARE vAccc_no ...


1

We don't charge for SQL Developer. If you want official support, you need a database licensed and covered by My Oracle Support. It's more complicated than this, but it's effectively free.


1

SQLDeveloper shows tables that are owned by the logged in user. To see other users' tables, right click on connection ("dev" in your case), click "Schema Browser", and then select a user whose object you want to see. Or directly query system views (e.g. all_tables )


1

I think check your network connection. In between your network connection interrupted. So In your sqlnet.ora and also on sqlnet.ora on your server try to change inbound connection or outbound connection and also try to set connection timeout parameter in sqlnet.ora file.


1

PL/SQL Developer can only connect to an Oracle database using what's known as a TNS Alias. This is defined a file called "tnsnames.ora" that contains some text something a bit like this (shamelessly stolen from @Balazs's answer): EDBDEV = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edbdev.mycompany.com)(PORT = 1521)) (CONNECT_DATA = (...


1

You are on the right track, but need to note a few items: First, since you are defining your "connection type" as "basic", SQL Dev will be using it's built-in jdbc thin client, and so will NOT be using tnsnames.ora. If you changed your connection type to "tns", you would get a different set of fields, including a drop-list that is populated by the entries ...


1

EDBDEV is a TNS alias. If you search for a file called tnsnames.ora, that should contain an entry similar to below: EDBDEV = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edbdev.mycompany.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = EDBDEV) ) ) Based on that, you can fill in the required ...


1

Some pondering if you're in fact having a XY problem. YMMV. Using an existing utility, is there a way to store package methods as discrete files in source control and deploy them to their package individually? To me this sounds a peculiar requirement. In database a PL/SQL package is a single atomic unit (in fact there is two units (a specification and a ...


1

I already had that problem. The thing is that Oracle saves your objects in Uppercase. When you use the FOPEN, you MUST use directory name in uppercase as well. Try this: v_FileHandle := UTL_FILE.FOPEN ('LOG_DIR','Lect.csv', 'r');


1

You're doing something wrong. Test case follows... Create schemaa and schemab: SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 21 14:28:47 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> create user schemaa identified by "schemaa"; SP2-0640: Not connected SQL> conn / as sysdba Connected. SQL> create user schemaa identified ...


1

http://www.oracle.com/technetwork/developer-tools/sql-developer/pricing-faq-085267.html Is Oracle SQL Developer free? Yes. PL/SQL Developer is another product, that is not free. Other tools, even applications or application servers connect the database remotely. So yes, it is.


1

To add single quotes in a string literal, just double it, or use the chr function. E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote. To get your fixed end time, just concatenate it. select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from ...


1

You can use one of the functions of Oracles owa_util's package named WHO_CALLED_ME. To demonstrate this feature, we can create a table which will store the results of the call to Oracle's procedure. create table T_CALLSTACK ( OWNER_NAME varchar2(30), OBJECT_NAME varchar2(30), LINE_NUMBER number, CALLER_INFO varchar2(100), ...


1

I believe you are missing the datatype before the initialization of the type/object. See the updated (but untested code below). Oracle Documentation declare rajesh CUSTOM_DATATYPE; begin rajesh := CUSTOM_DATATYPE('xyz',123,'13-02-14','12','fdfd','add1','add2','add3','add4','packtype','noofpacktype','1234','dfs','dde'); DN_Generated(...


Only top voted, non community-wiki answers of a minimum length are eligible