Search Results

Search found 14452 results on 579 pages for 'oracle apex'.

Page 214/579 | < Previous Page | 210 211 212 213 214 215 216 217 218 219 220 221  | Next Page >

• Issue configuring Oracle database for SSL

  - by Santhosha

  Hello, I want to setup Oracle for SSL communication. I am not using SSL authentication for database user. As first requirement, generated self signed certificate using OpenSSL and added certificate to wallet. The wallet location is specified in server configuration. Created listener and it is starting however it does not provide any service. The default listener (non SSL) is working fine. When I execute LSNRCTL.EXE status SSLLISTENER it gives below output. STATUS of the LISTENER Alias SSLLISTENER Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production Start Date 14-NOV-2009 01:47:08 Uptime 16 days 22 hr. 14 min. 3 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\app\Administrator\product\11.1.0\db_1\network\admin\listener.ora Listener Log File c:\app\administrator\diag\tnslsnr\\ssllistener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=)(PORT =2484))) The listener supports no services The command completed successfully Here is exact content of various files after configuration. 1) File Name: tnsnames.ora ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) 2) File Name: sqlnet.ora SSL_VERSION = 0 NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) sqlnet.authentication_services= (NONE) tcp.validnode_checking = no tcp.invited_nodes=(PS0803.oraebs.com,PS2948,PS5098) SSL_CLIENT_AUTHENTICATION = FALSE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\app\Administrator\admin\orcl\Server_Wallet) ) ) 3) File Name: listener.ora S SL_CLIENT_AUTHENTICATION = FALSE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = C:\app\Administrator\admin\orcl\Server_Wallet) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT 1521)) ) ) SSLLISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = )(PORT = 2484)) ) Thanks Santhosh

  Read the article

• Issues Connecting to SQLExpress using Oracle SQL Developer

  - by ArtDeveloper

  Hey Guys, I'm trying to create a connection inside Oracle SQL Developer to a SQLExpress database I have Everything I have resides on the same machine so there isn't any network issues I should have to deal with but everytime I follow the instructions and I try to connect I get the following message "Failure - Unable to get information from SQL Server: localhost." I can connect to the SQLExpress DB using the SQL Management Studio and through an ODBC connection. I've installed the third party extensions and I've enabled the TCP protocol on the SQL Server Configuration manager as well as enabled the IP Addresses I'm assuming that the SQLExpress Database is on port 1433 because I didn't change this when I installed but if someone can tell me how to double check that I would appreciate that info as well. I setup the new connection with the following information name: databasename I'm using windows authentication so the username and password aren't filled in host:localhost port:1433/databasename;instance=SQLEXPRESS *databasename - this is replaced with the actual DB name I've just changed the name here to protect the innocent I've spent about a full day on this trying to get it connected and many google attempts where other ppl have had this issue but have gotten it solved through various methods that I've tried and it hasn't resolved my issue. Any information would be much appreciated Thank you in Advance, AD

  Read the article

• Problem with date parameter - Oracle

  - by Nicole

  Hi everyone! I have this stored procedure: CREATE OR REPLACE PROCEDURE "LIQUIDACION_OBTENER" ( p_Cuenta IN NUMBER, p_Fecha IN DATE, p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE ) IS BEGIN SELECT FILADETALLE INTO p_Detalle FROM Liquidacion WHERE (FILACUENTA = p_Cuenta) AND (FILAFECHA = p_Fecha); END; and my c# code: string liquidacion = string.Empty; OracleCommand command = new OracleCommand("Liquidacion_Obtener"); command.BindByName = true; command.Parameters.Add(new OracleParameter("p_Cuenta", OracleDbType.Int64)); command.Parameters["p_Cuenta"].Value = cuenta; command.Parameters.Add(new OracleParameter("p_Fecha", OracleDbType.Date)); command.Parameters["p_Fecha"].Value = fecha; command.Parameters.Add("p_Detalle", OracleDbType.Varchar2, ParameterDirection.Output); OracleConnectionHolder connection = null; connection = this.GetConnection(); command.Connection = connection.Connection; command.CommandTimeout = 30; command.CommandType = CommandType.StoredProcedure; OracleDataReader lector = command.ExecuteReader(); while (lector.Read()) { liquidacion += ((OracleString)command.Parameters["p_Detalle"].Value).Value; } the thing is that when I try to put a value into the parameter "Fecha" (that is a date) the code gives me this error (when the line command.ExecuteReader(); is executed) Oracle.DataAccess.Client.OracleException : ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYSTEM.LIQUIDACION_OBTENER", line 9 ORA-06512: at line 1 the thing is that te date in the data base is saved like a date but it's format is "2010-APR-14" and the value I send is a datetime that has this format: "14/04/2010 00:00:00" could it be that??? I hope my post is understandable.. thanks!!!!!!!!!!

  Read the article

• oracle pl/sql bug: can't put_line more than 2000 characters

  - by FrustratedWithFormsDesigner

  Has anyone else noticed this phenomenon where dbms_output.put_line is unable to print more than 2000 characters at a time? Script is: set serveroutput on size 100000; declare big_str varchar2(2009); begin for i in 1..2009 loop big_str := big_str||'x'; end loop; dbms_output.put_line(length(big_str)); dbms_output.put_line(big_str); end; / I copied and pasted the output into an editor (Notepad++) which told me there were only 2000 characters, not 2009 which is what I think should have been pasted. This also happens with a few of my test scripts - only 2000 characters get printed. I have a workaround to print like this: dbms_output.put_line(length(big_str)); dbms_output.put_line(substr(big_str,1,1999)); dbms_output.put_line(substr(big_str,2000)); This adds new lines to the output, makes it hard to read when the text you're working with is preformatted. Has anyone else noticed this? Is it really a bug or some sort of obscure feature? Is there a better workaround? Is there any other information on this out there? Oracle version is: 10.2.0.3.0, using PL/SQL Developer (from Allround Automation).

  Read the article

• SSRS - Oracle DB, Passing Date parameter

  - by davidl98

  Using SSRS with an Oracle Database. I need to prompt the user when running the report to enter a date for report. What is the best way to add in the parameter in my SSRS Report. Having problem finding the right date format. under the "Report Parameter" menu, I have setup the Report Parameters using the DateTime Datatype. Keep getting this error "ORA-01843: Not a Valid Month" Thank you for your help. Select a.OPR_Name, a.OPR, a.Trans_Desc, a.Trans_Start_Date, Cast(a.S_Date as date) as S_Date, Sum(a.Duration) as T From ( Select US_F.OPR_Name, ITH_F.OPR, ITH_F.ITH_RID, ITH_F.TRANSACT, Transact.DESC_1 as Trans_Desc, To_CHAR(ITH_F.Start_Time,'DD-Mon-YY') as Trans_Start_Date, To_CHAR(ITH_F.Start_Time,'MM/DD/YYYY') as S_Date, Substr(To_CHAR(ITH_F.Start_Time,'HH24:MI'),1,6) as Start_Time, To_CHAR(ITH_F.End_Time,'DD-Mon-YY') as Trans_End_Date, Substr(To_CHAR(ITH_F.End_Time,'HH24:MI'),1,6) as End_Time, Cast(Case When To_CHAR(ITH_F.Start_Time,'DD-Mon-YY') = To_CHAR(ITH_F.End_Time,'DD-Mon-YY') Then (((To_CHAR(ITH_F.End_Time,'SSSSS') - To_CHAR(ITH_F.Start_Time,'SSSSS')) / 60))/60 Else ((86399 - (To_CHAR(ITH_F.Start_Time,'SSSSS')) + To_CHAR(ITH_F.End_Time,'SSSSS'))/60)/60 End as Decimal(3,1)) as Duration from Elite_76_W1.ITH_F Left Join Elite_76_W1.Transact on Transact.Transact = ITH_F.Transact Left Join Elite_76_W1.US_F on US_F.OPR = ITH_F.OPR Where ITH_F.TRANSACT not in ('ASN','QC','LGOT') ) a Where a.S_Date = @Event_Date Having Sum(a.Duration) < 0 Group By a.OPR_Name, a.OPR, a.Trans_Desc, a.Trans_Start_Date, a.S_Date Order by a.OPR_Name

  Read the article

• Oracle performance problems with large batch of XSL operations

  - by FrustratedWithFormsDesigner

  I have a system that is performing many XSL transformations on XMLType objects. The problem is that the system gradually slows down over time, and sometimes crashes when it runs out of memory. It seems that the slow down (and possibly memory crash) is around the dbms_xslprocessor.processXSL function call, which gradually takes longer and longer to complete. The code looks like this: v_doc dbms_xmldom.DOMDocument; v_transformer dbms_xmldom.DOMDocument; v_XSLprocessor dbms_xslprocessor.Processor; v_stylesheet dbms_xslprocessor.Stylesheet; v_clob clob; ... transformer := PKG_STUFF.getXSL(); v_transformer := dbms_xmldom.newDOMDocument(transformer); v_XSLprocessor := Dbms_Xslprocessor.newProcessor; v_stylesheet := dbms_xslprocessor.newStylesheet(v_transformer, ''); ... for source_data in (select id in source_tbl) loop begin v_doc := PKG_CONVERT.convert(in_id => source_data.id); --start time of operation v_begin_op_time := dbms_utility.get_time; --reset the CLOB v_clob := ' '; --Apply XSL Transform dbms_xslprocessor.processXSL(p => v_XSLprocessor, ss => v_stylesheet, xmldoc => v_Doc, cl => v_clob); v_doc := dbms_xmldom.newDOMDocument(XMLType(v_clob)); --end time v_end_op_time := dbms_utility.get_time; --calculate duration v_time_taken := (((v_end_op_time - v_begin_op_time))); --log the duration PKG_LOG.log_message('Time taken to transform XML: '||v_time_taken); ... ... DBMS_XMLDOM.freeDocument(v_Doc); DBMS_LOB.freetemporary(lob_loc => v_clob); end loop; The time taken to transform the XML is slowly creeping up (I suppose it might also be the call to dbms_xmldom.newDOMDocument, but I had thought that to be fairly straightforward). I have no idea why.... :( (Oracle 10g)

  Read the article

• Converting Complicated Oracle Join Syntax

  - by Grasper

  I have asked for help before on porting joins of this nature, but nothing this complex. I am porting a bunch of old SQL from oracle to postgres, which includes a lot of (+) style left joins. I need this in a format that pg will understand. I am having trouble deciphering this join hierarchy: SELECT * FROM PLANNED_MISSION PM_CTRL, CONTROL_AGENCY CA, MISSION_CONTROL MC, MISSION_OBJECTIVE MOR, REQUEST_OBJECTIVE RO, MISSION_REQUEST_PAIRING MRP, FRIENDLY_UNIT FU, PACKAGE_MISSION PKM, MISSION_AIRCRAFT MA, MISSION_OBJECTIVE MO, PLANNED_MISSION PM WHERE PM.MSN_TASKED_UNIT_TYPE != 'EAM' AND PM.MSN_INT_ID = MO.MSN_INT_ID AND PM.MSN_INT_ID = PKM.MSN_INT_ID (+) AND PM.MSN_INT_ID = MA.MSN_INT_ID (+) AND COALESCE(MA.MA_RESOURCE_INT_ID,0) = (SELECT COALESCE(MIN(MA1.MA_RESOURCE_INT_ID),0) FROM MISSION_AIRCRAFT MA1 WHERE MA.MSN_INT_ID = MA1.MSN_INT_ID) AND MA.FU_UNIT_ID = FU.FU_UNIT_ID (+) AND MA.CC_COUNTRY_CD = FU.CC_COUNTRY_CD (+) AND MO.MSN_INT_ID = MC.MSN_INT_ID (+) AND MO.MO_INT_ID = MC.MO_INT_ID (+) AND MC.CAG_CALLSIGN = CA.CAG_CALLSIGN (+) AND MC.CTRL_MSN_INT_ID = PM_CTRL.MSN_INT_ID (+) AND MO.MSN_INT_ID = MRP.MSN_INT_ID (+) AND MO.MO_INT_ID = MRP.MO_INT_ID (+) AND MRP.REQ_INT_ID = RO.REQ_INT_ID (+) AND RO.MSN_INT_ID = MOR.MSN_INT_ID (+) AND RO.MO_INT_ID = MOR.MO_INT_ID (+) AND MO.MSN_INT_ID = :msn_int_id AND MO.MO_INT_ID = :obj_int_id AND COALESCE(PM.MSN_MISSION_NUM, ' ') LIKE '%' AND COALESCE( PKM.PKG_NM,' ') LIKE '%' AND COALESCE( MA.FU_UNIT_ID, ' ') LIKE '%' AND COALESCE( MA.CC_COUNTRY_CD, ' ') LIKE '%' AND COALESCE(FU.FU_COMPONENT, ' ') LIKE '%' AND COALESCE( MA.ACT_AC_TYPE,' ') LIKE '%' AND MO.MO_MSN_CLASS_CD LIKE '%' AND COALESCE(MO.MO_MSN_TYPE, ' ') LIKE '%' AND COALESCE( MO.MO_OBJ_LOCATION,COALESCE( MOR.MO_OBJ_LOCATION, ' ')) LIKE '%' AND COALESCE(CA.CAG_TYPE_OF_CONTROL, ' ') LIKE '%' AND COALESCE( MC.CAG_CALLSIGN,' ') LIKE '%' AND COALESCE( MC.ASP_AIRSPACE_NM, ' ') LIKE '%' AND COALESCE( MC.CTRL_MSN_INT_ID, 0) LIKE '%' AND COALESCE(MC.CTRL_MO_INT_ID, 0) LIKE '%' AND COALESCE( PM_CTRL.MSN_MISSION_NUM,' ') LIKE '%' Any help is appreciated.

  Read the article

• Problem with oracle stored procedure - parameters

  - by Nicole

  I have this stored procedure: CREATE OR REPLACE PROCEDURE "LIQUIDACION_OBTENER" ( p_Cuenta IN NUMBER, p_Fecha IN DATE, p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE ) IS BEGIN SELECT FILADETALLE INTO p_Detalle FROM Liquidacion WHERE (FILACUENTA = p_Cuenta) AND (FILAFECHA = p_Fecha); END; / ...and my c# code: string liquidacion = string.Empty; OracleCommand command = new OracleCommand("Liquidacion_Obtener"); command.BindByName = true; command.Parameters.Add(new OracleParameter("p_Cuenta", OracleDbType.Int64)); command.Parameters["p_Cuenta"].Value = cuenta; command.Parameters.Add(new OracleParameter("p_Fecha", OracleDbType.Date)); command.Parameters["p_Fecha"].Value = fecha; command.Parameters.Add("p_Detalle", OracleDbType.Varchar2, ParameterDirection.Output); OracleConnectionHolder connection = null; connection = this.GetConnection(); command.Connection = connection.Connection; command.CommandTimeout = 30; command.CommandType = CommandType.StoredProcedure; OracleDataReader lector = command.ExecuteReader(); while (lector.Read()) { liquidacion += ((OracleString)command.Parameters["p_Detalle"].Value).Value; } the thing is that when I try to put a value into the parameter "Fecha" (that is a date) the code gives me this error (when the line command.ExecuteReader(); is executed) Oracle.DataAccess.Client.OracleException : ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYSTEM.LIQUIDACION_OBTENER", line 9 ORA-06512: at line 1 I tried with the datetime and was not the problem, I eve tried with no input parameters and just the output and still got the same error. Aparently the problem is with the output parameter. I already tried putting p_Detalle OUT VARCHAR2 instead of p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE but it didn't work either I hope my post is understandable.. thanks!!!!!!!!!!

  Read the article

• Oracle sqlldr: column not allowed here

  - by Wade Williams

  Can anyone spot the error in this attempted data load? The '\\N' is because this is an import of an OUTFILE dump from mysql, which puts \N for NULL fields. The decode is to catch cases where the field might be an empty string, or might have \N. Using Oracle 10g on Linux. load data infile objects.txt discardfile objects.dsc truncate into table objects fields terminated by x'1F' optionally enclosed by '"' (ID INTEGER EXTERNAL NULLIF (ID='\\N'), TITLE CHAR(128) NULLIF (TITLE='\\N'), PRIORITY CHAR(16) "decode(:PRIORITY, BLANKS, NULL, '\\N', NULL)", STATUS CHAR(64) "decode(:STATUS, BLANKS, NULL, '\\N', NULL)", ORIG_DATE DATE "YYYY-MM-DD HH:MM:SS" NULLIF (ORIG_DATE='\\N'), LASTMOD DATE "YYYY-MM-DD HH:MM:SS" NULLIF (LASTMOD='\\N'), SUBMITTER CHAR(128) NULLIF (SUBMITTER='\\N'), DEVELOPER CHAR(128) NULLIF (DEVELOPER='\\N'), ARCHIVE CHAR(4000) NULLIF (ARCHIVE='\\N'), SEVERITY CHAR(64) "decode(:SEVERITY, BLANKS, NULL, '\\N', NULL)", VALUED CHAR(4000) NULLIF (VALUED='\\N'), SRD DATE "YYYY-MM-DD" NULLIF (SRD='\\N'), TAG CHAR(64) NULLIF (TAG='\\N') ) Sample Data (record 1). The ^_ represents the unprintable 0x1F delimiter. 1987^_Component 1987^_\N^_Done^_2002-10-16 01:51:44^_2002-10-16 01:51:44^_import^_badger^_N^_^_N^_0000-00-00^_none Error: Record 1: Rejected - Error on table objects, column SEVERITY. ORA-00984: column not allowed here

  Read the article

• Porting Oracle Procedure to PostgreSQL

  - by Grasper

  I am porting an Oracle function into Postgres PGPLSQL.. I have been using this guide: http://www.postgresql.org/docs/8.1/static/plpgsql.html CREATE OR REPLACE PROCEDURE DATA_UPDATE (mission NUMBER, task NUMBER) AS BEGIN IF mission IS NOT NULL THEN UPDATE MISSION_OBJECTIVE MO SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED, MO.MO_TKR_TOTAL_RECEIVERS) = (SELECT NVL(SUM(RR.TRQ_FUEL_OFFLOAD),0), NVL(SUM(RR.TRQ_NUMBER_RECEIVERS),0) FROM REFUELING_REQUEST RR, MISSION_REQUEST_PAIRING MRP WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID AND MO.MO_INT_ID = MRP.MO_INT_ID AND MRP.REQ_INT_ID = RR.REQ_INT_ID) WHERE MO.MSN_INT_ID = mission AND MO.MO_INT_ID = task ; END IF ; COMMIT ; END ; I've got it this far: CREATE OR REPLACE FUNCTION DATA_UPDATE (NUMERIC, NUMERIC) RETURNS integer as ' DECLARE mission ALIAS for $1; task ALIAS for $2; BEGIN IF mission IS NOT NULL THEN UPDATE MISSION_OBJECTIVE MO SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED, MO.MO_TKR_TOTAL_RECEIVERS) = (SELECT COALESCE(SUM(RR.TRQ_FUEL_OFFLOAD),0), COALESCE(SUM(RR.TRQ_NUMBER_RECEIVERS),0) FROM REFUELING_REQUEST RR, MISSION_REQUEST_PAIRING MRP WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID AND MO.MO_INT_ID = MRP.MO_INT_ID AND MRP.REQ_INT_ID = RR.REQ_INT_ID) WHERE MO.MSN_INT_ID = mission AND MO.MO_INT_ID = task ; END IF; COMMIT; END; ' LANGUAGE plpgsql; This is the error I get: ERROR: syntax error at or near "SELECT" LINE 1: ...OTAL_OFF_SCHEDULED, MO.MO_TKR_TOTAL_RECEIVERS) = (SELECT COA... I do not know why this isn't working... any ideas?

  Read the article

• Identifying and Resolving Oracle ITL Deadlock

  - by Allan

  I have an Oracle DB package that is routinely causing what I believe is an ITL (Interested Transaction List) deadlock. The relevant portion of a trace file is below. Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TM-0000cb52-00000000 22 131 S 23 143 SS TM-0000ceec-00000000 23 143 SX 32 138 SX SSX TM-0000cb52-00000000 30 138 SX 22 131 S session 131: DID 0001-0016-00000D1C session 143: DID 0001-0017-000055D5 session 143: DID 0001-0017-000055D5 session 138: DID 0001-001E-000067A0 session 138: DID 0001-001E-000067A0 session 131: DID 0001-0016-00000D1C Rows waited on: Session 143: no row Session 138: no row Session 131: no row There are no bit-map indexes on this table, so that's not the cause. As far as I can tell, the lack of "Rows waited on" plus the "S" in the Waiter waits column likely indicates that this is an ITL deadlock. Also, the table is written to quite often (roughly 8 insert or updates concurrently, as often as 240 times a minute), so and ITL deadlock seems like a strong possibility. I've increased the INITRANS parameter of the table and it's indexes to 100 and increased the PCT_FREE on the table from 10 to 20 (then rebuilt the indexes), but the deadlocks are still occurring. The deadlock seems to happen most often during an update, but that could just be a coincidence, as I've only traced it a couple of times. My questions are two-fold: 1) Is this actually an ITL deadlock? 2) If it is an ITL deadlock, what else can be done to avoid it?

  Read the article

• (PL/SQL)Oracle stored procedure parameter size limit(VARCHAR2)

  - by Jude Lee

  Hello, there. I have an problem with my oracle stored procedure as following. codes : CREATE OR REPLACE PROCEDURE APP.pr_ap_gateway ( aiov_req IN OUT VARCHAR2, aov_rep01 OUT VARCHAR2, aov_rep02 OUT VARCHAR2, aov_rep03 OUT VARCHAR2, aov_rep04 OUT VARCHAR2, aov_rep05 OUT VARCHAR2 ) IS v_header VARCHAR (100); v_case VARCHAR (4); v_err_no_case VARCHAR (5) := '00004'; BEGIN DBMS_OUTPUT.ENABLE (1000000); aov_rep01 := lpad(' ', 190, ' '); dbms_output.put_line('>> ['||length(aov_rep01)||']'); aov_rep01 := lpad(' ', 199, ' '); dbms_output.put_line('>> ['||length(aov_rep01)||']'); aov_rep01 := lpad(' ', 200, ' '); dbms_output.put_line('>> ['||length(aov_rep01)||']'); aov_rep01 := lpad(' ', 201, ' '); dbms_output.put_line('>> ['||length(aov_rep01)||']'); END pr_ap_gateway; / results : >> [190] >> [199] >> [200] and then error 'buffer overflow' ORA-06502: PL/SQL: ?? ?? ? ??: ??? ??? ?? ???? I know that VARCHAR2 type can contain 32KB in PL/SQL. But, in my test, VARCHAR2 parameter contains only 200 Bytes. What's wrong with this situation? This procedure will called by java daemon program. So, There's no declaration of parameters size before calling procedure. Thanks in advance for your reply.

  Read the article

