I have one package that has different procedures, and one main procedure through which I am calling other procedures. Through the front end, I am passing the procedure name into main(). Is there any way by which the procedure can be called just writing the parameter name containing('Procedure Name that is need to be called')? CREATE O...
Hi guys i'm having trouble running the below code on an Oracle DB not too sure why - getting ORA-905 error - missing keyword This works fine on MSSQL and MYSQL. Any indication as to how to fix will be much appreciated. Cheers. SELECT product.productId, product.version FROM product INNER JOIN (SELECT productId, MA...
What is the more easy way to INSERT a row if it doesn't exist, in PL/SQL (oracle)? I want something like: IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN INSERT INTO table VALUES ("jonny", null); END IF; But it's not working. Note: this table has 2 fields, say, name and age. But only name is PK. ...
Is there any way to merge two strings returned in a query like this: I have one string '<6 spaces>XYZ' and other string '<3 spaces>ABC<3 spaces>'. Basically each string is divided in 3 parts and two of any parts will be blank. I want to merge these two strings to produce the output: '<3 spaces> ABCXYZ'. Another example can be 'ABC<6 sp...
I'm developing a JEE application (JSF + Richfaces + Oracle 10g), and i wanted to use JPA. But in the end, i didn't see any advantages of using it, because it's going to complexify my code. I found that calling my procedures (stored procedures in my orale DB) is better than using JPA (because i can, for example, change some lines in tho...
I have this procedure: PROCEDURE P_LOAD_EXPIRED_ACCOUNT ( pDayDiff IN NUMBER, ExpiredCur OUT MEGAGREEN_CUR ) IS BEGIN OPEN ExpiredCur FOR SELECT ACCOUNT_NAME, SERVICE_TYPE, CASE WHEN SERVICE_TYPE = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3) WHEN SERVICE_TYPE = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6) WH...
I am new to pl/sql. can any one tell me how to call pl/sql function inside a trigger. I tired it but it gives an error when i try to run it. DROP TRIGGER INTF_CONTROLLER_TREXE; CREATE OR REPLACE TRIGGER INTF_CONTROLLER_TREXE before insert ON INTF_CONTROLLER for each row begin BACKOFFICE_UPDATE(); end; CREATE OR REPLACE FUNCTION BAC...
I had a 'procedure A' in which it has to return a null cursor to front end and then immediately it should continue with the next step in which it will call a procedure which will take 20 min to complete the proc. Procedure A(cur_out refcursor) begin OPEN cur_out for select null empname, null empid from dual; procedure B();//Wil...
Is it possible to create/replace PL/SQL via the Oracle JDBC driver, i.e not via SQLPLus* ? Update 1: Driver: ojdbc14 (JDBC) Update 2: Change is being applied to oracle via Ant <SQL> task, which is passed a sql script file. In this instance using SQLPlus in the Ant script is not possible (long story). ...
Hi lads, I have another simple one. How to make a long decode inside a function in oracle? My select looks like this: select something, sum(ofsomethingelse) from a_table where code in ('390','391','392','393','394','395','396','397','398','400','402','406', '407','408','409','410','411','412','413','414','416','418','471','473', '173...
Hi Oracle experts. I'm having a little issue with a piece of bulk collect sql that I was hoping you could help out with. With the following code: declare cursor c1 is select customer,product from products; type type_cust is table of products.customer%type; type type_prod is table of products.product%type; ...
Everything's in the title. I am Looping on a cursor and would like to have the EXIT WHEN curs%NOTFOUND when there is no more row, what is the equivalent of %NOTFOUND under PostgreSQL ? Edit Or the other cursors attributes %ISOPEN, %EMPTY, etc... ...
I have an SSIS package set up to pull data from an Oracle database into SQL Server. I recently ran into an issue that was preventing this data pull. The following code works fine in Oracle SQL Developer (it returns rows, as it should): SELECT a.MyField , a.MyOtherField, a.FromDate FROM MyTable a WHERE a.FromDate BETWEEN CONCA...
Platform: Oracle Language: PL/SQL Issue: Want to output a procedure OUT cursor into the SQLDeveloper SQLWosksheet. Anyone know how to use the Oracle "Select * from Table( PipelinedFunction( Param ) ) " to check procedure code output cursors? I am using Crsytal Reports off of an Oracle stored procedure. Crystal requires that a procedu...
Hi, I have an sql stored procedure which accepts two dates, however when I send them in my open query, oracle does not like the date format for some reason.... How can I change the dateformat to YYYY-MM-DD from dd-mm-yyyy in the stored procedure before sending using it. e.g SET @startdate = CONVERT?? Thanks, Tina ...
I have a problem about oracle procedure,since I'm new in sql language :D, here is my query CREATE OR REPLACE PROCEDURE MONDESINT.UPDATECOADESCRIPTION IS DESCRIPT MONDES_MSTR_CHART_OF_ACCOUNT.NAMA_AKUN%type; BEGIN FOR SATU IN (select NO_AKUN, NAMA_AKUN from mondes_mstr_chart_of_account where NO_A...
Where can I find an official grammar for the PL/SQL programming language? I see that the Antlr project has a user-contributed grammar, but I was hoping to find a more authoritative source. ...
There is a way to use a calculated field in the where clause ? I want to do something like SELECT a, b, a+b as TOTAL FROM ( select 7 as a, 8 as b FROM DUAL UNION ALL select 8 as a, 8 as b FROM DUAL UNION ALL select 0 as a, 0 as b FROM DUAL ) WHERE TOTAL <> 0 ; but I get ORA-00904: "TOTAL": invalid identifier. So I hav...
I know that fetching a cursor will give me access to variables like %ROWCOUNT, %ROWTYPE, %FOUND, %NOTFOUND, %ISOPEN ...but I was wondering if there are any other reasons to use Open - Fetch - Close instructions to loop a cursor rather than Loop the cursor with a FOR cycle... (In my opinion this is better becase it is simple) What...
With this, set serveroutput on size 900000; DECLARE test VARCHAR(255):=0; BEGIN SELECT id INTO test FROM sku WHERE id = 1515; dbms_output.put_line('Result:' || test); END; I'm having the error "no data found" When ID doesn't exist in database. How can I use something like nvl() here, so I can get a default value instead ...