Result from pipelined function, always will sorted as “written”, or not?
Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated pipelined-table-functions This means, it will start processing the rows before get fetched completely and that's why...
PL/SQL: Join between two tables error [on hold]
Your cursor does select d.dlocof the table dept d, but it is named loc in your screenshot. CURSOR staff_cursor IS SELECT e.empno, e.ename, e.sal, d.dname, d.loc -- ... ...
PL/Sql query execution error
oracle,plsql,triggers,procedure
You don't need to query SALESTRANSACTION within a row trigger on SALESTRANSACTION - you should instead be using the :NEW values on the SALESTRANSACTION row for which the trigger was invoked. I suggest rewriting your trigger as: create table excellentsales (EXCELLENTSALES_ID NUMBER CONSTRAINT PK_EXCELLENTSALES PRIMARY KEY USING INDEX, agentid varchar2(5)...
Using XMLTABLE and xquery to extract data from xml
As per your first question, the documentation you linked has this to day about omitting PATH: The optional PATH clause specifies that the portion of the XQuery result that is addressed by XQuery expression string is to be used as the column content. If you omit PATH, then the XQuery...
Oracle authentication not working?
oracle,plsql,oracle11g,procedure,mod-plsql
I recommend using Custom OWA and your own cookie as a means of authenticating users. Set up the DAD to authorize the schema using custom_owa. Then create the custom_owa.authorize package/function in your schema. The custom_owa.authorize function will be called before each page is accessed. If it returns true the request...
How do I extract data from an XMLTYPE field with no namespace and label?
You can use XMLTABLE to convert XML to rows and columns and then apply your filter. Query: SQL> with x(y) as ( select xmltype('<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 Care Proxy review" EditValue="NoEditValue" />...
PL/SQL stored procedure which outputs values depending on user input value
oracle,stored-procedures,plsql
Designing procedures requires a bit of planning and thought. You need to understand the requirements and implement them in code. So the first requirement is: the user is asked to insert a department they wish This means your procedure should take a single parameter: CREATE OR REPLACE PROCEDURE outputBuilding (i_building_type...
PLSQL trigger - update records based on select query
Your update statement is wrong, because your WHERE condition is always true. Use this one: UPDATE houseroomdevices SET switch = :NEW.switch WHERE houseroomdevice_id IN (SELECT houseroomdevice FROM modedevices WHERE house_mode = :NEW.mode_id); ...
using Not in in SubQuery with not equal to
Table ClientList Table employee Id Id Status -- -- ------ 1 1 Y 2 2 N 3 3 null 4 First query counts id's: 2, 3, 4 - rows which are in ClientList and are not in employee and marked as Y. Second query: id 2 - shows rows...
Generate random ROWID
Rather than generate your own random value, which may or may not point to an actual object (but probably not), you could get the real ROWID of a random row from a real table. If you don't have your own data you could use any table you can see; look...
Why doesn't PL/SQL respect privileges granted by Roles?
Otherwise if you drop a role then the PL/SQL package would become INVALID in some cases (without having the option to re-compile). DROP ROLE ... is a DCL (Data Control Language) statement. Looks like Oracle decided: "A PL/SQL package shall not become INVALID by a DCL statement"...
How to update table1 field by using other table and function
You want the MERGE statement. Something like this might work: MERGE INTO TABLE2 t2 USING ( SELECT GET_shop_name(t1.batch_id) AS shop_name , t1.shop_code shop_code FROM TABLE1 T1 ) t1 ON (t2.shop_code = t1.shop_code) WHEN MATCHED THEN UPDATE SET t2.shop_name = t1.shop_name ; You'll have to excuse if the exact code above...
ORA-06502 Error Only in SQLPLUS
oracle,plsql,oracle11g,sqlplus
Problem solved. Was a internal error of the package. Thanks for all the answers.
PLSQL Need REFCURSOR DATE + TIME
The problem here seems to be as follows: You are setting o_besteltijden as a value selected from CAST(v_besteltijden AS t_openingstijd) where v_besteltijden is calculated from to_date t_openingstijd is a table of date type (CREATE OR REPLACE TYPE t_openingstijd IS TABLE OF DATE;). So your answer will be in date format....
Error ORA-06550 & ORA-00933
You have your INTO in the wrong place, and you also declared a variable named minimum but then selected into minnumber. The correct code would be: declare minimum number (10); begin SELECT MIN(SWLR_ASSET_ID) INTO minimum from SWLR_ASSET where swlr_key=:p26_swlr_id; end; ...
How to store previous value and current value in for loop
To my understanding, this should do the work for your with minor changes: declare v_previous_status test.status%type; cursor c1 is select status from test; begin open c1; fetch c1 bulk collect into l_status; for i in 1..l_status.count if (v_previous = 'ERROR' and l_status(i) = 'VALID' ) then update .... end if;...
Can Oracle PL/SQL CASE statement include a SELECT query?
sql,oracle,plsql,case,plsqldeveloper
Yes, it's possible. See an example below that would do what you are intending. The difference is that it uses EXISTS instead of IN. SELECT a.number, (CASE WHEN EXISTS (SELECT null FROM some_table b where b.num_val = a.number) THEN 'Y' ELSE 'N' END) AS YES_NO FROM some_other_table a; EDIT: I...
oracle unimplemented feature error while inserting
Adding this as an answer since it has solved the problem: Do not define another type (t_header) but declare l_header as header_o and it should work fine....
PLSQL generate random integer
sql,oracle,plsql,oracle11g,oracle-sqldeveloper
Variables require PL/SQL; it's not clear from your question whether your code is a proper PL/SQL block. In PL/SQL variables are populated from queries using the INTO syntax rather than the assignment syntax you're using. declare txt varchar2(128); n pls_integer; begin -- this is how to assign a literal txt...
What is the correct way of checking if varchar2 is empty?
Assuming that you are using varchar2 throughout your code, l_version is null will be future proof. Oracle created the varchar2 data type when the ANSI standards declared that varchar should treat NULL and the empty string as separate entities. The intention was that the behavior of the varchar2 data type...
SQL error ORA-00979 Group By error
Have your dimensions such as make and model in group by. Include statistics with your numeric fields. SELECT car.make, car.model, SUM(servinv.totalcost), AVG(servinv.totalcost) FROM s2.servinv INNER JOIN s2.car ON servinv.cname = car.cname WHERE car.make = 'MERCEDES' AND car.cyear = '2009' GROUP BY car.make, car.model; This way, we tell the DB to...
'ORA-00942: table or view does not exist' only when running within a Stored procedure
Sounds like an issue with select privileges granted via a role, rather than directly to the schema. See ORA-00942: table or view does not exist (works when a separate sql, but does nto work inside a oracle function).
Write to a file in PL/SQL without spools or utl_file
sql,plsql,sqlplus,spool,utl-file
No. You will need access to init.ora to get this done the nice way through Oracle. The only other option I can think of is the use of a Java procedure to do the file writing. I couldn't find any special requirements you need to have set to use that....
Total ROWS selected in a LOOP ORACLE PL/SQL
So you want to process all but the last value returned by the cursor. Since a cursor doesn't know how many rows it will return you can't just say "Break if this is the second-to-last row". But you can save the value to be processed, then process it on the...
Query with more than one like condition, with order by LIKE
You can try to use ORDER BY CASE: SELECT p.productName FROM products p WHERE p.productName LIKE '%sun%' OR p.productName LIKE '%screen%' OR p.productName LIKE '%sun screen%' ORDER BY CASE WHEN p.productName LIKE '%sun screen%' THEN 1 ELSE 2 END ...
What is the purpose of RAISE_APPLICATION_ERROR?
raise_application_error does more than print an error message to the console (like dbms_output.put_line does). First, it's an actual error - it fails the statement, terminates the current block's execution, and propagates to outer blocks (similar to throw in Java or raise in Python). Second, it actually returns this error regardless...
Oracle Function Based Index: Modify a deterministic function?
oracle,indexing,plsql,deterministic
Yes, you have to rebuild the index. Check this link on Oracle Docs, section Disadvantages of Function-Based Indexes. An index does store physical data, be it function-based or otherwise. If you modify the underlying deterministic function, your index no longer contains the valid data and you have to rebuild it...
CLOB value in out/return from plsql (invalid LOB locator specified: ORA-22275)
At this point: dbms_lob.loadclobfromfile( DEST_LOB => l_clob ... your l_clob OUT parameter hasn't been initialised. Making it an empty CLOB doesn't work either (so even if you made l_clob an IN OUT parameter it would still complain) as the documentation for empty_clob mentions: You cannot use the locator returned from...
PL/SQL Trigger in Oracle errors
Surely, there are other way to implement your logic, but if you want to use your trigger, the following works for me Create equipe: CREATE TABLE equipe ( ID_EQ number(6) not null, NOM varchar2(50), ENREGIS number(6), ID_CAPITAINE number(6), ID_ENT number(6), CONSTRAINT equipe_pk PRIMARY KEY (ID_EQ) ); Create joueur: create table...
PL/SQL assinging values to a variable using cursor columns
For executing the SQL statements created dynamically, you need to use EXECUTE IMMEDIATE: create or replace procedure p_profiling (V_tablename IN varchar2) IS cursor c1 is select TABLE_NAME, COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='V_tablename'; REC_CNT NUMBER; distinct_cnt NUMBER; is_valid NUMBER; not_null NUMBER; BEGIN FOR table_rec in c1 LOOP IF c1%ROWCOUNT = 1...
How to see the table just modified in a script with Sql Developer
sql,oracle,select,plsql,oracle-sqldeveloper
You can use CURSOR: declare a temp.id%type; --name column in your table cursor c1 is select id from temp; begin delete from temp; insert into temp values (1); open c1; loop fetch c1 into a; dbms_output.put_line (a); exit when c1%notfound; end loop; CLOSE C1; end; ...
04098. “trigger '%s.%s' is invalid and failed re-validation”
Your IF statement syntax is incorrect. It should be: CREATE OR REPLACE TRIGGER test BEFORE DELETE OR UPDATE ON emp FOR EACH ROW BEGIN IF UPDATING AND :NEW.sal < :OLD.sal THEN raise_application_error(-20500, 'You cannot decrease emp salary'); ELSIF DELETING THEN raise_application_error(-20500, 'You cannot delete records from emp'); END IF; END...
Oracle Trigger with two tables
database,oracle,plsql,triggers
Hello sorry for the late answer and the unclear question. I solved the question myself. I created a staging table with a trigger and this works fine for me. Thank you all for your input....
Does a nested table retain order while not being stored?
First of all you should know, what is NESTED TABLE According to Oracle Doc Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows...
How to fix ORA-01427
sql,oracle,stored-procedures,plsql
The only place (that I notice) where you have a "single row subquery" is: UPDATE t_name_match nm SET nm.DESCRIPT = (SELECT x.DESCRIPT from CHAR_FEATURE_XRF x where x.KBID = nm.KBID and x.SYMBOLID = nm.SYMBOLID ); How you fix it depends on what you want to do. Two simple ways are MAX()...
Getting value from cursor
You don't need cursor to get average salary as single value. You need to use aggregate functions. And even if you needed a cursor to, lets say, return data to calling code, you still use aggregate function to calculate averages and return cursor of that. This is just an example...
Oracle stored procedure wrapping compile error with inline comments
database,oracle,stored-procedures,plsql
If you execute, show errors after the call, you will see a message like this: 1/70 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior Here is a simplified version of your...
Get unmatched records without using oracle minus except not in
oracle,plsql,inner-join,outer-join
The one option left with you is using NOT EXISTS SELECT t1.name FROM table1 t1 WHERE NOT EXISTS (SELECT 'X' FROM table2 t2 WHERE t2.name = t1.name); Update: Using Join with table_ as ( select t1.name t1_name, t2.name t2_name from table1 t1 left join table2 t2 on t1.name = t2.name)...
PL/SQL errors; ORA-00933: SQL command not properly ended
when you want to a write in plsql a block statement you have to include begin and end; and you have to decalre your variables declare p26_swlr_id number(specify a number); v_name varchar2(500); v_name1 varchar2(500); begin p26_swlr_id :=33;( or if its from a table select swrl_id into p26_swlr_id from table where...
How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?
Some exceptions have names such as 'TOO_MANY_ROWS'. However, most of oracle exceptions do not have names. So if you want to trap any of them, you need to give them names. For your exception, you can do something like this: DECLARE .... NULL_STRING EXCEPTION; PRAGMA EXCEPTION_INIT(NULL_STRING, -06535); .... Begin .........
PLSQL invalid number error after fetch
I'm not sure what is the data type of LAT and LON in your table POSTCODE. But whatever variables you define to fetch data into, they must match these types. For examples, assuming that LAT and LON are of the type NVARCHAR2, then I would rewrite your declaration section as...
Identifier 'EXTRACTVALUE' must be declaredCompilation failed
oracle,plsql,oracle11g,oracle-apex,plsqldeveloper
ExtractValue isn't a PLSQL function, and can only be used as part of an SQL statement I think. You could either do: select to_number(ExtractValue(PXML, '/OBJECT/Item/ItemUomLevelsList/ItemUomLevels/ConversionFactor')) INTO VarConverstionFact FROM DUAL; or VarConverstionFact := PXML.extract('/OBJECT/Item/ItemUomLevelsList/ItemUomLevels/ConversionFactor').getNumberVal(); EDIT: I'm assuming PXML is an XMLTYPE...
Liquibase endDelimiter usage
try DECLARE row_count NUMBER; BEGIN DELETE MYTABLE; INSERT INTO MYTABLE(A,B,C) VALUES ('A', 'B', 'C'); SELECT COUNT(1) INTO row_count FROM OTHERTABLE; IF (row_count = 0) THEN DELETE MYTABLE WHERE A LIKE 'BLAH:%'; END IF; END; or with a "/" after each statement...
Warning in PL/SQL after function
Following this line: END is_number; You should add a slash in order to execute the CREATE FUNCTION statement. Without that, the parser continues to read the following lines as part of that statement, but since you have already ended the function, they are invalid....
Migrate records to another table
Just use insert . . . select: insert into second_table(second_id, second_name, audit_cre_at, audit_cre_from) SELECT first_id, first_name, audit_cre_at, audit_cre_from FROM FIRST_TABLE f WHERE f.FIRST_VALID = 1; You don't need PL/SQL for this. Also, when comparing numbers, use numeric constants and =, not like and strings....
PL/SQL Extract Column Names and use in select statment
I do not think you can do that in one query. First, your extract column names query can be simplified to one query as a cursor, and then use a dynamic select statement as follows: CREATE OR REPLACE proc_dyn_select IS CURSOR c1 IS SELECT column_name FROM user_tab_cols WHERE LOWER(table_name) ='temp'...
How can I modify my PL/SQL procedure to go to my exception handling?
oracle,plsql,exception-handling
The CURSOR..FOR loop has the property of executing zero or more times. It doesn't throw NO_DATA_FOUND. There are a couple of solutions. One is to include a count inside the loop, and raise an exception afterwards. l_count := 0; FOR r_variable in c_river loop .... l_count := l_count + 1;...
Oracle Time Difference over a repeating set
Solution with function lead(): select tsource, starttime, endtime from ( select tsource, ttime starttime, status, lead(ttime) over (partition by tsource order by ttime) endtime from test) where status = 'Started' SQLFiddle Edit: If there may happen that you have two rows with status Started consecutively without Finished between then you...
Oracle Stored Procedure varray beyond count
oracle,stored-procedures,plsql,varray
You've created an empty array so the count will be 0 when you get to the loop. As Bob Jarvis points out, if you want to iterate through the loop 13 times, you'd want to use the limit, not the count of the array. SQL> ed Wrote file afiedt.buf 1...
Revert backup table data to original table SQL
sql,database,oracle,plsql,backup
Instead of dropping the table, which, as you noted, would lose all the permission defitions, you could truncate it to just remove all the data, and then insert-select the old data: TRUNCATE TABLE country; INSERT INTO country SELECT * FROM county_bkp; ...
Triggers not created
Put / after every END; statement. When you put / it tells compiler that execute sql statement present in buffer or above /...
Generate a .log file plsql / oracle
sql,plsql,oracle11g,plsqldeveloper
I found the probleme ! Where I stored my files, I had no right to create files / folders. THANKS all !
exception is not coming with sql max function
oracle,plsql,exception-handling,oracle11g,max
No, it won't go into exception. MAX will not raise no_data_found as it will return a NULL value. See this: SQL> select max(a_id) from table_a; MAX(A_ID) ---------- SQL> select a_id from table_a; no rows selected SQL> is there any other alternative than taking the count() and then getting the value...
PL/SQL Check if SYSDATE is between two DATETIMES “HH24:mi”
Using TRUNC on a date sets it as 00:00 on that day. I assume what you're after here is "Check if the, right now, is between X and Y". Like Mick said, the following should be good: SELECT count(*) INTO v_temp_suc FROM dual WHERE to_char(sysdate, 'HH24:MI') BETWEEN i.open AND i.gesloten;...
PL/SQL can't put value into index by table
table,indexing,plsql,insert,value
Your TYPE emp_table is table of employees%ROWTYPE INDEX BY PLS_INTEGER; only declares a type, not an actual variable you can write into. You need to add the variable too: TYPE emp_table_type is table of employees%ROWTYPE INDEX BY PLS_INTEGER; emp_table emp_table_type; Please note I added "_type" suffix into your definition....
Error(26,14): PLS-00103: Encountered the symbol when expecting one of the following: := . ( @ % ;
your problem is that the second cursor is not declared within a DECLARE block, you will also need to redeclare the variable you are using (CRITVALID) create or replace PROCEDURE STUDY_ORA AS BEGIN DECLARE CRITVALID INTEGER; CURSOR CRIT_CURSOR IS SELECT ID FROM USERLIST; BEGIN OPEN CRIT_CURSOR; LOOP FETCH CRIT_CURSOR INTO...
Using if-else condition inside a insert-select block
oracle,plsql,case,insert-select
Try: INSERT INTO hasClaim (mID, startDate, endDate, has_claim) SELECT e.mID, e.startDate, e.endDate, CASE WHEN c.serviceDate BETWEEN e.startDate AND e.endDate THEN 'Y' ELSE 'N' END AS has_claim FROM elig e inner JOIN claim c ON e.mID=c.mID; This uses the searched case statement (from the 10g docs but it is the same...
Getting a missing keyword error in CASE WHEN statement
Select START_DATE, Round(Avg(Run_TIME), 3) as 'Average_RunTime', Round(Max(Run_TIME), 3) as 'Max_RunTime', Round(Median(Run_time), 3) as 'Median_RunTime' from (Select job_id, (Case :P1_DATE_CHOOSER WHEN 'Daily' THEN TRUNC(start_time) WHEN 'Weekly' THEN to_char(start_time, 'DAY') WHEN 'Monthly' THEN to_char(start_time, 'MONTH') END) AS 'START_DATE', 1440*(END_TIME - START_TIME)) as "RUN_TIME" from NI_INFA_ACTIVITY_LOG_V ) group by START_DATE order by START_DATE...
PS/SQL dereference from nested table
Replace the line select (deref(v)) into tow from r.towary(i) v; with select deref(r.towary(i).produkt) into tow from dual; r.towary(i) isn't a table so you can't select from it. Furthermore, tow has type towar, and r.towary(i) is a towar_zamowienie, so I assume you want to access the produkt attribute....
ORA-0131 :insufficient privileges DEBUG CONNECT SESSION
As you mentioned , its problem of privileges. Quoted from this site ORA-01031: insufficient privileges Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle...
NVL WITH DISTINCT IN PL/SQL [closed]
The NVL() function replaces a NULL in a result set. There must be returned rows for it to work. "how can I handle if it returns no row" You need to generate a row. This might be a bit of a cheat but it does what you want to do:...
How do i format Oracle Output for this Block?
As mentioned at Docs you can use the to_char with a proper formatter. In your case it would be to_char(pledge_bal, '$9,999.99') Using the format as '$9,999.99' fulfills the following objectives : Returns a comma in the specified position. You can specify multiple commas in a number format model. Returns a...
PL/ SQL Loop No data found
sql,plsql,oracle11g,oracle-apex
Obviously table ITEM_UOM_LEVELS sometimes has no entry for an ITEM_ID and thus results in a NO DATA FOUND error. So use a subselect instead: SELECT ITEM_ID bulk collect INTO VarNewIdOne FROM BIZZXE_V2_SCH.ITEMS WHERE PARENT_ITEM_ID = VarId; FOR k IN VarNewIdOne.First ..VarNewIdOne.Last LOOP DELETE FROM BIZZXE_V2_SCH.ITEM_UOM_LEVEL_CONTROLS WHERE LEVEL_ID IN ( SELECT...
Pl/Sql array inside a statement
You're wrapping a string in explicit single quotes; that is making the quotes part of the string itself, which you don't want. You need to convert the string to a data type, which you are sort of doing in a commented-out section - in that case you do need the...
Separate handling for two posisble unique constraint exceptions on the same insert
The simplest thing is not to hit the first exception at all. There is a hint to ignore the duplicate violation, but that would apply to both unique constraints, so it isn't useful here. You could query to see if there is already a record with the WINKEL_ID and only...
EXECUTE IMMEDIATE PL/SQL?
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 RENAME COLUMN observations_Reparation TO libelle_piece'; END IF; END; if you also need to change the declaration of the column you...
PLS-00103: Encountered the symbol “;” when expecting one of the following:
No need (and not valid) to add a block label outside of the code. Try something like: DECLARE o_mgr_id NUMBER(6) := 1; dept_count number := 0; BEGIN SELECT count(*) INTO dept_count FROM EMP WHERE EMPNO = o_mgr_id; IF dept_count > 0 THEN <<inner_block>> DECLARE dept_name VARCHAR2(30); i_mgr_id NUMBER(6) := 1;...
How to refresh a select list when another item is changed by a dynamic action?
You need to add both P1_PRIMARY_INSTANCE and P1_PRIMARY_VERSION to the list of items to submit under the cascading lov options. These drive your list, and they're only being changed on the page itself. Only submitting P1_PRIMARY_INSTANCE will mean that P1_PRIMARY_VERSION is still NULL when the query is executed, thus returning...
insert a single value into new created column
I think you want update, not insert: updatE myTable set newColumn = newValue where oldColumn = 'something'; ...
Remove simple HTML-Tags from String in Oracle via RegExp, Explanation needed
Because regex is greedy by default. I.e. the expressions .* or .+ try to take as many characters as possible. Therefore <.+> will span from the first < to the last >. Make it lazy by using the lazy operator ?: regexp_replace(teststring, '<.+?>') or regexp_replace(teststring, '<.*?>') Here the search of...
How do I fix function compilation error?
oracle,function,plsql,sql-update
I would try not to use a function at all. Using straight SQL is usually a better solution. Based on the information you gave we have the following tables. (Your function does compile with these tables.) CREATE TABLE emp_task (emp_id NUMBER PRIMARY KEY ,grade_id NUMBER ,sal NUMBER); CREATE TABLE sal_inc...
Need help solving Pl/sql issue
use the pattern str := 'select x from t where...'; execute immediate str into var; instead of str := 'select x into var from t where...'; execute immediate str; ...
Bind variable Error while creating trigger on table
:NEW and :OLD are pseudo columns. How to use them ? :NEW.your_table_column OR :OLD.your_table_column In your trigger v_sql_txt := ',sql_txt = ' || :new.sql_text; instead, change above line to below. Your table column was sql_txt not sql_text v_sql_txt := ',sql_txt = ' || :new.sql_txt; ==Update== Concatenating newline into the string...
ORA-00947 : Not Enough Values while putting values into a type inside a procedure
oracle,plsql,user-defined-types,ora-00947
Here is why you get that message: you are selecting into a nested table. Oracle won't cast a result set to a type for you: you need to do it yourself. select RESULTS_ADMIN( row_number() over (order by a.asset_id), a.asset_id, a.book_id, asset_name , book_author , asset_location, asset_cat , l.asset_type, p.publisher_name, c.books_available...
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?
By using the MERGE I have came to the correct answer which is satisfying the requirements as mentioned in my question. MERGE INTO TABLE_A USING ( SELECT * FROM TABLE_B ) T ON ( TABLE_A.PK=TABLE_B.PK ) WHEN MATCHED THEN UPDATE SET TABLE_A.COL1 = T.COL1, ... TABLE_A COL46 = T.COL46; ...
How to create/fill a Table with the content extracted from another table?
You can create a table as the results of your query. Read up on the create table statement. Basically: CREATE TABLE new_table_name [ ( column [, ...] ) ] AS SELECT [ ( column [, ...] ) ] FROM existing table_name To go to a flat file as your post...
How to use subquery result as the column name of another query
Use your sub query as an inline table. Something like.... select item, item_type, .. decode(fore_column_name, 'foo', 1, 2) * 0.9 as finalforcast, decode(fore_column_name, 'foo', 1, 2) * 0.8 as newforcast from sales_data, ( select fore_column_name from forecast_history where ... ) inlineTable I'm assuming here that the value from the sub-query...
PLSQL move data from csv list stored in varchar2 to a collection of integers
Performance is going to vary depending on platform, configuration etc. so a definitive answer is going to be difficult. 0.1s for the regex seems a lot, and increasing to 6s to populate a collection seems excessive. Since your string only consists of numbers you can try a trick with XMLTable:...
Gaps and Islands solution in Oracle - use of recursive
This is a variation of a gaps-and-islands problem, with the added complication of the maximum number of rows in each island. This is a bit long-winded but you could start by identifying the groups caused by the sequence order: select t.*, row_number() over (partition by "Description" order by "Start") as...
PL SQL Find Dependencies on Table Field
database,oracle,plsql,dependencies
What version of Oracle are you using? If you are using at least 11.1, which introduced column-level dependency tracking, and you're not afraid to leverage some undocumented data dictionary tables, you can create a dba_dependency_columns view that will give you this information. But that will show every piece of code...
Suppose there is an update query in a procedure. How to check whether the data is updated or not?
If you call DML inside PL/SQL procedure and want to check if any data were updated then you can use implicit cursor attribute %rowcount. You need to call it immediately after you DML. update table set some_column = 'Some value' where id = p_id; if sql%rowcount = 0 then dbms_output.put_line('No...
Using LAG to Find Previous Value in Oracle
The lag is only applied within the rows that match the where clause filter, so you would only see the previous value if that was also yesterday. You can apply the lag in a subquery, and then filter in an outer query: SELECT * FROM ( SELECT P.Person_Id AS Person_ID,...
Delete duplicate records from a table only if the count is greater than 3
If I understand your question clearly, why not restrict the deletion to those rows with the desired values using subquery. Something like this: DELETE FROM test5 x WHERE x.ROWID > ANY( select y.ROWID FROM test5 y WHERE X.AA = Y.AA AND X.BB = Y.BB AND X.CC = Y.CC) AND (X.AA,...
UTL_file: continue reading even if it encounters blank rows
Maybe you could try this: ... Loop BEGIN UTL_FILE.GET_LINE(F1,V1); IF (TRIM(V1) is not null) AND ((TRIM(V1) <> CHR(10)) OR (TRIM(V1) <> CHR(13))) THEN dbms_output.put_line(V1); END IF; ... I don't know what you mean with dbms_output.put_line(emptylines); so I didn't care a bout it :). Maybe you could check if the line...
Pipeline table function
You need to populate an array. The easiest way to do this is to use the BULK COLLECT syntax. Then loop round the array and pipe out rows. Here is my revised version of your package. CREATE OR REPLACE PACKAGE BODY MANAGE_SPACE AS FUNCTION list_tblspcs_excd_thresld RETURN tblespaces_table PIPELINED AS --...
Return the sum of the count of two seperate tables PL/SQL
database,oracle,plsql,oracle12c
If you still want to have it in one query, here is the option create FUNCTION check_parts (p_partno IN VARCHAR2) RETURN NUMBER IS sum_exists NUMBER; BEGIN select count(1) into sum_exists from ( SELECT outline_pn FROM outline_pn op WHERE op.outline_pn = p_partno UNION ALL SELECT sub_assy_pn FROM sub_pn sp WHERE sp.sub_assy_pn...
PLSQL - Error in associative array
oracle,plsql,associative-array
You have some unterminated append operations || on lines: Dbms_Output.Put_Line('Dropping TABLE '|| L_Key ||); And EXECUTE IMMEDIATE 'create table schema1.' ||l_key||' as select * from schema2.'||l_tbl(l_key)||; Get rid of the || at the end. Also the way you are using LOOP is incorrect. Refer example: while elem is not null...
Select fields from same table with different conditions
Try this SqlFiddle select PersonID, Name, case when ((b1=true or b2=true) and workingdays<100) then count(PersonID) end as column3, case when ((b1=1 or b2=1)) then b1+b2 end as column4, case when (((b1=true or b2=true) and workingdays<100) and (b1=1 or b2=1)) then count(PersonID) + b1+b2 when ((b1=true or b2=true) and workingdays<100) then...
Calling MySQL stored procedure with OUT parameter through Rails
The solution below worked for me: I just changed the #{return_code) with @return_code (no need to pass return_code = -1 as input, as it is an OUT parameter): return_code = self.connection.execute("call manager(#{amount}, '#{list}', #{acc_id}, @return_code)") In the stored procedure, I replaced return_code with @return_code and added a select statement in...
subtract two dates from a table in sql
sql,oracle,date,plsql,date-difference
Subtracting two date columns in Oracle will result in the difference in days, which you could divide by 365 to get the difference in years: SELECT FLOOR(ABS((d1 - d2) / 365)) AS age_diff FROM (SELECT dob AS d1 FROM employee WHERE empId = 'some_id') t1 CROSS JOIN (SELECT dob AS...
ORA-04091 mutating table error when calling a function from a procedure
oracle,stored-procedures,plsql
Your function is referring to the same table you're using in the procedure at the point you call that function, which is what causes this error. You're updating and querying it at the same time, in a way that could cause indeterminate (or confusing) results, even though you aren't querying...
How to design a history for n:m relations
What about this commonly used model? create table cross_ref ( a_id references a , b_id references b , from_ts timestamp , to_ts timestamp , primary key (a_id, b_id, from_ts) ); (NB I used timestamp as you did; normally I would use date)...
PL/SQL error: PLS-00103: Encountered the symbol “EXCEPTION”
oracle,plsql,database-administration,error-handling
As others advised in the comments, all what you need to do is remove the first END as follows: 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 VARCHAR2(100); BEGIN program := 'assign the program here'; statment...