• Using Pragma in Oracle Package Body

  - by asalamon74

  I'd like to create an Oracle Package and two functions in it: A public function ( function_public ) and a private one ( function_private ). The public function calls the private one. I'd like to add the same pragma to the functions: WNDS, WNPS. Without the pragma I can create a code like this: CREATE OR REPLACE PACKAGE PRAGMA_TEST AS FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2; END PRAGMA_TEST; CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS BEGIN -- code END; FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS BEGIN -- code -- here is a call for function_private -- code END; END PRAGMA_TEST; If I'd like to add WNDS, WNPS pragma to function_public I should also add the same pragma to function_private because function_public calls function_private. It seems to me pragma can be used only in the package declaration, and not in package body, so I have to declare function_private in the package as well: CREATE OR REPLACE PACKAGE PRAGMA_TEST AS FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES( function_private, WNDS, WNPS); FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES( function_public, WNDS, WNPS); END PRAGMA_TEST; CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS BEGIN -- code END; FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS BEGIN -- code -- here is a call for function_private -- code END; END PRAGMA_TEST; This solution makes my function_private public as well. Is there a solution to add pragma to a function which can be found only in the package body?

  Read the article

• SQL ORACLE - Datatable in where clause

  - by Gage

  Currently I have a sql call returning a dataset from a MSSQL database and I want to take a column from that data and return ID's based off that column from the ORACLE database. I can do this one at a time but that requires multiple calls, I am wondering if this can be done with one call. String sql=String.Format(@"Select DIST_NO FROM DISTRICT WHERE DIST_DESC = '{0}'", row.Table.Rows[0]["Op_Centre"].ToString()); Above is the string I am using to return one ID at a time. I know the {0} can be used to format your value into the string and maybe there is a way to do that with a datatable. Also to use multiple values in the where clause it would be: String sql=String.Format(@"Select DIST_NO FROM DISTRICT WHERE DIST_DESC in ('{0}')", row.Table.Rows[0] ["Op_Centre"].ToString()); Although I realize all of this can be done I am wondering if theres an easy way to add it all to the sql string in one call. As I am writing this I am realizing I could break the string into sections then just add every row value to the SQL string within the "WHERE DIST_DESC IN (" clause... I am still curious to see if there is another way though, and because someone else may come across this problem I will post a solution if I develop one. Thanks in advance.

  Read the article

