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...
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 -- ... ...
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)...
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,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...
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" />...
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...
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); ...
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...
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...
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"...
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...
oracle,plsql,oracle11g,sqlplus
Problem solved. Was a internal error of the package. Thanks for all the answers.
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....
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; ...
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;...
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...
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....
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...
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...
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...
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).
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....
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...
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 ...
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,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...
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...
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...
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...
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; ...
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...
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....
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...
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()...
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...
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...
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)...
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...
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 .........
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...
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...
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...
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....
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....
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'...
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;...
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-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...
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; ...
Put / after every END; statement. When you put / it tells compiler that execute sql statement present in buffer or above /...
sql,plsql,oracle11g,plsqldeveloper
I found the probleme ! Where I stored my files, I had no right to create files / folders. THANKS all !
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...
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;...
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....
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...
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...
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...
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....
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...
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:...
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...
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...
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...
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...
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...
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;...
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...
I think you want update, not insert: updatE myTable set newColumn = newValue where oldColumn = 'something'; ...
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...
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...
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; ...
: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...
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...
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; ...
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...
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...
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:...
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...
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...
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...
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,...
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,...
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...
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 --...
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...
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...
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...
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...
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...
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...
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)...
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...