BEGIN
text3:='&text3';
select sal
into number1
from emp
where ename=text3;
dbms_output.put_line(text1||' today on '||text2||' salary of Mr.'||text3||' is='||number1);
end test;
**************************************
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.TEST", line 10
ORA-06512: at line 1
I have table in Oracle with one column PRODUCT. Column PRODUCT have following values -
Account Management Active Directory Adobe Acrobat Reader NT Account Application Security
[code]....
I am designing application where I need to search for PRODUCT based upon user's input. Lets say user wants search on 'Laptop Account Broken'. I want to search for all products which contains any of words in user's input. So based upon user's input I want output like below.
Expected Output:
Account Management NT Account WebSite Account HP Laptop
Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?
I am trying to redirect to different pages based on user inputs. I have a form in which I have one text item and a submit button. For example consider text item to be empno; So once the user enters a empno and hits submit button; I need to redirect to different pages based on this job.
For Ex:
if user inputs empno whose job is 'ANALYST' then redirect to page 1
if user inputs empno whose job is 'MANAGER' then redirect to page 10
For this scenario. i wrote a pl/sql process for the submit button; but no luck -- its not at all redirecting instead its in the same page. Below is the process.
declare v_job varchar2(20); begin select job into v_job from emp where empno = :P9_EMPNO_R; if v_job = 'ANALYST' then OWA_UTIL.REDIRECT_URL('f?p=&APP_ID.:1:&SESSION.::&DEBUG.::', TRUE);
I am trying to ultimately as the title says separate a user input list into one column of entries. I am doing this through Cognos not a normal SQL editor which is what makes this a little harder to do. So far I have gotten that in general I can use the
SELECT 'First Entry' Asset FROM Dual Union SELECT 'Second Entry' Asset FROM Dual Union SELECT 'Third Entry' Asset FROM Dual
and this will give me 3 entries of data in one column. More can be added as long as the last statement doesn't have the union on it. So, the next step it would seem is to have a for loop combined with an if then or case statement that would find the number of entries and loop until we reach the number of entries and give me either SELECT 'First Entry' Asset FROM Dual Union or SELECT 'First Entry' Asset FROM Dual if we are on the last entry. I don't know the lingo to do this though. I have tried to get this to work with a simple test like cat, dog, horse, cow, pig, etc but it's frustrating that I can't get it to work. I can do all the individual steps I just can't seem to get it to work together. I have all the functions I need, I just need to the syntext to do a for loop along with an if then or case statement where the outcome is a valid select statement.
resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace function fun_test_timestamp(P_HOUR varchar2) return varchar2 is sql_stmt varchar2(1000); begin
[Code].....
Input:- select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace function fun_test_timestamp(P_HOUR varchar2) return timestamp is sql_stmt varchar2(1000);
[Code]...
Input:- select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8 00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.
There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.
I would like to know if 'user creation definition' is exported in user mode export if export is done with DBA role..If it is Not, does it mean we always need to precreate the user before we import the dump created using 'user mode export'?
We are trying to implement the following security to our database.
As of now, the access rights are same for all the windows users logging into the Oracle application with the same Oracle user Id.
But now, we want to improve our security by granting different levels of rights to the users based on their Network Id even though they use the same Oracle User Id to log into the application.
We are not looking for the users to be identified externally.
(CREATE USER "OPS$ORACLE-BASE.COMTIM_HALL" IDENTIFIED EXTERNALLY; GRANT CONNECT TO "OPS$ORACLE-BASE.COMTIM_HALL"; )
- we have user id parameter.can we update the parameter(:parameter.p_userid) before firing "WHEN NEW FORM INSTANCE TRIGGER"(when new form instance trigger contain code for tree node), for login another canvas as per user rights?
- i have created a login form in one canvas.
- also tree node Hierarchy form created on another canvas. now we want to login through login screen, after login only those forms should show in tree Hierarchy which users have rights. for this purpose we want to pass the parameter of userid before connecting to tree node form through the following query
SELECT COUNT (*) INTO v_count FROM usersinfo WHERE usersname = :USERBLOCK.usernames AND passwords = :USERBLOCK.passwords ;
[code]...
Every thing is working but when we click on button in login form at that time parameter.p_userid will return null. because above code is define on button click in login screen, i cannot understand where i need to define the above code .
CREATE OR REPLACE TRIGGER TRIGGER1 BEFORE INSERT ON table1 FOR EACH ROW
[code]......
Here , I Want To Insert The Data From My User To Test User . In This Situation When I Execute The Above Trigger It Shows The Error PL/SQL: ORA-00942: table or view does not exist
I have a strange problem when creating a view in user from another user
I have a user called "Cash_tst"
its syntax creation is
-- Create the user create user CASH_TST identified by "" default tablespace CASH temporary tablespace TEMP profile DEFAULT quota unlimited on cash; -- Grant/Revoke object privileges grant connect to CASH_TST; grant dba to CASH_TST; grant resource to CASH_TST;
-- Grant/Revoke system privileges grant create any view to CASH_TST; grant unlimited tablespace to CASH_TST;
I want to create a view
CREATE VIEW TAMER AS SELECT * FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO
"AROFL" is another user on the same database when try to create the view "tamer" i got message of "insufficent privilege" although i granted "create any view" to the user "cash_tst"
we are trying ti implement BMC monitoring tool for our oracle 11g2 RAC/solaris10 environment. for the purpose we create a user called bmcuser in OS level, this user connecting database to monitor, this user occupy shared memory as oracle. Explain why this user taking that much shared memory just to connect database using sqlplus.
oracle taking 55% bmcuser taking 44%,
SQL> show parameters memory
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 memory_max_target big integer 3232M memory_target big integer 3232M shared_memory_address integer 0 SQL> show parameters sga
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 3232M sga_target big integer 0 PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6449 bmcuser 47M 44M sleep 23 10 0:57:31 0.2% PatrolAgent/1 [code]........
User A contains tables, views, LOB's, types, procedures, triggers, sequences, indexes, synonyms. User B wants to have read-only privilege on the objects of User A.
I can provide select on privilege on tables and views. How about providing read-only privileges to other objects?
Conn xyz/passwordxyz grant create procedure to 'xyz';
I want to give permissions to my current user 'xyz' , I am able to give permissions to user using system user but Is there any way to grant permissions to user 'xyz' while I am using 'xyz' user.
I have two databases (say DATABASE 1 with user USER1 with table A and DATABASE2 with user USER1 with table A). I want to select the table A in USER1 DATABASE2 from DATABASE1 USER1.
I have enabled Auditing in my oracle Database but I am not able to see any database for the operations of sys user or any other user in my "SYS.AUD$" and "SYS.FGA_LOG$" tables.
Value for the parameter "AUDIT_TRAIL" is set to "db,extended".
I am working as "SYS" user and I have shutdown and again startup the database but neither there was any information in both the tables nor I can see any files at the destination specified by "AUDIT_FILE_DEST".
I'm running a C++ program to call sqlplus to execute some sql scirpts. The program will accept arguments from users at the command line, and execute the script which the name will also be passed in as an argument to the program eg:
./MY_PROG.sh ABC.sql 23 (MY_PROG will call ABC.sql script and pass 23 as an argument to ABC.sql)
it works fine when the argument is passed in correctly, and my ABC.sql scirpt is able to execute correctly, however when the argument is not passed in, the program just hangs there, waiting for an input from the users. This is because "&1" in my script is expecting some value from users.
Because this program will be scheduled to run, and no one will be there to monitor the job, if so happens arguments were missing, the program will wait there for the entire night. Is there any way that I could make the program or the script to exit when there is no input to be found? I can't use timeout as I do not know how long does the script take to finish it operations, I could be updating millions of record from a script.
I am often supplied with a list of numbers to query against & normally take the easy option of editing the file & placing the select on each line. Like so. what to do to loop this. The input file would just be the numbers in a flat file.
Select status from thetable where MPN=�01234567890�; Select status from thetable where MPN=�12345678901�; Select status from thetable where MPN=�23456789012�; Select status from thetable where MPN=�34567890123�;
i am trying to create a procedure that will take an input and based on that input it will delete or update a row, if the input is wrong we will dbs_ouput a line telling the user to enter the input again. can i have a input parameter in a procedure and insert multiple rows with a single insert on a procedure?
We are on Oracle 11.2.0.2 on Solaris 10. I have a procedure call that errors out with this error:
SP2-0027: Input is too long (> 2499 characters) - line ignored.
It is one single procedure but it takes too many long inputs in it. This giant procedure call -with some names changed - is as below -
SQL> exec func_j23k_TYPE_ADD( 'coR-EXECUTION-SUCCEEDED-XLS-PDF-HTM-TXT-CSV-XML-FLF','co Reporting','NOT',func_j23k_TYPE_ACTION_ARRAY(func_j23k_TYPE_ACTION_TYPE(LANG_TEXT_ARRAY(LANG_TEXT_TYPE('en_US', 'Find in Report List'),LANG_TEXT_TYPE('en_CA', 'Find in Report List'),LANG_TEXT_TYPE('fr_CA', 'Chercher dans la liste des rapports'),LANG_TEXT_TYPE('es_US', 'Buscar en lista de informes')),'/Reporting/el/',1,func_TYPE_ACTION_STATUS_ARRAY(),func_TYPE_ACTION_SCREEN_ARRAY(),'NWR',NULL, NULL),func_j23k_TYPE_ACTION_TYPE(LANG_TEXT_ARRAY(LANG_TEXT_TYPE('en_US', 'Open using Excel'),LANG_TEXT_TYPE('en_CA', 'Open using Excel'),LANG_TEXT_TYPE('fr_CA', 'Ouvrir au format Excel'),LANG_TEXT_TYPE('es_US', 'Abrir usando [code]....
SP2-0027: Input is too long (> 2499 characters) - line ignored.
I have an issue in oracle form 6i. I want to create an input box that accepts only number. As the user press any key other than allowed for numbers it should not accept.
How to use date as an input parameter,im supposed to use varchar2 as the data type
CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 ) IS sender VARCHAR2(30) := 'xyz@gmail.com'; mailhost VARCHAR2(100) := 'host address'; TAB VARCHAR(2) := CHR(9); mail_conn utl_smtp.connection;
[code].....
cursor c1 is
--select activity_date,procedure_name,status_message,error_desc from staging_activity_log where rownum between 1 and 10 ; select activity_date,procedure_name,status_message,error_desc from staging_activity_log where error_desc is not null and trunc(activity_date) >= to_date(p_date,'DD-MON-YYYY') ; BEGIN
[code].....
when i execute i get
BEGIN mail1 ('xxx@gmail.com,yyy@gmail.com,'28-jan-2008'); END; Error at line 1 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "MAIL1", line 27 ORA-06512: at line 1
