Print text in Oracle SQL Developer SQL Worksheet window

I am using Oracle SQL (in SQLDeveloper, using the SQL Worksheet). I would like to print a statement before my select, such as PRINT 'Querying Table1'; SELECT * from Table1; What do I use to Print / show text output? It's not Print, because that gives me the error: Bind Variable Table1 is NOT DECLARED. DBMS_OUTPUT.PUT_LINE is an unknown command. (Obviously, I'm an inexperienced SQLDeveloper and Oracle user. There must be some synonym for Print, but I'm having trouble finding help on it wi

Oracle Sequence starting with 2 instead of 1

Unexpected behavior: I am encountering strange behavior of Oracle sequences with 11g (works with 10g): CREATE SEQUENCE test_sequence START WITH 1; CREATE TABLE test_table ( val INT ); INSERT INTO test_table VALUES ( test_sequence.NEXTVAL ); Even though the sequence starts with 1, the first value inserted is 2: SELECT * FROM test_table; VAL ---------- 2 Expected behavior: Selecting NEXTVAL without the insert works as expected: CREATE SEQUENCE test_sequence_2 START W

Installing Oracle XE (older) AFTER installing APEX 10g?

Awhile back I installed the Oracle 10g XE database on my dev machine. Since then, I installed Application Express over top of the XE installation. In other words, it's APEX running on 10g. I found I don't really need APEX after all (it's turned out I'm developing in ASP.NET and Oracle instead), and I recall having more of a GUI to manage the XE 10g instance BEFORE I installed APEX. Can I remove APEX while leaving my original XE installation? I tried to re-install the original XE, but I didn't s

Oracle No more data to read from socket error

