2018-10-22         Odelette

How to create a trigger to call the procedure with an update statement

I need to write a procedure and update trigger. When any update is done on the table, the trigger should make a call to the procedure. Procedure should update the changes in another table. In that another table old value, updated value should be there. What you described sounds like an ordinary logging; you don't really need a procedure, trigger does it all. Here's an example:SQL> create table emp_log (empno number, sal_old number, sal_new number);Table created.SQL> create or replace trigger trg_bu_emp 2 before update of sal on emp 3 for each row 4 begin ...

 oracle                     1 answers                     62 view
 2018-10-22         Eugene

Fix for Maximum length allowed for char in Oracle 4000 character

I am currently using Oracle 11g. As per my requirement, I need to store first 4000 (max limit) characters to my variable (used in triggers).For that I am using VarName = SUBSTR function(VarName, 1, 4000), but it seems like it is storing nothing (I am passing '111...'(4096 characters to store) out of which I want to store ('111...') first 4000 characters only).It worked for 4 characters but not working for maximum limits. I tried with length as 3999, 3000 but nothing worked for me.Kindly look into this and suggest me some solution for this.(*Note: I can't change the variable...

 oracle                     2 answers                     62 view
 2018-10-22         Jocelyn

Report Builder and connecting to OracleDb

Been out of the setup game for a long while.I'm having issues connecting Report Builder (and by extension SSRS) to the OracleDb.I have Report Builder 3.0 installed and have installed all components of the ODAC 32bit driver set from Oracles website as per their recommendations.I create a new data source, test and save the password - All lights are green.I create a data set (even a simple SELECT * FROM Table) and it fails error below;"The selected data extension ORACLE is not installed or cannot be loaded. Verify that the selected data extension is installed on the client fo...

 oracle                     1 answers                     94 view
 2018-10-22         Jill

select * through dblink

I have some trouble when trying to update a table by looping cursor which select from source table through dblink.I have two database DB1, DB2.They are two different database instance.And I am using this following statement in DB1:CURSOR TestCursor IS SELECT a.*, 'A' TEST_COL_A, 'B' TEST_COL_B FROM rpt.SOURCE@DB2 a;BEGIN For C1 in TestCursor loop INSERT into RPT.TARGET ( /*The company_name and cust_id are select from SOURCE table from DB2*/ COMPANY_NAME, CUST_ID, TEST_COL_A, TEST_COL_B ) values ( ...

 oracle                     1 answers                     80 view
 2018-10-22         Benedict

Oracle Analytics inside Cursor

I'm wondering why my "analytics" line inside a "cursor" in Oracle Forms generates an error. This is how it's written:CURSOR READ_C1_REC IS SELECT DISTINCT MACH, TCODE, COUNT(ALL TRANS_NO) OVER (PARTITION BY MACH, TCODE ORDER BY MACH, TCODE) TOTALPERMACHPERTCODE FROM ( SELECT .... ) ORDER BY MACH, TCODE; C1_REC READ_C1_REC%ROWTYPE;...and this is the error message during compilation:Error 103 at line 17, column 29 Encountered the symbol "(" when expecting one of the following: , from..and the error points to the part after the word "...

 oracle                     2 answers                     80 view
 2018-10-22         Lucy

How to find Current open Cursors in Oracle

What is the query to find the no. of current open cursors in an Oracle Instance?Also, what is the accuracy/update frequency of this data?I am using Oracle 10gR2 Total cursors open, by session:select a.value, s.username, s.sid, s.serial#from v$sesstat a, v$statname b, v$session swhere a.statistic# = b.statistic# and s.sid=a.sidand b.name = 'opened cursors current';Source: http://www.orafaq.com/node/758As far as I know queries on v$ views are based on pseudo-tables ("x$" tables) that point directly to the relevant portions of the SGA, so you can't get more accurate than t...

 oracle                     6 answers                     90 view
 2018-10-22         Gale

Rownum not working properly

This question already has an answer here: How to use Oracle ORDER BY and ROWNUM correctly? 5 answers The ROWNUM pseudo-column is added to the selected columns in the order Oracle retrieves the rows and then the ORDER BY clause is applied. What you appear to want is to order the rows first and then generate the ROWNUM against this ordering but that is not what your query will do.For table X, it happens that Oracle retrieves the rows in the same order as the id column. However, for table ...

 oracle                     1 answers                     102 view
 2018-10-22         Carol

Our database team wants to revoke the PUBLIC privilege to the packages UTL_FILE, UTL_HTTP, UTL_TCP, UTL_SMTP, DBMS_RANDOM

I am a Informatica developer and using source and target are Oracle database.My database team asking that do you need EXECUTE permissions to the packages UTL_FILE, UTL_HTTP, UTL_TCP, UTL_SMTP, DBMS_RANDOM to my Database.Can someone explain what is the impact of removing PUBLIC access to these packages and do I need these EXECUTE permissions to my database or not. What your database team wants to do is not unusual. However, if they do this, they risk breaking things including your applications and tools (could be Oracle tools or 3rd party tools like SQL*Navigator). They...

 oracle                     1 answers                     65 view
 2018-10-22         Tiffany

Oracle Kafka integration

I researched about the use of Golden gate, confluent and JDBC, "poor man's CDC with flashback query" What is the most effective way to load the Oracle data on Kafka producer? Flashback Query and Kafka Connect JDBC would be equivalent options. You're running a query and sending events into Kafka, not necessarily knowing what's altered in the database, but getting all ResultSets placed into the topic.GoldenGate, Attunity, or work being done in the Debezium project allow you to capture every event, as they occur, capturing even database schema modifications in near real-tim...

 oracle                     1 answers                     101 view
 2018-10-22         Marsh

ORDER BY DECODE(BLAH, [COLUMN NUMBER]) on a single column query. How does it work?

Hi I need help to understand the decode part of a query that goes something like the following.SELECT ax.animal_code FROM raw_animal_xref ax, animal_xref_type axtWHERE ax.animal_mnemonic = l_animal_mnemonic -- Example 'COUGAR'AND ax.animal_code_type = axt.animal_code_typeORDER BY DECODE (animal_type, l_type_to_be_matched, -1, -- Example 'CATS' l_current_type, 0, -- Example 'BIG CATS' nvl(axt.type_search_priority, 100)) ASC; -- EXAMPLE 'Big Cats' Priority is 1Since this query returns only 1 query, I'm a little stumped ...

 oracle                     3 answers                     78 view
 2018-10-22         Vanessa

Procedure to alter and update table on hierarchical relationship to see if there are any children

I have a hierarchical table on Oracle pl/sql. something like:create table hierarchical ( id integer primary key, parent_id references hierarchical , name varchar(100));I need to create a procedure to alter that table so I get a new field that tells, for each node, if it has any children or not.Is it possible to do the alter and the update in one single procedure?Any code samples would be much appreciated.Thanks You can not do the ALTER TABLE (DDL) and the UPDATE (DML) in a single step.You will have to do the ALTER TABLE, followed by t...

 oracle                     2 answers                     50 view
 2018-10-22         Joyce

Gitlab Could not find artifact com.oracle ojdbc8

I get this error on my gitlab project:[ERROR] Failed to execute goal on project importparcoursup: Could not resolve dependencies for project nc.unc:importparcoursup:jar:1.0-SNAPSHOT: Could not find artifact com.oracle:ojdbc8:jar:12.2.0.1Source:https://gitlab.com/tyvain/parcoursup/-/jobs/94672390This is normal as oracle jdbc is not available on maven central repo.Locally I managed to solve it by installing the lib in my local repo with this command:mvn install:install-file -Dfile=lib/ojdbc8.jar -DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=12.2.0.1 -Dpackaging=jarQuesti...

 oracle                     1 answers                     53 view
 2018-10-22         Harriet

Retrieve objects in Oracle

I have a Oracle object called Menu which has a object type as an attribute.menu typeCreate Type Menu as Object( beer REF beertype, price float )beertypeCreate Type beertype as object( name char(20), seller char(20) )table created using menu typeCreate table Sells of Menutable created using beertypeCreate table beer of beertypeNow what I want to do is I want to write a select query which retrieves the name of the beer which has price more than 3.5.I triedSELECT beer.name FROM Sells; andSELECT Sells.beer.name FROM Sells; Here's an example: test case first:SQL> crea...

 oracle                     1 answers                     49 view
 2018-10-22         Arvin

Oracle Object Types

In my lectures I learnt about ORACLE OBJECT TYPES mentioning COLUMN OBJECT and ROW OBJECT. I don't find enough details in my lectures regarding the object types. I have googled a lot to find at least a article about these object types, but I was unable to find any article which mentions about COLUMN OBJECT and ROW OBJECT.This may be a beginner question. Would anyone please explain or provide an article link? Try this link Oracle typesAnd there is a perfect definition:Row Objects and Column ObjectsObjects that are stored in complete rows in object tables are called row ob...

 oracle                     1 answers                     51 view
 2018-10-22         Andrew

Stream Processing Framework Vs Database

Why it is not possible to do real time streaming processing using database sql using cursors, PL SQL , Triggers etc. Why do we need a separate stream processing framework like Spark. Stream processors can scale better and more consistently operate with low latency and high throughput. [XXX]

 oracle                     1 answers                     73 view
 2018-10-22         Sean

Mutating table exception is not occurring

I have created a trigger and was expecting a mutating table error in below case but didn't get one through normal insert but getting an error while inserting using a query. I am not sure which concept I am missing here.drop table temp;create table temp (id number,name varchar2(500),is_active number);create or replace trigger temp_trg before insert on tempfor each rowdeclare v_count number;begin select count(1) into v_count from temp; update temp set is_active=0 where is_active=1 and id=:new.id;end;/select * from temp;insert into temp values (1,'xyz',1);insert into temp val...

 oracle                     2 answers                     56 view

Page 1 of 192  |  Show More Pages:  Top Prev Next Last