I have a stored procedure FOO belonging to user A. User B has EXECUTE permission on FOO. One of the procedures in FOO uses a DB Link to retrieve data from BARTABLE@REMOTEDB. A has a DB Link to REMOTEDB connecting as user AREMOTE while B has a DBLink with the same name connecting to REMOTEDB but as user BREMOTE. When B executes FOO, w...
I have a sample query like below in my procedure: result_rec mypkg.mytype; OPEN CUR1 FOR select col1, col2, col3 from table1 where something = 'a'; --rows will always be 50 LOOP FETCH CUR1 INTO myrectype; EXIT WHEN CUR1%NOTFOUND; result_rec.col1 := myrectype.col1; resu...
I have the below sql query to insert the records from a table. The problem is when there is no records for select query i still want an record with the sequence number,null,'3' value inserted. Nothing get's inserted when there is no record found for select query. how can i do it? insert into test_details(seqnbr,trepid,type) select '&se...
Hi, When i ran the below queries it's failing in the second query becuase prev_test_ref1 variable is not defined. If i remove the insert statement in the first query ,run again then it's working and using the prev_test_ref1 value from the first sql query in second query. Is it because of variable scope? How can i resolve this with...
Q.accrept any string having any no.of comma in the string and display all the words seperated by commas in seperate lines? example: delhi,mumbai,pune,puri i want output like this outpt: delhi mumbai pune puri ...
Hi folks, I have a table in this format (similar to the class schedule you had in college): create table match_times ( match_time varchar2(20 char), match_sun_day char(1 char), match_mon_day char(1 char), match_tue_day char(1 char), match_wed_day char(1 char), match_thu_day char(1 char), match_fri_day char(1 cha...
hello Im trying this query: DECLARE v_week NUMBER; BEGIN SELECT WEEK FROM WEEKDESTINATION INTO (v_week) WHERE STARTDATE BETWEEN '02-JAN-2010'AND '09-JAN-2010'; END; but it throws the following error: PL/SQL: ORA-00933: SQL command not properly ended please help ...
The following simple statement: INSERT INTO mydb.inventory (itemID) VALUES (:itemID) WHERE playerID = :ID; Generates the following error: ORA-00933: SQL command not properly ended I have tried it without the semi-colon as well as with, but both give me the error. I am certain that the variables are being bound as well. All my Goo...
I have the below sql script. When there is no record found by the select query there are no records inserted by the insert statement. If no records found by select query i want to have a record inserted with the new sequence numbers and other fields with null values. how can i do it. insert into testing.test_ref_details(SEQNUM, TEST_T...
How can we define output parameter size in stored procedure? ...
I need to count number of email addresses in a db that have 3 or less characters before the @ sign, for example [email protected]. The parsename function isn't present in Oracle and I'm not sure how to write a regexp for this. Any help would be greatly appreciated! ...
Hi - I have a query that works on MySQL but doesn't work on Oracle, and I'm trying to convert. This is my table: unique_row_id http_session_id page_name page_hit_timestamp ---------------------------------------------------------------- 0 123456789 index.html 2010-01-20 15:00:00 1 123456789 i...
Hi, I am getting the below oracle error. I checked the test scheme for any constraint name CMF_CMP using toad. But i am unable to find it. How do i detect the reason for failure and how to resolve it. ERROR at line 1: ORA-20001: -2298: ORA-02298: cannot validate (TEST.FMF_CMP) - parent keys not found ORA-06512: at test.test_SYN", ...
how can I append (insert) 3 or 4 different values to an oracle type and then later open it up for a cursor. For example (pseudo): insert into mytype select 1 from dual; insert into mytype select 3 from dual; insert into mytype select 5 from dual; open cursor_1 for select * from table(mytype); Is this possible to do in pl/sql? I kn...
I need to split an address which is a single string into multiple strings. Below are the requirements to for the pl/sql procedure that splits up the address into the corresponding address lines. The parameters of the procedure are: create or replace procedure split_address ( address in varchar2, al1 out varchar2, al2 out varchar2, al3 o...
I have a table with 14 columns in Oracle. All the columns are of integer type. i need to check no two columns in one row have same integer value in them. How can i do this using SQL. Or is it only possible to do using PL/SQL? ...
Hi, I have a small doubt regarding BEFORE INSERT TRIGGER in oracle, my trigger looks like this: CREATE OR REPLACE TRIGGER some_trigger BEFORE INSERT ON some_table REFERENCING NEW AS newRow FOR EACH ROW DECLARE some_var number(25, 4); BEGIN -- do some stuff :newRow.some_column :=some_var; exception when no_data...
We are fetching column names from all_tabs_cols, where we get some unused columns that are not present in the table. How can I determine all the unused columns that are present in all_tabs_cols. Does there exists a data dictionary table for this ? procedure xxx_mig_db_column_list_proc is begin insert into xxx_mig_db_column_list ...
I want to write a loop that iterates over numbers 105 102 19 17 101 16 106 107 for each iteration I want to plug the number in a query and insert it into a table. pseudo: LOOP (105 102 19 17 101 16 106 107) FETCH select * from some_table where value=current_iteration_index --105..etc. INTO my_rec_type END LOOP; ...
how can i call a web-service inside a Pl/SQL with a serialize class in the parameters. knowing that i need to get data from pl/sql and pass it to another web-service which will return the result as XML. so shall i take the data from PL/SQL as data-table or can the PL/SQL understand such complex Type as the class. if either of them how...