Hi, I have an oracle table with a column from type "SYS.XMLTYPE" and a storage procudure which is doing the insert: (Short version): PROCEDURE InsertXML ( pXMLData IN LONG ) IS BEGIN INSERT INTO MY_TABLE (XML_DATA) VALUES(pXMLData); END InsertXML; I call this sp from my C# code with type "OracleType.LongVarChar". Now t...
In our project to generate massive reports in oracle we use some permanent table to hold intermediate results. For example to generate one report we run few queries and populate the table, at the final step we join the intermediate table with huge application tables. These intermediate tables are cleared for next report run. We have few...
Hi folks, I have a very large Oracle database, with many many tables and millions of rows. I need to delete one of them, but want to make sure that dropping it will not break any other dependent rows that point to it as a foreign key record. Is there a way to get a list of all the other records, or at least table schemas, that point to ...
I have an Execute SQL task (SQL 2008) where I'm using two SSIS variables to interact with an Oracle database: DECLARE ParamTest number; TempOutputRun varchar(255); ParamTest := ?; TempOutputRun := ?; BEGIN IF ParamTest = 0 THEN SELECT CAST(OUTPUT_RUN_ID AS VARCHAR(15)) AS OUTPUT_RUN_ID FROM GL_EXTRACT_STATUS WHERE STATUS='NEW' ORDE...
Hi For most records this field is null. I need to wake up only some records. The table is very big and I want to know which records to wake up. I don't want database to seek every row. Could you show me your idea? ...
Hi everyone, I'm working on the modification of some code to use preparedStatement instead of normal Statement, for security and performance reason. Our application is currently storing information into an embedded derby database, but we are going to move soon to Oracle. I've found two things that I need your help guys about Oracle a...
I have this function: CREATE OR REPLACE FUNCTION CREATE_AIRSPACE_AVAILABILITY_RECORD (cur_user VARCHAR, start_time VARCHAR, start_date VARCHAR, end_time VARCHAR, end_date VARCHAR, airspace_name VARCHAR) RETURNS VOID AS ' DECLARE c_user ALIAS for $1; BEGIN IF start_time IS NULL OR start_date IS NULL OR end_time IS N...
I'm making a request from a java webapp to an Oracle' stored procedure which happens to have a Timestamp IN parameter. The way info travels is something like: javaWebApp --} webservice client --} ws --} storedProcedure And I send the Timestamp param as a formatted string from the webservice client to the ws. In the testing environmen...
Short version: If the function wwv_flow_custom_auth_std.is_session_valid (or apex_custom_auth_std.is_session_valid) returns TRUE, is it possible that the session is expired but still alive? If so, how can you check if a session is expired? Long version: I have created a single-sign-on system for our Oracle APEX applications, roughly b...
Unfortunately most of my DB experience has been with MSSQL which tends to hold your hand a lot more than Oracle. What I'm trying to do is fairly trivial in tSQL, however, pl/sql is giving me a headache. I have the following procedure: CREATE OR REPLACE PROCEDURE USPX_GetUserbyID (USERID USERS.USERID%TYPE, USERRECORD OUT XMLTYPE) AS...
Hello there, First: Thanks! I finished my other project and the big surprise: now everything works as it should :-) Thanks to some helpful thinkers of SO! So here I go with the next project. I'd like to get something like this: SELECT * FROM tablename WHERE field1=content AND field2=content2 ... As you noticed this can be a very...
Hi. I am making an application in VB as frontend; backend is Oracle . I want autogenerated ID on click of "New" button. It works well if data is present in the table, but shows error if the table is empty. What i need to insert so that it work when I am using the appliction for the first time? My button code is as follows: Private Sub ...
Hello, i need some help to boost this SQL-Statement. The execution time is around 125ms. During the runtime of my program this sql (better: equally structured sqls for different tables) will be called 300.000 times. The average row count in the tables lies around 10.000.000 rows and new rows (updates/inserts) will be added with a time...
undefine dates declare v_dateInput VARCHAR(10); v_dates DATE; begin v_dateInput := &&dates; v_dates := to_date(v_dateInput,'dd-mm-yyyy'); DBMS_OUTPUT.put_line(v_dates); end; Not sure why whenever I run this code with ,for example , input of 03-03-1990, this error shows up. Error report: ORA-01847: day of month must be betwe...
In my vb application I want an autogenerated id of alphanumeric characters, like prd100. How can I increment it using Oracle as backend? ...
How can I work out the maximum row size in a table, if I'm only given the datatype lengths (from all_tab_cols.data_length column) of the columns in the table (ie no statistics or ANALYZE)? There's extra complications in that this is an IOT, so there's index tree size to consider as well. ...
I have been struggling with this for quite some time having been accustomed to SQL Server. I have the following code and I have verified that the OracleDbType's are correct and have verified that the actual values being passed to the parameters match. I think my problem may rest with the return value. All it does is give me the row count...
I have a table with multiple indexes, several of which duplicate the same columns: Index 1 columns: X, B, C, D Index 2 columns: Y, B, C, D Index 3 columns: Z, B, C, D I'm not very knowledgeable on indexing in practice, so I'm wondering if somebody can explain why X, Y and Z were paired with these same columns. B is an effective date....
CREATE OR REPLACE PROCEDURE p_create_dynamic_table IS v_qry_str VARCHAR2 (100); v_data_type VARCHAR2 (30); BEGIN SELECT data_type || '(' || data_length || ')' INTO v_data_type FROM all_tab_columns WHERE table_name = 'TEST1' AND column_name = 'ZIP'; FOR sql_stmt IN (SELECT * FROM test1 WHERE zip IS NOT NULL) ...
I'm having a weird problem with index organized table. I'm running Oracle 11g standard. i have a table src_table SQL> desc src_table; Name Null? Type --------------- -------- ---------------------------- ID NOT NULL NUMBER(16) HASH NOT NULL NUMBER(3) ........ SQL> select count(*) from src_table; COU...