I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).
With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.
Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.
My question is:
Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?
In the example below I believe I have created a Nested Table of PL/SQL type and have tried various references to get the SET operation to work, line containing MEMBER OF. Taking the example below from the oracle documentation I have two questions.
1) As I understand it I should be able to use SET operations on Nested tables of PL/SQL types, (I am not using the CREATE OR REPLACE DDL statement prior to the DECLARE block.). Is this correct? 2) I am assuming that I have to reference the record, can I reference by its type / row instance or can I only retrieve the record like a Cursor Fetch solution, (which would defeat the purpose.).
I created a table with a column "id" and values for this column is attached a sequence. And now i need, if any value deleted from the table the column "id" will need to be sequence.
ex:
id name -- ------- 1 xxxx 2 yyyy 3 zzzzz 4 pppp 5 rrrrrr
if i delete
delete from test where id=4;
then automatically.. "id" column values will again in sequence... like this
id name -- -------1 xxxxx 2 yyyyy 3 zzzzzz 4 rrrrrr
note: in the above if i delete the id=4 from the table again it will have be in sequence and if i inserted the again it has to take the next value continue to sequence....
ex : insert to test values(seq_name.nextval,'tttt');
Doing some data conversion at the moment and using V$SESSION_LONGOPS to predict when the current task will be finished so I can run the next one.
V$SESSION_LONGOPS seems to have only the last 16 long operations for the session. Older operations are automatically purged. My bigger tables have 32 partitions, so after the first 16 are processed, I cannot tell which partition I am up to.
Googling "old longops" and "longops history" didn't work, nor did the same searches on this site. The Oracle Reference manual section on V$SESSION_LONGOPS did not mention that older entries are purged.
I am trying to run spatial operations through dblinks. See the example query below
select a.OGC_GEOMETRY.sdo_gtype from <table>@dblink a where sdo_nn(a.OGC_GEOMETRY,mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(0,0,null),null,null),'sdo_num_res=1')='TRUE'.
Query fails with the following error
ORA-13249: ORA-06512: at "MDSYS.MD", line 1723 ORA-06512: at "MDSYS.MDERR", line 17 ORA-06512: at "MDSYS.PRVT_IDX", line 9 *13249. 00000 - "%s"* **Cause: An internal error was encountered in the extensible spatial index* component. The text of the message is obtained from some other server component. **Action: Contact Oracle Support Services with the exact error text.*
Same Query runs fine in the original database but fails with dblinks. Is it possible to run spatial operations through dblink?
Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.
I should get values as 0, -34 and 77. But I don't get this as all operations with null gives null. These are fetched data and don't exist in the form shown. I have to use these row values of "Change" further. Is there any way of obtaining these results?
I am executing a sql statement which is doing FTS in parallel mode The server has 8 cpus and threads_per_cpu is 2
The v$sql shows PX_SERVERS_EXECUTIONS as 8
select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_id='0q0nk5117yth2' 8, select /*+ full(a) parallel(a)....
however the px_sessions shows 17 sessions (16 parallel session + 1 parent session (where sid = qcsid) Now in px_sessions, these 16 parallel session are divided in 2 server sets 1 and 2 and values for degree and required degree are 8 and 16 respectively
However, all the time, only 8 sessions which belong to server set = 1, were active though its state was waiting with event "PX Deq Credit: send blkd"
The other session which belong to server set = 2 were never active and always had waint event ='PX Deq: Execution Msg'
what could be the reason that 16 parallel session could not be started though I am the only person using the server, there aren't any batch jobs, dbms_jobs,even archivelogs (not a prod system)?
Note that paralel_max_servers setting is 16
Another issue being the duing start of the query approximately 100-115 blocks were read for the query (checked from longops) however after 60-70% blocks are read the number of blocks read / seconds falls down to 10-20 blocks / second across all parallel sessions.
I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).
What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?
I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).
Background: We are migrating a lot of databases from one SAN appliance to another. We are doing this by adding new disks from the new SAN appliance to the existing disk groups, re balancing, removing the old disks from the disk groups, and then re balancing again.
Question: If I execute two ALTER commands with the same power on 2 or more separate disk groups, do both operations start executing right away? Or do they queue up and execute one after another?
I ask because we would like to queue up several re-balances so we don't have DBAs watching status bars all day.
I have a two different Databases. I created a db link in DB 1 to connect to DB 2 and it is working fine when I select data from any table. but I have one table in the DB2 which has a column with user defined data type . so when I try to select this column from DB 2 by using the DB link it gives me this error :ORA-22804 remote operations not permitted on object tables or user-defined type columns.
I have enabled Auditing in my oracle Database but I am not able to see any database for the operations of sys user or any other user in my "SYS.AUD$" and "SYS.FGA_LOG$" tables.
Value for the parameter "AUDIT_TRAIL" is set to "db,extended".
I am working as "SYS" user and I have shutdown and again startup the database but neither there was any information in both the tables nor I can see any files at the destination specified by "AUDIT_FILE_DEST".
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
I've been tasked to parse tags from a string that look like the following:
{Date + XXX}
where XXX represents a numeric value. I have to replace this, including the brace characters with
SYSDATE + XXX
which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. I was trying to do something like the following
I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.
There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:
SQL> desc emp;
Name Null? Type ----------------------------------------- -------- --------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
Now when I run the 'select mgr from emp e;' query the output which I get is:
7902 7698 7698 7839 7698 7839 7839 7566
7698 7788 7698 7566 7782
Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .
I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.
way where max can be avoided or a more tuned query .
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER) RETURN NUMBER IS Sumnums NUMBER; BEGIN SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL; DBMS_OUTPUT.PUT_LINE(Sumnums); RETURN 1; END sumfivenumbers; / SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code
create or replace function Get_Annual_Comp (Sal in number, Commission in number) return number as [code]...
When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.
