PL/SQL :: Output Parameter Does Not Accept Formatted Column Value

Nov 21, 2012

Here's my table structure:

<h4><font color="Blue">CREATE TABLE "SSPFUSER05"."PERSONS"
(     SSN NUMBER(13,0),
PIN VARCHAR2(7 BYTE)
);</font>
</h4>

And here's my procedure below:

<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.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Accept Input And Output To Screen - Getting ORA-06550 Error

Jan 18, 2011

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

View 3 Replies View Related

Reports & Discoverer :: How To Accept Parameter Between Two Report

Jul 20, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: Dynamic Cursor / Loop Will Accept Table Name From Parameter

Oct 11, 2013

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.

DECLARE
--initialize variables here
v_date varchar2(10);
v_rebuild_index varchar2(250);
v_sql VARCHAR2(250);
p_table_name varchar2(250) := 'DS_ABSENCE';
p_source_table varchar2(30) := 'STG_ABSENCE';
p_source_owner varchar2(30) := 'STG_SAP';
v_for_sql varchar2(1000);
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Dash (#) In A Formatted Column

Jul 14, 2011

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.

View 4 Replies View Related

SQL & PL/SQL :: How EVENTTIMESTAMP Column Is Formatted

Oct 19, 2012

I am new to SQL for Oracle,So I am trying to run a simple query

SELECT * FROM ORCHP01DBO.WGN3EVENTAUDIT
WHERE EVENTTIMESTAMP = TO_DATE ('04/11/2012, 11:35:27 AM ') ;

Getting an error = ORA-01843: not a valid month

Here is how the EVENTTIMESTAMP column is formated 04/13/2000 10:10:48 AM...How can I correct it.

View 5 Replies View Related

Insert Formatted Template In A Table Column

Jan 17, 2011

I want to insert a formatted template into a table column. Is there any function for this requirement.

want is this format

Best Regards,
XYZ

View 1 Replies View Related

JDeveloper, Java & XML :: Query A Column Containing XML Formatted Data

Jun 26, 2011

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.

View 9 Replies View Related

How To Output Parameter To App C# Procedure PL/SQL

Sep 17, 2013

this PROCEDURE for Paging.

PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Output After Passing Parameter

Mar 23, 2012

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

create table empl(companyname varchar2(10),jobdate date)

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')

output:

after passing parameter month=feb 2011

companyname jobdate
abc 1-feb-2011
abc 10-feb-2011
abc 21-feb-2011

View 3 Replies View Related

SQL & PL/SQL :: Create Stored Procedure With Array As Output Parameter?

Jul 14, 2011

how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...

View 7 Replies View Related

SQL & PL/SQL :: How To Format Output Of Column

Jun 10, 2011

I have output from select something like :

A_PID B_TEXT id_f
-------------- ----------------------------- -----------
23 B_text sample 9888888

and my question is how can I format the output to have it thinner .

I can do it for text: COLUMN FORMAT B_TEXT A5, but i dont know to apply it for fields with number e.g. A_PID column.

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Column In Output?

Oct 15, 2013

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.

View 3 Replies View Related

SQL & PL/SQL :: Column Renaming From Query Output?

Jul 14, 2010

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...)?

i.e. I want "column4" to read "CountofCalId"

View 4 Replies View Related

SQL & PL/SQL :: Display Output For Null Column

Jul 24, 2012

I am having following table, and below are the out put and desired output.

CREATE TABLE tbl1
(
mon VARCHAR2(10)
, grp VARCHAR2(50)
, visits NUMBER
, redirect VARCHAR(50)
)
;

[Code]....

Query:

SELECT
mon
, grp
, SUM(visits)
FROM
tbl1
WHERE
redirect IS NOT NULL
GROUP BY mon, grp
;

Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13

Desired Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
May-12 Red 0
May-12 Orange 0

How can this be acheived?

View 1 Replies View Related

SQL & PL/SQL :: Formatting Output - Only Email Column Should Be Printed

May 4, 2010

i have column names as empno,ename,email...

i want output as

asb@asd.com,sad@sad.com,..... only email column should be printed

how can we do that?

View 3 Replies View Related

XML - Formatted Alert Log?

Jul 7, 2013

 My database version is 11.2.0.3  Is there any added advantage using XML-formatted alert log file over text format alert log?

View 4 Replies View Related

SQL & PL/SQL :: Column Name As Parameter

Feb 9, 2012

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.

View 16 Replies View Related

Server Administration :: Get Size Of CLOB Column But Not Getting Any Output

Sep 10, 2012

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]...

View 2 Replies View Related

SQL & PL/SQL :: Do Not Accept Punctuation Character While Inserting

Jul 11, 2013

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.

create table emp
(
ename varchar2(30) ,
constraint ck_ename check ( regexp_like (ename , '[^[:punct:]]')));

and also how I can remove double space.

View 8 Replies View Related

SQL & PL/SQL :: Get Data To Be Formatted A Certain Way Through Pipelining It

Jul 10, 2012

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.

View 4 Replies View Related

Client Tools :: TOAD - Query Output With Column Headers?

May 14, 2010

I can't figure out how to configure the query editor to copy the column headers with the query results on to the clipboard?

View 18 Replies View Related

SQL & PL/SQL :: Function To Accept 4 Digit Number Which Should Not Be Repeated?

Aug 15, 2012

i nee a function which accepts 4 digit number and in four digit number the number should not be repeated. i want all the number in the output.

ex:1234
2367
1262(is not valid)

View 20 Replies View Related

SQL & PL/SQL :: Procedure Parameter From Database Column

Feb 7, 2011

I knew procedure can be run like 'EXEC (****);' and can be written like

'Create or replace procedure(**** IN VARCHAR2)

BEGIN
END
/

But the requirements are 'Parameter can be come from referencing DB table column?')

If table has value like PARMTAG PARMVAL, I read LOGSTORETYPE data and if exist, fetch the PARMVAL value 7 to my parameter? LOGSTORETYPE 7

Is it possible?

The reason is batch process was written by ProC and DB upgrade time with exadata, we want to change batch to Procedure about our DB delete.

View 2 Replies View Related

SQL & PL/SQL :: Concatenate Parameter With A Column In Query

Sep 30, 2010

SELECT OM.ORG_NAME||' Unit - ':p_unit name
FROM org_mst om

In the above query I want to concatenate Unit number passed by a parameter.

View 4 Replies View Related

SQL & PL/SQL :: Revised Procedure Pass_ref_cur So That It Can Accept Multiple Columns?

Oct 25, 2011

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?

View 17 Replies View Related

SQL & PL/SQL :: Procedure To Accept Schema And Produce CSV File For Table

Mar 23, 2012

The Utility should have a procedure that will be able to accept a schema and table name and produce CSV file for that table.

This is what i have so far:

CREATE OR REPLACE Procedure print_table (schema_name varchar2, tab_name varchar2) IS
BEGIN--begin procedure
DECLARE
vpath varchar2(100) := 'C:UsersUserDocumentsDocsDBAProject';
[code].....

It works outside of the procedure but the nature of the question requires a stored procedure.

View 26 Replies View Related

SQL & PL/SQL :: Ref Cursor As In Parameter / How To Fetch Column Values

Sep 21, 2010

i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be

mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)

How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?

View 6 Replies View Related

SQL & PL/SQL :: Creating Store Procedure That Will Accept A Username From A Flat File?

Apr 8, 2012

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;
/

[code].....

View 21 Replies View Related

Client Tools :: Code Review Options In Toad Formatted

Sep 15, 2011

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved