SQL & PL/SQL :: Saving Procedures With Compilation Errors
Feb 21, 2010
When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?
CREATE or REPLACE TRIGGER my_trigger BEFORE UPDATE OF PASSWORD ON mytable FOR EACH ROW DECLARE BEGIN :NEW.PASSORD := 'xyzt'; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Error '|| sqlerrm); END trigger_create_user;
But, when i execute it, i get the following message:Warning: Trigger created with compilation errors.Even if i do nothing in trigger body:
CREATE or REPLACE TRIGGER my_trigger BEFORE UPDATE OF PASSWORD ON mytable FOR EACH ROW DECLARE BEGIN END trigger_create_user;
I know that this my question is a little to much for some DB tool like TOAD, but maybe this tip is exist and I can't find it!
Does TOAD when I write something wrong in PL/SQL (trigger for example) and after compilation (run as script) when show message:
"Warning: compiled but with compilation errors" to show what is wrong in sql code? I found this option is some db tools, but I can't find this in TOAD, of course if it can do this!
The 1st 2 tables are used only to fetch values through a LOV and no insert happen during save and the 3rd has initial values defined and has to insert these values while click on the save (when button pressed trigger) button which has the below pasted code, but no error is thrown and neither the record is inserted,
I've compiled a package with warnings ON. Getting message SP2-0809: Package Body Created with Compilation Errors.When I checked the errors by issuing "Show Errors", it shows "No error".
SQL> ALTER PACKAGE PKG_ABC COMPILE PLSQL_WARNINGS = 'ENABLE:ALL'; SP2-0809: Package Body Created with Compilation Errors SQL> SHOW ERRORS No Errors
how to check errors for that, or oracle simply pointing that package might have performance issue.
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection AS PROCEDURE sp_final_load_mkt_hub; END pkg_mkt_hub_load_collection; / CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection AS c_default_limit CONSTANT PLS_INTEGER:=5000;
[code]....
show error
error code
SQL> @pkg_mkt_hub_load_collection.sql Package created. Warning: Package Body created with compilation errors. Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION: LINE/COL ERROR -------- ----------------------------------------------------------------- 57/4 PL/SQL: Statement ignored PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL' SQL>
I have to run queries on a gov't computer. There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)So I do this, in the hope of trapping the error
BEGIN run_stored_proc (my_cursor, my_id, param3); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM); END;Well, it can still crash. SQL> @c:mysql un_test ERROR IN ID (2692). The error was -20100 -ERROR- ORA-20100: Exception occurred in some_other_stored_procedure
ORA-01422: exact fetch returns more than requested number of rowsSo it looks like run_stored_proc calls some_other_stored_procedure which is having issues. I was hoping that by trying to capture the error in the top level procedure, it would also capture the error in a lower-level "sub-procedure". This is the way a TRY-CATCH in java works. If an error happens anywhere along the way inside the TRY-CATCH, it is caught. Sure, PL/SQL ins't java, but is there a way for me to catch a lower level error and not have this code crash?
Fortunately for me, it actually doesn't crash that often. But I have to put this code in a loop, and run it for many different my_id's. And when it does crash, it isn't significant from a data analysis standpoint. I can't do without a small percentage of the data. But I'd love to be able to just run my script and not have to watch it for unexpected crashes.
In oracle 11.2.0.3 I am getting, PL/SQL:ORA-00904 TR_STATE_NAME invalid identifier
but the same code compiled without any compilation error (though column name specified was wrong).I corrected the column name and its working fine in 11.2.0.3 now. Is there any enhancement in Oracle Version 11.2.0.3 in terms of compilation error?
if it is possible to save an email send from an ORACLE pl-sql packages in the Oracle database it self? The email is in my case created and send directly using the oracle utl_smtp-package.
I have no problems with attaching doc's and so on in this mail. But i simply cannot find a way to save, the email i'm sending in the Oracle-database. The purpose of saving the email in the DB is that i want to be able to reopen an send email, to se exactly what was send.
I am having 3 procedures a,b,c. Procedure a is calling b,procedure b is calling c, and procedure c is calling procedure a.There is compilation error in procedure a but while compiling it is saying dependent procedure is uncompiled.
I listed invalid object, but when compile object, say that not exist.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_NAME ='PCK_PIR_NFEL_MILLENIUM' AND OWNER='PIRAMIDE' OBJECT_NAME OBJECT_TYPE --------------------------------- -------------------- PCK_PIR_NFEL_MILLENIUM PACKAGE
SQL> ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE; ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE * ERROR at line 1: ORA-04043: object PCK_PIR_NFEL_MILLENIUM does not exist
I have created one master detail form where on master i have one field with name status and if the status is changed to A it will prompt me to another detail block where i will fill the details and in case after i entered the details and then i decide it not to save and come back to main form without saving...how i can do it . I used go_blok In detail to go to master it goes to master but when i am saving the master the details get saved along with it.I dont want to save the detail and go to master block.
i have developed a form in 9i. can i view the fmx file on web. or tell me the extenstion what name of form should i use to save the form to view on the web.
My query is: I have created a master-child forms on one canvas. In a child form i have to enter multiple records, but after entering every record its prompting me to save it. I want to avoid it and save the entire thing once all my records are entered.
i need to compile a proc program, say prog.pc.have oracle 10g in my system. Since i am new to proc programming, me on the steps to compile the proc program in oracle proc compiler.
I am doing report migration from 10g report to 11g version 11gR2(11.1.2.0.0). It is not converting into REP when I check the logs . I got compilation error, then I convert 10g RDF to 11g RDF and try to compile it in 11g report builder developer tool, It is giving me compilation error.
It is not allowing me any sql in that proc but if i comment it it get successfully compiled and if the same report i open with oracle report 10g it get successfully compile.
Command using for Conversion: rwconverter userid=<schema_name>/<schema_name>@tns batch=yes source=<source RDF path> stype=rdffile DTYPE=REPFILE dest =<Dest RDF/REP Path> OVERWRITE=yes
Order time is not properly displayed in the local timezone format and day light saving calculation is also another issue. Description:
We have web application & a centralized database where users can create orders globally. When orders are created time is saved in UTC format in the centralized global database.
somehow along with the UTC order time, timezone description is also being stored in one of the Database table as
UTC time as: '28-NOV-2010 11:24:54 PM' Timezone description: (GMT) Casablanca, Monrovia (GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London (GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna (GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague (GMT+01:00) Brussels, Copenhagen, Madrid, Paris (GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb (GMT+01:00) West Central Africa (GMT+02:00) Athens, Beirut, Istanbul, Minsk (GMT+02:00) Bucharest (GMT+02:00) Cairo (GMT+02:00) Harare, Pretoria (GMT+02:00) Helsinki, Kyiv, Riga, Sofia, Tallin ..... ...
With this information how can I map timezone description to V_$TIMEZONE_NAMES. I read Oracle automatically converts to local timezones by considering day light saving timings when TZNAME is given as input with the datetime. by using above timezone descriptions how can I get TZNAMES which are in V_$TIMEZONE_NAMES.
I am connected to an oracle database on campus thru my laptop. The interface i am using is oracle sql developer. We are practicing entering SQL commands. The connection name is DBA120. So, the name of the database is DBA120, correct? Also, I must review the respective ERD (i.e., to know the table relationships) in order to determine the proper sql commands, correct?
Well, in this database connection (DBA120), there are several tables that actually pertain to three different databases, ACCOUNTING, BOOK, and DJ. (I have the respective ERDs.)
I want to be able to save a database to file so I can run it on another computer. How do I save each of the three databases to a file or can't I? Or must I save the DBA120 database? how do i do that? do i save it(them) as sql files?