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
I am inquiring about the PL/SQL functionality of passing focus from one procedure/function to another via HTML web pages, using FORM ACTION="post" against "get". I've successfully written a good bit of code using the "get" fu [More]
I am trying to install a certificate chain using the SUN Java Web Server 7.0U1 HTTPS User interface. What I have tried so far: 1. Created a single file using vi editor containing the four certificates in the chain by cutting an pasting each certifica [More]
I have looked quite a bit throughout the forums and via Google and am having a hard time answering this question.  I'm positive I'm not the first to ask this.  Anyway, our business users are not too keen on the look and feel of the WebDynpros or BSP' [More]
I have this problem with my hp slate 7 voice tab. When I installed the kitkat update, my tablet kept on rebooting and then it stops and shows options or ways I could fix the issue. I tried every single option and it still does not work. I have been w [More]
I just purchased a new WRT54G V8.0. I upgraded to 8.0.0.2 and set up my network. I have 3 wireless devices and one wired device. The wireless devices are the Desktop, the Laptop, and the Wii console. The wired device is the Xbox. My problem is this.. [More]
Need to kniow which 5 computers are authorized.  I may need to deauthorize some but don't have the answer.  Trying to play some songs I purchased but am not allowed.  Get the 5 computer authorization message!  I know I have Itunes at work and on two [More]
Hi all, At first, please look at my attachment. There is the part of my code (it is inside of a while loop) which saves a raw data from my firewire camera. I am thinking how does this saving process work? How does it save the data in each while loop' [More]
Hi All, I have two table in one panel and I used textfield in cell editor, Input verifier is set to these textfield and once focus is gained to other component verify method is called so far so good, But problem is that when i entered invalid value i [More]
I don't know how to print half sheets on my hp 4014 laser printer.  I seem to have it correctly set up in the program but when I print, I get the first sheet and then it stops and says that the paper size is incorrect and it doesn't let me continue n [More]
Dear All, when i perform startsap in linux 64 bit message coming like that. [[email protected] ~]# su - prdadm ram:prdadm 1> startsap ping: unknown host fam Usage: grep [OPTION]... PATTERN [FILE]... Try `grep --help' for more information. Checking PRD Databas [More]