am learning the Oracle PL/SQL language using CBT Nuggets tutorials and practicing what I learn. I seem to be getting confused with the ROUND function...For example, what am I doing wrong in this statement:
select avg(salary) from employees, ROUND (avg(salary) , 2;
Basically I am trying to round the result to two decimal places but get the error ORA - 00933 - SQL command not properly ended?
My requirement with one table which contains 20 columns. Now I want to convert 18 column into rows. I tried with the following query but its throwing sql error is given below.
select * from tmpl_pop_age_range_col_lvl UNPIVOT ( quantity FOR product IN (COUNT_0_TO_4 as 'a', COUNT_5_TO_9 as 'b', COUNT_10_TO_14 as 'c', COUNT_15_TO_19 as 'd', COUNT_20_TO_24 as 'e', COUNT_25_TO_29 as 'f',
[code].....
ERROR at line 4: ORA-00933: SQL command not properly ended
After creating this procedure...we r not able to execute it...
SQL> ed Wrote file afiedt.buf 1 create or replace procedure dyn_pro1 2 (tablename varchar2,colname varchar2, prodid number) is 3 cmd varchar2(100); 4 begin 5 cmd := 'delete ' || tablename || ' where'|| colname|| '=' ||prodid; 6 execute immediate cmd ;--using College_id; 7* end; SQL> /
Procedure created.
SQL> exec dyn_pro1('stu_4','COLLEGE_ID',200); BEGIN dyn_pro1('stu_4','COLLEGE_ID',200); END; * ERROR at line 1: ORA-00933: SQL command not properly ended ORA-06512: at "APPS.DYN_PRO1", line 6 ORA-06512: at line 1
What I am trying to do is get my report to list every room in the table even if there is nothing scheduled in the room for the selected date. I add a command to the report to force the left outer join but I keep running into errors. This is how I have it worded:
SELECT "ROOM"."ROOM_ID", "PATIENT_CARE_EVENT"."OR_NUM" FROM "ROOM"."ROOM" LEFT OUTER JOIN "PATIENT_CARE_EVENT"."PATIENT_CARE_EVENT" ON "PATIENT_CARE_EVENT"."OR_NUM"="ROOM"."ROOM_ID" AND "PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" IN {?Start Date} TO {?End Date}
Someone else suggested that I change the IN/TO wording in the last line to BETWEEN/AND. When I do that it gives me an error stating that the table or view does not exist.
i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).
now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!
Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?
e.g.
DECLARE name := 'BOB'; BEGIN insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES); END;
In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.
If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?
e.g.
name := 'BOB'; insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);
In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT
I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).
create table dest_test ( destination varchar2(4) not null, db_name varchar2(10) not null )
desc dest
[code]...
I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.
P/s: I don't have OEM installed so I must use SQL command
Example of dropping the index ALTER TABLE table_name drop CONSTRAINT constraint_name;
I use sqlplus in oracle (linux).I have a table and the string cell have long string .
Like below :
column A Column B
A BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB .....................................BBBBBBBBBBB
So, I need to edit/update the row A and the value in Column B.But the string in Column B is so long and I only need to edit one character.IF I use update command , I need to type very long string and it is easy to wrong edit .
I want to creat a table using Execute Immediate that includes tablespace name in the create table command. But I don't know the tablespace name that the client is using. Can I incorporate in a script? If yes, then how?
I have written the following proc. While executing , it gives me the error: ORA-00959: tablespace 'V_TABLESPACE_NAME' does not exist.
create or replace procedure pr_create_table as v_tablespace_name varchar2(4000); begin select distinct tablespace_name into v_tablespace_name from user_tables where table_name like 'ABC%'; [code]....
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
I use following command to determine which table can benefit from shrinking
select * from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) order by reclaimable_space desc
then i give following command to get reclaimable space
alter table t1 enable row movement ; alter table t1 shrink space cascade; alter table t1 disable row movement ;
in table t1 427MB was shown as reclaimable space, after executing above commands, i run dbms_space procedure again to check the out come, but result was same.I understand tablespaces are by default ASSM in 11g, none of table has LONG datatype or LOB indexes or MVIEW with ON COMMIT.
i need a trigger with alter commands to alter the table structure,it will be captured in a separate meta data table(META)
CREATE OR REPLACE TRIGGER meta_alter AFTER Alter ON SCHEMA BEGIN update meta set column_name=:new where table_name=ora_dict_obj_name column_name=:old; END; /
Meta table contains Table name and column name..i attached the table data in atext file
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..
[URL].......
how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?
assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?
create materialized view my_view refresh fast as select * from t1;
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1 BRANCH = 2B EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28 [code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK