FAQ Database Discussion Community


oracle sql pl/sql how to subtract dates/workweeks

sql,oracle,date,plsql
I have two columns as below. I calculated difference column by using ww1-ww2. But i didn't get correct results on first 2 rows. How should i modify my calculation? I tried to_date(ww1)-to_date(ww2) but it didnt work :( ww1 ww2 difference 201401 201347 54 201401 201346 55 201405 201404 1 201406...

Remove simple HTML-Tags from String in Oracle via RegExp, Explanation needed

regex,oracle,plsql
I do not understand, why my columns reg1 and reg2 remove "bbb" from my string, and only reg3 works as expected. WITH t AS (SELECT 'aaa <b>bbb</b> ccc' AS teststring FROM dual) SELECT teststring, regexp_replace(teststring, '<.+>') AS reg1, regexp_replace(teststring, '<.*>') AS reg2, regexp_replace(teststring, '<.*?>') AS reg3 FROM t TESTSTRING REG1...

PL/SQL stored procedure which outputs values depending on user input value

oracle,stored-procedures,plsql
I have an Oracle table (sample): +----------------------------+----------------+-----------------------+ | DEPT | BUILDINGID | GEOMETRY | +----------------------------+----------------------------------------+ | Emergency Services AMB | 516 | [MDSYS.SDO_GEOMETRY] | | Emergency Services AMB | 287 | [MDSYS.SDO_GEOMETRY] | | Emergency Services FIRE | 283 | [MDSYS.SDO_GEOMETRY] | | Emergency Services FIRE | 460 | [MDSYS.SDO_GEOMETRY]...

oracle unimplemented feature error while inserting

oracle,plsql
i have a object type header_o create or replace type header_o as object( col1 number, col2 number, col3 number); Then i create a table: create table tab1( id number, header header_o, ins_date date); I have a procedure that assigns values to a record. create or replace package pck1 as type...

PL/SQL: Join between two tables error [on hold]

sql,table,join,plsql
I have got a task in which I have to display information from two different tables using a cursor. I tried doing it with an example we did before, but it doesn't seem to work! Below mentioned are the two tables that I have to take records from and display...

Identifier 'EXTRACTVALUE' must be declaredCompilation failed

oracle,plsql,oracle11g,oracle-apex,plsqldeveloper
I tryed to retrive xml data to variable but giving me error saying "identifier 'EXTRACTVALUE' must be declaredCompilation failed" VarConverstionFact NUMBER; BEGIN VarConverstionFact := TO_NUMBER(ExtractValue(PXML, '/OBJECT/Item/ItemUomLevelsList/ItemUomLevels/ConversionFactor')); IF VarConverstionFact > 0 THEN SELECT BIZZXE_V2_SCH.SQ_ITEM_ID.NEXTVAL INTO varID FROM DUAL; END IF; END; This is save xml nodes <Item> <LocationId>0</LocationId> <Id>0</Id> <ItemId>0</ItemId>...

PLSQL - Error in associative array

oracle,plsql,associative-array
Im trying to delete a set of tables and afterwards I want to recreate them using as select from. For couriousity I wanted to do this with an associative array. Unfortunately something is messed up, several errors appear and I can't find the reasons. This is the code: DECLARE TYPE...

ORA-04091 mutating table error when calling a function from a procedure

oracle,stored-procedures,plsql
I have a task: Write a procedure to update salary (salary * % of increment) in emp table based on grade. Use function to get increment This is my procedure: CREATE OR REPLACE PROCEDURE sal_incre IS CURSOR c_cur IS SELECT * FROM emp_task; BEGIN UPDATE emp_task SET sal = sal...

CLOB value in out/return from plsql (invalid LOB locator specified: ORA-22275)

sql,oracle,plsql,blob,clob
I've got stored plsql procedure, that takes big text from file create or replace procedure dbst_load_a_file( p_file_name in varchar2, l_clob out clob ) as l_bfile bfile; dst_offset number := 1 ; src_offset number := 1 ; lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX; warning number; begin l_bfile := bfilename( 'SCHEMES_OF_PS', p_file_name ); dbms_lob.fileopen(...

'ORA-00942: table or view does not exist' only when running within a Stored procedure

oracle,plsql
This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a previous question. If so, please provide a link to the exact logical duplicate question that...

Oracle Stored Procedure varray beyond count

oracle,stored-procedures,plsql,varray
i've created 3 stored procedures. Each of their functions are : 1. proc_insertleveluser -> insert into "leveluser" table and return the last id inserted 2. proc_insertpermissiondtl -> insert into "permission_dtl" table and return the last id inserted 3. proc_insert_relation_lpd -> insert into "lvl_permission_dtl" table with data provided from procedure 1...

Migrate records to another table

sql,oracle,plsql
I have two tables which are not fully equal but similar. They look like this: CREATE TABLE FIRST_TABLE( FIRST_ID RAW(16) NOT NULL CONSTRAINT FIRST_PK PRIMARY KEY, FIRST_NAME VARCHAR2(2000), FIRST_VALID NUMBER(1) NOT NULL, AUDIT_CRE_AT TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL, AUDIT_CRE_FROM VARCHAR2(32) DEFAULT 'system' NOT NULL, CONSTRAINT SECOND_VALID CHECK (SECOND_VALID IN (0,1))...

Why doesn't PL/SQL respect privileges granted by Roles?

oracle,plsql
When executing a PL/SQL block, any privileges granted to roles are ignored. Instead you must give specific users specific grants to run it. If I want to give DBAs access to a package or a function or a procedure, I can't give the DBA role a grant. I have to...

Revert backup table data to original table SQL

sql,database,oracle,plsql,backup
I have created a backup for my country table. create table country_bkp as select * from country; What SQL should I use to restore the country table to it's original state? I can do insert into country select * from country_bkp; but it will just have duplicate entries and probably...

How to update the rows that are not identical in terms of data in a tableB from tableA given that table structure are same in oracle?

database,oracle,plsql,rdbms
Basically I've two tables with almost 46 columns now I wanted some PL/SQL script that can update the row if found unequal from a source table A to table B ( which needs to be updated) there is one primary key on the basis of which comparison will take place...

Oracle pl/sql script which increments number

oracle,plsql
Looking for a way to create an Oracle script using the equivalent of java ++ syntax to increment a variable. Ie: int id=10 DELETE MYTABLE; INSERT INTO MYTABLE(ID, VALUE) VALUES (id++, 'a value'); INSERT INTO MYTABLE(ID, VALUE) VALUES (id++, 'another value'); ... Trying to use a variable and not a...

PL/SQL Extract Column Names and use in select statment

oracle,plsql
not sure if this is possible at all but im trying to do this with as little manual work as possible. I have a table with 150 columns based on different combinations of factors. I wish to extract the column names where a certain certain string is inside the column...

How to use subquery result as the column name of another query

sql,oracle,plsql
I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example: select item, item_type ... forcast_0 * 0.9 as finalforcast forcast_0 * 0.8 as...

Liquibase endDelimiter usage

oracle,plsql,liquibase
Trying to run this from Liquibase: --changeset blah runOnChange:true endDelimiter:\n/\s*\n|\n/\s*$ DELETE MYTABLE; INSERT INTO MYTABLE(A,B,C) VALUES ('A', 'B', 'C'); DECLARE row_count NUMBER; BEGIN SELECT COUNT(1) INTO row_count FROM OTHERTABLE; IF (row_count = 0) THEN DELETE MYTABLE WHERE A LIKE 'BLAH:%'; END IF; END; / But getting this error: Caused by:...

Pl/Sql array inside a statement

arrays,oracle,plsql
I'm trying to prepare a function, so I've started this sql sketch to figure out how to manage my situation: DECLARE x XMLType; begin x := XMLType('<?xml version="1.0"?> <ROWSET> <ROW> <START_DATETIME>29/05/2015 14:23:00</START_DATETIME> </ROW> <ROW> <START_DATETIME>29/05/2015 17:09:00</START_DATETIME> </ROW> </ROWSET>'); FOR r IN ( SELECT ExtractValue(Value(p),'/ROW/START_DATETIME/text()') as deleted FROM TABLE(XMLSequence(Extract(x,'/ROWSET/ROW'))) p )...

Error(26,14): PLS-00103: Encountered the symbol when expecting one of the following: := . ( @ % ;

oracle,plsql
I am currently new to oracle with a MSSQL knowledge. currently, i am writing down few procedures to understand the syntax . I am not able to compile the following procedure. Not sure what is going wrong . I am constantly facing the error Error(26,14): PLS-00103: Encountered the symbol "CRIT_CURSOR"...

How to design a history for n:m relations

sql,plsql,many-to-many
Problem: I have a n:m relation between a table A and another table B and it is required to have the complete history of this relation, in other words I must be able to reproduce the status of any point the past. Ideas: My first attempt is to dissect to...

How can I modify my PL/SQL procedure to go to my exception handling?

oracle,plsql,exception-handling
I am using SQL developer to write a procedure. The objective is to get the name of the drainage system from one table and get the count of how much the drainage system name code appears in another table. My procedure works, but when I enter an incorrect value, it...

Total ROWS selected in a LOOP ORACLE PL/SQL

sql,oracle,plsql
I just want to iterate from 0 to n-1 in a oracle loop, just like this: FOR X IN CLEEVECTOR LOOP But am I wonder if it is possible to use a conditional like: if X = TOTALROWS - 1 then exit; END IF: There is some recomendation, or solution...

Does a nested table retain order while not being stored?

oracle,plsql,nested-table
I have a PLSQL function that populates and returns a nested table: select distinct id bulk collect into my_nested_table from user order by id; return my_nested_table; According to the docs nested tables are multisets and have no inherent ordering. Can I nevertheless assume that the nested table returned from the...

How to fix ORA-01427

sql,oracle,stored-procedures,plsql
I'm working on converting a stored procedure from SQL to Oracle and I get this error: ORA-01427: single-row subquery returns more than one row ORA-06512: at "CMIUSER.PROCEDURE3", line 21 ORA-06512: at line 7 t_name_match and t_descript_match are global temp tables. Can someone explain to me what I'm doing wrong? create...

What is the purpose of RAISE_APPLICATION_ERROR?

oracle,exception,plsql
I understand that RAISE_APPLICATION_ERROR associates a message error which only has a meaning to the user. But can't the user just write a similar exception the following way? DECLARE e_negative EXCEPTION; BEGIN IF v_sid < 0 THEN RAISE e_negative; ... EXCEPTION WHEN e_negative THEN DBMS_OUTPUT.PUT_LINE ('An id cannot be negative');...

PS/SQL dereference from nested table

plsql
I got a nested table with reference to other object: tables: create type towar as object ( towar_id integer, nazwa varchar2(64), cena decimal(6,2) ); create table towar_tab of towar; create or replace type towar_zamowienie as object ( ilosc integer, produkt ref towar ); create type towar_zamowienie_tab as table of towar_zamowienie;...

What is the correct way of checking if varchar2 is empty?

oracle,plsql,varchar,varchar2
According to official 11g docs Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls. Consider a function getVersion that returns...

Can Oracle PL/SQL CASE statement include a SELECT query?

sql,oracle,plsql,case,plsqldeveloper
I'm trying to do something similar to this: CASE WHEN number IN (1,2,3) THEN 'Y' ELSE 'N' END; Instead I want to have a query in the place of the list, like so: CASE WHEN number IN (SELECT num_val FROM some_table) THEN 'Y' ELSE 'N' END; I can't seem to...

PLS-00103: Encountered the symbol “;” when expecting one of the following:

plsql,plsqldeveloper
What is wrong with my PLSQL? I get this error message: ORA-06550: line 4, column 0:PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:* & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >=...

PLSQL trigger - update records based on select query

plsql,triggers
I have three tables: HouseMode: mode_id (INT, PK) switch (CHAR 1BYTE) ModeDevices: modedevice_id (INT, PK) house_mode (INT, FK) houseroomdevice (INT, FK) HouseRoomDevices: houseroomdevice_id (INT, PK) switch (CHAR 1BYTE) I would like to have a trigger which updates switches from HouseRoomDevices table after updating switch in HouseMode table. My trigger: CREATE...

Oracle Function Based Index: Modify a deterministic function?

oracle,indexing,plsql,deterministic
I'm using an own DETERMINISTIC function for a function based index on one of my tables. What will happen if I modify the PL/SQL-Code of this function? (located in an package) Will the Index invalides? Do I have to rebuild it (manually)? Thanks! Edit: Here is my example script, which...

ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword

sql,oracle,plsql
I am writing some PL/SQL code for a apex database application. With the code a want to realise that when I make a purchase order, a purchase orderline is automatically generated based on the purchase order_id. However, I'm getting a ORA-04071 error running the code below: create or replace trigger...

How to see the table just modified in a script with Sql Developer

sql,oracle,select,plsql,oracle-sqldeveloper
I've this block in sql developer: begin delete from temp; insert into temp values (1); dbms_output.put_line('Done'); end; If I hit F5 the script runs fine. The table is updated with the value 1 and the "Script output" tab says 'Done'. However I would like to automatically visualize the table that...

Write to a file in PL/SQL without spools or utl_file

sql,plsql,sqlplus,spool,utl-file
Trying to create an output file through a procedure but am unable to modify the init.ora to allow for utl_file_dir or create directory. Is there another way to accomplish this without creating a table and doing a simple spool, this unfortunately, is out the question, too.

Delete duplicate records from a table only if the count is greater than 3

oracle,plsql
I have a table as follows: A|B|C 1|2|3 1|2|3 1|2|3 1|2|3 1|2|3 4|5|6 4|5|6 4|5|6 4|5|6 7|8|9 7|8|9 7|8|9 I want to remove the duplicates and retain only one value for the record which appear more than 3 times. It should be like: A|B|C 1|2|3 4|5|6 7|8|9 7|8|9 7|8|9 I...

Using LAG to Find Previous Value in Oracle

oracle,plsql
I'm trying to use the LAG function in Oracle to find the previous registration value for donors. To see my data, I started with this query to find all registrations for the particular donor: select registration_id, registration_date from registration r where r.person_id=52503290 order by r.registration_date desc; Then I used the...

Error when trying to write to file

sql,oracle,plsql,utl-file
I have the following procedure which is supposed to write some data to a file. CREATE OR REPLACE PROCEDURE export_to_xml IS F UTL_FILE.FILE_TYPE; CURSOR c_cursor IS SELECT x FROM (select xmlelement( "user", xmlforest( uname, action, time ) ) as x from table_log); action c_cursor%ROWTYPE; BEGIN F := UTL_FILE.FOPEN('XML', 'log.xml', 'w');...

Oracle stored procedure wrapping compile error with inline comments

database,oracle,stored-procedures,plsql
I am trying to wrap a stored procedure in Oracle using dbms_ddl.create_wrapped() method. If the stored procedure contains an inline comment I get ORA-24344: success with compilation error Errors: BEGIN dbms_ddl.create_wrapped('CREATE OR REPLACE PROCEDURE TEST_WRAP ' || '(' || ' NAME IN VARCHAR2 ' || ') AS ' || '...

Select fields from same table with different conditions

mysql,sql,plsql
I am working with sql language. I tried to select out some fields from one table with different conditions. query1: select PersonID,Name,count(PersonID) as column3 from Persons where (b1=true or b2=true) and workingdays<100 group by PersonID query 2: select PersonID,Name,b1+b2 as column4 from Persons where (b1=1 or b2=1) group by PersonID)...

How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?

oracle,plsql
I would like to catch specific exception for exception "ORA-06535: statement string in OPEN is NULL or 0 length" But couldn't figure out the exact "exception name" for it. Please note I don't want to catch it under generic other exceptions block.. EXCEPTION WHEN <exception_name1> THEN executable_statements; WHEN <exception_nameN> THEN...

How to create/fill a Table with the content extracted from another table?

sql,oracle,plsql
I am trying to put all results of a query in a output table. I am using Toad - Oracle SQL. the query is SELECT P_ID, M_NAME FROM CLARITY_TDL_TRAM I need the results of this query to be put in a new table with column names NAME_SD, HOUSE_NAME in a...

Run a .bat script from Oracle

oracle,batch-file,plsql
I have a batch script wich, double-clicked, populates a table with values imported from a .csv. I want to execute that script with a PL/SQL script..so I did: BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB( job_name => 'e_job', job_type => 'EXECUTABLE', job_action => 'C:\WINDOWS\system32\cmd.exe', job_class => 'DEFAULT_JOB_CLASS', comments => 'Job to call batch script on...

Result from pipelined function, always will sorted as “written”, or not?

sql,oracle,plsql,oracle12c
Needed get renumbered result set, for example: CREATE OR REPLACE TYPE nums_list IS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(from_n INTEGER, to_n INTEGER, cycle_max INTEGER) RETURN nums_list PIPELINED AS cycle_iteration INTEGER := from_n; BEGIN FOR i IN from_n..to_n LOOP PIPE ROW( cycle_iteration ); cycle_iteration := cycle_iteration + 1; IF...

Get unmatched records without using oracle minus except not in

oracle,plsql,inner-join,outer-join
Actually I have two table and each having column name, I just want the result which are not there in Table2 Table1 ---- Name --- |A| |B| |C| |D| Table2 ------ |Name| ----- |A| |B| Answer |C| |D| I am able to do it by using minus select name from...

PL/SQL can't put value into index by table

table,indexing,plsql,insert,value
I can't put my value of v_emp into my emp_table. It is all going wrong at this line: emp_table(counter) := v_emp; The error that i get is: invalid use of type name of subtype name I can't see what the problem is.. CREATE OR REPLACE PROCEDURE laatste_emp IS v_emp employees%ROWTYPE;...

PL/SQL errors; ORA-00933: SQL command not properly ended

oracle,plsql
I'm new to pl/SQL. The error I'm getting is : ORA-06550: line 3, column 2: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 2, column 1: PL/SQL: SQL Statement ignored So far I have tried adding parentheses, but no luck. I know its a syntax error, but I'm not...

Calling MySQL stored procedure with OUT parameter through Rails

mysql,ruby-on-rails,plsql
The stored procedure looks something like: CREATE PROCEDURE `manager`(IN amount decimal(9,2), IN list text, IN acc_id int(11), OUT return_code int(1)) BEGIN DECLARE exit handler for sqlexception, sqlwarning BEGIN set return_code = 1; rollback; END; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --my code set return_code = 0; END The method from rails...

How do i format Oracle Output for this Block?

plsql,oracle11g
I'm stuck on this block of statements and don't know how to program the output when the code runs to show the amount of the payment as $10.00 and the balance remaining in the same format. My code also prompts me twice for idpledge... I've got some markup in the...

insert a single value into new created column

sql,oracle,plsql,sql-insert
First of all: It sounds pretty basic to me, but i did not find anything on this topic that comes close. I am using pl-sql and have a table thats contains records. Now i have created a new column and want to insert an entry when specific conditions given. Something...

Warning in PL/SQL after function

oracle,plsql
I wrote a PL/SQL function that checks if a string is actually number or not, and the execution code. But when I excute the function, I get the follwing warning: Warning: execution completed with warning FUNCTION is_number Compiled. 13/1 PLS-00103: Encountered the symbol "BEGIN" Here is my PL/SQL code with...

Getting a missing keyword error in CASE WHEN statement

sql,oracle,plsql
I'm getting a missing keyword error in Oracle SQL and I'm not sure why. Here's my code. I want to have a column split by different times depending on the variable P1_DATE_CHOOSER. (I'm using Apex App Developer if that helps). Select START_DATE, Round(Avg(Run_TIME), 3) as "Average_RunTime", Round(Max(Run_TIME), 3) as "Max_RunTime",...

PLSQL invalid number error after fetch

plsql,numbers,fetch
sorry if this is an confusing post and the style is not proper but this is my first question but im having an error after i want to fetch c_latlong : the things he will fetch are: LAT value : 50,9459744669855 LON value : 5,9704909091251 ORA-01722: invalid number ORA-06512: at...

using Not in in SubQuery with not equal to

oracle,plsql,subquery
Following two queries are resulting into different number of records by interchanging the condition in following way: select count(1) from clientlist where userid not in (select distinct userid from Clientlist where userid in (select uniqueid from employee e where emplstatus = 'Y' )) Returning 38885 number of records select count(1)...

Pipeline table function

oracle,plsql
I would like to understand why pipelined function is not returning any results Any ideas what i am doing wrong here CREATE OR REPLACE PACKAGE MANAGE_SPACE AS g_tblspce_threshold number := 80; TYPE tblespaces_record IS RECORD( tablespace_name VARCHAR2(30), percentage_used NUMBER ); TYPE tblespaces_table IS TABLE OF tblespaces_record; function list_tblspcs_excd_thresld return tblespaces_table...

Oracle authentication not working?

oracle,plsql,oracle11g,procedure,mod-plsql
I implemented a LogOut button which logouts user from webpage. Server is Oracle HTTP server. When clicked on LogOut it executes below procedure PROCEDURE log_me_off IS BEGIN -- Open the HTTP header owa_util.mime_header('text/html', FALSE, NULL); -- Send a cookie to logout owa_cookie.send('WDB_GATEWAY_LOGOUT', 'YES', path=>'/'); -- Close the HTTP header owa_util.http_header_close;...

Query with more than one like condition, with order by LIKE

mysql,sql,database,plsql
I am using the more than one like, and try to get the order by main keyword "sun screen", but I'm not getting the result as I want, please check my query. select productName from products where product like "%sun%" or product like "%screen%" or product like "%sun screen%" order...

PL/SQL error: PLS-00103: Encountered the symbol “EXCEPTION”

oracle,plsql,database-administration,error-handling
I'm making a PL/SQL error handling template. I'm getting this error: 'PLS-00103: Encountered the symbol "EXCEPTION"' and I can't figure out what's wrong. We're using an Oracle database. DECLARE err_num VARCHAR(100); -- We're not gonna do math on this so I made it a string. err_msg VARCHAR2(100); program VARCHAR2(100); statement...

Triggers not created

oracle,plsql,database-trigger
I have three triggers in a SQL file generating an Oracle base. When i start the file, i have no message of "Trigger created" and it seems that execution is locked at the trigger creation (i have creation confirmation for everything before). Can you help me ? Here are the...

PL/SQL assinging values to a variable using cursor columns

oracle,plsql
I am trying to take some statistics against the Emp table, the create table & the rows inserted are given below. I am trying to develop a store procedure which will get all the columns for a particular table from oracle ALL_TAB_COLUMNS & I will generate the statistics. The PL/SQL...

how to select 10 rows from column in oracle

oracle,table,select,plsql
I have table in Oracle with column "identification number" which contains identification number for customers. I want to select 10 optionly identity numbers - how to do that can you help me P.S. I'm a newbee to Pl/SQL

How do I extract data from an XMLTYPE field with no namespace and label?

sql,xml,oracle,plsql
Forgive me for being a total beginner. I am looking at a column named FORM_XML that is described as data type XMLTYPE. The contents of one field is: <Form FormID="0" Name="Preventive Care(F)"> <FormObject Name="prevcare01" Type="DateTime" Label="Physical Exam" EditValue="04/05/2007" /> <FormObject Name="prevcare02" Type="DateTime" Label="Lipid Profile" EditValue="NoEditValue" /> <FormObject Name="prevcare03" Type="DateTime" Label="Health...

Need help solving Pl/sql issue

plsql
I am trying to fill a database with random data, but the primary key needs to still be unique. I can fill the database with random data, but now I am trying to solve the primary key issue. When running this code I get errors. set SERVEROUTPUT on create or...

Using if-else condition inside a insert-select block

oracle,plsql,case,insert-select
I have two tables elig(mID, startDate, endDate) and claim(mID, serviceDate). I have to check if the serviceDate is between the startDate and endDate, and insert into a new table hasClaim(mID,startDate,endDate,Flag). The flag is to be set to 'Y' if the condition in satisfied and 'N' otherwise. What I have done...

PL SQL Find Dependencies on Table Field

database,oracle,plsql,dependencies
I am needing to find all references to a table's field, and I have hundreds of stored procedures to search through. The goal is to find where it is being used in where clauses and add an extra value to the in statement. For example, where myTable.Field_X in (1,2,3) needs...

Error ORA-06550 & ORA-00933

plsql
I'm getting the following errors: ORA-06550: line 3, column 43: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 3, column 1: PL/SQL: SQL Statement ignored . declare minimum number (10); begin SELECT MIN(SWLR_ASSET_ID) from SWLR_ASSET INTO minnumber where swlr_key=:p26_swlr_id; end; I believe its a simple syntax error somewhere, but...

ORA-0131 :insufficient privileges DEBUG CONNECT SESSION

oracle,debugging,plsql
I got this error message "ORA-0131 :insufficient privileges DEBUG CONNECT SESSION" trying to start debug PL/SQL script using PL/SQL Developer. Oracle Database 11g Release 11.1.0.7.0 I'm a part of admin group. Is this privileges necessary? grant DEBUG CONNECT SESSION to =<bobens_83-here_goes_your_db-username> ...

Oracle Time Difference over a repeating set

oracle,plsql
I am sure this is an easy problem to solve however I cannot see how no matter which way round I try and look at it. I am using an Oracle database and I am trying to report on processing times. The system is running a process that picks up...

SQL error ORA-00979 Group By error

sql,database,oracle,plsql
So the question asks me that: "What is the average cost of service visits on 2009 Mercedes?" I am therefore joining two tables: ServInv and Car, to get the result but keep getting an error: SELECT car.make, car.model, servinv.totalcost, AVG(servinv.totalcost) FROM s2.servinv INNER JOIN s2.car ON servinv.cname = car.cname WHERE...

PLSQL generate random integer

sql,oracle,plsql,oracle11g,oracle-sqldeveloper
In Oracle Sql developer 11g, how do I generate a random integer and assign it to a variable? This is what I've tried so far: S_TB := SELECT dbms_random.value(1,10) num FROM dual; With this code I got error: S_TB := SELECT dbms_random.value(1,10) num FROM dual Error report - Unknown Command...

Generate random ROWID

oracle,random,plsql,rowid
I'm trying to create a random ROWID for testing purposes. I know it's not the way to go, but since I have no data (yet) I just need to guarantee when inserting the "value" of the ROWID (using ROWIDTOCHAR function) I have a different string everytime. I've been reading DBMS_ROWID...

How to refresh a select list when another item is changed by a dynamic action?

ajax,plsql,oracle-apex
I am using Apex 4.2 and Oracle XE 11G. I have a fairly complex page but have created a simplified version and imported it into apex.oracle.com and hope someone can suggest the problem. Note the design may appear a little strange due to the parts i have removed but i...

PLSQL move data from csv list stored in varchar2 to a collection of integers

sql,oracle,collections,plsql
Oracle Express 11G R2: In PLSQL I have a varchar2 containing a CSV list of values like this: vList:='1212,3232,3232,4343,54545,65654,65665,65654,788787' I would like to move these into a collection, so i create a new type: CREATE TYPE INTEGER_TT AS TABLE OF INTEGER; then declare my variable: my_list INTEGER_TT; What is the...

Getting value from cursor

oracle,plsql
I recently started studying PL/SQL and I found myself with this problem. I have a cursor that has the data for a specific person selected via code from another table. I'm fetching all the months with the salary of that person and outputting them. Now I need to make an...

exception is not coming with sql max function

oracle,plsql,exception-handling,oracle11g,max
I wrote the below query expecting NO_DATA_FOUND exception to be thrown when no rows found. BEGIN SELECT MAX(A_id) + 1 INTO id_variable from table_A; EXCEPTION WHEN NO_DATA_FOUND THEN SELECT MAX(A_id) + 1 INTO id_variable from table_A_archive; END; there is no data in table_A but no exception was thrown and eventually...

Suppose there is an update query in a procedure. How to check whether the data is updated or not?

sql,oracle,plsql
Suppose there is an update query in a procedure. How to check whether the data is updated or not?

How to store previous value and current value in for loop

sql,oracle,plsql
I have a cursor C1 which selects column status from table test. Using for loop, I have to check the previous value for status is ERROR and current value for status is VALID.If so then I have to update table test1. E.G. for 1 in 1..c1 loop if previous.status =...

ORA-00947 : Not Enough Values while putting values into a type inside a procedure

oracle,plsql,user-defined-types,ora-00947
I have created some Oracle types: create or replace TYPE "RESULTS_ADMIN" AS OBJECT ( ROWNUMBER NUMBER, ASSET_ID VARCHAR2(1000 CHAR), BOOK_ID VARCHAR2(10 CHAR), asset_name VARCHAR2(50 CHAR) , book_author VARCHAR2(30 CHAR) , asset_location VARCHAR2(30 CHAR), asset_cat VARCHAR2(50 CHAR), asset_type VARCHAR2(10 CHAR), publisher_name VARCHAR2(50 CHAR), books_available NUMBER ); create or replace TYPE "RESULT_ADMIN_TEMP"...

PL/SQL Check if SYSDATE is between two DATETIMES “HH24:mi”

sql,oracle,plsql
I'm trying make a function to check if my sysdate is between 2 datas on the HH24:mi. If this is true then it needs to return 1 if its not return 0. I tried this one but without succes: Check if current date is between two dates Oracle SQL Here...

Return the sum of the count of two seperate tables PL/SQL

database,oracle,plsql,oracle12c
I'm trying to search through two separate tables for the COUNT of a particular value and return it from a function. FUNCTION check_parts (p_partno IN VARCHAR2) RETURN NUMBER IS out_exists NUMBER; sub_exists NUMBER; BEGIN SELECT COUNT(*) INTO out_exists FROM outline_pn op WHERE op.outline_pn = p_partno UNION ALL SELECT COUNT(*) FROM...

Oracle Trigger with two tables

database,oracle,plsql,triggers
I want to build a trigger to fix some kind of bug inside a Program. There are different rows in t2 and the program merges selected ones together in t1 and gives them one single id. So n-rows in t2 are 1 row in t1. Now i want to restrict...

Separate handling for two posisble unique constraint exceptions on the same insert

sql,oracle,plsql
I'm trying to catch two different exceptions from the same statement in PL/SQL. If one exception is raised then it needs to keep on looping If the other one is raised then it needs to exit the loop And if the insert is successful it needs to exit the loop....

oracle sql varray contains an element

oracle,plsql,varray
I have a type declaration like this: FUNCTION ... IS TYPE stati_va IS VARRAY (10000) OF varchar(1); stati stati_va; v_counter INTEGER := 0; BEGIN stati := stati_va (); --this is actually in a loop so the array contains more values v_counter := v_counter + 1; stati.EXTEND; stati (v_counter) := '4';...

PL/Sql query execution error

oracle,plsql,triggers,procedure
This is my query. A Trigger which automatically stores in a separate table called ‘ExcellentSale’ the Sales Agent name, car model and manufacturer name, each time the agreed price of a SalesTransaction is more than 20% of the car’s asking price. (Note: You need to create the ‘ExcellentSale’ table before...

How to update table1 field by using other table and function

oracle,plsql,sql-update
I have two table and one function, Table1 contains shop_code,batch_id,registry_id shop_code| batch_id|registry_id 123 | 100 |12 124 | 100 |13 125 | 100 |12 Table2 contains shop_code,shop_name shop_code| shop_name 123 | need to populate 124 | need to populate 125 | need to populate Function1 take parameter registry_id from table1...

Bind variable Error while creating trigger on table

plsql
I am PL/SQL newbie. I am not able to create trigger getting bind variabale errors for all variables. CREATE OR REPLACE TRIGGER ddl_change_after_insert1 AFTER INSERT ON changeddl.stats$ddl_log FOR EACH ROW DECLARE v_osuser varchar2(100); v_current_user varchar2(100); v_host varchar2(100); v_terminal varchar2(100); v_owner varchar2(30); v_ddl_date date; v_object_type varchar2(50); v_object_name varchar2(100); v_ddl_type varchar2(30); v_sql_txt...

EXECUTE IMMEDIATE PL/SQL?

sql,oracle,plsql,oracle11g
CREATE OR REPLACE TRIGGER P88 AFTER INSERT ON reparation FOR EACH ROW DECLARE vope number; BEGIN SELECT observation_reparation into vope from repartion; if(vope IS NULL)THEN EXECUTE IMMEDIATE 'ALTER TABLE ' || reparation.observations_Reparation || ' MODIFY libelle_piece NVARCHAR2(50)'; END IF; END; / I get this: error:table or view does not exist....

ORA-06502 Error Only in SQLPLUS

oracle,plsql,oracle11g,sqlplus
I have an Oracle 11 database on a development server. I'm trying to execute a stored procedure: declare date_start varchar2(15); date_end varchar2(15); begin Fecha_inicio := '01/01/2014'; Fecha_fin := '01/01/2016'; FCBK.PKG_PACKAGE.Generate(Fecha_inicio,Fecha_fin); end; If I execute the procedure from SQL Developer or from SQL*Plus on my personal Windows machine, the procedure has...

How do I fix function compilation error?

oracle,function,plsql,sql-update
--this is my function create or replace function sal_incr ( p_grade number) return number is v_inc number; begin select raise_percent into v_inc from sal_inc where grade_id in (select grade_id from emp_task where grade_id = p_grade); return v_inc; end; -- Update statement update emp_task set sal = sal + sal_incr(grade_id); --...

04098. “trigger '%s.%s' is invalid and failed re-validation”

oracle,plsql
I would like to write a trigger which blocks deleting records from table and blocks decreasing salary on update. It works well as two seperate triggers, but I would like to merge it into one. Here is my trigger after merging: CREATE OR REPLACE TRIGGER test BEFORE DELETE OR UPDATE...

Using XMLTABLE and xquery to extract data from xml

oracle,plsql,xquery,xmltable
I have the following piece of XML: <per:Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.something.com/2014/11/bla/webservice.xsd" xmlns:per="http://www.something.com/2014/11/bla/person"> <per:Initials>E.C.</per:Initials> <per:FirstName>Erik</per:FirstName> <per:LastName>Flipsen</per:LastName> <per:BirthDate>1980-07-01</per:BirthDate> <per:Gender>Male</per:Gender> </per:Person> From this xml I want to...

subtract two dates from a table in sql

sql,oracle,date,plsql,date-difference
I have a table which has date of births of some employees. How can I find the age difference between two employees?

PL/ SQL Loop No data found

sql,plsql,oracle11g,oracle-apex
I tried to take values for VarNewIdOne as a bulk collection. So i got values to bulk collection after i tried to equel that values to another variable (VarOldLevlIdOne) then getting out put as a NO data found. SELECT ITEM_ID bulk collect INTO VarNewIdOne FROM BIZZXE_V2_SCH.ITEMS WHERE PARENT_ITEM_ID = VarId;...

Gaps and Islands solution in Oracle - use of recursive

oracle,plsql,group
I have a problem that could be easily solved using curser in Oracle. However, I wonder if that could be done using select only. I have 1 data set that contains the following fields: Start, Description, MaximumRow, SequentialOrder. The data set is ordered by Description, Start, SequentialOrder. This is the...

Generate a .log file plsql / oracle

sql,plsql,oracle11g,plsqldeveloper
What i want do, is to generate a .log file that describes all my error, start time, end time, and so one. I found a way to have something like that but not in corect way. I want to generate that file automaticaly, without being required to define it manually....

PL/SQL Trigger in Oracle errors

oracle,plsql,triggers
i don't know how to start !! i have a work in oracle database,and it is all about triggers and constraints ... the work is to create triggers and constraints on some tables of database of league of hokey ... and since i'm new , and not familiar with triggers...

PLSQL Need REFCURSOR DATE + TIME

date,time,plsql,cursor
im using this code and I had 1 problem, the o_besteltijden cursor has this as output: 01-06-15 ( a date), but I prefer output like: 16:00 (so only time) Or 01-06-15 16:00:00 (date + time). is that possible? SET SERVEROUTPUT ON; CREATE OR REPLACE TYPE t_openingstijd IS TABLE OF DATE;...

UTL_file: continue reading even if it encounters blank rows

sql,plsql,plsqldeveloper
I'm new in pl/sql and maybe it is sounds silly,but I have a question. By Using utl_file,I'm reading a text file. But my text file contains blank spaces between rows, and I need to continue to read all content even after that blank space. below I displayed my code that...

NVL WITH DISTINCT IN PL/SQL [closed]

oracle,plsql
why NVL function is not working with distinct in pl/sql? Eg: select DISTINCT (NVL(b.state_id,'0')) From branch_detail b where b.state_name like ('TAMILNADU%') Here is the table structure( branch_detail ===================== state_id state_name -------- --------- 1 kerala 1 kerala 2 karnataka 2 karnataka Expected Output: 0...