• oracle sequence init

  - by gospodin

  I wanted to export 3 tables from db1 into db2. Before the export starts, I will create the sequences for those 3 tables. CREATE SEQUENCE TEST_SEQ START WITH 1 INCREMENT BY 1; After the export, I will reinitialize sequnce values to match the max(id) + 1 from the table. CREATE OR REPLACE PROCEDURE "TEST_SEQUENCE" AUTHID CURRENT_USER is v_num number; begin select max(ID) into v_num from TABLE_1; EXECUTE IMMEDIATE 'ALTER SEQUENCE TEST_SEQ INCREMENT BY ' || v_num; EXECUTE IMMEDIATE 'ALTER SEQUENCE 1TEST_SEQ INCREMENT BY 1'; end; / show errors; execute TEST_SEQ; This procedure compiles and executes without problems. But when I want to check t he last value of the sequence, like select TEST_SEQ.nextval from dual; I still get the "1". Can someone tell me why my procedure did not impact my sequence? ps. I am using oracle sql developper to pass sql. Thanks

  Read the article

• joining table of oracle

  - by Deven

  Hi friends i am having problem in joining two tables in oracle my two tables are shown bellow table1 looks like id        Name      Jan 7001    Deven    22 7002    Clause    55 7004    Monish    11 table2 looks like id        Name      Feb 7001    Deven    12 7002    Clause    15 7003    Nimesh    20 7004    Monish    21 7005    Ritesh    22 i want to combine this two table and want answer like bellow table2 looks like id        Name      Jan    Feb 7001    Deven    22     12 7002    Clause   55     15 7003    Nimesh    -       20 7004    Monish   11     21 7005    Ritesh    -        22

  Read the article

• oracle query with inconsistent results

  - by Spencer Stejskal

  Im having a very strange problem, i have a complicated view that returns incorrect data when i query on a particular column. heres an example: select empname, has_garnishment from timecard_v2 where empname = 'Testerson, Testy'; this returns the single result 'Testerson, Testy', 'N' however, if i use the query: select empname, has_garnishment from timecard_v2 where empname = 'Testerson, Testy' and has_garnishment = 'Y'; this returns the single result 'Testerson, Testy', 'Y' The second query should return a subset of the first query, but it returns a different answer. I have dissected the view and determined that this section of the view definition is where the problem arises(Note, I removed all of the select clause except the parts of interests for clarity, in the full query all joined tables are required): SELECT e.fullname empname , NVL2(ded.has_garn, 'Y', 'N') has_garnishment FROM timecard tc , orderdetail od , orderassign oa , employee e , employee3 e3 , customer10 c10 , order_misc om, (SELECT COUNT(*) has_garn, v_ssn FROM deductions WHERE yymmdd_stop = 0 OR (LENGTH(yymmdd_stop) = 7 AND to_date(SUBSTR(yymmdd_stop, 2), 'YYMMDD') sysdate) GROUP BY v_ssn ) ded WHERE oa.lrn(+) = tc.lrn_order AND om.lrn(+) = od.lrn AND od.orderno = oa.orderno AND e.ssn = tc.ssn AND c10.custno = tc.custno AND e.lrn = e3.lrn AND e.ssn = ded.v_ssn(+) One thing of note about the definition of the 'ded' subquery. The v_ssn field is a virtual field on the deductions table. I am not a DBA im a software developer but we recently lost our DBA and the new one is still getting up to speed so im trying to debug this issue. That being said, please explain things a little more thoroughly then you would for a fellow oracle expert. thanks

  Read the article

• Combining multiple rows into one row, Oracle

  - by Torbjørn

  Hi. I'm working with a database which is created in Oracle and used in a GIS-software through SDE. One of my colleuges is going to make some statistics out of this database and I'm not capable of finding a reasonable SQL-query for getting the data. I have two tables, one with registrations and one with registrationdetails. It's a one to many relationship, so the registration can have one or more details connected to it (no maximum number). table: Registration RegistrationID Date TotLenght 1 01.01.2010 5 2 01.02.2010 15 3 05.02.2009 10 2.table: RegistrationDetail DetailID RegistrationID Owner Type Distance 1 1 TD UB 1,5 2 1 AB US 2 3 1 TD UQ 4 4 2 AB UQ 13 5 2 AB UR 13,1 6 3 TD US 5 I want the resulting selection to be something like this: RegistrationID Date TotLenght DetailID RegistrationID Owner Type Distance DetailID RegistrationID Owner Type Distance DetailID RegistrationID Owner Type Distance 1 01.01.2010 5 1 1 TD UB 1,5 2 1 AB US 2 3 1 TD UQ 4 2 01.02.2010 15 4 2 AB UQ 13 5 2 AB UR 13,1 3 05.02.2009 10 6 3 TD US 5 With a normal join I get one row per each registration and detail. Can anyone help me with this? I don't have administrator-rights for the database, so I can't create any tables or variables. If it's possible, I could copy the tables into Access.

  Read the article

