Anyone know how to place not functional "NOTES" in an SQL statements like the authors name or to note functionality?? thanks GRTTry using --
:D|||thanks works great!|||Just as an fyi, you can highlight entire lines in Query Analyzer and use these:
CTRL+SHIFT+C --Comments everything.
CTRL+SHIFT+R --Removes comments from everything.
This is great when you are troubleshooting or want to just type then highlight and comment.|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
and you guys who give a database-specific answer to a generic question should preface your remarks that way
for example, if you're using microsoft access, you can't put comments in the sql at all
if you're using microsoft sql server, the double dash works, and so does /* comment */
guess which database allows //|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
That's right. Repeat after me: "Microsoft SQL Server and SQL are not the same thing"...|||-- supposed to be ansi standard.
:rolleyes:|||PL SQL developer oracle version 9i
Showing posts with label statements. Show all posts
Showing posts with label statements. Show all posts
Tuesday, March 20, 2012
Friday, March 9, 2012
PL/SQL: couldnt create trigger
Hi,
i have a mass operation with some triggers to increment the id of a table ...
now, some of the copy&pasted statements working, some won't ... :(
part of the statement:
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_LICENSE_ID BEFORE INSERT ON SHEMA.LICENSE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)
^^ this doesn't print out any error.
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_SOFTWARE_ID BEFORE INSERT ON SHEMA.SOFTWARE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)
^^ there's an error like:
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_WORKSTATION_ID BEFORE INSERT ON SHEMA.WORKST
*
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed
whats going wrong?? both tables exists in the database with an id field defined as number(10) and flagged with a primary key.
maybe somebody can explain ...
thanksproblem solved|||Hi, I am stuck with this same problem. Please can someone help. It is quite urgent.
Thaks in advanced for your help...
Regards,
Paul|||This is what the error means:
Cause: In a declaration, the name of a variable or cursor is misspelled or the declaration makes a forward reference. Forward references are not allowed in PL/SQL. A variable or cursor must be declared before it is referenced it in other statements, including other declarative statements. For example, the following declaration of dept_rec raises this exception because it refers to a cursor not yet declared:
DECLARE
dept_rec dept_cur%ROWTYPE;
CURSOR dept_cur IS SELECT ...
...
Without seeing your code, it's hard to comment further.|||Hi,
This problem is getting strange.
I am using this code to product a primary key for a table. I have created a sequene and am using the next value from the sequence to act as the key.
The code I am using for the trigger is as follows
BEGIN
IF :new.ORDER_ID IS NULL THEN
SELECT AUTO_ORDER_ID.NEXTVAL ///SEQUENCE
INTO :new.ORDER_ID
FROM dual;
END IF;
END;
This has worked for about 8 tables with the exception of this one. I have tried to re-create the table using a different name and a different ID atttribute i.e table called sales and Sales_id and still no joy.
any ideas??
Oh.. I have also reinstalled the whole 9i database and I still get the same problem
Thanks for your help in advanced.
Regards,
Paul|||/// is not a valid comment syntax in PL/SQL, so that won't work! If that isn't the problem, I can't see anything obvious. You are getting:
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
yes? On what line?
If still struggling, please post the ENTIRE trigger text exactly as you are running it - including the CREATE..., and the description of the table...|||I am stuck with the exact same error as above, the code I am using is as follows:
CREATE OR REPLACE TRIGGER "FYP"."TEST" BEFORE
INSERT ON "FYP"."JOBS"
FOR EACH ROW begin
select seq_jobs.nextval into :new.id from dual;
end;
sequence named seq_jobs exists.
The table named exists too and the code is as follows:
CREATE TABLE "FYP"."JOBS" ("ID" NUMBER(10), "ALARM_TEXT"
VARCHAR2(255 byte), "POINT_ID" NUMBER(10), "DATE" DATE, "PRORITY"
NUMBER(10))
TABLESPACE "FYP" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PARALLEL ( DEGREE DEFAULT )
LOGGING
This is really annoying: I cannot see the forward reference in the code: I don't see anything referenced apart from the table, the colum ID and the sequence...|||Hello all,
I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.
Code for the table:
CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Code for the Sequence:
CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER
Code for the Trigger:
CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;
Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.
Any help would be very much appreciated! Thanks!
With kind regards,
Gog|||Originally posted by Gog
Hello all,
I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.
Code for the table:
CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Code for the Sequence:
CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER
Code for the Trigger:
CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;
Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.
Any help would be very much appreciated! Thanks!
With kind regards,
Gog
Looking at your code and that of Rhs98 posted earlier, I notice that you both have table and column names in double quotes, and in fact both have a column called "DATE" which is of course a reserved word in Oracle.
Changing the column name from "DATE" to ADATE (i.e. anything that isn't a reserved word) makes the problem go away.
Moral: NEVER put double quotes round table and column names, and (as a consequence) NEVER use reserved words as table and column names!|||hey thanks for the fast reply .. It seems to have worked. But this raises another question.
Why did a few other tables with a field called 'DATE' in it work properly than? Sorry! Apparently I had another name then 'DATE' for my date fields in the other table :-$ So never mind about that!
And ps. I didn't put the quotes there, DBA Studio studio gave that code to me when I selected 'show object ddl' :-D
thx for the help!
i have a mass operation with some triggers to increment the id of a table ...
now, some of the copy&pasted statements working, some won't ... :(
part of the statement:
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_LICENSE_ID BEFORE INSERT ON SHEMA.LICENSE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)
^^ this doesn't print out any error.
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_SOFTWARE_ID BEFORE INSERT ON SHEMA.SOFTWARE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)
^^ there's an error like:
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_WORKSTATION_ID BEFORE INSERT ON SHEMA.WORKST
*
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed
whats going wrong?? both tables exists in the database with an id field defined as number(10) and flagged with a primary key.
maybe somebody can explain ...
thanksproblem solved|||Hi, I am stuck with this same problem. Please can someone help. It is quite urgent.
Thaks in advanced for your help...
Regards,
Paul|||This is what the error means:
Cause: In a declaration, the name of a variable or cursor is misspelled or the declaration makes a forward reference. Forward references are not allowed in PL/SQL. A variable or cursor must be declared before it is referenced it in other statements, including other declarative statements. For example, the following declaration of dept_rec raises this exception because it refers to a cursor not yet declared:
DECLARE
dept_rec dept_cur%ROWTYPE;
CURSOR dept_cur IS SELECT ...
...
Without seeing your code, it's hard to comment further.|||Hi,
This problem is getting strange.
I am using this code to product a primary key for a table. I have created a sequene and am using the next value from the sequence to act as the key.
The code I am using for the trigger is as follows
BEGIN
IF :new.ORDER_ID IS NULL THEN
SELECT AUTO_ORDER_ID.NEXTVAL ///SEQUENCE
INTO :new.ORDER_ID
FROM dual;
END IF;
END;
This has worked for about 8 tables with the exception of this one. I have tried to re-create the table using a different name and a different ID atttribute i.e table called sales and Sales_id and still no joy.
any ideas??
Oh.. I have also reinstalled the whole 9i database and I still get the same problem
Thanks for your help in advanced.
Regards,
Paul|||/// is not a valid comment syntax in PL/SQL, so that won't work! If that isn't the problem, I can't see anything obvious. You are getting:
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
yes? On what line?
If still struggling, please post the ENTIRE trigger text exactly as you are running it - including the CREATE..., and the description of the table...|||I am stuck with the exact same error as above, the code I am using is as follows:
CREATE OR REPLACE TRIGGER "FYP"."TEST" BEFORE
INSERT ON "FYP"."JOBS"
FOR EACH ROW begin
select seq_jobs.nextval into :new.id from dual;
end;
sequence named seq_jobs exists.
The table named exists too and the code is as follows:
CREATE TABLE "FYP"."JOBS" ("ID" NUMBER(10), "ALARM_TEXT"
VARCHAR2(255 byte), "POINT_ID" NUMBER(10), "DATE" DATE, "PRORITY"
NUMBER(10))
TABLESPACE "FYP" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PARALLEL ( DEGREE DEFAULT )
LOGGING
This is really annoying: I cannot see the forward reference in the code: I don't see anything referenced apart from the table, the colum ID and the sequence...|||Hello all,
I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.
Code for the table:
CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Code for the Sequence:
CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER
Code for the Trigger:
CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;
Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.
Any help would be very much appreciated! Thanks!
With kind regards,
Gog|||Originally posted by Gog
Hello all,
I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.
Code for the table:
CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Code for the Sequence:
CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER
Code for the Trigger:
CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;
Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.
Any help would be very much appreciated! Thanks!
With kind regards,
Gog
Looking at your code and that of Rhs98 posted earlier, I notice that you both have table and column names in double quotes, and in fact both have a column called "DATE" which is of course a reserved word in Oracle.
Changing the column name from "DATE" to ADATE (i.e. anything that isn't a reserved word) makes the problem go away.
Moral: NEVER put double quotes round table and column names, and (as a consequence) NEVER use reserved words as table and column names!|||hey thanks for the fast reply .. It seems to have worked. But this raises another question.
Why did a few other tables with a field called 'DATE' in it work properly than? Sorry! Apparently I had another name then 'DATE' for my date fields in the other table :-$ So never mind about that!
And ps. I didn't put the quotes there, DBA Studio studio gave that code to me when I selected 'show object ddl' :-D
thx for the help!
PL/SQL & Recordsets
You can use PL/SQL functions in SQL statements and you can return recordsets with PL/SQL. Is there any way to combine the two in the FROM clause of a SQL statement?
function foo(a in number);
select *
from table1, foo(3)
where table1.field1 = foo.field1
Thanks.Hello,
use unnested collection types to create a nested table and a view that accesses the unnested collection types.
Then you can use the view to access your function.
If you need more help see the docu or contact me at www.alligatorsql.com
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
function foo(a in number);
select *
from table1, foo(3)
where table1.field1 = foo.field1
Thanks.Hello,
use unnested collection types to create a nested table and a view that accesses the unnested collection types.
Then you can use the view to access your function.
If you need more help see the docu or contact me at www.alligatorsql.com
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
Subscribe to:
Posts (Atom)