<h4><font color="Blue">
CREATE OR REPLACE
PROCEDURE SP_SSN_BY_PIN(
V_REQUESTEDPIN IN VARCHAR2,
V_SSN OUT CHAR)
AS
BEGIN
SELECT
[code]....
As you can see the type of the SSN column is NUMBER(13,0). But I leftpad it with 0 and assign it to my output paramter V_SSN, whose type is CHAR. But I get 111196100099 instead of 0111196100099. I've tried TO_CHAR(LPAD(SSN,13,'0')) but still doesn't work. However, if I return the left padded SSN inside a SYS_REFCURSOR I get what I want.
I am trying to accept three numbers and one string as input, and then display the values on the screen. I don't understand why I am getting these errors.
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 id_number NUMBER := &id_number_input; 3 trans_num NUMBER := &trans_num_input; 4 remar VARCHAR2(75) := &remark_input; 5 receipt NUMBER := &receipt_input;
[Code] .....
ERROR at line 4: ORA-06550: line 4, column 24: PLS-00201: identifier 'HELLO' must be declared ORA-06550: line 4, column 8: PL/SQL: Item ignored ORA-06550: line 10, column 61: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 10, column 2: PL/SQL: Statement ignored
I am running rep_1, and before executing second rep_2 the user should enter department number, after accepting department no, the second rep_2 should be executed. But after accepting parameter the second rep_2 is not displaying any record.
I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.
I've formatted a column (COLUMN column_name format a20) as it was too large, but now when I run the query I get all dashes ####### instead of the values. I've tried to make it bigger again, but I keep getting ###### as values for that column.
how to use dbms_xmlgen to take the results of a query and convert it into a XML formatted data. But if a column (clob) contains data in an XML format, is there a way to query that column and easily parse the data? I know that I could write loops that do string searches for each of the XML tags and nested tags.
we have a table which name is empl have two column (comapnyname,jobdate) i want output after passing parameter which name is month which value is like(feb 2011,or mar 2011 or apr 2011 or jun 2011 etc) output should come the nameof company with jobdate whose jobdate is equal to 3 or greate than three
name of company,jobdate whose job date in particular month is greater than three or equal to three the purpose of this we want to find only the name of company where we visit morethan three times in particular month table structure is
insert into empl values('ABC','1-feb-2011') insert into empl values('ABC','10-feb-2011') insert into empl values('ABC','21-feb-2011') insert into empl values('xyz','18-feb-2011') insert into empl values('xyz','1-feb-2012') insert into empl values('xyz','1-apr-2011') insert into empl values('ABC','28-feb-2012')
I need to have columns dynamically defined in my SQL based on the column values in the table.
Selct emp_no,dept_no,grade from emp; emp_no dept_no grade ---------------------------------- 1 10 A 2 20 B 3 20 A 4 10 C 5 10 A 6 20 C 7 10 B 8 20 C
I need the output as dept_no Count_A Count_B Count_C --------------------------------------------- 10 2 1 1 20 1 1 2
I can write hard coded sql to get this output but I need the count columns to be generated dynamically as there could be new entries in the grade column later. For example if there is a entry as D for the grade column my SQL should have a column count_D.
I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId) from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%' group by A.OrigRef, A.DisplayName, A.ExtCode order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
From ETL tool i will be passing two parameters to a procedure. I want to know whether this parameter can be used in a select statement of a correlated query.
Query to be in the procedure :
begin update table1 set table1.col1 = (select parameter2 from table2 where table2.x = table1.x and table2.x = parameter1) ; commit; end;
parameter2 will be having the columnname to be passed. And everytime different columnname will be passed to this procedure.
I'm trying to get size of CLOB column but not getting any output.
SQL> desc TABLE_STEP_INST234 Name Null? Type ----------------------------------------- -------- ---------------------------- NUM_PENDING_PREREQS NOT NULL NUMBER(10) OBJID NOT NULL VARCHAR2(31) OUTFLOW_BITS NUMBER(19) PARAMS CLOB PARENT2PROC_INST NOT NULL VARCHAR2(31) ROOT2PROC_INST NOT NULL VARCHAR2(31) START_TIME DATE STATUS NOT NULL NUMBER(2) [code]...
I am learning Regular expression I am solving one problem with regular expression. I am creating a table while inserting I have to restrict punctuation character.
I really have had no experience with SQL besides the basic commands, joins, and displaying data.I now need to somehow get data to be formatted a certain way through "pipelining" it. For example, I need to take the field name, combine it with the line of data, and put symbols in it.
i was asked to built a report file that will run from oracle form using web. show_document() built-in object to produce a data extract. the objective is to built a dynamic SQL and use that to a cursor.
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS TYPE array_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; rec_array array_t; BEGIN FETCH p_cursor BULK COLLECT INTO rec_array; FOR i IN rec_array.FIRST .. rec_array.LAST LOOP dbms_output.put_line(rec_array(i)); END LOOP; END pass_ref_cur; [code]....
I found out that the procedure can only accepts a single column. When I run this code it works fine because I am using a single column on the SQL statement.
DECLARE rec_array SYS_REFCURSOR; BEGIN open rec_array For 'select p.muni from chips.project p, chips.proj_type pt where p.type = pt.id'; pass_ref_cur(rec_array); CLOSE rec_array; END;
The objective is to use a multiple column. How can I revised the procedure pass_ref_cur so that it can accept multiple columns?
I'm trying to create a store procedure that will accept a username from a flat file but i don't know how to do read file into store procedure.
Below is a sample store procedure by itself i created to add user which created okay but when i execute I got the error displayed below.
create or replace procedure addUsers(userNam in varchar2) is begin EXECUTE IMMEDIATE 'CREATE USER'||userNam||'IDENTIFIED BY "pass1234" DEFAULT TABLESPACE USERS'||'QUOTA "1M" ON USERS'|| 'PASSWORD EXPIRE'; end addUsers; /
Creating thread so we can share experience on using the "Code review options" that comes along with "Toad formatter".
Personally I have used it only for formatting code but not to enforce SQL Standards. any inputs on this and if it's advisable to use this as corporate standard.
