Jump to content

Search the Community

Showing results for tags 'oracle'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start





Website URL







Found 12 results

  1. My function does not return the date. I will publish the package body and call the function Can you tell me where the error is? My function in package body looks like this: SQL> CREATE OR REPLACE PACKAGE BODY account_api AS 2 PROCEDURE add_new_account 3 ( p_acc_id accounts.acc_id%type 4 , p_acc_name accounts.acc_name%type 5 , p_acc_amount accounts.acc_amount%type 6 , p_acc_date accounts.acc_date%type) 7 IS 8 BEGIN 9 INSERT INTO accounts(acc_id, acc_name, acc_amount, acc_date) 10 VALUES (p_acc_id, p_acc_name, p_acc_amount, p_acc_date); 11 COMMIT; 12 EXCEPTION 13 WHEN OTHERS THEN 14 ROLLBACK; 15 RAISE; 16 END; 17 PROCEDURE upd_account 18 (p_acc_id accounts.acc_id%type 19 , p_acc_name accounts.acc_name%type 20 , p_acc_amount accounts.acc_amount%type 21 , p_acc_date accounts.acc_date%type 22 ) 23 IS 24 BEGIN 25 UPDATE accounts 26 set acc_name = p_acc_name 27 , acc_amount = p_acc_amount 28 , acc_date = p_acc_date 29 WHERE acc_id = p_acc_id; 30 COMMIT; 31 END; 32 PROCEDURE del_accounts 33 (p_acc_id accounts.acc_id%type) 34 IS 35 BEGIN 36 DELETE FROM accounts WHERE acc_id = p_acc_id; 37 COMMIT; 38 EXCEPTION 39 WHEN OTHERS THEN 40 ROLLBACK; 41 RAISE; 42 END; 43 FUNCTION get_amount 44 (p_acc_id accounts.acc_id%type) 45 return Number is res number; 46 begin 47 select acc_amount into res 48 from accounts where acc_id =p_acc_id; 49 return res; 50 end; 51 FUNCTION get_date 52 (p_acc_id accounts.acc_id%type) 53 RETURN date IS res1 date; 54 BEGIN 55 SELECT acc_date INTO res1 56 FROM accounts WHERE acc_id = p_acc_id; 57 RETURN res1; 58 end; 59 end account_api; 60 / The function is executed but does not return the data. SQL> set serveroutput on SQL> declare 2 res1 date; 3 begin 4 res1 := account_api.get_date(1); 5 end; 6 / PL/SQL procedure successfully completed.
  2. itzkashi

    Error : invalid identifier

    Hi, please find the sample data below ; Requirement is to build the insert statement dynamically through the look up table(test_lkp) so that if any change in the columns can be handled through the look up table rather than modifying the script. To achieve this i have created the look up table TEST_LKP with the same structure as TEST and inserting values as V_COL. i am getting an error for variable "v_col" Is there any way to achieve this DROP TABLE TEST ; DROP TABLE TEST_LKP; DROP TABLE TEST_REF; DROP TABLE TEST_Q; CREATE TABLE TEST (COL VARCHAR2(10)); CREATE TABLE TEST_LKP (COL VARCHAR2(10)); CREATE TABLE TEST_REF (VAL VARCHAR2(10)); CREATE TABLE TEST_Q (ENAME VARCHAR2(10)); INSERT INTO TEST_LKP VALUES ('v_col'); INSERT INTO TEST_REF VALUES ('ENAME'); INSERT INTO TEST_Q VALUES ('TOM'); code : SET SERVEROUTPUT ON; declare v_col VARCHAR2(30); q VARCHAR2(1000); CURSOR c1 IS SELECT 'INSERT INTO TEST ' || 'SELECT ' || COL || ' FROM TEST_Q ' DMLS FROM TEST_LKP; BEGIN SELECT val INTO v_col FROM TEST_REF; dbms_output.put_line(v_col); FOR i IN c1 loop dbms_output.put_line(i.DMLS); execute immediate i.dmls; end loop; END; / erorr : Error report - ORA-00904: "V_COL": invalid identifier ORA-06512: at line 17 00904. 00000 - "%s: invalid identifier" *Cause: *Action: ENAME INSERT INTO TEST SELECT v_col FROM TEST_Q Expected : i want v_col to be replaced with ENAME as INSERT INTO TEST SELECT ENAME FROM TEST_Q what wrong i am doing here?
  3. romntkac

    Complex query - help needed :)

    Hello everyone, I need your advise to get the following output table: Team Scored Received GoalDiff Argentina 3 1 2 Germany 4 4 0 Nigeria 3 4 -1 Japan 1 2 -1 From this input table: HomeTeam AwayTeam HomeScore AwayScore Argentina Nigeria 2 0 Germany Japan 1 1 Japan Argentina 0 1 Germany Nigeria 2 3 Nigeria Japan 0 0 Germany Argentina 1 0 Any suggestions? Thanks for the help!
  4. I installed yesterday Oracle 12c. After installation I accessed normally to the Enterprise Manager requesting https://localhost:5500/em The problem: after restarting Windows I get this error on my web browser after requesting the same URL: Content-Length: 165 <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"> <HTML><HEAD> <TITLE>500 Internal Server Error</TITLE> </HEAD><BODY><H1>Internal Server Error</H1> </BODY></HTML>
  5. We have a very complex view that finishes execution only on first time, but hungs up when run from the same session second time. **complex view -** view with nested views, lots of inner and outer joins, multiple union all etc... But no locking statements, only pure read. $ sqlplus sys/system as sysdba; SQL> SELECT * FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Release - 64bit Production PL/SQL Release - Production CORE Production TNS for Linux: Version - Production NLSRTL Version - Production SQL> alter session set current_schema = DB_2017_2018; SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD'; SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- runs in 33 sec SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- hangs up.. Ctrl + C after 3 minutes SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- runs in 1m 30 sec SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018; -- hangs up.. Ctrl + C after 5 - 10 minutes -- ALL THESE COMMANDS / QUERIES RUN IN SAME SESSION I figured that running the exact same query second time is when the problem occurs. If I simply change any select column or where condition, then it runs for first time and hangs up from second time (see the above commands). Suspect 1 One thing I suspect from this is may be it's related to oracle's cache. So when I run the following commands before each query execution, I didn't get any query hang up. SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; Suspect 2 (root cause might be Suspect 1) Once any query hangs up, even if I run the same query in another session, it still hangs up. So If I kill the active sessions that's running the hang up query, then I can run the query successfully only for first time but stuck with same problem from second run. SQL> ALTER SYSTEM KILL SESSION '12,256' IMMEDIATE; AFAIK, only thing I can be sure is that something is badly wrong with oracle's `buffer_cache` and `shared_pool` in our version. This problem doesn't happen for tables or simple views, only for this complex view (didn't tested other complex views). Also we were facing this kind of strange issue only in last 2 - 3 weeks. It's hosted in cloud (AWS EC2), so it may be related to spectre / meltdown patches by Amazon as that's what changed in last 2 - 3 weeks (unrelated ?!). Or might be due to some db data threshold level reached in that period. Ready to provide any other analysis or metrics Post in DBA stackexchange
  6. Hi, I have a program working with xml files that is not performing anymore since we installed Oracle 12 databases. The error we suddenly have is this one : ORA-31011: XML parsing failed ORA-06512: at "MYUSER.MYPACKAGE", line 1663 ORA-19213: error occurred in XML processing at lines 2 LPX-00209: PI names starting with XML are reserved ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at "MYUSER.MYPACKAGE", line 1162 ORA-06512: at line 1 It seems there is a problem on line 2 of the xml. Indeed this file is particular. There is an xml declaration on each line and lines are pretty long, defining several xml blocks : <?xml version="1.0" encoding="utf-8"?><Flag:...... <?xml version="1.0" encoding="utf-8"?><Message:...... <?xml version="1.0" encoding="utf-8"?><Message:...... ..... <?xml version="1.0" encoding="utf-8"?><Message:...... <?xml version="1.0" encoding="utf-8"?><EndFile:...... In the program the first thing done is to create a blob file from this xml file (this is a function called at the beginning : FUNCTION TransformToCLOB(filename varchar2) RETURN CLOB) src_clob:=BFILENAME('MY_REPERTOIRE', filename); dbms_lob.createtemporary(dest_clob, cache=>TRUE); DBMS_LOB.FILEOPEN(src_clob); DBMS_LOB.LoadFromFile( dest_clob , src_clob ,DBMS_LOB.GETLENGTH(src_clob) ); dbms_lob.close(src_clob); RETURN dest_clob; after that, there is this instruction (ligne 1162 where the error occurs): v_xml:=XMLTYPE(dest_clob); What has changed in Oracle 12 that causes the problem? I have the feeling that several declarations in one file are not welcome anymore.
  7. Hi Guys, I am new to Oracle. In my Dataguard configuration on a 12c environment, when I perform a switchover from primary (foo) to standby (bar) and then try to SQL plus in with user@foo, I receive the "ORA-011033: ORACLE initialization or shutdown in progress" error instead of being able to connect to the new primary(bar). Now, I know that this is because the connection is still pointing to the foo address instead of the bar address, and according to many articles, you need to create a new "virtual" service to handle the different connections, as well as a trigger to stop/start this service after a rolechange. I did this and with the service_names parameter set to this new "virtual" service, a switchover will work and can also switch back but when trying to SQL plus with user@foo it throws ORA-011033. The trigger is useless and the service continues to run on both foo and bar because it seems that having that parameter set overrides whatever the trigger tries to do. Now if I don't define that parameter, my switchover works fine, and the triggers do their job, and when I sqlplus user@foo, it will correctly redirect me to the bar address. However, when I try to switchover back to foo, I receive the following in the DG alert log: Redo transport problem detected: redo transport to database foo has the following error: ORA-16047: DGID mismatch between destination setting and target database 08/05/2016 16:01:02 Initialization of connection failed. Expected destination db_unique_name is foo. Database actually reached is bar Failed to send message to site foo. Error code is ORA-16642. Data Guard Broker Status Summary: Type Name Severity Status Configuration foo_dg Warning ORA-16607 Primary Database bar Error ORA-16778 Physical Standby Database foo Error ORA-16642 ON FOO: log_archive_dest_2='service="bar"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="bar" net_timeout=30','valid_for=(online_logfile,all_roles)' fal_client='foo' fal_server='bar' ON BAR: log_archive_dest_2='service="foo", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="foo" valid_for=(online_logfile,all_roles)' fal_client='bar' fal_server='foo' Entries in LDAP: foo: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=foo_home)(PORT=15940))(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=virtual_foo)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic)))) bar: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=bar))) I'm not sure what I'm missing or misunderstanding. Can anyone shed any light as to what I might be doing wrong? Can provide more info if needed. Thanks & Regards Camillelola
  8. sundara

    Huge SQL data Insert

    I have couple of tables that I need to use to load a 3rd table. The total number of rows to load comes around 30 million. My Insert SQL query is a INSERT-INTO-<TABLE>-SELECT. My Insert SQL query hangs (Waited for 30 mins before killing the session). But when I used ROWNUM in the SELECT query with the ROWNUM value equal to the total number of result rows, the INSERT query ran in 43 seconds. And, both the results are consistent. I am completely lost here. Can you please throw your expert opinion on why the INSERT query without ROWNUM hangs and the INSERT query with ROWNUM runs much faster ? Details : TABLE - A : has a total of 50,000,000 rows; No index TABLE - B : has a total of 200,000 rows; Index present in COLUMN1 (Relation with TABLE-A is one to maasny) TABLE - C : Output table Original INSERT query: INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B); INSERT query with ROWNUM : SELECT COUNT(*) FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B); -- No issues with this query INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B) AND ROWNUM <=30,000,000;
  9. I saw on the internet, some oracle duplication commands used multi channels. Their log looks like: ... using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2 using channel ORA_AUX_DISK_3 using channel ORA_AUX_DISK_4... However, when I perform duplication, the rman uses only one channel ORA_AUX_DISK_1. And the performance is very bad. Please tell me how to configure rman when backup to use multi channels to improve duplication performance when duplication. (I duplicate oracle database without target connection.) Thanks.
  10. Lianamelissa

    oracle export and import

    Hi, this is liana working on oracle goldengate in a level 3 company in my company we use two oracle data bases we consider as D1,D2, every day after completion of my work i had exported to D1 again get back from D2 after when i used it, this is the correct procedure i am fallow are not.
  11. i just want to execute this in oracle form 6i but it will give me an error like ORA-00600: internal code error DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation might cause division-by-zero error. pe_ratio := stock_price / net_earnings; DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio); EXCEPTION -- exception handlers begin -- Only one of the WHEN blocks is executed. WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.'); pe_ratio := NULL; WHEN OTHERS THEN -- handles all other errors DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.'); pe_ratio := NULL; END; -- exception handlers and block end here
  12. We are facing issue while accessing master form in forms 11g. Generally, we have have a master form which requires DB credentials, which get successfully verified and the master form is displayed. ISSUE : --> Randomly the users who try to access this master form (the first form) are shown an applet/form window where it prompts user to enter the password of the database user, using which the form session establishes it's connection to the database. So this frustrates the user, because obviously you can't expect a user or anyone to view a login prompt asking password of that particular DB user. Attached document will help everyone understand better. Since we are approaching month end, it will be fantastic if someone can come up with useful suggestion or solution. STEPS WHICH ARE FOLLOWED FOR LOGGING ONTO THE FORMS APPLICATION.docx