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
Hello, I can't install this product on this server, I'm stuck in the installation of the software prerequisite. Apparently, it can't install IIS... Here is my log file : 2012-12-07 13:01:23 - Processor architecture is (9) 2012-12-07 13:01:23 - Readin [More]
Hi, i've done a search but can't seem to find the answers. Before I purchased, i was wondering a few things: 1) what's the difference from the mini 4gb to 6gb, besides the size/# of songs? I thought I read somewhere that there were also enhancements? [More]
Hi all.       Can any one pls let me know how to call the trfc,arfc function modules designed in sap from netweaver system. Is the method of calling trfc , arfc fuction modules from non sap system same? If not let me know how to call trffc & arfc fun [More]
Can anyone tell me if imove embeds video into imovie project, or does is always need to find the video file to play it? thanksfryedmeb, I'm not sure if I completely follow your question, but I'll take a stab at it. What appears as a single iMovie pro [More]
Anyone try it after the update? I know it didn't work before... And a side question, since there doesn't seem to be anywhere to ask...are the support forums (especially logging in) constantly crashing Safari (Mac version, desktop) for you?The Apple R [More]
Hi, I am at a large Telco customer and I am urgently looking for a demo of 'BAM with Siebel' for Order Management. Where can I find such a demo? It could a recorded video demo or a VM instance that I could run would be fantastic. Please point me in t [More]
I Had a 4s for 18 months with IOS 6 - no problems - I then upgraded to 7.1 and the phone immediately stopped charging. A diagnostic through the apple store told me it was the connector that had broken and I would need to by a new one. I purchased a r [More]
There is a Text-Web-Item in our Template which is not bound in the export command. That´s why this Text-Web-Item was never exported in the past. Now we´ve made a upgrade from SAP BW 7.0 to SAP BW 7.31. And now the same Text-Web-Item will be exported [More]
I created a network share variable in LabVIEW 8.5. But after I changed its properity to "Target relative",I came across a error "Error 1003" when built an exe. And if I changed it to "Absloute",I got no error! Is that a bug ? [More]
Hi All, Can any one tell me how to find the user exits related to particular transaction code? Regards, PremDear PremKumar, *Please Search SDN Before posting any thread the question you have asked is already available since you are new to SDN and for [More]