• oracle plsql select pivot without dynamic sql to group by

  - by kayhan yüksel

  To whom it may respond to, We would like to use SELECT function with PIVOT option at a 11g r2 Oracle DBMS. Our query is like : "select * from (SELECT o.ship_to_customer_no, ol.item_no,ol.amount FROM t_order o, t_order_line ol WHERE o.NO = ol.order_no and ol.item_no in (select distinct(item_no) from t_order_line)) pivot --xml ( SUM(amount) FOR item_no IN ( select distinct(item_no) as item_no_ from t_order_line));" As can be seen, XML is commented out, if run as PIVOT XML it gives the correct output in XML format, but we are required to get the data as unformatted pivot data, but this sentence throws error : ORA-00936: missing expression Any resolutions or ideas would be welcomed, Best Regards -------------if we can get the result of this to sys_refcursor using execute immediate it will be solved ------------------------ the procedure : PROCEDURE pr_test2 (deneme OUT sys_refcursor) IS v_sql NVARCHAR2 (4000) := ''; TYPE v_items IS TABLE OF NVARCHAR2 (30); v_pivot_items NVARCHAR2 (4000) := ''; BEGIN FOR i IN (SELECT DISTINCT (item_no) AS items FROM t_order_line) LOOP v_pivot_items := ',''' || i.items || '''' || v_pivot_items; END LOOP; v_pivot_items := LTRIM (v_pivot_items, ','); v_sql := 'begin select * from (SELECT o.ship_to_customer_no, ol.item_no,ol.amount FROM t_order o, t_order_line ol WHERE o.NO = ol.order_no and OL.ITEM_NO in (select distinct(item_no) from t_order_line)) pivot --xml ( SUM(amount) FOR item_no IN (' || v_pivot_items || '));end;'; open DENEME for select v_sql from dual; Kayhan YÜKSEL

  Read the article

• Oracle DBMS_PROFILER only shows Anonymous in the results tables

  - by Greg Reynolds

  I am new to DBMS_PROFILER. All the examples I have seen use a simple top-level procedure to demonstrate the use of the profiler, and from there get all the line numbers etc. I deploy all code in packages, and I am having great difficulty getting my profile session to populate the plsql_profiler_units with useful data. Most of my runs look like this: RUNID RUN_COMMENT UNIT_OWNER UNIT_NAME SECS PERCEN ----- ----------- ----------- -------------- ------- ------ 5 Test <anonymous> <anonymous> .00 2.1 Profiler 5 Test <anonymous> <anonymous> .00 2.1 Profiler 5 Test <anonymous> <anonymous> .00 2.1 Profiler I have just embedded the calls to the dbms_profiler.start_profiler, flush_data and stop_profiler as per all the examples. The main difference is that my code is in a package, and calls in to other package. Do you have to profile every single stored procedure in your call stack? If so that makes this tool a little useless! I have checked http://www.dba-oracle.com/t_plsql_dbms_profiler.htm for hints, among other similar sites.

  Read the article

• Problem importing Oracle .dmp file

  - by BitFiddler

  So I have looked at all the suggested ways of importing .dmp files and non of them seem to answer this question: where does the data go once you import it? Context: I created a user like so: SQL> create user IMPORTER identified by "12345"; SQL> grant connect, unlimited tablespace, resource to IMPORTER; I then ran the 'imp' command as follows: C:\>imp system/password FROMUSER=OVIEDOE TOUSER=IMPORTER file=c:\database1.dmp Now there were 9 .dmp files, after each one it asked me for the next one and then I received the message "Import terminated successfully with warnings." The warning was: Warning: the objects were exported by OVIEDOE, not by you import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set export client uses WE8ISO8859P1 character set (possible charset conversion) IMP-00046: using FILESIZE value from export file of 2147483648 Now it says it was terminated successfully so my assumption (I am new to oracle so this may be wrong) is that the data was loaded. However, when I use SQL developer to connect to the database and look under the 'tables' node under the IMPORTER user, there is nothing there. What is going on? Did the data load? If so, where can I find it?

  Read the article

• Problem with UPDATE statement in stored-procedure in Oracle Database

  - by MKP

  Hello, I have stored-procedure in Oracle database like this: create or replace PROCEDURE EDYTUJ_PRACOWNIKA (PR_IMIE IN VARCHAR2, PR_NAZWISKO IN VARCHAR2, PR_PENSJA IN FLOAT, PR_PRZELOZONY IN NUMBER, PR_ODDZIAL IN NUMBER, PRAC_ID IN NUMBER) AS tmpPensja FLOAT := 0; tmpPrzel NUMBER := 0; BEGIN select przelozony into tmpPrzel from pracownik where id = PRAC_ID; IF(tmpPrzel IS NOT NULL) THEN select pensja into tmpPensja from pracownik where id = tmpPrzel; IF(tmpPensja < 1150) THEN UPDATE PRACOWNIK SET pensja = 1000 WHERE id = tmpPrzel; ELSE UPDATE PRACOWNIK SET pensja = pensja - 150 WHERE id = tmpPrzel; (4) END IF; END IF; IF(PR_PRZELOZONY > 0) THEN UPDATE PRACOWNIK SET imie = PR_IMIE, nazwisko = PR_NAZWISKO, pensja = PR_PENSJA, przelozony = PR_PRZELOZONY, oddzial = PR_ODDZIAL WHERE id = PRAC_ID; (2) select pensja into tmpPensja from pracownik where id = PR_PRZELOZONY; IF(tmpPensja > 4850) THEN UPDATE PRACOWNIK SET pensja = 5000 WHERE id = PR_PRZELOZONY; ELSE UPDATE PRACOWNIK SET pensja = pensja + 150 WHERE id = PR_PRZELOZONY; (1) END IF; ELSE UPDATE PRACOWNIK SET imie = PR_IMIE, nazwisko = PR_NAZWISKO, pensja = PR_PENSJA, przelozony = NULL, oddzial = PR_ODDZIAL WHERE ID = PRAC_ID; (3) END IF; END; where przelozony and pensja are columns in pracownik table. And I have problem that when running procedure with parameters that provide that line marked with "(1)" (there is the same problem with line marked with "(4)") should be executed that update statement don't have any effect. What's more statements in lines marked with "(2)" and "(3)" works fine. I have no ideas how to fix it. Thank you in advance for your help.

  Read the article

• Oracle SQL: ROLLUP not summing correctly

  - by tommy-o-dell

  Hi guys, Rollup seems to be working correcly to count the number of units, but not the number of trains. Any idea what could be causing that? The output from the query looks like this. The sum of the Units column in yellow is 53 but the rollup is showing 51. The number of units adds up correctly though... And here's the oracle SQL query... select t.year, t.week, decode(t.mine_id,NULL,'PF',t.mine_id) as mine_id, decode(t.product,Null,'LF',t.product) as product, decode(t.mine_id||'-'||t.product,'-','PF',t.mine_id||'-'||t.product) as code, count(distinct t.tpps_train_id) as trains, count(1) as units from ( select trn.mine_code as mine_id, trn.train_tpps_id as tpps_train_id, round((con.calibrated_weight_total - con.empty_weight_total),2) as tonnes from widsys.train trn INNER JOIN widsys.consist con USING (train_record_id) where trn.direction = 'N' and (con.calibrated_weight_total-con.empty_weight_total) > 10 and trn.num_cars > 10 and con.consist_no not like '_L%' ) w, ( select to_char(td.datetime_act_comp_dump-7/24, 'IYYY') as year, to_char(td.datetime_act_comp_dump-7/24, 'IW') as week, td.mine_code as mine_id, td.train_id as tpps_train_id, pt.product_type_code as product from tpps.train_details td inner join tpps.ore_products op using (ore_product_key) inner join tpps.product_types pt using (product_type_key) where to_char(td.datetime_act_comp_dump-7/24, 'IYYY') = 2010 and to_char(td.datetime_act_comp_dump-7/24, 'IW') = 12 order by td.datetime_act_comp_dump asc ) t where w.mine_id = t.mine_id and w.tpps_train_id = t.tpps_train_id having t.product is not null or t.mine_id is null group by t.year, t.week, rollup( t.mine_id, t.product)

  Read the article

• How to store unlimited characters in Oracle 11g?

  - by vicky21

  We have a table in Oracle 11g with a varchar2 column. We use a proprietary programming language where this column is defined as string. Maximum we can store 2000 characters (4000 bytes) in this column. Now the requirement is such that the column needs to store more than 2000 characters (in fact unlimited characters). The DBAs don't like BLOB or LONG datatypes for maintenance reasons. The solution that I can think of is to remove this column from the original table and have a separate table for this column and then store each character in a row, in order to get unlimited characters. This tble will be joined with the original table for queries. Is there any better solution to this problem? UPDATE: The proprietary programming language allows to define variables of type string and blob, there is no option of CLOB. I understand the responses given, but I cannot take on the DBAs. I understand that deviating from BLOB or LONG will be developers' nightmare, but still cannot help it.

  Read the article

• comma separated values in oracle function body

  - by dmitry

  I've got following oracle function but it does not work and errors out. I used Ask Tom's way to convert comma separated values to be used in select * from table1 where col1 in <> declared in package header: TYPE myTableType IS table of varchar2 (255); Part of package body: l_string long default iv_value_with_comma_separated|| ','; l_data myTableType := myTableType(); n NUMBER; begin begin LOOP EXIT when l_string is null; n := instr( l_string, ',' ); l_data.extend; l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) ); l_string := substr( l_string, n+1 ); END LOOP; end; OPEN my_cursor FOR select * from table_a where column_a in (select * from table (l_data)); CLOSE my_cursor END; above fails but it works fine when I remove select * from table (l_data) Can someone please tell me what I might be doing wrong here??

  Read the article

< Previous Page | 210 211 212 213 214 215 216 217 218 219 220 221  | Next Page >