We are using Oracle as the database for our Web application. The application runs well most of the time, but we get this "No more data to read from socket" error. Caused by: java.sql.SQLRecoverableException: No more data to read from socket at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1( at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1( at oracle.jdbc.driver.T4CTTIfun.receive( at oracle.jdbc.driver.T4CTTIfun.doRPC(

Oracle How to track how long it takes to do something in SQL Developer

Basically I'm doing this in a procedure: DECLARE CURSOR r_cursor is SELECT * from imp_exp.results where code = 8223558 FOR UPDATE OF c_timestamp; BEGIN FOR idx IN r_cursor LOOP --dbms_output.put_line(idx.sample_code); update imp_exp.results set c_timestamp = TO_DATE('10-MAY-99', 'DD-MON=YY') WHERE CURRENT OF r_cursor; END LOOP; END; How can I display how long this took? Thanks!

Inserting multiple records from a trigger in oracle

i created a trigger to insert the employeeIDs whenever a position is updated. In some cases, there are many employees attached to one position so the trigger isnt able to insert all the employees (only 1). i need all the employee Ids can any1 help me with this code? Regards create or replace trigger postn_updt after update on postn for each row declare cursor m is select u.login from user u, party_per s where u.row_id=s.person_id and s.party_id=:new.row_id; rownum varchar2(10); begin

Oracle convert double latin letters to US7ASCII

How can i convert Æ(latin capital letter ae) to AE(US7ASCII) in oracle.I have tried with following query below: SELECT CONVERT ('Æ', 'US7ASCII') FROM DUAL; But it gives me ? as output ,but the same query is working for single characters as shown below: SELECT CONVERT ('à', 'US7ASCII') FROM DUAL; OUTPUT ========== a ==========

Oracle: using enum / binary values to enhanche performance

I have often to deal with data that may assume just a few values. I think Oracle should use 16 bytes to keep a VARCHAR2(16) (on an index or on a table). I could use a FK but it implies Oracle to check that values exists on another table. I'd prefer using just computation and RAM. And it's less readable. If I replace that VARCHAR2(16) with a number or an enum, or a CHAR(1), I would waste just one to 4 bytes. When I've got 4 million rows maybe it could help to avoid wasting space. It could have

Oracle PL/SQL - execute immediate in pipelined function

I want to execute dynamic query in my pipelined function and return results of this query. Is it possible to do this? Pipelined function is convenient for me to achieve good interface for my application cause it behaves like a table. The function: CREATE OR REPLACE FUNCTION MyFunction(p_schema VARCHAR2) RETURN MyTableType Pipelined IS v_query VARCHAR2(1000); BEGIN v_query := 'SELECT * FROM TABLE ('||p_schema||'.somepackage.SomeFunction)'; --SomeFunction is another pipelined function EXECUT

Oracle and possible constant predicates in "WHERE" clause

I have a common problem with ORACLE in following example code: create or replace procedure usp_test ( p_customerId number, p_eventTypeId number, p_out OUT SYS_REFCURSOR ) as begin open p_out for select e.Id from eventstable e where (p_customerId is null or e.CustomerId = p_customerId) and (p_eventTypeId is null or e.EventTypeId = p_eventTypeId) order by Id asc; end usp_test; The "OR" in "(p_customerId is null or e.CustomerId = p_customerId)" kills proc

Oracle Forms setting item invalid

When I use the When-Validate-Item Trigger, it just executes once, when I want to validate, if an Item is not null. It is giving me the message, that something went wrong now. But now I am able to leave the current record. Is there any way to set the status of an item to invalid, so the trigger fires more then once. I am using multirecord spec. Here is some code that illustrates what I'm thinking: if :system.trigger_item = 'BLOCK.ITEM' then if :BLOCK.ITEM is null then -- set the item

Oracle Create and Drop table everyday

My Customer want to create new table everyday, e.g. MyTable<yyyymmdd>, and then my application will read this table and do some cross-check. After done all cross-check, I need to drop this table. Every day will be create and drop table. Is it good solution? could be database will crash because of this? please advice me. i already give another solution, which customer just insert the data into one table. After that, I'll delete all records once done. But customer refuse to use my solut

Oracle BI Publisher auto width of excel columns

When creating a BI Publisher rdf template in Microsoft Word, with intended output to Microsoft Excel; is there a way to specify that a table should use auto width for the columns in the Excel output. By default, when you use the BI Publisher Desktop addin for Microsoft Word; the table wizard creates a table in Word that does not result in output to Excel where the columns in Excel is sized to fit the data. Users of the report then have to manually size the excel columns every time they run the

ORACLE - check if time is in between two timestamp

I have a simple query which will return either A or B depending on the projected oven out date and time. If the projected oven out date and time is between 6am and 6pm, A should be returned. Otherwise B if time is between 6pm and 6am of the next day. My problem is that I do not know how to display A or B depending on the projected oven out date and time. I am using the query below to get the projected oven out date and time. SELECT DISTINCT to_char((ti.txndate + pm.baketime/24),'

Oracle Package Error

I want to create a package and inside the package we have a function having a parameter. I have table for TIME_DIM and the granularity of that table is one record/second. I am getting an error while crating this package any one help me on this. create or replace PACKAGE PKG_TIME_DIM IS Function FUN_TIME_DESC ( TIME_IN IN varchar2 ) RETURN varchar2 IS TIMEDESC varchar2; CURSOR c1 IS SELECT TIME_DESC from TIME_DIM where TIME_DESC = TIME_IN; BEGIN open c1; fet

Oracle - How to check my date field value has time portion or not

I have a table containing a date type field and it holds few records with date value with time portion and few records only have date value without time portion. How to check which record has time portion and what not without navigating record by record? This is what I'm using at the moment, but I want something faster: SELECT MY_TAB.SEQ, MY_TAB.CRTE_DT, CASE WHEN TRUNC (MY_TAB.CRTE_DT) = MY_TAB.CRTE_DT THEN 'False' ELSE 'True' END AS "Has Time Portion" FROM (SELECT 1 AS SEQ,

Mysterious declaration in Oracle PL/SQL code

What means this &n declaration in Oracle PL/SQL? Usage in code sample (taken from here) is shown below: DECLARE myex EXCEPTION; PRAGMA EXCEPTION_INIT(myex,-20015); n NUMBER := &n; BEGIN FOR i IN 1..n LOOP dbms_output.put.line(i); IF i=n THEN RAISE myex; END IF; END LOOP; EXCEPTION WHEN myex THEN dbms_output.put.line('loop is end'); END;

Oracle SQL ordered by Elapsed Time in AWR report

I was trying to analyze the AWR report generated for a particular process with a duration of one hour. I am trying to find out which query is taking much time to while running the process. When I have gone through the report, I can see SQL ordered by Gets,SQL ordered by CPU Time,SQL ordered by Executions,SQL ordered by Parse Calls, SQL ordered by Sharable Memory,SQL ordered by Elapsed Time etc. I can see the SQL Text from the table SQL ordered by Elapsed Time. My question: Is this the right

Oracle Database Schema Privileges for ATG

I am setting up a new instance of ATG (version 10.0.3). I am using an Oracle database (Oracle SE ONE). I need to create the four basic database users - atg_pub, atg_prod, atg_cata, and atg_catb. Which privileges should I assign to these users in the Oracle database? I assume each user needs, at the minimum CONNECT CREATE/ALTER/DROP TABLE CREATE/ALTER/DROP INDEX Is there a definitive list? P.S. I am using a shared Oracle instance and do not have the privileges to GRANT ALL to these users

Selecting elements and inserting them into List of tables in Oracle

I'm trying to insert the selected value of columns into List of Tables, Here is my code: First I've Created an Object: CREATE OR REPLACE TYPE ordersinf as OBJECT(orderDate DATE, orderName VARCHAR2(20), orderPrice NUMBER) Then CREATE OR REPLACE orders_tab IS TABLE OF ordersinf and Finally my Function: create or replace function execImmFunc(orderName in VARCHAR2, orderPrice in NUMBER) return orders_tab is Result orders_tab := orders_tab(); myName orders_table.orders_name%type; myPric

Oracle 12C Audit Trigger On Rename

I have an audit trigger used for source control where we track all oracle (12c) object modifications. How can I get the "new" object name within a rename (whether through "alter" or "rename")? Regardless if I fire the trigger before or after "ALTER or RENAME or DDL ON DATABASE", I see the "old" name in ORA_DICT_OBJ_NAME.

Oracle How to limit the data but not use a break point within an ID

I have a sql statement to load data within Oracle which is run by a Unix script. I am using Oracle version 10.5 whereas, I have the following script which will limit the data selection to 600,000 records. I still want to limit the data but I do not want to break it up within an ID. So the break point cannot be within a dataset for any ID. insert into tablea select ID, chan, datetime, sign(usage) * least(abs(usage),999999999), --target tables can

Concat an item with a loop variable in Oracle APEX

I have a set of item that has the same name so I add at the end of each the position number like :P77_VARIABLE_1, :P77_VARIABLE_2. So in the process to add them to my database I must get those item and make a loop. The loop will look like this for i in 1..:P77_NOMBRE_VARIABLE loop l_variable := new SFD_SI_VARIABLE_TYP(sfd_si_variable_seq.nextval, :P77_NOM_VARIABLE_i, :P77_TYPE_VARIABLE_i); SFD_SI_VARIABLE_PKG.AJOUTER(l_variable); end loop; But the problem is this doesn't work. C

Oracle PL/SQL - how to repeat alternating text for a given number of times with a final output?

I want to make a procedure called OE that will have text output based on the number that I define. For example, inputting the number 6 will give the following output: odd even odd even odd even = even steven! and inputting the number 5 will give the following output: odd even odd even odd = you oddball! I'm completely new at this and have been struggling to get the odd number to load correctly (for some reason, it gets stuck in an infinite loop). Any help would be appreciated! Here is wha

Oracle Returning data from a Dynamic PL/SQL Query

I am fetching a query stored in the database, attempting to execute it with parameters, and then handle the result. DECLARE SQLSTR VARCHAR(5000); BEGIN SELECT SelectString INTO SQLSTR FROM MySelectTable WHERE Name = 'QueryOfDoom'; EXECUTE IMMEDIATE SQLSTR USING 1; END; The query executes but nothing is returned. I've searched around on here and found you can't return data from a bloc. That's fine. However how can I get data out? Since it's dynamic it strikes me as cumbersome if I had to

Oracle PL/SQL Bubble-Sorting - ORA-01403: no data found ORA-06512: at line 12

Here is the code: declare p_arr dbms_sql.Number_Table; i pls_integer; procedure do_sort(p_arr in out dbms_sql.Number_Table, p_asc in boolean default null, p_nulls_last in boolean default null) is x pls_integer; p_temp number; begin for i in 1..p_arr.COUNT-1 loop for x in 2..p_arr.COUNT loop if p_arr(x) < p_arr(x-1) then p_temp := p_arr(x-1); p_arr(x-1) := p_arr(x); p_arr(x) := p_temp;

Oracle Serializing query result

I have a financial system with all its business logic located in the database and i have to code an automated workflow for transactions batch processing, which consists of steps listed below: A user or an external system inserts some data in a table Before further processing a snapshot of this data in the form of CSV file with a digital signature has to be made. The CSV snapshot itself and its signature have to be saved in the same input table. Program updates successfully signed rows to make

Oracle condition that does not have a specific type of filter using EXISTS

I want to perform an EXISTS condition on a procedure: AND EXISTS (SELECT ContractType FROM VW_CONTRACT WHERE "EmployeeId" = ACTIVITY. "EmployeeId" AND UPPER (ContractType) IN ('SPECIAL TYPE') AND ROWNUM = 1) But the SPECIAL TYPE is not a type present in the ContractType column in VW_CONTRACT, it exists only in the screen filters, and is treated on the procedure. When I have an employee allocated in an activity, but without a contract with the company, th

Having a case statement in where clause along with constant value in Oracle causes wrong results

I am executing the same queries in Oracle and SQL Server but the results are different. Ideally, I am expecting the SQL Server results as my desired output. Please let me know how Oracle treats the query and gets the below results. If i change the where condition in Oracle to 'where b.a between 1 and (select case when 4>2 then 4 else 2 end from dual) ' then i get correct results. I am using SQL Server 2008 and Oracle 11g Full Oracle version: Oracle Database 11g Enterprise Edition Release 11.1

Oracle sqlplus within a loop - unix

Is there a way send multiple sqlplus commands within a loop but waiting for one to run with success in order for the next one to start ? Here is a sample of my code. I have tried to add that sleep 15 because the functions that I'm going to execute are taking like 10-20s to run. I want to get rid of that 15s constant and make them run one after the other. if [ "$#" -eq 1 ]; then checkUser "$1" while read line; do sqlplus $user/$pass@$server $line sleep 15 done < "$wrapperLi

Oracle dba_snapshot_refresh_times use in PL SQL

Essentially I have an MVIEW that doesn't need to be refreshed every time I run my report so as a test I'm just checking the minute interval where if it hasn't been refreshed in the last 20 min, I would refresh it. So, if I run this script by itself, it works properly: declare LastRefresh float; myvar varchar2(10) := 'NO'; BEGIN SELECT ROUND(ABS((LAST_REFRESH - SYSDATE)*24*60),0) INTO LastRefresh FROM dba_snapshot_refresh_times WHERE owner = 'ME' AN

Stored Procedure Oracle (expression 'TO_NUMBER(SQLDEVBIND1Z_1)' cannot be used as an assignment target)

We've started to learn about stored procedures in class the other week, professor already gave a big assignment that is very complicated, and at this point I'm very confused. I'm not even sure what exactly am I trying to accomplish here. I need to create a procedure for the following select statement SELECT * FROM Vehicle, VAN WHERE SEATCAPACITY > 5 AND MAXIMUMPAYLOAD > 5000; This is what I have compiled and stored procedure was compiled successfully. create or replace PROCEDU

Oracle Create a nested table and insert data only in the inner table

I have this table: employees(id, firstname, lastname, salary) and I added another column that will store all the past salaries of every employee. CREATE OR REPLACE TYPE salary_list AS TABLE OF NUMBER; / ALTER TABLE employees ADD (salary_history salary_list) NESTED TABLE salary_history STORE AS salary_history_tab; Now, the table is employees(id, firstname, lastname, salary, salary_history) and salary_history is null. When I modify the salaray value, how do I insert the current salary value int

Oracle REF Cursors

Is there a way to perform any processing after a ref cursor is opened for an application to read from? So, I have a ref cursor: OPEN p_data FOR v_select. The application reads from that. Afterwards, can my procedure continue processing? Or is it done? Please advise

Oracle Detecting the last record in a for loop

I have a series of loops that put together a clob from a table of data in a weird pseudo-json format. The issue I have is that each of these loops needs to end out the section with the relevant character, but I am unable to figure out how to identify the last record. So far, I have: for y in (select distinct ident from table(an_object)) loop pseudojson := pseudojson || '{'; the_row := y.ident; for z in (select * from table(an_object) where ident = the_row) loop pseudojson := ps

Importing DMP file into oracle 10g xe

I exported backup file of user hr By using this command exp hr/usman file=c:\backup\hr.dmp owner=hr Now I am importing dmp file into oracle 10 express edition after importing dmp file tables are not showing in user or schema. imp system/sys file=c:\backup\hr.dmp fromuser=hr touser=hr

Join Table with oracle sql

I am newbie for Oracle SQL. I wish to join 2 tables. In TABLE 1 two tables had been join with the below presentation Here is the sql script for TABLE 1: select a.ID, a.CLASSIFICATION_CODE, a. CATEGORY_CODE from a, b where locality = 'xxx' and a.DIV = b.DIV and a.TYPE = b.TYPE and a.DIST = b.DIST and a.BS = b.BS and a.LOT = b.LOT; TABLE 2 Here is the sql script for TABLE 2: select CODE_TYPE,CODE_1,CODE_DESC fr

Is there an automatic way to convert Oracle's join syntax (+) to standard join syntax?

Converting from Oracle to PostgreSQL, have lots of SQL to convert from Oracle's join syntax "(+)" to standard join syntax. Is there an automatic way - tool - to do this? Barring that, are there any rules of thumb to use when doing such a conversion? For example, convert this: SELECT request.requestId FROM request, incident, changeRequest WHERE incident.requestId = request.requestId AND changeRequest.requestId = request.requestId(+) / to this SELECT request.reque

Create table as select statement primary key in oracle

Is it possible to specify which is the primary key on creating table as select statement? My aim is to include the declaration of primary key on the create table not modifying the table after the creation. CREATE TABLE suppliers AS (SELECT company_id, address, city, state, zip FROM companies WHERE company_id < 5000);

How to copy data along a dimension while avoiding unique name constraint issues in Oracle

Database is Oracle 18c Data info: We have the temporal notion of Seasons, and we have a set of Business Groups in each Season. My table, AGROUP, has columns(ID*[auto-generated], NAME, SEASON_ID). Group Names must be unique by Season. Said another way, I can have two groups with the same name ONLY if they are in different seasons. Task At Hand: I need to create a stored procedure that, given a source season and a target season, can copy all the groups from the source season to the target sea

ORA-00904 for ORDER BY in Oracle

I can not understand what goes wrong in this query: select last_name, first_name a from employees order by "a"; Output is: ORA-00904: "a": invalid identifier However this query working and sort results by first_name in ascending order: select last_name, first_name a from employees order by "A";

Oracle execute dynamic sql select script and return tabular data

I am trying to execute dynamic sql select statement and return the result back to C# ExecuteReader. I can retrieve the result in sql server using following sql code. Create Table TestCustomer(ID Varchar(50), CustomerName Varchar(100)); Insert Into TestCustomer (ID, CustomerName) Values ('1', 'One'); Insert Into TestCustomer (ID, CustomerName) Values ('2', 'Two'); Insert Into TestCustomer (ID, CustomerName) Values ('3', 'Three'); DECLARE @SQLScript VARCHAR(MAX), @CustomerName VARCHAR(100); SET

merge 2 Oracle queries into one with some generic data specified with null

Can I write one Oracle query that can do something like that? if code is XXX, then return V1,V2 if code is not XXX, then return those values whose code = null, i.e. V3, V4 Table1 code value ---- ----- XXX V1 XXX V2 null V3 null V4 I need to write something like that: select * from Table1 where code = 'XXX' if no rows return, run the follow query select * from Table1 where code is null

  1    2   3   4   5   6  ... 下一页 最后一页 共 173 页