We have procedure that writes text to an Excel file.
The 10g database is setup for US7ASCII character set. This causes the characters on the Excel spreadsheet to appear strangely. As an example an 'a' would appear with a character similar to '~' on top of the 'a'.
If there anyway of resetting the character set at runtime of the procedure.
How to handle special charater '&' in stored procedure.
Following is a test code I want to Implement
CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS
BEGIN
INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary
[code]....
The code above resuts into INVALID state of stored procedure and cannot be executed.
I have written a stored procedure that has started returning the error:
Error starting at line 1 in command:
call p_glpost('DSTUK', 'L', '2008-01-01', '2008-01-01', '2011-02-18', 1, 1, 1, 0, 'Hi there')
Error report:
SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CLARITY.P_GLPOST", line 173
06502. 00000 - "PL/SQL: numeric or value error%s"
I can't seem to find a tool that will let me step into the actual stored procedure line by line to see where the error occurs. It mentions line 173, which seems to be a red-herring, as line 173 is simply one of the 'END IF' lines within this block:
IF NVL(r_dist.transtype,'wild') = 'wild' THEN
NULL;
elsif r_wip.transtype = r_dist.transtype THEN
v_matchCount := v_matchCount+1;
elsif r_wip.transtype <> r_dist.transtype THEN
[code]......
Tell me if it is possible to trace through a SP, and which tool is best (I am trying to use Oracle SQL Developer).
how do we know database character set is either single character set or multi character set?
While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".
Below are steps taken to resolve the issue -
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
i got this error: ORA-12712: new character set must be a superset of old character set
below are the commands executed by me:
SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;
And its working...
I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?
I am working with an asp.net/vb.net application whose users have oracle accounts to connect to the application.The database is set up to allow three failed login attempts. When connecting directly to the database, the sys.user$ lcount field increments on unsuccessful login attempts, and resets to 0 on successful login attepts. This is what I expect should happen.
However, when logging in through the application, using:
objConnection = New OracleConnection(m_strConnectionString)
objConnection.Open()
the lcount field sporatically increments on unsuccessful login attempts (sometimes it does, sometimes it doesn't), and never resets to 0 on successful login attempts. I would have expected it to behave as it does when connecting directly to the database.
Why does the lcount field not reset on a successful login through the application? How can I make it increment on each unsuccessful login attempt and reset on each successful login attempt?
I'm wondering how I can use a counter to number records in a table I'm inserting into. I need the counter to reset based on changing data in my table. For example, I have the following
seq_number name_type
1 Short Name
1 Short Name
1 Short Name
2 Short Name
2 Short Name
I'd like my results to be the following:
seq_number name_type
1 Short Name - 1
1 Short Name - 2
1 Short Name - 3
2 Short Name - 1
2 Short Name - 2
I'd like my counter to increment so that I can add a sequence number to the end of my name type, but when my seq_number field changes I'd like to reset and restart my counter.
My supervisor wants to remove all the archivelogs since it was just a test for 1 year the DB is not actually YET been used. Problem is they want it to be used as soon as possible w/o recreating again the database and just removing some data on tables and removed archive logs. how to safely removed the existing archived logs and create a full backup with archived fresh to sequence 1.
View 2 Replies View Related Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
PL/SQL Release 11.1.0.6.0 - CORE 11.1.0.6.0
TNS for Linux: Version 11.1.0.6.0 - NLSRTL Version 11.1.0.6.0
When I checked the status of $emctl status dbconsole. I got this error OC4J Configuration issue.
I was told by the forums to drop and recreate the repos. But I noticed the moment you drop the repos you will loose the passwords of SYSMAN and DBSNMP. emca -deconfig dbcontrol db -repos dropemca -config dbcontrol db -repos createI tried default passwords does not worked. it looks like the only option I have is to reset the passwords. but my main concerns is this impact the DB in generally or its common. or is there any other way we can get the OEM back(I already dropped the repos).
I just found out that the LISTENER I've creating with a password was with "-inherit" settings. Twice I succeeded in doing this, but it seems that it's not working when I tested it. Thus, I opted to get rid of it's listener.bak file and set the password again. Now I just can't stop it because of this error message: "TNS-01169: The listener has not recognized the password".
I've tried every passwords I made, and tried resetting it, but I'm not successful either, it's the same error message.My last option would be to restart the db Server (since the original listener.ora is already intact),
Is there any way could alter the sequence depending on specific condition to start with 1 in the form builder?
i wanna to reset the sequence to 1 whenever the month change.is there any way?
Could i make detail block invisible and whenever button pressed i will display the block? I want to put the where clause for that block in the button and if the button did not pressed i want to display nothing in the detail block?
We have Oracle 10g and user trying to Insert following in one of our table field and getting error:
'REVOLUCIÃ"N Historical Corruptions Agenda'
Getting follwoing error:
ORA-00911: invalid character
create table Employee
(Emp_ID Varchar2 (10),
Company_Name Varchar2 (40)
)
Insert into Employee
values ('Emp1', 'REVOLUCIÃ"N Historical Corruptions Agenda');
I'm trying to insert a character from the extended ascii character set. Specifically, there's a company that has an accented e (�) in the name. Right now, the company name doesn't have the e at all, accent or no accent. So I'm trying to do an update, something like
update table1 set company_name='blah�" where company='blah'
It runs, but doesn't do the update. Even when I try to forcefully do an insert (instead of an update) I get nowhere; the accented is simply dropped. So the basic question is, how do you insert extended ascii characters into oracle?
I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'
I want to get the string between second underscore and third underscore i.e. 'MEGAMART'
How to achieve this in sql statement?
I am using C++ OCI LIB, to insert some report data from remote OCI client to oracle 11 server. This data is read by another process to create the report.The DB CHARSET is UTF-8. But the report tool expects the data to be ISO08859-1 encoded. So while inserting the data into the database i specify the following LANG and CHARSET for my table colulmn in client:
The TARGET DB CHARSET is UTF-8
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
size_t csid = 871; // UTF-8
OCIAtrSet((void *) bnd1p, (ub4) OCI_HTYPE_BIND,
*(void *)&csid*,
(ub4) 0,
(ub4)OCI_ATTR_CHARSET_ID, errhp);
This solution works for almost every case of ASCII and Extended ASCII Charest but we are facing issues if we have few specific characters to be inserted.f we are trying to insert single beta character [β] through client, the data goes empty to the column.
Beta Character details:
DEC OCT HEX BIN Symbol Description
223 337 DF 11011111 ß Latin small letter sharp s - ess-zed
DB Output after insert single β:
select rawtohex(NAME) from PERSONS where EID=333;
RAWTOHEX(NAME)
---------------------------
But if the string is *"ββ"* everything work fine:
DB Output for "ββ":
select rawtohex(NAME) from PERSONS where EID=333;
RAWTOHEX(NAME)
---------------------------
DFDF
runtime monitoring procedures....need a sample script
View 1 Replies View RelatedHow can I get forms and reports runtime and from where?
View 2 Replies View Related find the attached file.i want to get the remarks on runtime.i have written this formula in when_validate_item.
declare
a number;
begin
a := nvl(:get_mark,0);
if a >= (33/100)*:get_mark then
:remarks := 'PASS'
[code]...
I want to create a table in runtime.
SQL> select 'Test'|| '_' || extract(day from sysdate) ||
2 lpad(extract(month from sysdate), 2, 0)
3 from dual;
'TEST'||'_'||EXTRACT(DAYFROMSY
-----------------------------------------------------
Test_1502
SQL>
I want to create a table with this table name 'Test_1502'
I am using the create command as:
SQL> create table Test || '_' || (select extract(day from sysdate) ||
2 lpad(extract(month from sysdate), 2, 0) from dual) as
3 select * from Emp;
create table Test || '_' || (select extract(day from sysdate) ||
lpad(extract(month from sysdate), 2, 0) from dual) as
select * from Emp
ORA-00922: missing or invalid option
SQL>
can I control at length of field at tuntime?i want to set the number of the size (length) at runtime.
(no by use "HORIZONTAL ELASTICITY"= variable/expand because I need a specific length)
I want to get runtime parameter in .sql file . read values at runtime in .sql file
View 6 Replies View RelatedI have a query, when i run this this will give another sql statement. I want run this dynamically...
How to proceed?
I want add a text item at run time depending on the number of values retrieved i want to change the number of text items. Is it possible at run time to add a text item?
View 1 Replies View Relatedhow can change image through runtime i mean if have 5 pictures first image will showing and second image showing
View 1 Replies View RelatedIs there any other ways to measure a sql run time more accurate and more efficient? I meant using a spool and set timming on like this.
[oracle@milo ~]$ cat measure_sql.sql
spool result.txt;
set timing on;
select * from t3 where empno=7788;
set timing off;
spool off;
[oracle@milo ~]$ sqlplus scott/tiger @measure_sql.sql
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 27 01:36:32 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
16 rows selected.
Elapsed: 00:00:00.01
I Have got a requirement where we have to generate an xml conforming to a particular XSD.
What i will be having already in place is an XSD document.
We are thinking of making a procedure which will be recieving the set of inputs required for creating that xml.(Data content of that XML.)We would have to make the XML according to that XSD.
Everytime Parsing the XSD is not feasible.We are looking at a solution which would be easy to make the xml from xsd at runtime.These values (DATA) will be available at run time.
I have tables dynamically created with dynamic number of columns. There will be 7 columns that would always be fixed with their names and data types. They will always be last 7 columns.
way to write a select statement on those dynamic tables where only those known 7 columns are not selected and I need to select all the rest columns but leave the 7 columns names I know.
We have a Form for viewing some datas, now i need to create a new button and Hide that button while run time for All Users except one user, how to set this option.
Addl.info: This Button should be displayed only for a single user login not for all the users using that form. how to make this option?
I want to search the record in a report. I want option when the reports forms is running.
View 6 Replies View RelatedIs it possible to read the application URL while running the application
[URL]
Above is my URL i need to read i and t hold it on a variable at runtime
in below program,i have to pass column name to cursor at runtime..but this giving error.
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record hr.employees%rowtYPE;
v_stmt_str VARCHAR2(200);
colname varchar2(10):='Last_name';
BEGIN
[code]....
dbms_output.put_line(emp_record);
*
ERROR at line 13:
ORA-06550: line 13, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored