I am a PHP developer with little Oracle experience who is tasked to work with an Oracle database. The first thing I have noticed is that the tables don't seem to have an auto number index as I am used to seeing in MySQL. Instead they seem to create an index out of two fields. For example I noticed that one of the indexes is a combinati...
Can someone explain how to see the results of a procedure, everything is working fine and the code is valid, executed and compiled with no errors. Now how can I see the results as Query or anything. The ex procedure is about sum of salary. CREATE OR REPLACE PROCEDURE HR.TOTAL_SALARY AS total_salary NUMBER(12,2); BEGIN ...
I have this procedure: PROCEDURE P_SUMMARIZE_ACTIVATED ( pStartDate IN DATE, pEndDate IN DATE, SummaryCur OUT MEGAGREEN_CUR ) IS BEGIN OPEN SummaryCur FOR SELECT USER_ID, sum(case SERVICETYPE_ID WHEN 1 THEN 1 ELSE 0 END) AS Package1, SUM(CASE SERVICETYPE_ID WHEN 2 THEN 1 ELSE 0 END) AS Pack...
As far as I know, when using JDBC, it is not possible to remotely call Oracle procedures that have records or booleans as arguments. I wonder if there is any, not necessarily Java-based, interface that does not have such limitations. I know about JBublisher, but I'd rather there was some way that would not require to install any wrappers...
There is a composite primary key stored in a DB that consists of a date field and a foreign key ID. Normally this would create duplicates however the date field (although it only displays the day, month, year appears to have timestamp information stored as well) My question is how to extract the timestamp information (I think using the ...
I need to insert a record into a table and set the value of a column (e.g. orderid) to a unique #. And return that number used. I thought the process would be to do use a sequence and an insert statement with nextval: insert into ordersTable(orderid) values(ordernums.nextval); But how to I get the number that was used? My thought i...
I have 2 tables which have many records (say both TableA and TableB has about 3,000,000 records).vr2_input is a varchar input parameters enter by the users and I want to get the most 200 largest "dateField" 's TableA records whose stringField like 'vr2_input' .The 2 tables are joined as the following: select * from( select * from...
Hi, I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value. I tried using DBMS_LOB.substr(column) and i get the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small How can i query the CLOB column? ...
I'm getting my first experience with Oracle and TOAD (I know SSMS). I came across this "%Type" next to an input parameter in an update procedure and I have no idea what it is or what it means. I found links on Google related to "%Rowtype". Is the same thing or something entirely different? If this is vague, I apologize. As always, thank...
How do I apply IN parameter as my_email and OUT as my_salary in this procedure: CREATE OR REPLACE PROCEDURE FYI_CENTER AS my_email employees.email%TYPE; -- **IN Parameter** my_salary employees.salary%TYPE; -- **OUT Parameter** BEGIN SELECT email, salary INTO my_email, my_salary FROM employees WHERE employee_id = 101; ...
Topic is self-explanatory. My goal is to automate the process of code beautification, so a program like SQLPlus will compile code after it has been beautified. ...
I have a query that is executed on a remote database: select /*+ DRIVING_SITE(rd) */ 'test' as tst, rd.id from mytable@remotedb rd When I execute this query I get: ORA-22992: cannot use LOB locators selected from remote tables Every column in mytable@remotedb is either INTEGER or VARCHAR2. If I remove 'test' as tst there is no pro...
Please can anyone tell me of sites i can access to download free oracle pl/sql ebooks or video tutorials? Thanks. Moze ...
Hey, I'd like to do something like this with PL/SQL: for ACCOUNT in account_cursor loop for related_data in (select something from table where some_column = ACCOUNT.column) loop endloop; endloop; (caps for emphasis) I'm away from my dev environment so I can't test this out, so please ignore any minor syntactical...
How do I convert a comma separated string to a array? I have the input '1,2,3' , and I need to convert it into an array. ...
This works: SELECT (CASE WHEN x = 'value' THEN a.col1 ELSE nvl(a.col1, a.col2) END) FROM table1 a WHERE a.this = 'that' GROUP BY (CASE WHEN x = 'value' THEN a.col1 ELSE nvl(a.col1, a.col2) ...
Hi We have a third party oracle based application, that ships with precompiled binary(wrapped) packages. But when I compile them in Oracle SQL Developer (right click -> compile all), they get invalidated. Is recompiling a safe operation with no side effects? ...
Hi If I write a custom package with nothing but static SQL in it, would it invalidate other package. (other third party packages that were shipped as wrapped code). a co worker suggests that I re write the custom package in Dynamic SQL, and I will not see this problem. I find this hard to believe because the third party packages are not...
Hi, I am kind of new to triggers and cant figure it out how to resolve this. After insert a new row on a specicfic table it should influence other tables aswell. So if I add(insert) an order on a table which includes 3 quantity, I want to be 3 less In_stock in another table(column)... thanks in advance ...
Hi, How to query rows in a table where the created_date column value in the table falls on a weekend date ONLY, i.e. Saturday/Sunday using Oracle SQL.. Thanks ...