Sitemap

Amicuk Programming Answers

How to compare the value of a binding variable with a string "HOLD" ......?

2015-10-11   Views:0

Advertisement

Hi All, I have two tables - TABLE1 & TABLE2 and both the tables are having STATUS column. The requirement is like if the STATUS column of TABLE1 is updated as "HOLD" then the same value has to be updated to the STATUS column of TABLE2. creat

Hi All,
I have two tables - TABLE1 & TABLE2 and both the tables are having STATUS column. The requirement is like if the STATUS column of TABLE1 is updated as "HOLD" then the same value has to be updated to the STATUS column of TABLE2.
create or replace trigger "TEST_TRG"
BEFORE
update of "STATUS" on "TABLE1"
for each row
begin
if(:new.STATUS ='HOLD')then
insert into TABLE2 (STATUS)
value (:new.STATUS);
end if;
end;
COMPILATION ERROR:
Compilation failed, line 3 (02:40:14) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00103: Encountered the symbol "{" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "{" was ignored. Compilation failed, line 7 (02:40:14) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00103: Encountered the symbol "}" when expecting one of the following: ( begin case declare else elsif end exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "}" was ignored.
I am getting the compilation errors as above when the trigger is compiled. Can anyone please help me to correct it.
Thanks and Regards,
Suhas

The replay answer
Advertisement
CREATE OR REPLACE TRIGGER "TEST_TRG"
   BEFORE UPDATE OF "STATUS"
   ON "TABLE1"
   FOR EACH ROW
BEGIN
   IF (:NEW.status = 'HOLD')
   THEN
      INSERT INTO table2
                  (status
           VALUES (:NEW.status
   END IF;
END;You should learn how to write PL/SQL code.
Denes Kubicek
http://deneskubicek.blogspot.com/
http://www.apress.com/9781430235125
http://apex.oracle.com/pls/apex/f?p=31517:1
http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
-------------------------------------------------------------------

Go to See the other 6 answers

How to compare the value of a binding variable with a string "HOLD" ......?

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I have two tables - TABLE1 & TABLE2 and both the tables are having STATUS column. The requirement is like if the STATUS column of TABLE1 is updated as "HOLD" then the same value has to be updated to the STATUS column of TABLE2. creat[More]

How to use a bind variable in an IN clause

Category:DefaultRelease time:2015-10-11Views:130

I am trying to use a bind variable in an IN clause where the column is a varchar2 type. Something like: select * from test where test_column in (:bindVariable)I have tried assigning the bind variable comma separated strings (eg. test,test,test) and c[More]

Inconsistent datatypes: expected - got CHAR, Detail view bind variables

Category:DefaultRelease time:-0001-11-30Views:130

Hi. Here is my problem: I have master detail views connected with a view link. Both of views have bind variables that hold some session info: It's a menu on database and I am trying to hide some values based on user permissions. When running applicat[More]

Please help on binding variables

Category:DefaultRelease time:-0001-11-30Views:130

Hello, I need to bind the variable in following function. If the single deptno is passed into function, the function will return correct result. However, if the set of deptno is passed, the function cannot give correct result. Could anyone please hel[More]

Bind variable problem in cascading LOVs

Category:DefaultRelease time:-0001-11-30Views:130

Hi, after upgrade from APEX 3.0 to 3.1 all my cascading LOVs stopped working correctly. First level LOV is OK, but the second level LOV, which contains a bind variable in its SQL code, fails. Debugging shows that the bind variable referrencing to the[More]

Bind Variable Basics

Category:DefaultRelease time:-0001-11-30Views:130

Can anyone tell me how Bind variables are implemented within a PL./SQL block? I managed to learn the basics like Bind variable as a place holder for a query..etc. But i don't understand how this concept is implemented in a real program SQL> variable[More]

Report Performance with Bind Variable

Category:DefaultRelease time:2015-10-11Views:130

Getting some very odd behaviour with a report in APEX v 3.2.1.00.10 I have a complex query that takes 5 seconds to return via TOAD, but takes from 5 to 10 minutes in an APEX report. I've narrowed it down to one particular bind. If I hard code the dat[More]

SLOW report performance with bind variable

Category:DefaultRelease time:2015-10-11Views:130

Environment: 11.1.0.7.2, Apex 4.01. I've got a simplified report page where the report runs slowly compared to running the same query in sqldeveloper. The report region is based on a pl/sql function returning a query. If I use a bind variable in the[More]

SOLVED -Newbie with Bind Variable Issue in Data template

Category:DefaultRelease time:2015-10-11Views:130

My XML output looks the way I want it structure-wise, but the data is the same for each group. Obviously my bind variable never gets updated. How can this be achieved? Here are the dataquery and data structure parts of my template: <dataQuery>      [More]

Performance when using bind variables

Category:DefaultRelease time:2015-10-11Views:130

I'm trying to show myself that bind variables improve performance (I believe it, I just want to see it). I've created a simple table of 100,000 records each row a single column of type integer. I populate it with a number between 1 and 100,000 Now, w[More]

Bind variable peeking does not work

Category:DefaultRelease time:2015-10-11Views:130

Oracle 9.2.0.8 create table my_table (c number); create index i on my_table(c); declare par varchar2(10); begin par:='qqq'; for rec in (select * from my_table t where c = par or par is null )loop null; end loop; --USES FULL TABLE SCAN, works SLOW!!![More]

Hot
Hi all, I have been working on this problem for a few days now. I created an ODS object, transformations,  DTP and I am using a  R/3 datasource for extraction. The ODS populates fine in the Dev box, the tranformations and the datasource are fine only [More]
I am using Entourage and followed all the instructions above and still getting IMAP Server error.  Error msg:Security failure.  Data decryption error.Read other 2 answers [More]
Hi guys i had a battery booster app i recently added to my iphone, took it off as i realised it did actaully boost my battery but it did have useful information on the app display itself. I had downloaded it again for free from itunes app store but w [More]
Hi, We have done LSMW with BAPI method to create Bank master. In Idoc Inbound Processing settings - We created partner type as 'B' i.e, for BANK While creating Partner Number it is not allowing to create Partner Number. The error is  "Enter a permiss [More]
Dear All, I have two queries in OPM quality module. 1.Is there any seeded graphical report available in OPM quality module? 2.Is there anything like Collection plans of collecting multiple elements and doing an analysis based on the Collected element [More]
I am getting several errors when running the system maintainance job against my document directory in G6. It looks like the errors are showing up when refreshing documents that have been upgraded from the 4.5 portal. These documents are all web URLs [More]
I> I developed a flex application using FLEX BUILDER3. My main.mxml and background image file was under src directory in flex project. There was a folder named history under which index.template.html was present. I inserted the javascript code inside [More]
Hi everyone, I have a laptop (MacBook Pro OSX10.9.5), which shares a network drive in my office. The drive is with a different computer. When I take the laptop out of the office network (to a meeting or home), InDesign will not open, it gives me the [More]
I work in IT and several of our clients are have trouble syncing contacts and calendar to outlook 2010 or iphones. I have uninstall itunes completely from the computer, deleted the backup and clear the sync history nothing seems to be working. Is the [More]
Hi All, When I try to Publish a report to PDF  the Chinese/Japanese language characters disappear. Same report when published as Excel shows all the language characters. Any suggestions to resolve the issue? Thanks, HemaI am not expert in these, but [More]