I need to insert data into particular table through pl/sql stored procedure. My requirements are: while inserting it should generate PRIMARY KEY values for a particular column; it should return that PRIMARY KEY value to an output variable; and for another column it should validate my string such that it should contain only characters, ...
I am trying to insert 1 to 10 numbers except 6and 8 in table messages,but when i fetch it from table mesages1, output is coming in this order 4 5 7 9 10 1 2 3 It should be like this 1 2 3 4 5 7 9 10 According to the logic ,it works fine when i omit commit or put it some where else, Please explain why it is happening? this is my code. ...
Possible Duplicate: Any suggestions on how to extract 6 million records from an oracle10g ? Any Sample pl-sql code on the same will be highly appreciated. ...
I've got the following in one of my Oracle procedures, I'm using it to generate XML -- v_client_addons is set to '' to avoid null error OPEN C_CLIENT_ADDONS; LOOP FETCH C_CLIENT_ADDONS INTO CLIENT_ADDONS; EXIT WHEN C_CLIENT_ADDONS%NOTFOUND; BEGIN v_client_addons := v_client_addons || CLIENT_ADDONS.XML_DATA; E...
Hi ALL, I know heading is not so clear.This is the picture I am using &a it is considering it as $a. ,in the output &a and &a. is giving the same output. and why single .(DOT) after &a is giving no error but if we put any character, operator or wild characters after &a gives error. This is the code. BEGIN FOr i in &&a...3 LOOP ...
Hi ALL, I am trying to change the value of upper bound in For loop ,but the Loop is running till the upper bound which was defined in the starting. According to logic loop should go infinite, since value of v_num is always one ahead of i,But loop is executing three time.Please explain This is the code DECLARE v_num number:=3; ...
I tried to modify a type using the following code and it gave me the error code: 'ORA-02303'. I don't know much about Oracle or PL/SQL but I need to solve this; so I'd appreciate any further help with this. Thanks in advance. The code is just an example. But then again, I need to check its dependents first. create or replace type A as ...
I have a column with comma separated values like 6,7,99.3334. I need write a PL SQL procedure that will give me these values separately. The Length of the column is 40. Can anyone help me with this? ...
i'm joining tables like: select * from tableA a full join tableB b on a.id = b.id But the output should be: row without null fields row with null fields in tableB row with null fields in tableA Like: a.id a.name b.id b.name 5 Peter 5 Jones 2 Steven 2 Pareker 6 Paul null null 4 Ivan null null null...
I am writing a PL/SQL procedure that loads some data from Schema A into Schema B. They are both very different schemas and I can't change the structure of Schema B. Columns in various tables in Schema A (joined together in a view) need to be inserted into Schema B as key=>value pairs in 2 columns in a table, each on a separate row. For ...
I have a query that returns a lot of data into a CSV file. So much, in fact, that Excel can't open it - there are too many rows. Is there a way to control spool to spool to a new file everytime 65000 rows have been processed? Ideally, I'd like to have my output in files named in sequence, such as large_data_1.csv, large_data_2.csv, large...
If I use many CLOB variables in the PL/SQL stored procedure to store many long length string , are there any performance issues? Is the length of the CLOB is also variable? Are there any known limitations/disadvantages for CLOB instead using varchar2 and long? ...
I need to convert a list of numbers that fall within certain ranges into a list of values, ordered by a priority column. The table has the following values: | YEAR | R_MIN | R_MAX | VAL | PRIO | ------------------------------------ 2010 18000 90100 52 6 2010 240000 240099 82 3 2010 250000 259999 50 5 2...
Hi! I call stored function with such signature: FUNCTION ADDDAYS (city VARCHAR2, startDate DATE, numDays INTEGER) from java code: JdbcTemplate jt = getJdbcTemplate(); Object o = jt.execute("{? = call ADDDAYS(?, ?, ?)}", new CallableStatementCallback() { public Object doInCallableStatement(CallableStat...
I recognized that calling a method on an Oracle Object Type takes longer when the instance gets bigger. The code below just adds rows to a collection stored in the Object Type and calls the empty dummy-procedure in the loop. Calls are taking longer when more rows are in the collection. When I just remove the call to dummy, performance ...
In my code i am entering the salary which is not available in employees table and then again inserting duplicate employee_id in primary key column of employee table in exception block where i am handling no data found exception but i do not why No data found exception in the end also? OUTPUT coming: Enter some other sal ORA-01400: can...
I'm writing some JDBC code which calls a Oracle 11g PL/SQL procdedure which has a Custom Object return type. Whenever I try an register my return types, I get either ORA-03115 or PLS-00306 as an error when the statement is executed depending on the type I set. An example is below: PLSQL Code: Procedure GetDataSummary (p_my_key IN ...
Our team is willing to unit-test a new code written under a running project extending an existing huge Oracle system. The system is written solely in PL/SQL, consists of thousands of tables, hundreds of stored procedures packages, mostly getting data from tables and/or inserting/updating other data. Our extension is not an exception. M...
We have been using temporary table to store intermediate results in pl/sql Stored procedure. Could anyone tell if there is a performance difference between doing bulk collect insert through pl/sql and a plain SQL insert. Insert into [Table name] [Select query Returning huge amount of data] or Cursor for [Select query returning huge am...
I'm trying to write a query that returns the shortest string value in the column. For ex: if ColumnA has values ABCDE, ZXDR, ERC, the query should return "ERC". I've written the following query, but I'm wondering if there is any better way to do this? The query should return a single value. select distinct ColumnA from ( select Colum...